In Excel, we often need to set the format, such as the date format (yyyymmdd), the decimal point format (1.20), the currency format ($2000), and the percentage format (99.99%, in the past, we may have to generate these things on the server side, not only increasing the amount of code on the server side, but also causing unnecessary string replacement operations, now npoi will free the server from such unnecessary operations, and everything will be handled by Excel on the client.
When using npoi, note that all formats are assigned to cells through cellstyle. dataformat, rather than directly assigned to cells.
Case 1 Date Format
Assume that the date to be displayed is in the format of May 5, 2008, which can be generated using the following code:
Hssfsheet sheet = hssfworkbook. createsheet ("new sheet"); hssfcell cell = sheet. createrow (0 ). createcell (0); cell. setcellvalue (New datetime (2007,5, 5); // set date formathssfcellstyle cellstyle = hssfworkbook. createcellstyle (); hssfdataformat format = hssfworkbook. createdataformat (); cellstyle. dataformat = format. getformat ("yyyy-m-d"); cell. cellstyle = cellstyle;
Because the "YYYY, yyyy, month, and day" is in a custom format (different from the built-in format in Excel), you must use hssfworkbook. createdataformat () creates an hssfdataformat instance, and then uses format. getformat to obtain the corresponding format, as long as it is supported by Excel format, this method can be achieved.
Case 2: retain 2 decimal places
Suppose we have a cell with a value of 1.2. How can we display it as 1.20? "0.00" can be used in Excel, so the following code can be completed:
// Create a row and put some cells in it. Rows are 0 based.HSSFCell cell = sheet.CreateRow(0).CreateCell(0);//set value for the cellcell.SetCellValue(1.2);//number format with 2 digits after the decimal point - "1.20"HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");cell.CellStyle = cellStyle;
This is different from the above. hssfdataformat is used. the getbuiltinformat () method uses this because 0.00 is an embedded format in Excel. For the complete list of embedded formats in Excel, you can see the custom list in this window:
Npoi format is set to 2-time, sub-bit, traditional Chinese, decimal place