有了Apache POI，轻轻松松实现Excel文档合并
近期有个开发需求是用户发布文档模板的任务，接收者根据模板填报数据，最终由发布者进行数据汇总。最方便的解决方案是通过在线文档进行实现，但是考虑到不同填报人的数据隐私等安全问题只能选择文件上传的传统方案。
通过调研，最终采用基于Apache POI组件进行实现。
Maven引入
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>代码实现
- ExcelService接口
public interface TaskService {
/**
*
* @param filePaths 合并文件路径列表
* @param templateFilePath 模板文件路径
* @param targetFilePath 目标文件路径
* @param dataStartRowNum 数据起始行号
*/
void combine(List<String> filePaths, String templateFilePath, String targetFilePath, Integer dataStartRowNum);
}- ExcelServiceImpl实现
import com.yusher.excel.util.PoiUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelServiceImpl implements ExcelService {
public void combine(List<String> filePaths, String templateFilePath, String targetFilePath, Integer dataStartRowNum) {
try {
FileInputStream templateInputStream = new FileInputStream(templateFilePath);
Workbook templateWorkbook = new XSSFWorkbook(templateInputStream);
Sheet templateSheet = templateWorkbook.getSheetAt(0);
Workbook mergedWorkbook = new XSSFWorkbook();
Sheet mergedSheet = mergedWorkbook.createSheet(templateSheet.getSheetName());
// 用于记录总行数
int allRowNum = 0;
// 复制模板的表头到合并工作表
for (int i = 0; i < dataStartRowNum; i++) {
Row headerRow = templateSheet.getRow(i);
Row newHeaderRow = mergedSheet.createRow(i);
PoiUtil.copyXssRow(headerRow, newHeaderRow, (XSSFWorkbook) mergedWorkbook);
allRowNum++;
}
// 存放合并区域的Map
Map<String, CellRangeAddress> mergedRegionsMap = new HashMap<>();
// 添加模板表头的合并区域
for (int i = 0; i < templateSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = templateSheet.getMergedRegion(i);
if (mergedRegion.getFirstRow() > dataStartRowNum) {
break;
}
mergedSheet.addMergedRegion(mergedRegion);
String regionKey = mergedRegion.getFirstRow()
+ "-" + mergedRegion.getLastRow()
+ "-" + mergedRegion.getFirstColumn()
+ "-" + mergedRegion.getLastColumn();
mergedRegionsMap.put(regionKey, mergedRegion);
}
// 处理用户上传的文档
for (String filePath : filePaths) {
FileInputStream userInputStream = new FileInputStream(filePath);
Workbook userWorkbook = new XSSFWorkbook(userInputStream);
Sheet userSheet = userWorkbook.getSheetAt(0);
// 用于记录总数据行数
int dataNum = 0;
// 从数据起始行开始遍历
for (int j = dataStartRowNum; j <= userSheet.getLastRowNum(); j++) {
Row userRow = userSheet.getRow(j);
if (PoiUtil.isBlankRow(userRow)) {
break;
}
Row newRow = mergedSheet.createRow(allRowNum + dataNum);
PoiUtil.copyXssRow(userRow, newRow, (XSSFWorkbook) mergedWorkbook);
dataNum++;
}
// 遍历数据行的合并区域
for (int i = 0; i < userSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = userSheet.getMergedRegion(i);
if (mergedRegion.getFirstRow() < dataStartRowNum) {
continue;
}
int newStartRow = mergedRegion.getFirstRow() + allRowNum -dataStartRowNum;
int newEndRow = mergedRegion.getLastRow() + allRowNum - dataStartRowNum;
CellRangeAddress newMergedRegion = new CellRangeAddress(newStartRow, newEndRow, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
String regionKey = newStartRow
+ "-" + newEndRow
+ "-" + mergedRegion.getFirstColumn()
+ "-" + mergedRegion.getLastColumn();
if (!mergedRegionsMap.containsKey(regionKey)) {
mergedSheet.addMergedRegion(newMergedRegion);
mergedRegionsMap.put(regionKey, newMergedRegion);
}
}
allRowNum += dataNum;
userInputStream.close();
}
FileOutputStream outputStream = new FileOutputStream(targetFilePath);
mergedWorkbook.write(outputStream);
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}- PoiUtil工具类
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class PoiUtil {
// 复制行数据与样式
public static void copyXssRow(Row sourceRow, Row targetRow, XSSFWorkbook targetWorkbook) {
if (sourceRow == null) {
return;
}
for (Cell sourceCell : sourceRow) {
Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex(), sourceCell.getCellType());
copyXssCell(sourceCell, targetCell, targetWorkbook);
}
}
// 复制单元格与样式
private static void copyXssCell(Cell sourceCell, Cell targetCell, XSSFWorkbook targetWorkbook) {
if (sourceCell == null) {
return;
}
switch (sourceCell.getCellType()) {
case STRING:
targetCell.setCellValue(sourceCell.getStringCellValue());
break;
case BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case FORMULA:
targetCell.setCellFormula(sourceCell.getCellFormula());
break;
case BLANK:
break;
default:
break;
}
CellStyle sourceCellStyle = sourceCell.getCellStyle();
CellStyle targetCellStyle = targetWorkbook.createCellStyle();
targetCellStyle.cloneStyleFrom(sourceCellStyle);
targetCell.setCellStyle(targetCellStyle);
}
// 判断行是否为空
public static boolean isBlankRow(Row row) {
boolean isBlank = true;
for (int i = 0; i < row.getLastCellNum(); i++) {
if (row.getCell(i) != null && row.getCell(i).getCellType() != CellType.BLANK) {
isBlank = false;
break;
}
}
return isBlank;
}
}要点说明
- 合并区域位置计算
int newStartRow = mergedRegion.getFirstRow() + allRowNum -dataStartRowNum;
int newEndRow = mergedRegion.getLastRow() + allRowNum - dataStartRowNum;
CellRangeAddress newMergedRegion = new CellRangeAddress(newStartRow, newEndRow, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
String regionKey = newStartRow
+ "-" + newEndRow
+ "-" + mergedRegion.getFirstColumn()
+ "-" + mergedRegion.getLastColumn();由于每个文档数据起始行都是用户根据模板填入的，所以在合并到目标文档的时候需重新计算合并区域的位置，若不处理，则会导致目标文档的合并被覆盖或缺失，导致目标文件不符合要求。
补充说明
以上代码可满足大部分需求，但是还有缺漏，如单元格长度设置、Excel版本兼容等可自行参考Apache POI使用文档进行补充。
- 单元格长度设置
int numberOfColumns = templateSheet.getRow(0).getLastCellNum();
for (int i = 0; i < numberOfColumns; i++) {
mergedSheet.setColumnWidth(i, templateSheet.getColumnWidth(i));
}- Excel版本兼容
String fileSuffix = templateFilePath.substring(templateFilePath.lastIndexOf(".") + 1);
Workbook templateWorkbook;
if (fileSuffix.equals("xlsx")) {
templateWorkbook = new XSSFWorkbook(templateInputStream);
} else {
templateWorkbook = new HSSFWorkbook(templateInputStream);
}总结
通过以上代码即可实现Excel文档的合并，无需根据特定文件定制化修改代码。
