NPOI 1.2.3教程 -9 格式化Format

using System;using System.Text;using System.IO;using NPOI.HSSF.UserModel;using NPOI.HPSF;using NPOI.POIFS.FileSystem;using NPOI.SS.UserModel;namespace SetDateCellInXls{ class Program { static void Main(string[] args) { InitializeWorkbook(); Sheet sheet = hssfworkbook.CreateSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.CreateRow(0); // Create a cell and put a date value in it. The first cell is not styled as a date. Cell cell = row.CreateCell(0); cell.SetCellValue(DateTime.Now); // we style the second cell as a date (and time). It is important to Create a new cell style from the workbook // otherwise you can end up modifying the built in style and effecting not only this cell but other cells. CellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"); cell.CellStyle=cellStyle; //set chinese date format Cell cell2 = row.CreateCell(1); cell2.SetCellValue(new DateTime(2008, 5, 5)); CellStyle cellStyle2 = hssfworkbook.CreateCellStyle(); DataFormat format = hssfworkbook.CreateDataFormat(); cellStyle2.DataFormat = format.GetFormat("yyyy年m月d日"); cell2.CellStyle = cellStyle2; WriteToFile(); } static HSSFWorkbook hssfworkbook; static void WriteToFile() { //Write the stream data of workbook to the root directory FileStream file = new FileStream(@"test.xls", FileMode.Create); hssfworkbook.Write(file); file.Close(); } static void InitializeWorkbook() { hssfworkbook = new HSSFWorkbook(); //Create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI Team"; hssfworkbook.DocumentSummaryInformation = dsi; //Create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "NPOI SDK Example"; hssfworkbook.SummaryInformation = si; } }}

 

另外数字的格式化，NumberFormat

 

using System;using System.Text;using System.IO;using NPOI.HSSF.UserModel;using NPOI.HPSF;using NPOI.POIFS.FileSystem;using NPOI.SS.UserModel;namespace NumberFormatInXls{ class Program { static void Main(string[] args) { InitializeWorkbook(); Sheet sheet = hssfworkbook.CreateSheet("new sheet"); //increase the width of Column A sheet.SetColumnWidth(0, 5000); //create the format instance DataFormat format = hssfworkbook.CreateDataFormat(); // Create a row and put some cells in it. Rows are 0 based. Cell cell = sheet.CreateRow(0).CreateCell(0); //set value for the cell cell.SetCellValue(1.2); //number format with 2 digits after the decimal point - "1.20" CellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); cell.CellStyle = cellStyle; //RMB currency format with comma - "¥20,000" Cell cell2 = sheet.CreateRow(1).CreateCell(0); cell2.SetCellValue(20000); CellStyle cellStyle2 = hssfworkbook.CreateCellStyle(); cellStyle2.DataFormat = format.GetFormat("¥#,##0"); cell2.CellStyle = cellStyle2; //scentific number format - "3.15E+00" Cell cell3 = sheet.CreateRow(2).CreateCell(0); cell3.SetCellValue(3.151234); CellStyle cellStyle3 = hssfworkbook.CreateCellStyle(); cellStyle3.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00"); cell3.CellStyle = cellStyle3; //percent format, 2 digits after the decimal point - "99.33%" Cell cell4 = sheet.CreateRow(3).CreateCell(0); cell4.SetCellValue(0.99333); CellStyle cellStyle4 = hssfworkbook.CreateCellStyle(); cellStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%"); cell4.CellStyle = cellStyle4; //phone number format - "021-65881234" Cell cell5 = sheet.CreateRow(4).CreateCell(0); cell5.SetCellValue( 02165881234); CellStyle cellStyle5 = hssfworkbook.CreateCellStyle(); cellStyle5.DataFormat = format.GetFormat("000-00000000"); cell5.CellStyle = cellStyle5; //Chinese capitalized character number - 壹贰叁 元 Cell cell6 = sheet.CreateRow(5).CreateCell(0); cell6.SetCellValue(123); CellStyle cellStyle6 = hssfworkbook.CreateCellStyle(); cellStyle6.DataFormat = format.GetFormat("[DbNum2][$-804]0 元"); cell6.CellStyle = cellStyle6; //Chinese date string Cell cell7 = sheet.CreateRow(6).CreateCell(0); cell7.SetCellValue(new DateTime(2004,5,6)); CellStyle cellStyle7 = hssfworkbook.CreateCellStyle(); cellStyle7.DataFormat = format.GetFormat("yyyy年m月d日"); cell7.CellStyle = cellStyle7; //Chinese date string Cell cell8 = sheet.CreateRow(7).CreateCell(0); cell8.SetCellValue(new DateTime(2005, 11, 6)); CellStyle cellStyle8 = hssfworkbook.CreateCellStyle(); cellStyle8.DataFormat = format.GetFormat("yyyy年m月d日"); cell8.CellStyle = cellStyle8; WriteToFile(); } static HSSFWorkbook hssfworkbook; static void WriteToFile() { //Write the stream data of workbook to the root directory FileStream file = new FileStream(@"test.xls", FileMode.Create); hssfworkbook.Write(file); file.Close(); } static void InitializeWorkbook() { hssfworkbook = new HSSFWorkbook(); //create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI Team"; hssfworkbook.DocumentSummaryInformation = dsi; //create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "NPOI SDK Example"; hssfworkbook.SummaryInformation = si; } }}