1.直接上代码，下载NOPI dll 地址：/detail/qq_25580555/9576072 2.代码函数封装 #region NOPI导出Excel多个sheet
/// <summary>
/// 导出EXCEL,可以导出多个sheet
/// </summary>
/// <param name="dtSources">原始数据数组类型</param>
/// <param name="strFileName">路径</param>
public void ExportEasy(DataSet dtSources, string strFileName)
{
try
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
for (int k = 0; k < dtSources.Tables.Count; k++)
{
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(dtSources.Tables[k].TableName.ToString()); //填充表头
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(0); //特殊处理，需要添加一行数据 if (k == 1)
{
dataRow.CreateCell(0).SetCellValue("户号绑定时间：" + txt_Start.Value+"--"+txt_End.Value); dataRow = sheet.CreateRow(1); foreach (DataColumn column in dtSources.Tables[k].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
} //填充内容
for (int i = 0; i < dtSources.Tables[k].Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 2);
for (int j = 0; j < dtSources.Tables[k].Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSources.Tables[k].Rows[i][j].ToString());
}
}
}
else
{
foreach (DataColumn column in dtSources.Tables[k].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
} //填充内容
for (int i = 0; i < dtSources.Tables[k].Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < dtSources.Tables[k].Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSources.Tables[k].Rows[i][j].ToString());
}
}
}
} //保存
using (MemoryStream ms = new MemoryStream())
{
//using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{ //System.IO.MemoryStream ms = new System.IO.MemoryStream();
workbook.Write(ms);
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=绩效统计.xls"));
Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(strFileName + ".xls"));//注意
Response.BinaryWrite(ms.ToArray());
workbook = null;
ms.Close();
ms.Dispose(); //workbook.Write(fs);
}
}
}
catch (Exception ex)
{
Function.ShowMessage(ex.Message.ToString());
} }
/// <summary>
/// 导出单个EXCEL
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strFileName"></param>
public static void ExportEasy(DataTable dtSource, string strFileName)
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); //填充表头
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
} //填充内容
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < dtSource.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
}
} //保存
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
//workbook.Dispose(); } #endregion
