在网上搜得,自己修改了一下拿来用了,记录一下,方便其他朋友用
/*gridView1 是form中的控件,数据源就是myTable
*/
public void ExportDataToExcel( DataTable myTable)
{
try
{
Excel.Application xlApp = new Excel.ApplicationClass();
int rowIndex;
int colIndex;
rowIndex = 2;
colIndex = 0;
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//之前用的DataGrid控件,如果有设定样式可以放开这里
/* if (gridView1.PrintStyles .Count > 0)
{
Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, grid.TableStyles[0].GridColumnStyles.Count]);
range.MergeCells = true;
// xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
foreach (DataGridColumnStyle colu in grid.TableStyles[0].GridColumnStyles)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = colu.HeaderText;
}
int k = 0;
//得到的表所有行,赋值给单元格
for (int row = 0; row < myTable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < grid.TableStyles[0].GridColumnStyles.Count; col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = myTable.Rows[row][col].ToString();
k++;
if (k >= 500)
{
Application.DoEvents(); //此举为了提高性能
k = 0; //归零
}
}
}
}
else
{*/
Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, myTable.Columns.Count]);
range.MergeCells = true;
// xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
//将表中的栏位名称填到Excel的第一行
foreach (GridColumn Col in gridView1.Columns)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = Col.FieldName;
}
//得到的表所有行,赋值给单元格
for (int row = 0; row < myTable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < myTable.Columns.Count; col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = myTable.Rows[row][col].ToString();
}
}
// }
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
xlApp.Visible = true;
}
catch (Exception e)
{
throw e;
}
}
}
效果大体如下:
导出:2千笔数据大约15秒导完:
---------------------------------
广告:俺的淘宝小店,赚点零花钱,请大家支持:http://lovehealthylife.taobao.com