之前使用myxml组件,但是打开excel后,总是会提示,内容有问题。。。。。。是在没有能力解决,后来使用了Npoi,导出excel。
具体代码如下:
创建ExcelHelper.cs
代码:
/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
{
//NPOI.SS.UserModel.IWorkbook workbook = new NPOI.SS.UserModel.IWorkbook();
IWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
string[] sheetNames = sheetName.Split(',');
for (int i = 0; i < sheetNames.Length; i++)
{
ISheet sheet = workbook.CreateSheet(sheetNames[i]);
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceDs.Tables[i].Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return ms;
}
/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="fileName">指定Excel工作表名称</param>
/// <returns>Excel工作表</returns>
public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName)
{
MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream;
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
ms.Close();
ms = null;
}
然后在后台直接调用ExportDataSetToExcel(ds,filename,"sheet1");