導出DataGridView、DataTable到Excel，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 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
c#怎把datagridview導出到excel
這裡有
www.2cto.com/kf/201209/152319.html
不引用Excel Com組件,c# Winform datatable或dataset或DataGridView導出Excel的方法
public void ExportDataGridViewToExcel(DataGridView dataGridview1)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "導出Excel文件到";
DateTime now = DateTime.Now;
saveFileDialog.FileName = now.Year.ToString().PadLeft(2) + now.Month.ToString().PadLeft(2, '0') + now.Day.ToString().PadLeft(2, '0') + "-" + now.Hour.ToString().PadLeft(2, '0') + now.Minute.ToString().PadLeft(2, '0') + now.Second.ToString().PadLeft(2, '0');
saveFileDialog.ShowDialog();
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
string str = "";
try
{
//寫標題
for (int i = 0; i < dataGridview1.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dataGridview1.Columns[i].HeaderText;
}
sw.WriteLine(str);
//寫內容
......余下全文>>
