【POI框架实战】——POI导出Excel时设置单元格类型为数值类型

叁歲伎倆 2022-08-10 04:51 2179阅读 0赞

背 景

--------------------

最近做的一个ITFIN的项目中，后台需要用POI实现导出功能，导出的数据中有文本格式，也有货币格式，所以为了方便在将来导出的表格中做计算，存放货币的单元格需要设置为数值类型。

导出的Excel的单元格都是文本格式（单元格左上角有个小三角）：

![这里写图片描述][format_png]

费了不少功夫，终于把“小三角”去掉了，这里总结并分享一下问题的解决方法。  
  
  
　　通过poi导出excel的过程大致是这样的：

规定单元格的格式  
　　　　　　　　↓  
　　　　　　创建单元格  
　　　　　　　　↓  
　　　　　**设置单元格的格式**  
　　　　　　　　↓  
　　　　　设置数据的格式  
　　　　　　　　↓  
　　　　**把数据存放到单元格中**  
　　　　　　　　↓  
　　　　　 通过IO流输出

背景POI导出Excel时设置单元格类型为数值类型

--------------------

要想存放数值的单元格以数值类型导出，其中最关键的步骤就是上面加粗的两步，设置单元格的格式和向单元格中存放数据。

核心代码如下：

/**
    	 * 导出Excel-2015年11月11日
    	 * 
    	 *@param outPutParam Excel数据实体，包括要导出的excel标头、列标题、数据等
    	 * */
    	private void createContentRows(ExcelParam outPutParam) {
    		HSSFWorkbook workbook=new HSSFWorkbook(); //创建一个Excel文件
    		// 遍历集合数据，产生数据行
    		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);//获取第i行第j列列标题
    				String headerName = headTitle.getName();//获取第j列列标识
    				Object data = rowDate.get(headerName);//获取第i行第j列所放数据
    				HSSFCellStyle contextstyle =workbook.createCellStyle();
    				HSSFCell contentCell = contentRow.createCell(j);				
    				Boolean isNum = false;//data是否为数值型
    				Boolean isInteger=false;//data是否为整数
    				Boolean isPercent=false;//data是否为百分数
    				if (data != null || "".equals(data)) {
    					//判断data是否为数值型
    					isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
    					//判断data是否为整数（小数部分是否为0）
    					isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
    					//判断data是否为百分数（是否包含“%”）
    					isPercent=data.toString().contains("%");
    				}
    				
    				//如果单元格内容是数值类型，涉及到金钱（金额、本、利），则设置cell的类型为数值型，设置data的类型为数值类型
    				if (isNum && !isPercent) {
    					HSSFDataFormat df = workbook.createDataFormat(); // 此处设置数据格式
    					if (isInteger) {
    						contextstyle.setDataFormat(df.getBuiltinFormat("#,#0"));//数据格式只显示整数
    					}else{
    						contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
    					}					
    					// 设置单元格格式
    					contentCell.setCellStyle(contextstyle);
    					// 设置单元格内容为double类型
    					contentCell.setCellValue(Double.parseDouble(data.toString()));
    				} else {
    					contentCell.setCellStyle(contextstyle);
    					// 设置单元格内容为字符型
    					contentCell.setCellValue(data.toString());
    				}
    			}
    		}
    	}

如上，有两个比较重要的点：  
　　1、先用正则表达式判断数据是否为数值型，如果为数值型，则设置单元格格式为整数或者小数；  
　　2、然后往单元格中存放数据的时候要设置数据的格式为double类型，如果查看poi的源码HSSFCell.java会发现设置数据的方法如下，所以用setCellValue(double)方法即可。

![这里写图片描述][format_png 1]

优化

--------------------

到了这里，您可能以为万事大吉啊了，其实上面的代码有个陷阱，如果不经过大数据量的测试是发觉不出来的哦~~

如果数据量大的话，系统可能会报错“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(); //创建一个Excel文件
    		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);//获取第i行第j列列标题
    				String headerName = headTitle.getName();//获取第j列列标识
    				Object data = rowDate.get(headerName);//获取第i行第j列所放数据
    				HSSFCell contentCell = contentRow.createCell(j);				
    				Boolean isNum = false;//data是否为数值型
    				Boolean isInteger=false;//data是否为整数
    				Boolean isPercent=false;//data是否为百分数
    				if (data != null || "".equals(data)) {
    					//判断data是否为数值型
    					isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
    					//判断data是否为整数（小数部分是否为0）
    					isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
    					//判断data是否为百分数（是否包含“%”）
    					isPercent=data.toString().contains("%");
    				}
    				
    				//如果单元格内容是数值类型，涉及到金钱（金额、本、利），则设置cell的类型为数值型，设置data的类型为数值类型
    				if (isNum && !isPercent) {
    					HSSFDataFormat df = workbook.createDataFormat(); // 此处设置数据格式
    					if (isInteger) {
    						contextstyle.setDataFormat(df.getBuiltinFormat("#,#0"));//数据格式只显示整数
    					}else{
    						contextstyle.setDataFormat(df.getBuiltinFormat("#,##0.00"));//保留两位小数点
    					}					
    					// 设置单元格格式
    					contentCell.setCellStyle(contextstyle);
    					// 设置单元格内容为double类型
    					contentCell.setCellValue(Double.parseDouble(data.toString()));
    				} else {
    					contentCell.setCellStyle(contextstyle);
    					// 设置单元格内容为字符型
    					contentCell.setCellValue(data.toString());
    				}
    			}
    		}
    	}

最后导出的正确格式：

![这里写图片描述][format_png 2]

--------------------

【 转载请注明出处——胡玉洋[《POI导出Excel时设置单元格类型为数值类型》][POI_Excel]】

[format_png]: /images/20220731/f6c8683606c046708206a04e272bbb20.png
[format_png 1]: /images/20220731/c8fa59a46c014ff590f4062d1a179ed5.png
[format_png 2]: /images/20220731/d30f0b15810047898097dc5c40817abf.png
[POI_Excel]: http://blog.csdn.net/huyuyang6688/article/details/49786227