• DataGridView如何快速导出Excel


    从DataGridView或DataTable导出Excel文件,为了按照数据类型设置单元格格式,导出Excel时速度都比较慢,一直找不到好的办法。

    最后从外文网站上找到解决办法,使用ws.get_Range(excelRange, Type.Missing).Value2方法批量设置单元格的值。详细代码如下:

        public void DataToExcelFast(DataGridView dgv, string fName)  
        {  
            try  
            {  
                Excel.Application excel = new Excel.Application();  
                Excel.Workbook wb = excel.Workbooks.Add(true);  
                Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet;  
                ws.Name = fName;  
                string sFile = string.Empty;  
                SaveFileDialog dialog = new SaveFileDialog();  
                dialog.Title = "Save export file";  
                dialog.Filter = "EXECL File(*.xlsx) |.xlsx";  
                dialog.FileName = fName;  
                dialog.FilterIndex = 1;  
                if (dialog.ShowDialog() == DialogResult.OK)  
                {  
                    string FileName = dialog.FileName;  
                    if (File.Exists(FileName))  
                        File.Delete(FileName);  
                    sFile = FileName;  
                    excel.Visible = false;  
                    excel.DisplayAlerts = false;  
                    excel.Calculation = Excel.XlCalculation.xlCalculationManual;  
                    ArrayList cols = new ArrayList();  
                    #region 列标题,并根据列的数据类型设置Excel列的格式  
                    int visibleColumnCount = 0;  
                    foreach (DataGridViewColumn col in dgv.Columns)  
                    {  
                        if (col.Visible)  
                        {  
                            visibleColumnCount++;  
                            ws.Cells[1, visibleColumnCount].Value2 = col.HeaderText.ToString();  
                            ws.Cells[1, visibleColumnCount].Font.Bold = true;  
                            cols.Add(col.Name);  
                            if (col.ValueType != null)  
                            {  
                                if (col.ValueType.Name.Equals("DateTime"))  
                                {  
                                    ws.Columns[visibleColumnCount].NumberFormatLocal = @"yyyy-mm-dd HH:mm";  
                                    ws.Columns[visibleColumnCount].Font.Color   
                                        = System.Drawing.ColorTranslator.ToOle(Color.Blue);  
                                }  
                                else if (col.ValueType.Name.Equals("Decimal")   
                                    || col.ValueType.Name.Equals("Double"))  
                                {  
                                    ws.Columns[visibleColumnCount].NumberFormat = "0.00";  
                                }  
                                else if (col.ValueType.Name.Equals("Int32"))  
                                {  
                                    ws.Columns[visibleColumnCount].NumberFormat = "0";  
                                }  
                                else  
                                {  
                                    ws.Columns[visibleColumnCount].NumberFormatLocal = @"@";  
                                }  
                            }  
                            else  
                                ws.Columns[visibleColumnCount].NumberFormatLocal = @"@";  
                        }  
                    }  
                    #endregion  
                    #region 写入行  
                    // Copy each DataTable  
                    // Copy the DataTable to an object array  
                    object[,] rawData = new object[dgv.Rows.Count, cols.Count];  
                    // Copy the column names to the first row of the object array  
                    for (int col = 0; col < cols.Count; col++)  
                    {  
                        rawData[0, col] = cols[col];  
                    }  
                    // Copy the values to the object array  
                    for (int col = 0; col < cols.Count; col++)  
                    {  
                        for (int row = 0; row < dgv.Rows.Count; row++)  
                        {  
                            rawData[row, col] = dgv.Rows[row].Cells[cols[col].ToString()].Value;  
                        }  
                    }  
                    // Calculate the final column letter  
                    string finalColLetter = string.Empty;  
                    string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";  
                    int colCharsetLen = colCharset.Length;  
                    if (cols.Count > colCharsetLen)  
                    {  
                        finalColLetter = colCharset.Substring(  
                            (cols.Count - 1) / colCharsetLen - 1, 1);  
                    }  
                    finalColLetter += colCharset.Substring(  
                            (cols.Count - 1) % colCharsetLen, 1);  
                    // Fast data export to Excel  
                    string excelRange = string.Format("A2:{0}{1}",  
                        finalColLetter, dgv.Rows.Count + 1);  
                    ws.get_Range(excelRange, Type.Missing).Value2 = rawData;  
                    #endregion  
                    excel.Calculation = Excel.XlCalculation.xlCalculationAutomatic;  
                    // 51表示2007-2010格式的xlsx  
                    ws.SaveAs(FileName, 51, Type.Missing, Type.Missing,   
                        Type.Missing, Type.Missing,   
                        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,   
                        Type.Missing, Type.Missing, Type.Missing);  
                    wb.Close(true, Type.Missing, Type.Missing);  
                    excel.Quit();  
                    // 安全回收进程  
                    System.GC.GetGeneration(excel);  
                    if (MessageBox.Show("已经导出Excel,您是否要打开?", "提示",  
                         MessageBoxButtons.YesNo) == DialogResult.Yes)  
                        System.Diagnostics.Process.Start(FileName);  
                }  
            }  
            catch (Exception ex)  
            {  
                MessageBox.Show("导出Excel时遇到错误
    " + ex.Message,  
                "Error",MessageBoxButtons.OK,MessageBoxIcon.Error);  
            }  
        } 
  • 相关阅读:
    Qt-网易云音乐界面实现-4 实现推荐列表和我的音乐列表,重要在QListWidget美化
    Qt-网易云音乐界面实现-3 音乐名片模块的实现
    Qt-网易云音乐界面实现-2 红红的程序运行图标,和相似下方音乐条
    Qt-网易云音乐界面实现-1 窗口隐藏拖拽移动,自定义标题栏
    Qt 利用XML文档,写一个程序集合 四
    promise的简单理解
    toast弹框组件的封装-插件方式
    vuex自动获取当前的时间
    用vue对tabbar的封装
    子组件与父组件的各种传递关系
  • 原文地址:https://www.cnblogs.com/liuzhengdao/p/5689725.html
Copyright © 2020-2023  润新知