導出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 + "http://區域查詢導出.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         }