Npoi_2.1.3_ Study Record (6) Set decimal, percent, currency, date, scientific notation, and capitalization in-excel

In the operation of Excel, some special value conversion is unavoidable, the following gives the conversion method is similar, the code is as follows:

Hssfworkbook Hssfworkbook =NewHssfworkbook (); Isheet sheet= Hssfworkbook.createsheet ("Sheet1");//Set the first column (Cell) widthSheet. Setcolumnwidth (0, the);//To create a formatted instance objectIDataFormat format =Hssfworkbook.createdataformat ();//Preserve two-bit decimal format//Create a cell "1.20"Icell cell = sheet. CreateRow (0). Createcell (0);//set the value of a cellCell. Setcellvalue (1.2); Icellstyle CellStyle=Hssfworkbook.createcellstyle ();//Formatting ValuesCellstyle.dataformat = Hssfdataformat.getbuiltinformat ("0.00"); cell. CellStyle=CellStyle;//currency "￥20,000"Icell cell2 = sheet. CreateRow (1). Createcell (0); cell2. Setcellvalue (20000); Icellstyle cellStyle2=Hssfworkbook.createcellstyle (); Cellstyle2.dataformat= format. GetFormat ("￥#,# #0"); cell2. CellStyle=CellStyle2;//Scientific Counting Method "3.15E+00"Icell Cell3 = sheet. CreateRow (2). Createcell (0); Cell3. Setcellvalue (3.151234); Icellstyle cellStyle3=Hssfworkbook.createcellstyle (); Cellstyle3.dataformat= Hssfdataformat.getbuiltinformat ("0.00E+00"); Cell3. CellStyle=CellStyle3;//percent "99.33%"Icell CELL4 = sheet. CreateRow (3). Createcell (0); Cell4. Setcellvalue (0.99333); Icellstyle cellStyle4=Hssfworkbook.createcellstyle (); Cellstyle4.dataformat= Hssfdataformat.getbuiltinformat ("0.00%"); Cell4. CellStyle=cellStyle4;//phone Number "021-65881234"Icell CELL5 = sheet. CreateRow (4). Createcell (0); cell5. Setcellvalue (02165881234); Icellstyle cellStyle5=Hssfworkbook.createcellstyle (); Cellstyle5.dataformat= format. GetFormat ("000-00000000"); cell5. CellStyle=cellStyle5;//capitalization-one and three dollarsIcell cell6 = sheet. CreateRow (5). Createcell (0); cell6. Setcellvalue (123); Icellstyle cellStyle6=Hssfworkbook.createcellstyle (); Cellstyle6.dataformat= format. GetFormat ("[dbnum2][$-804]0 Yuan"); cell6. CellStyle=CellStyle6;//date FormattingIcell CELL7 = sheet. CreateRow (6). Createcell (0); Cell7. Setcellvalue (NewDateTime (2004,5,6)); Icellstyle CellStyle7=Hssfworkbook.createcellstyle (); Cellstyle7.dataformat= format. GetFormat ("yyyy m month D Day"); Cell7. CellStyle=CellStyle7;//date FormattingIcell Cell8 = sheet. CreateRow (7). Createcell (0); Cell8. Setcellvalue (NewDateTime (2005, One,6)); Icellstyle CellStyle8=Hssfworkbook.createcellstyle (); Cellstyle8.dataformat= format. GetFormat ("yyyy m month D Day"); Cell8. CellStyle=CellStyle8; FileStream file=NewFileStream (@"Test.xls", FileMode.Create); hssfworkbook.write (file); file. Close ();

There are two ways to convert in the above code:

1 . Hssfdataformat.getbuiltinformat () This type of identity conversion format is embedded in Excel format, such as: "0.00", the complete list of Excel inline format you can see.  2. Format. GetFormat ("000-00000000") is formatted according to the format of the

Excel inline format,

Npoi_2.1.3_ Study Record (6) Set decimal, percent, currency, date, scientific notation, and capitalization in-excel