使用POI中XSSF 实现“合并单元格时,仅保留左上角单元格的值,而放弃其他的值“ 问题排查笔记

一、背景介绍
        在项目中借助POI 和 JXLS 两个开源工具jar实现excel数据导出,原有使用POI->HSSF方式进行数据导出,随着导出数据量的增大远远超出单sheet 65535条上限,将导出方式由POI->HSSF升级为POI->XSSF方式。
        术语说明:
       
JXLS:国外常用数据模版导出工具,easyPOI 是国内常用数据模版导出工具,
        POI->HSSF方式:支持Excel 97-2007版本的文件导出,单个sheet页最多能到导出65535条记录
        POI->XSSF方式:支持Excel2007以上版本的文件导出,单个sheet无条数限制

二、核心代码
        maven jar包依赖

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.15</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.15</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml-schema</artifactId>
	<version>3.15</version>
</dependency>
<dependency>
	<groupId>net.sf.jxls</groupId>
	<artifactId>jxls-core</artifactId>
	<version>1.0.6</version>
</dependency>

     HSSF方式:

import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
try{
	//JXLS文件模版地址 文件配置详参:http://jxls.sourceforge.net/getting_started.html
	String outFilePath ="xxxx/file.xls";
	//数据集合
	Map<String, Object> dataMap = new HashMap<>();
	//数据赋值 略
	dataMap.put("users",new ArrayList<>());
	//加载JXLS文件模版
	InputStream fileStream = new FileInputStream(new File(outFilePath));
	//对JXLS文件模版 进行数据赋值,数据来源有
	XLSTransformer transformer = new XLSTransformer();
	HSSFWorkbook workbook = (HSSFWorkbook) transformer.transformXLS(
			fileStream, dataMap);
	//获取 excel 文件的第一个sheet页
	HSSFSheet sheet = workbook.getSheetAt(0);
	// 合并单元格操作  详参:https://poi.apache.org/apidocs/4.0/
	// 将第一个sheet页中的 第4列 第1行到16行进行单元格合并(行和列从0开始)
	sheet.addMergedRegion(new CellRangeAddress(1, 17, 3, 3));
	// 数据文件导出到指定目录   简化
	FileOutputStream fout = new FileOutputStream("xxxx/exportData.xls");
	workbook.write(fout);
	fout.close();
}catch (Throwable e){
	//......
}

  XSSF方式:

import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
try{
	//JXLS文件模版地址 文件配置详参:http://jxls.sourceforge.net/getting_started.html
	String outFilePath ="xxxx/file.xlsx";
	//.....同上
	XSSFWorkbook workbook = (XSSFWorkbook) transformer.transformXLS(
			fileStream, dataMap);
	//获取 excel 文件的第一个sheet页
	XSSFSheet sheet = workbook.getSheetAt(0);
	// 合并单元格操作  详参:https://poi.apache.org/apidocs/4.0/
	// 将第一个sheet页中的 第4列 第1行到16行进行单元格合并(行和列从0开始)
	sheet.addMergedRegion(new CellRangeAddress(1, 17, 3, 3));
	// 数据文件导出到指定目录   简化
	FileOutputStream fout = new FileOutputStream("xxxx/exportData.xlsx");
	workbook.write(fout);
	fout.close();
}catch (Throwable e){
	//......
}

三、XSSF问题说明
       
正主来了,升级为POI->XSSF方式后,发现生成的excel文件,合并单元格列如下所示:

发现合并后的列中的值并未清空,导致excel默认计数和求和数值不正确。经排查发现,因为POI->XSSF中进行addMergedRegion 合并单元格时并不支持“合并单元格时,仅保留左上角单元格的值,而放弃其他的值”。在excel2007版本在进行合并单元格时会默认提示如下所示:

排查了到POI 的4.1.0 版本为止,都不存在合并单元格时保留仅保留坐上角值的相关配置,于是技术上搞不定的事只能通过业务手段搞定,与业务沟通后退求其次,允许excel默认计数值错误,保证excel默认求和正确,具体操作方式如下所示:

try{
	//.....同上略
	XSSFWorkbook workbook = (XSSFWorkbook) transformer.transformXLS(
			fileStream, dataMap);
	//获取 excel 文件的第一个sheet页
	XSSFSheet sheet = workbook.getSheetAt(0);
	//将2到16行 第3列的值设置为0 
	for(int i=2;i<17;i++){
		sheet.getRow(i).getCell(3).setCellValue(0);
	}
	//.....同上略
}catch (Throwable e){
	//......
}

最终通过手动将需合并的1~16行中第3列,保留第1行第3列的值,将第2行到第6行的第3列的值设置为0的方式保证导出的excel文件中默认求和数据的正确性。

四、XSSF问题说明
1.IllegalArgumentException: Merged region A4 must contain 2 or more cells
   原因:由于CellRangeAddress 四个参数配置错误,计算公式为 (_lastRow - _firstRow + 1)*(_lastCol - _firstCol + 1)<2 ,通过计算公式算出可合并的行数小于2,说明没有可合并的列或行导致报错


2.org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF) 
   原因:在使用POI->XSSF时,使用的FileInputStream 模版文件格式为file.xls导致问题产生,将模版另存为.xlsx

相关推荐
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页