On the server C #, how to use NPOI to build an Excel template,

On the server C #, how to use NPOI to build an Excel template,

Currently, I have used two templates for export: (1) C # using the NPOI interface to create an Excel template, and using a Data Rendering template on the server (2) using a framework established by my predecessors at the front end, compile an xml template using office to render data on the client. The exported format is word. Both methods can meet the basic requirements when creating a report, but the excel template is more powerful, because the layout of the xml template is generally determined in advance, and the scalability is not high, the excel template can customize the layout based on the characteristics of the data to improve scalability. The following describes how to use NPOI to build an excel template on the server.

To put it simply, NPOI is a library that can process multiple file formats, such as xls, xlsx, doc, ppt, and sealing. In addition to creating an excel template, you can also read data in excel. This document describes how to create an excel template.

After NPOI is referenced, the following interfaces are introduced in the file header,

HSSF is used in xls versions earlier than 2007, and XSSF is applicable to xlsx Versions later than 2007. They are read/write databases in excel/doc format. NPOI. SS is a common Excel interface and an Excel Formula computing engine. You can use Baidu for more specific functions and interfaces.

The specific implementation is as follows:

 

Public List & lt; string & gt; 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; // construct the file cache directory 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 // file name 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 {// create a workbook 21 HSSFWorkbook hssfWorkBook = new HSSFWorkbook (); // edit the file information, such as the file company, author, and creation date. 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; // create a table named YYYYYY, sheet 30 HSSFSheet hssfSheet = hssfWorkBook. createSheet ("YYYYYY") as HSSFSheet; 31 32 // set the column width to 33 for (int c = 0; c <7; c ++) 34 {35 hssfSheet. setColumnWidth (c, 12*266); 36} 37 38 // set the cell style of the column header 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 = verticalignment. center; 46 cellStyle. wrapText = true; 47 cellStyle. setFont (cellFont); 48 cellStyle. borderTop = cellStyle. borderRight = cellStyle. borderBottom = cellStyle. borderLeft = BorderStyle. thin; // BorderStyle. none 49 // left-aligned style 50 HSSFCellStyle leftCellStyle = hssfWorkBook. createCellStyle () as HSSFCellStyle; 51 leftCellStyle. cloneStyleFrom (cellStyle); 52 leftCellStyle. alignment = HorizontalAlignment. left; 53 // center fill style 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 // center filling style (no border) 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 // value style 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 = verticalignment. center; 76 valueStyle. setFont (valueFont); 77 // value style (no border) 78 HSSFCellStyle valueStyle2 = hssfWorkBook. createCellStyle () as HSSFCellStyle; 79 evaluate (valueStyle); 80 valueStyle2.BorderTop = valueStyle2.BorderRight = valueStyle2.BorderBottom = valueStyle2.BorderLeft = BorderStyle. none; // BorderStyle. none 81 82 // start to build table 83 int rowIndex = 0; // record the row 84 // build table Question 85 String Title = "XXXXXX (" + type + ") count "+ year +" year "+ month +" month "; 86 // create the first cell hssfSheet in the first row of the table. createRow (0 ). createCell (0 ). cellStyle = fillCellStyle; // obtain the first cell of the first row of the table and assign it 87 hssfSheet. getRow (0 ). getCell (0 ). setCellValue (Title); 88 // merge cells/** cellRangeAddress can merge rows or columns. The first parameter is the starting row number, and the second parameter is the ending row number, the third parameter is the start column number, and the third parameter is the end column number */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 = "department"; 94 // construct the header 95 hssfSheet. createRow (rowIndex ). createCell (0 ). cellStyle = valueStyle; 96 hssfSheet. getRow (rowIndex ). getCell (0 ). setCellValue ("Serial Number"); 97/***** the details of table creation are omitted, nothing more than merging cells, fill in data **/323 // after the table is built, 324 FileStream file = new FileStream (strFileName, FileMode. create); 325 hssfWorkBook. write (file); 326 file. close (); 327 response. add (ret); 328}

View Code