C# NPOI 导出Excel 小数位数控制
关于导出excel 小数位数控制我尝试了很多次,花了很多时间。在这先记录一下我错误(无效)的写法,希望可以帮助大家避免出现a类似的错误:
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.BorderLeft = BorderStyle.Thin;
contentStyle.BorderRight = BorderStyle.Thin;
contentStyle.BorderTop = BorderStyle.Thin;
contentStyle.BorderBottom = BorderStyle.Thin;
contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
contentStyle.WrapText = true;//自动换行a
//contentStyle.ShrinkToFit = true;
//设置字体样式
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.IsItalic = false;
font.Underline = FontUnderlineType.None;
font.FontHeightInPoints = 10;
contentStyle.SetFont(font);
IDataFormat dataFormat = workbook.CreateDataFormat();
//以上样式还是字符型
ISheet sheet = workbook.CreateSheet();
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.Height = 15 * 20;
//错误写法
ICell cell = dataRow.CreateCell(0);
cell.CellStyle = contentStyle;
cell.SetCellValue("测试1");
contentStyle.DataFormat = dataFormat.GetFormat("0.00");
ICell cell1 = dataRow.CreateCell(1);
cell1 .CellStyle = contentStyle;//错误
cell1 .SetCellValue(12.1234);
contentStyle.DataFormat = dataFormat.GetFormat("0.0000");
ICell cell2= dataRow.CreateCell(2);
cell2.CellStyle = contentStyle;//错误
cell2.SetCellValue(12.12);
contentStyle.DataFormat = dataFormat.GetFormat("0.0");
ICell cell3= dataRow.CreateCell(3);
cell3.CellStyle = contentStyle;//错误
cell3.SetCellValue(12.12);
之前想偷懒,觉得设置一个样式,只要改变dataformat,就可以。但事实证明,并不可行,这种写法的结果就是数值型和字符型可以区分开,但是小数位一直都是excel默认的2位.那些小数位样式设置是无效的。
正确的写法应该一个小数位控制一个样式,不要来回改样式,这需要在开始就将需要用到的样式设置好。示例如下:
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.BorderLeft = BorderStyle.Thin;
contentStyle.BorderRight = BorderStyle.Thin;
contentStyle.BorderTop = BorderStyle.Thin;
contentStyle.BorderBottom = BorderStyle.Thin;
contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
contentStyle.WrapText = true;//自动换行
//contentStyle.ShrinkToFit = true;
//设置字体样式
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.IsItalic = false;
font.Underline = FontUnderlineType.None;
font.FontHeightInPoints = 10;
contentStyle.SetFont(font);
IDataFormat dataFormat = workbook.CreateDataFormat();
//4位小数样式
ICellStyle contentNumStyle4 = workbook.CreateCellStyle();
contentNumStyle4.BorderLeft = BorderStyle.Thin;
contentNumStyle4.BorderRight = BorderStyle.Thin;
contentNumStyle4.BorderTop = BorderStyle.Thin;
contentNumStyle4.BorderBottom = BorderStyle.Thin;
contentNumStyle4.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
contentNumStyle4.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
contentNumStyle4.WrapText = true;//自动换行
contentNumStyle4.SetFont(font);
contentNumStyle4.DataFormat = dataFormat.GetFormat("0.0000");
//1位小数样式
ICellStyle contentNumStyle1 = workbook.CreateCellStyle();
contentNumStyle1.BorderLeft = BorderStyle.Thin;
contentNumStyle1.BorderRight = BorderStyle.Thin;
contentNumStyle1.BorderTop = BorderStyle.Thin;
contentNumStyle1.BorderBottom = BorderStyle.Thin;
contentNumStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
contentNumStyle1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
contentNumStyle1.WrapText = true;//自动换行
contentNumStyle1.SetFont(font);
contentNumStyle1.DataFormat = dataFormat.GetFormat("0.0");
//2位小数样式
ICellStyle contentNumStyle2 = workbook.CreateCellStyle();
contentNumStyle2.BorderLeft = BorderStyle.Thin;
contentNumStyle2.BorderRight = BorderStyle.Thin;
contentNumStyle2.BorderTop = BorderStyle.Thin;
contentNumStyle2.BorderBottom = BorderStyle.Thin;
contentNumStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
contentNumStyle2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
contentNumStyle2.WrapText = true;//自动换行
contentNumStyle2.SetFont(font);
contentNumStyle2.DataFormat = dataFormat.GetFormat("0.00");
ICell cell = dataRow.CreateCell(0);
cell.CellStyle = contentStyle;
cell.SetCellValue("测试1");
ICell cell1 = dataRow.CreateCell(1);
cell1 .CellStyle = contentNumStyle4;
cell1 .SetCellValue(12.1234);
ICell cell2= dataRow.CreateCell(2);
cell2.CellStyle = contentNumStyle2 ;
cell2.SetCellValue(12.12);
ICell cell3= dataRow.CreateCell(3);
cell3.CellStyle = contentNumStyle1;
cell3.SetCellValue(12.12);
这样出来的效果如图所示,1位和4位小数位显示正常
关于小数位控制,我在网上看到还有一种写法
contentNumStyle4.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000");
这种写法我试了,有一点问题就是,若需要显示4位,如果一个数值小数位第3位或第四位是0,显示时会只显示到有值的小数部分,后面0那部分就不显示,比如12.3400,到excel 中就会显示成12.34,而第一种写法就会完整的显示为12.3400.