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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.