导出数据到Excel
但要服务器安装office
引用COM:Interop.Microsoft.Office.Core.dll和Interop.Microsoft.Office.Interop.Excel.dll
/// <summary>
/// 将数据集中的数据导出到EXCEL文件
/// </summary>
/// <param name="dataSet">输入数据集</param>
/// <param name="isShowExcle">是否显示该EXCEL文件</param>
/// <returns></returns>
public bool DataSetToExcel(System.Data.DataTable dataTable, bool isShowExcle)
{
int rowNumber = dataTable.Rows.Count;//不包括字段名
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;
if (rowNumber == 0)
{
return false;
}
//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = isShowExcle;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
object[,] objData = new object[rowNumber, columnNumber];
for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
//Application.DoEvents();
}
// 写入Excel
range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = objData;
//worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";
return true;
}