C# 根据模板 导出 Excel 图表 (NPOI组件)
C# 根据模板 导出 Excel 图表 (NPOI组件)
npoi 版本2.2.1
数据存在List中的。
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using System.IO;
//导出
protected void onclickbutton(object sender, EventArgs e){
getlist();//取出数据,就不详细说明
}
public bool ExportGJExcelFromList1(List<VW_ProductorOrderItem> lists, String FileName)
{
NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook = new HSSFWorkbook();
string strMouldPath = Server.MapPath("~") + @"\Mould\tongji\xiaoliang.xlt";
try
{
FileStream fs = File.OpenRead(strMouldPath);
hssfworkbook = new HSSFWorkbook(fs);
fs.Close();
}
catch (Exception e)
{
throw e;
}
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
int RowsNum = 1;
for (int i = 0; i < lists.Count; i++)
{
sheet.ShiftRows(RowsNum + i, sheet.LastRowNum, 1, true, false);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(RowsNum + i);
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(lists[i].TargetOfYear);
row.CreateCell(2).SetCellValue(lists[i].OrderDate);
Double a = Convert.ToDouble(lists[i].Price);//这是我模板选择的值,如果直接取出值,则图表不会显示,因为输出的是字符不是数字,识别不了。
row.CreateCell(3).SetCellValue(a);
row.CreateCell(4).SetCellValue("RMB");
row.CreateCell(5).SetCellValue(lists[i].Rate);
}
sheet.ShiftRows(RowsNum + lists.Count, sheet.LastRowNum, 1, true, false);
NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(RowsNum + lists.Count);
double b=0;
for (int i = 0; i < lists.Count; i++)
{
double a = Convert.ToDouble ( lists[i].Price);
b = a + b;
}
row1.CreateCell(0).SetCellValue("合计");
row1.CreateCell(1).SetCellValue(b);
sheet.ForceFormulaRecalculation = true;
System.IO.MemoryStream ms = new System.IO.MemoryStream();
hssfworkbook.Write(ms);
FileName = HttpUtility.UrlEncode(FileName, Encoding.UTF8);
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", FileName));
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
hssfworkbook = null;
ms.Close();
ms.Dispose();
return true;
}
//模板地址,.xls要另存为.xlt
//模板
//最后导出的样式