Npoi format is set to 2-time, sub-bit, traditional Chinese, decimal place

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