C# 将dataset数据导出到excel中
//添加引用 NPOI.dll
//添加 using NPOI.HSSF.UserModel;
/// <summary>
/// 导出数据到Excel
/// </summary>
/// <param name="returnMsg"></param>
/// <returns></returns>
public bool ExportOrderToExcel(ref string returnMsg)
{
try
{
DataSet dt = "SQL语句返回dataset数据集";
int rowIndexS = 0; //表格总行
if(dt!=null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet1 = (HSSFSheet)workbook.CreateSheet("工作簿名称");
HSSFRow headerRow1 = (HSSFRow)sheet1.CreateRow(0);
HeadControl(headerRow1); //行名称
foreach (DataRow dr in dt.Tables[0].Rows)
{
rowIndexS++;
HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(rowIndexS);
OrderFill(dataRow, dr); //填充数据
}
//保存
string path = "/FileRoot/temp/";//文件保存路径
string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; //文件名已时间命名
path += fileName;
System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(path), System.IO.FileMode.Create);
workbook.Write(file);
//关闭文件，释放对象
file.Close();
workbook = null;
returnMsg =path;//返回文件路径
return true;
}
else
{
returnMsg = "没有查到要导出的数据!";
return false;
}
}
catch (Exception)
{
returnMsg = "导出数据失败!";
return false;
}
}
/// <summary>
/// 填充报表数据
/// </summary>
/// <param name="dataRow"></param>
/// <param name="dr"></param>
public void OrderFill(HSSFRow dataRow, DataRow dr)
{
dataRow.CreateCell(0).SetCellValue(dr["字段名"].ToString());
dataRow.CreateCell(1).SetCellValue(dr["字段名"].ToString());
dataRow.CreateCell(2).SetCellValue(dr["字段名"].ToString());
dataRow.CreateCell(3).SetCellValue(dr["字段名"].ToString());
dataRow.CreateCell(4).SetCellValue(dr["字段名"].ToString());
dataRow.CreateCell(5).SetCellValue(dr["字段名"].ToString());
}
/// <summary>
/// 导出已支付订单的表头
/// </summary>
/// <param name="headerRow"></param>
public void HeadControl(HSSFRow headerRow)
{
headerRow.CreateCell(0).SetCellValue("列名");
headerRow.CreateCell(1).SetCellValue("列名");
headerRow.CreateCell(2).SetCellValue("列名");
headerRow.CreateCell(3).SetCellValue("列名");
headerRow.CreateCell(4).SetCellValue("列名");
headerRow.CreateCell(5).SetCellValue("列名");
}
附件：NOPI.DLL下载链接 https://files.cnblogs.com/a-mumu/NPOI.zip