ASP.NET 导出EXCEL
采用.Net Excel组件导出 EXCEL文件,
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="source">需要导出的数据</param>
/// <param name="exportFullPath">导出Excel文件的全路径</param>
/// <param name="hasHeadline">如果 true则生成表头 </param>
private void ExportExcel(DataTable source, string exportFullPath, bool hasHeadline)
{
#region 采用.Net Excel组件导出
Application objExcel = new Application();
if (objExcel == null)
{
throw new Exception("ERROR: You must install Microsoft Excel Application!");
}
//创建一个Excel文件(未保存,无文件名)
Workbooks objWorkbooks = objExcel.Workbooks;
_Workbook objWorkbook = objWorkbooks.Add(XlWBATemplate.xlWBATWorksheet); //默认创建sheet1
//取得Sheet1
Sheets objSheets = objWorkbook.Worksheets;
_Worksheet objWorksheet = (_Worksheet)objSheets.get_Item(1);
//写入标题
int intDataBeginRow = 1;
if (hasHeadline)
{
for (int i = 0; i < source.Columns.Count; i++)
{
objWorksheet.Cells[1, i + 1] = source.Columns[i].ColumnName.Trim();
}
//数据起始行加1
intDataBeginRow++;
}
//写入数据,Excel的索引是从1开始的
for (int j = 0; j < source.Rows.Count; j++)
{
for (int k = 0; k < source.Columns.Count; k++)
{
objWorksheet.Cells[j + intDataBeginRow, k + 1] = source.Rows[j][k].ToString();
}
}
objWorksheet.Hyperlinks.Delete(); //去掉超链
//保存文件(如果使用objWorkbook.SaveAs将不兼容excel2000,excelxp)
objWorkbook._SaveAs(exportFullPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//关闭文件,释放资源
objWorkbook.Close(false, exportFullPath, false);
objExcel = null;
#endregion
}
直接调用ExportExcel 即可
/// 导出Excel
/// </summary>
/// <param name="source">需要导出的数据</param>
/// <param name="exportFullPath">导出Excel文件的全路径</param>
/// <param name="hasHeadline">如果 true则生成表头 </param>
private void ExportExcel(DataTable source, string exportFullPath, bool hasHeadline)
{
#region 采用.Net Excel组件导出
Application objExcel = new Application();
if (objExcel == null)
{
throw new Exception("ERROR: You must install Microsoft Excel Application!");
}
//创建一个Excel文件(未保存,无文件名)
Workbooks objWorkbooks = objExcel.Workbooks;
_Workbook objWorkbook = objWorkbooks.Add(XlWBATemplate.xlWBATWorksheet); //默认创建sheet1
//取得Sheet1
Sheets objSheets = objWorkbook.Worksheets;
_Worksheet objWorksheet = (_Worksheet)objSheets.get_Item(1);
//写入标题
int intDataBeginRow = 1;
if (hasHeadline)
{
for (int i = 0; i < source.Columns.Count; i++)
{
objWorksheet.Cells[1, i + 1] = source.Columns[i].ColumnName.Trim();
}
//数据起始行加1
intDataBeginRow++;
}
//写入数据,Excel的索引是从1开始的
for (int j = 0; j < source.Rows.Count; j++)
{
for (int k = 0; k < source.Columns.Count; k++)
{
objWorksheet.Cells[j + intDataBeginRow, k + 1] = source.Rows[j][k].ToString();
}
}
objWorksheet.Hyperlinks.Delete(); //去掉超链
//保存文件(如果使用objWorkbook.SaveAs将不兼容excel2000,excelxp)
objWorkbook._SaveAs(exportFullPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//关闭文件,释放资源
objWorkbook.Close(false, exportFullPath, false);
objExcel = null;
#endregion
}