在服务端C#如何利用NPOI构建Excel模板

来源:转载

目前本人接触过两种模板导出的方式:(1)C#利用NPOI接口制作Excel模板,在服务端用数据渲染模板(2)在前端利用前人搭建好的框架,利用office编写xml制作模板,在客户端进行数据的渲染,导出的格式是word。在制作报表时两种方式都可以满足的基本需求,但excel模板更加强大,因为xml模板的布局大体在事先就要确定好,扩展性不高,而excel模板就可以根据数据的特点进行自定义布局,拓展性更强。下面介绍如何在服务端利用NPOI构建excel模板。

简单来讲,NPOI是一个库,它可以处理多种文件格式如xls、xlsx、doc、ppt、vsd等。除了制作excel模板,也可以读取excel中的数据。本文具体介绍制作excel模板。

添加引用NPOI后,在文件的头部引入如下几个接口,如图

HSSF使用于2007之前的xls版本,XSSF适用于2007及其之后的xlsx版本,它们是excel/doc格式读写库。NPOI.SS是Excel公用接口及Excel公式计算引擎。更多具体的功能以及接口可以自行百度。

具体实现如下:

 public List<string> GetExcel(string year, string month, string type, out string statusCode, out string errMsg) 2 { 3 statusCode = "0000"; 4 errMsg = ""; 5 List<string> response = new List<string>(); 6 string strFilePath = ""; 7 string strGuid = Guid.NewGuid().ToString(); 8 string FilePath = "//BufFile//OutFiles//DownLoadFiles//ExportExcel//" + strGuid; //构建文件缓存目录 9 strFilePath = System.IO.Directory.GetParent(System.IO.Directory.GetParent(System.AppDomain.CurrentDomain.BaseDirectory).FullName).FullName + FilePath; 10 if (!System.IO.Directory.Exists(strFilePath)) 11 { 12 System.IO.Directory.CreateDirectory(strFilePath); 13 } 14 //文件命名 15 string strFileName = strFilePath + "//" + "XXXXXX.xls"; 16 string ret = FilePath + "//" + "XXXXXX.xls"; 17 string uploadPath = System.IO.Directory.GetParent(System.IO.Directory.GetParent(System.AppDomain.CurrentDomain.BaseDirectory).FullName).FullName + "//BufFile//OutFiles//UploadFiles//"; 18 List<CompletionRateModel> data = GetCompletionRate(year, month, out statusCode, out errMsg); 19 try 20 { //创建工作薄 21 HSSFWorkbook hssfWorkBook = new HSSFWorkbook(); //编辑文件信息,如文件所属公司、作者、创建日期等 22 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); 23 dsi.Company = "ZondyCyber"; 24 SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 25 si.Author = "ZondyCyber"; 26 si.LastAuthor = "ZondyCyber"; 27 si.CreateDateTime = DateTime.Now; 28 hssfWorkBook.DocumentSummaryInformation = dsi; 29 hssfWorkBook.SummaryInformation = si; //创建名为YYYYYY的表sheet 30 HSSFSheet hssfSheet = hssfWorkBook.CreateSheet("YYYYYY") as HSSFSheet; 31 32 //设置列宽 33 for (int c = 0; c < 7; c++) 34 { 35 hssfSheet.SetColumnWidth(c, 12 * 266); 36 } 37 38 //设置列头的单元格样式 39 HSSFCellStyle cellStyle = hssfWorkBook.CreateCellStyle() as HSSFCellStyle; 40 HSSFFont cellFont = hssfWorkBook.CreateFont() as HSSFFont; 41 cellFont.Boldweight = Convert.ToInt16(FontBoldWeight.Bold); 42 cellFont.FontName = "宋体"; 43 cellFont.FontHeightInPoints = 12; 44 cellStyle.Alignment = HorizontalAlignment.Center; 45 cellStyle.VerticalAlignment = VerticalAlignment.Center; 46 cellStyle.WrapText = true; 47 cellStyle.SetFont(cellFont); 48 cellStyle.BorderTop = cellStyle.BorderRight = cellStyle.BorderBottom = cellStyle.BorderLeft = BorderStyle.Thin;//BorderStyle.None 49 //左对齐样式 50 HSSFCellStyle leftCellStyle = hssfWorkBook.CreateCellStyle() as HSSFCellStyle; 51 leftCellStyle.CloneStyleFrom(cellStyle); 52 leftCellStyle.Alignment = HorizontalAlignment.Left; 53 //居中填充样式 54 HSSFCellStyle fillCellStyle = hssfWorkBook.CreateCellStyle() as HSSFCellStyle; 55 fillCellStyle.CloneStyleFrom(cellStyle); 56 fillCellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; 57 //fillCellStyle.FillPattern = FillPattern.Diamonds; 58 //fillCellStyle.FillPattern = FillPattern.FineDots; 59 fillCellStyle.FillPattern = FillPattern.LeastDots; 60 fillCellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; 61 62 //居中填充样式(无边框) 63 HSSFCellStyle fillCellStyle2 = hssfWorkBook.CreateCellStyle() as HSSFCellStyle; 64 fillCellStyle2.CloneStyleFrom(fillCellStyle); 65 //fillCellStyle2.FillPattern = FillPattern.NoFill; 66 fillCellStyle2.BorderTop = fillCellStyle2.BorderRight = fillCellStyle2.BorderBottom = fillCellStyle2.BorderLeft = BorderStyle.None;//BorderStyle.None 67 //值的样式 68 HSSFCellStyle valueStyle = hssfWorkBook.CreateCellStyle() as HSSFCellStyle; 69 HSSFFont valueFont = hssfWorkBook.CreateFont() as HSSFFont; 70 valueFont.FontName = "宋体"; 71 valueFont.FontHeightInPoints = 10; 72 valueStyle.BorderTop = valueStyle.BorderRight = valueStyle.BorderBottom = valueStyle.BorderLeft = BorderStyle.Thin;//BorderStyle.None 73 valueStyle.WrapText = true; 74 valueStyle.Alignment = HorizontalAlignment.Center; 75 valueStyle.VerticalAlignment = VerticalAlignment.Center; 76 valueStyle.SetFont(valueFont); 77 //值的样式(无边框) 78 HSSFCellStyle valueStyle2 = hssfWorkBook.CreateCellStyle() as HSSFCellStyle; 79 valueStyle2.CloneStyleFrom(valueStyle); 80 valueStyle2.BorderTop = valueStyle2.BorderRight = valueStyle2.BorderBottom = valueStyle2.BorderLeft = BorderStyle.None;//BorderStyle.None 81 82 //开始构建表格 83 int rowIndex = 0;//记录用到第几行 84 //构建表题 85 String Title = "XXXXXX(" + type + ")统计 " + year + "年" + month + "月"; 86 //创建表格的第一行的第一个单元格 hssfSheet.CreateRow(0).CreateCell(0).CellStyle = fillCellStyle; //获取表格的第一行的第一个单元格,并为其赋值 87 hssfSheet.GetRow(0).GetCell(0).SetCellValue(Title); 88 //合并单元格 /* * cellRangeAddress可以合并行或列,第一个参数是起始行号,第二个参数是终止行号,第三个参数是起始列号,第三个参数是终止列号 */ 89 CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex + 2, 0, 6); 90 hssfSheet.AddMergedRegion(region); 91 hssfSheet.SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); 92 rowIndex = rowIndex + 3; //3 93 String Type = "部门"; 94 //构建表头 95 hssfSheet.CreateRow(rowIndex).CreateCell(0).CellStyle = valueStyle; 96 hssfSheet.GetRow(rowIndex).GetCell(0).SetCellValue("序号"); 97 /** ** 构建表格的具体细节省略,无非是合并单元格,填充数据 **/323 //表格构建完毕324 FileStream file = new FileStream(strFileName, FileMode.Create);325 hssfWorkBook.Write(file);326 file.Close();327 response.Add(ret);328 }

分享给朋友:
您可能感兴趣的文章:
随机阅读: