导出DataGridView、DataTable到Excel

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

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

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

另存为xml表格：

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

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

往下会看到列标题：

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

主要代码：

 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             try59             {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         }