NPOI_2.1.3 _ learning records (6)-set decimals, percentages, currencies, dates, scientific counts and amounts in Excel in upper case, npoi_2.1.3-excel

NPOI_2.1.3 _ learning records (6)-set decimals, percentages, currencies, dates, scientific counts and amounts in Excel in upper case, npoi_2.1.3-excel

Some special values are inevitable during Excel operations. The conversion method is similar below. The Code is as follows:

HSSFWorkbook hssfWorkbook = new HSSFWorkbook (); ISheet sheet = hssfWorkbook. createSheet ("Sheet1"); // you can specify the width of the first column (Cell. setColumnWidth (0, 5000); // create and format the Instance Object IDataFormat = hssfWorkbook. createDataFormat (); // retain the two-digit decimal format. // create a cell "1.20" ICell cell = sheet. createRow (0 ). createCell (0); // set the cell value. setCellValue (1.2); ICellStyle cellStyle = hssfWorkbook. createCellStyle (); // format the value cellStyle. 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 notation "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; // percentage "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; // The upper-case amount: ICell cell6 = sheet. createRow (5 ). createCell (0); cell6.SetCellValue (123); ICellStyle cellStyle6 = hssfWorkbook. createCellStyle (); cellStyle6.DataFormat = format. getFormat ("[DbNum2] [$-804] 0 RMB"); cell6.CellStyle = cellStyle6; // format iccell7 = sheet. createRow (6 ). createCell (0); cell7.SetCellValue (new DateTime (2004, 5, 6); ICellStyle cellStyle7 = hssfWorkbook. createCellStyle (); cellStyle7.DataFormat = format. getFormat ("yyyy-m-d"); cell7.CellStyle = cellStyle7; // format ICell cell8 = sheet. createRow (7 ). createCell (0); cell8.SetCellValue (new DateTime (2005, 11, 6); ICellStyle cellStyle8 = hssfWorkbook. createCellStyle (); cellStyle8.DataFormat = format. getFormat ("yyyy-mm-dd"); cell8.CellStyle = cellStyle8; FileStream file = new FileStream (@ "test.xls", FileMode. create); hssfWorkbook. write (file); file. close ();

In the above Code, there are two methods to convert:

1. The format of HSSFDataFormat. GetBuiltinFormat () is embedded in Excel, for example, "0.00". You can see the complete list of embedded Excel formats. 2. format. GetFormat ("000-00000000") is formatted according to the format.

Excel embedded format,