最近做的一个ITFIN的项目中,后台需要用POI实现导出功能,导出的数据中有文本格式,也有货币格式,所以为了方便在将来导出的表格中做计算,存放货币的单元格需要设置为数值类型。
导出的Excel的单元格都是文本格式(单元格左上角有个小三角):
费了不少功夫,终于把“小三角”去掉了,这里总结并分享一下问题的解决方法。 通过poi导出excel的过程大致是这样的:
规定单元格的格式 ↓ 创建单元格 ↓ 设置单元格的格式 ↓ 设置数据的格式 ↓ 把数据存放到单元格中 ↓ 通过IO流输出
背景POI导出Excel时设置单元格类型为数值类型
要想存放数值的单元格以数值类型导出,其中最关键的步骤就是上面加粗的两步,设置单元格的格式和向单元格中存放数据。
核心代码如下:
/**
* 导出Excel-胡玉洋-2015年11月11日
*
*@param outPutParam Excel数据实体,包括要导出的excel标头、列标题、数据等
* */
private void createContentRows (ExcelParam outPutParam) {
HSSFWorkbook workbook=new HSSFWorkbook();
for (int i = 0 ; i < outPutParam.getContent().size(); i++) {
int rowIndex = i + 2 ;
HSSFRow contentRow = sheet.createRow(rowIndex);
Map<String, Object> rowDate = outPutParam.getContent().get(i);
for (int j = 0 ; j < outPutParam.getTitleList().size(); j++) {
Title headTitle = outPutParam.getTitleList().get(j);
String headerName = headTitle.getName();
Object data = rowDate.get(headerName);
HSSFCellStyle contextstyle =workbook.createCellStyle();
HSSFCell contentCell = contentRow.createCell(j);
Boolean isNum = false ;
Boolean isInteger=false ;
Boolean isPercent=false ;
if (data != null || "" .equals(data)) {
isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$" );
isInteger=data.toString().matches("^[-\\+]?[\\d]*$" );
isPercent=data.toString().contains("%" );
}
if (isNum && !isPercent) {
HSSFDataFormat df = workbook.createDataFormat();
if (isInteger) {
contextstyle.setDataFormat(df.getBuiltinFormat("#,#0" ));
}else {
contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00" ));
}
contentCell.setCellStyle(contextstyle);
contentCell.setCellValue(Double.parseDouble(data.toString()));
} else {
contentCell.setCellStyle(contextstyle);
contentCell.setCellValue(data.toString());
}
}
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
如上,有两个比较重要的点: 1、先用正则表达式判断数据是否为数值型,如果为数值型,则设置单元格格式为整数或者小数; 2、然后往单元格中存放数据的时候要设置数据的格式为double类型,如果查看poi的源码HSSFCell.java会发现设置数据的方法如下,所以用setCellValue(double)方法即可。
优化
到了这里,您可能以为万事大吉啊了,其实上面的代码有个陷阱,如果不经过大数据量的测试是发觉不出来的哦~~
如果数据量大的话,系统可能会报错“The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook”,原因是style创建的次数太多了,解决这个问题的方法很简单,在循环体外面创建单元格格式contextstyle(即把它当成一个“全局”变量),不要在循环内部创建。
正确的代码如下:
/**
* 导出Excel-胡玉洋-2015年11月11日
*
*@param outPutParam Excel数据实体,包括要导出的excel标头、列标题、数据等
* */
private void createContentRows (ExcelParam outPutParam) {
HSSFWorkbook workbook=new HSSFWorkbook();
HSSFCellStyle contextstyle =workbook.createCellStyle();
for (int i = 0 ; i < outPutParam.getContent().size(); i++) {
int rowIndex = i + 2 ;
HSSFRow contentRow = sheet.createRow(rowIndex);
Map<String, Object> rowDate = outPutParam.getContent().get(i);
for (int j = 0 ; j < outPutParam.getTitleList().size(); j++) {
Title headTitle = outPutParam.getTitleList().get(j);
String headerName = headTitle.getName();
Object data = rowDate.get(headerName);
HSSFCell contentCell = contentRow.createCell(j);
Boolean isNum = false ;
Boolean isInteger=false ;
Boolean isPercent=false ;
if (data != null || "" .equals(data)) {
isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$" );
isInteger=data.toString().matches("^[-\\+]?[\\d]*$" );
isPercent=data.toString().contains("%" );
}
if (isNum && !isPercent) {
HSSFDataFormat df = workbook.createDataFormat();
if (isInteger) {
contextstyle.setDataFormat(df.getBuiltinFormat("#,#0" ));
}else {
contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00" ));
}
contentCell.setCellStyle(contextstyle);
contentCell.setCellValue(Double.parseDouble(data.toString()));
} else {
contentCell.setCellStyle(contextstyle);
contentCell.setCellValue(data.toString());
}
}
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
最后导出的正确格式: