/// <summary>
/// 根据模板名称获取工作薄对象
/// </summary>
/// <param name="tempName">模板名称</param>
/// <returns></returns>
private static HSSFWorkbook GetWorkBook(string tempName)
{
//创建工作簿对象
HSSFWorkbook hssfworkbook;
//打开模板文件到文件流中
using (
FileStream file = new FileStream(HttpContext.Current.Request.PhysicalApplicationPath + @"Template/ExplorationAndProduction/" + tempName,
FileMode.Open, FileAccess.Read))
{
//将文件流中模板加载到工作簿对象中
hssfworkbook = new HSSFWorkbook(file);
}
return hssfworkbook;
}
string tempName = "**文件名**.xls";
HSSFWorkbook hssfworkbook = GetWorkBook(tempName);
ISheet sheet1 =hssfworkbook.GetSheetAt(0);
var list = **数据源**;
//当行不存在
for (int i = 0; i < list.Count; i++)
{
var model = list[i];
var row = sheet1.CreateRow(3 + i);
row.HeightInPoints = 16;
for (int j = 0; j < 6; j++)
{
ICell cell = row.CreateCell(j);
switch (j)
{
case 0:
cell.SetCellValue(model.Unit);
break;
case 1:
cell.SetCellValue(model.Date);
break;
case 2:
cell.SetCellValue(model.Type);
break;
case 3:
cell.SetCellValue(DataConvertHelper.ParseDoubleValue(model.ZZJNum));
break;
case 4:
cell.SetCellValue(DataConvertHelper.ParseDoubleValue(model.WZJNum));
break;
case 5:
cell.SetCellValue(DataConvertHelper.ParseDoubleValue(model.RJC));
break;
}
row.Cells.Add(cell);
}
}
//当行存在
for (int i = 0; i < list.Count; i++)
{
switch (dt.Rows[i]["xmmc"].ToString())
{
case "天然气产量":
sheet1.GetRow(3).GetCell(2).SetCellValue(int.Parse(dt.Rows[i]["rcl"].ToString()));
break;
case "青海中浩":
sheet1.GetRow(6).GetCell(2).SetCellValue(int.Parse(dt.Rows[i]["rcl"].ToString()));
break;
}
}
//最后导出文件
ResponseWriteExcel(sheet1, hssfworkbook, **文件名**);
/// <summary>
/// 向客户端响应Excel下载
/// </summary>
/// <param name="sheet1">工作薄Sheet</param>
/// <param name="hssfworkbook">工作薄</param>
/// <param name="exportName">导出名称</param>
private void ResponseWriteExcel(ISheet sheet1, HSSFWorkbook hssfworkbook, string exportName)
{
//强制Excel重新计算表中所有的公式
sheet1.ForceFormulaRecalculation = true;
//设置响应的类型为Excel
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = System.Text.Encoding.Default;
//设置下载的Excel文件名
Response.AddHeader("Content-Disposition",
string.Format("attachment;filename={0}",
HttpUtility.UrlEncode((exportName + ".xls"))));
//Clear方法删除所有缓存中的HTML输出。但此方法只删除Response显示输入信息,不删除Response头信息。以免影响导出数据的完整性。
Response.Clear();
using (MemoryStream ms = new MemoryStream())
{
//将工作簿的内容放到内存流中
hssfworkbook.Write(ms);
//将内存流转换成字节数组发送到客户端
//Response.BinaryWrite(ms.GetBuffer());
// 打开Excel 显示部分内容有问题
//使用了 ms.GetBuffer() 返回文件内容,导致生成的 Excel 文件结尾处有大量的 00(空字节),改为 ms.ToArray() 即可得到正常的文件了。
Response.BinaryWrite(ms.ToArray());
Response.End();
}
}