导出DataGridView、DataTable到Excel_.NET_编程开发_程序员俱乐部

 

最近遇到导出DataGridView到Excel速度慢问题，数据量不大，但其中有几列字段很大（汉子2000左右），查了网上许多方法要么还是慢，

要么不能正常运行;修改一下，调试通过，导出比以前快很多，而且不会再卡死。整理如下：

 

用Excel将要生成的表格设计好：

 

另存为XML表格：

 

 

用文本编辑器打开保存的xml：

 

找到Table节点，将节点的ss:ExpandedRowCount=”2” 删除掉：

DBA96F55B5FECC1.jpg" alt="" />

 

往下会看到列标题：

 

下面就是数据，将数据Row删除并替换成 {0}：

 

主要代码：

class="code_img_closed" src="/Upload/Images/2014102123/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('cc3d3957-1de0-4007-9c32-2aac6854fcee',event)" src="/Upload/Images/2014102123/2B1B950FA3DF188F.gif" alt="" />

 1 protected override void btn_exprot_Click(object sender, EventArgs e)
 2         {
 3             BuildWhere();
 4 
 5             //dgv_Details.DataSource = controller.GetAreaSiteInfo(strWhere.ToString()).Tables[0];
 6 
 7             DataTable dt = controller.GetAreaSiteInfo(strWhere.ToString()).Tables[0];//取得数据
 8             string Row = @"<Row>
 9                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{0}</Data></Cell>
10                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{1}</Data></Cell>
11                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{2}</Data></Cell>
12                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{3}</Data></Cell>
13                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{4}</Data></Cell>
14                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{5}</Data></Cell>
15                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{6}</Data></Cell>
16                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{7}</Data></Cell>
17                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{8}</Data></Cell>
18                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{9}</Data></Cell>
19                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{10}</Data></Cell>
20                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{11}</Data></Cell>
21                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{12}</Data></Cell>
22                             <Cell ss:StyleID=""s21""><Data ss:Type=""String"">{13}</Data></Cell>
23                             </Row>";
24 
25             List<string> Rows = new List<string>();
26             foreach (DataRow dr in dt.Rows)
27             {
28                 Rows.Add(string.Format(Row, dr[0],dr[1],dr[2],dr[3],dr[4],dr[5],dr[6],dr[7],dr[8],dr[9],dr[10],dr[11],dr[12],dr[13]));
29             }
30 
31             StreamReader reader = new StreamReader(Application.StartupPath + "\\区域查询导出.xml");
32 
33             SaveFileDialog diag = new SaveFileDialog();
34             diag.Filter = "Excel文件(*.xls)|*.xls";
35             diag.RestoreDirectory = true;
36             string path = "";
37 
38             if (diag.ShowDialog() == DialogResult.OK)
39             {
40                 path = diag.FileName.ToString();
41                 StreamWriter writer = new StreamWriter(path);
42                 writer.Write(reader.ReadToEnd(), String.Join("\r\n", Rows.ToArray()));
43                 writer.Flush();
44                 writer.Close();
45             }
46             reader.Close();
47             if (ConvertExcel(path))
48                 FUIHelper.ShowDialog(this, "导出成功！！", "提示", MessageIcon.Information);
49             //dgv_Details.ExportToExcel("区域表查询");
50         }
51 
52         private bool ConvertExcel(string savePath)
53         {
54             //将xml文件转换为标准的Excel格式 
55             Object Nothing = System.Reflection.Missing.Value;//由于yongCOM组件很多值需要用Missing.Value代替   
56             Microsoft.Office.Interop.Excel.Application ExclApp = new Microsoft.Office.Interop.Excel.Application();// 初始化
57             Microsoft.Office.Interop.Excel.Workbook ExclDoc = ExclApp.Workbooks.Open(savePath, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);//打开Excl工作薄   
58             try
59             {
60                 Object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal;//获取Excl 2007文件格式  xlWorkbookNormal 
61                 ExclApp.DisplayAlerts = false;
62                 ExclDoc.SaveAs(savePath, format, Nothing, Nothing, Nothing, Nothing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);//保存为Excl 2007格式   
63             }
64             catch (Exception ex)
65             {
66                 return false;
67             }
68             ExclDoc.Close(Nothing, Nothing, Nothing);
69             ExclApp.Quit();
70             return true;
71         }

View Code