NPOI复制模板导出Excel

2018-06-22 07:05:53来源：未知阅读 ()

本人菜鸟实习生一枚，公司给我安排了一个excel导出功能。要求如下：1、导出excel文件有样式要求；2、导出excel包含一个或多个工作表；3、功能做活（我的理解就是excel样式以后可能会变方便维护修改）；如何图所示是公司提供的excel模板

导出excel的功能之前在学校做过，我的第一个想到的是使用Microsoft.Office.Interop.Excel.dll读取Excel文件，但是公司的电脑上没有安装office没有相应的组件，我只能换种方法查了资料最后决定使用NPOI。用什么的问题解决了后面该想想怎么做了，当时想自己用代码写样式不过盯着模板看了一会后想想自己写不现实而且不便于后期维护（其实就是会写）。于是换种思路通过复制模板生产中间excel进行导出即使以后样式要修改也可以通过修改模板来实现尽量不去动代码。下面是代码

 1  /// <summary>
 2         /// 复制sheet
 3         /// </summary>
 4         /// <param name="bjDt">sheet名集合</param>
 5         /// <param name="modelfilename">模板附件名</param>
 6         /// <param name="tpath">生成文件路径</param>
 7         /// <returns></returns>
 8         public HSSFWorkbook SheetCopy(DataTable bjDt,string modelfilename, out string tpath)
 9         {
10             string templetfilepath = @"files\" + modelfilename + ".xls";//模版Excel
11 
12             tpath = @"files\download\" + modelfilename + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";//中介Excel，以它为中介来导出，避免直接使用模块Excel而改变模块的格式
13             FileInfo ff = new FileInfo(tpath);
14             if (ff.Exists)
15             {
16                 ff.Delete();
17             }
18             FileStream fs = File.Create(tpath);//创建中间excel
19             HSSFWorkbook x1 = new HSSFWorkbook();
20             x1.Write(fs);
21             fs.Close();
22             FileStream fileRead = new FileStream(templetfilepath, FileMode.Open, FileAccess.Read);
23             HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileRead);
24             FileStream fileSave2 = new FileStream(tpath, FileMode.Open, FileAccess.Read);
25             HSSFWorkbook book2 = new HSSFWorkbook(fileSave2);
26             HSSFWorkbook[] book=new HSSFWorkbook[2]{book2,hssfworkbook};
27             HSSFSheet CPS = hssfworkbook.GetSheet("Sheet0") as HSSFSheet;//获得模板sheet
28             string rsbh=bjDt.Rows[0]["name"].ToString();
29             CPS.CopyTo(book2,rsbh , true, true);//将模板sheet复制到目标sheet
30             HSSFSheet sheet = book2.GetSheet(bjDt.Rows[0]["name"].ToString()) as HSSFSheet;//获得当前sheet
31             for (int i = 1; i < bjDt.Rows.Count; i++)
32             {
33                 sheet.CopySheet(bjDt.Rows[i]["name"].ToString(), true);//将sheet复制到同一excel的其他sheet上
34             }
35             return book2;
36         }

中介excel生成后进行数据填充

 1 /// <summary>
 2         /// 将datatable数据导出到excel
 3         /// </summary>
 4         /// <param name="bjDt">sheet名集合</param>
 5         /// <param name="stuDt">填充数据</param>
 6         /// <param name="modelfilename">模板名</param>
 7         /// <returns></returns>
 8         public string DataTableToExcel(DataTable bjDt, DataTable stuDt,string modelfilename)
 9         {
10             string path = "";
11             HSSFWorkbook book2 = SheetCopy(bjDt,modelfilename,out path);
12             for (int j = 0; j < bjDt.Rows.Count; j++)
13             {
14                 HSSFSheet sheets = book2.GetSheet(bjDt.Rows[j]["name"].ToString()) as HSSFSheet;
15                 sheets.GetRow(1).GetCell(1).SetCellValue(bjDt.Rows[j]["name"].ToString());
16                 DataRow[] strDt = stuDt.Select(" name='" + bjDt.Rows[j]["name"].ToString() + "'");//筛选出对应的工作表下的数据
17                 int rowIndex = 4;
18                 for (int i = 0; i < strDt.Length;i++ )
19                 {
20                     HSSFRow row0 = sheets.GetRow(rowIndex) as HSSFRow;  //第几行
21                     HSSFCell cell0 = row0.GetCell(0) as HSSFCell;   //第几列
22                     cell0.SetCellValue(strDt[i]["ID"].ToString()); //数据填充
23                     rowIndex++;
24                 }
25             }
26             using (FileStream fileSave = new FileStream(path, FileMode.Open, FileAccess.Write))
27             {
28                 book2.Write(fileSave);
29             }
30             return  path;
31         }

下载excel

 1 　　　　　void FileDown(string url)
 2         {
 3             string fileName = "test.xls";//客户端保存的文件名
 4             string filePath = url;//路径
 5 
 6             FileInfo fileInfo = new FileInfo(filePath);
 7             Response.Clear();
 8             Response.ClearContent();
 9             Response.ClearHeaders();
10             Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
11             Response.AddHeader("Content-Length", fileInfo.Length.ToString());
12             Response.AddHeader("Content-Transfer-Encoding", "binary");
13             Response.ContentType = "application/octet-stream";
14             Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
15             Response.WriteFile(fileInfo.FullName);
16             Response.Flush();
17             Response.End();
18         }