POI HSSFsheet 复制合并
因为项目比较老，jdk用的1.7，poi最高只能用到3.17，POI与JDK各版本对应关系如下：
poi 4.x ， 稳定版本 4.0.x、 4.1.x jdk1.8+
poi 3.11 和3.x 以后的版本 稳定版本 3.17 jdk1.6+
poi 3.5 - poi 3.10 jdk1.5+
poi 3.5 之前 jdk1.4+
项目需求，主要是列表数据导出时提高导出速度，这里采用了多线程生成多个excel的方案，然后将多份excel合并成一份excel。在此过程中，需要用到excel的sheet复制、cellstyle复制、行移动、合并单元格取消、行高自适应。
首先在做jar包升级的时候，遇到了jar包冲突的问题，简单来说，运行的时候出现noclassfound就是jar冲突。我遇到的是commons-compressjar包冲突，在idea中通过maven的show dependencies来排查，把冲突的jar包排除掉。
**
sheet合并复制
**
sheet合并，在网上能找到很多例子，但是当excel数据量过大的时候会出现 poi 超过最大style 4000的问题（The maximum number of cell styles was exceeded），其问题主要是在单元格复制时，HSSFWorkbook.createCellStyle()过多，按照这个问题去网上查，会发现很多人采用的都是在循环外，但这种方法并不适用于数据量大的sheet复制操作，后来找到一篇帖子提供了一个思路，将cellstyle放到缓存中，若存在则从缓存中获取。在这里需要注意，poi要求一个单元格的格式和单元格本身都在一个worksheet，不同的worksheet间不能共用HSSFCellStyle，否则会出现This Style does not belong to the supplied Workbook Are you的问题。
cellstyle缓存方法：
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CellStyleCache {
// 初始化大小
private static Map<String, List<HSSFCellStyle>> cellStyleCacheMap = new HashMap<>(24);
public CellStyleCache() {
}
// 每次导出，生成单次的主键，这里只缓存当前导出所需的excel样式，导出结束后，清空缓存
public static void Initialized(String key) {
cellStyleCacheMap.put(key, new ArrayList<HSSFCellStyle>(8));
}
// 添加缓存中不存在的样式
public static void addCellStyle(HSSFCellStyle style, String key) {
if (cellStyleCacheMap.containsKey(key)){
cellStyleCacheMap.get(key).add(style);
}
}
// 业务结束后，删除缓存中的对象，释放资源
public static void destroyCellStyle(String key) {
if (cellStyleCacheMap.containsKey(key)){
cellStyleCacheMap.get(key).clear();
cellStyleCacheMap.remove(key);
}
}
// 判断当前样式在缓存中是否存在，若缓存不存在，则获取当前样式，否则从缓存中获取样式
public static HSSFCellStyle getCellStyle(HSSFCellStyle cellStyle, String key) {
List<HSSFCellStyle> list = cellStyleCacheMap.get(key);
for (HSSFCellStyle style : list) {
if (style.getAlignment() == cellStyle.getAlignment()
//边框和边框颜色
&& style.getBorderBottom() == cellStyle.getBorderBottom()
&& style.getBorderLeft() == cellStyle.getBorderLeft()
&& style.getBorderRight() == cellStyle.getBorderRight()
&& style.getBorderTop() == cellStyle.getBorderTop()
&& style.getTopBorderColor() == cellStyle.getTopBorderColor()
&& style.getBottomBorderColor() == cellStyle.getBottomBorderColor()
&& style.getRightBorderColor() == cellStyle.getRightBorderColor()
&& style.getLeftBorderColor() == cellStyle.getLeftBorderColor()
//背景和前景
&& style.getFillBackgroundColor() == cellStyle.getFillBackgroundColor()
&& style.getFillForegroundColor() == cellStyle.getFillForegroundColor()
&& style.getHidden() == cellStyle.getHidden()
&& style.getVerticalAlignment() == cellStyle.getVerticalAlignment()) {
return style;
}
}
return null;
}
}单元格复制的方法：
// 获取样式
HSSFCellStyle newstyle = CellStyleCache.getCellStyle(fromCell.getCellStyle(), cellTypeKey);
if (StringUtil.isEmpty(newstyle)) {
// 这里会出现超4000的问题
newstyle = wb.createCellStyle();
// 样式复制,样式这块效率最慢,一旦数据量大,样式多,容易出错
newstyle.cloneStyleFrom(fromCell.getCellStyle())；
CellStyleCache.addCellStyle(newstyle, cellTypeKey);
}
// 新单元格给定样式
toCell.setCellStyle(newstyle);行高自适应
单元格设置setWrapText(true)时，生成的excel文件行高就会自适应，但是，如果又手动设置了row的height或HeightInPoints，就会导致行高自适应失效，始终是默认值。
行删除sheet.shiftRows
行删除，POI提供了两种方式，一种会保留行sheet.removeRow()，另一种不会保留行，是移动覆盖的操作sheet.shiftTows(excel起始行号索引，excel结束行号索引，移动行数正值向下负值向上)，移动范围包含起始结束行，行索引从0开始，比excel实际行号小一。这里需要注意，行移动后，通过sheet.getLastRowNum()获取的最大索引是不会发生变化的，移动后，原位置会留下空行。另外，当覆盖的行有合并单元格的情况，需要先去除合并单元格，再移动行。本人在实际使用中，在合并sheet的时候，需要跳过其余sheet的表头（当然，生成excel的时候，后续excel也可以通过不生成表头的方式来处理），因此需要从表头行下一行到sheet最后一行整体向上移动
// titleRowNums：表头在excel中的结束行号即需复制的内容起始索引
oldSheet.shiftRows(titleRowNums, oldSheet.getLastRowNum()+1, -titleRowNums)上述代码在初次使用的时候出现了一个bug，导致后面excel合并出现各种问题，后来耐心排查发现在移动行后，Sheet.getLastRowNum()发生了变化，且经过多次测试，发现这个值就比行移动前小一。当时一度怀疑是移动行的时候，getLastRowNum不识别遗留的空行或者行移动前，取消单元格合并时，行号获取有问题。在网上查了很多资料也没找到问题所在，最终，采取了终极手段——阅读源码，然后发现，这是shiftrows的一个坑，源码如下：
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight, boolean moveComments) {
--------------------中间及前部源码省略----------------------------------------------------
if (n > 0) {
-----------------------------------向下移动源码部分省略-------------------------------
} else {
if (startRow + n < this._firstrow) {
this._firstrow = Math.max(startRow + n, 0);
}
if (endRow == this._lastrow) {
this._lastrow = Math.min(endRow + n, SpreadsheetVersion.EXCEL97.getLastRowIndex());
// 出问题的地方，rownum是从endRow-1开始的，这一行虽然移动了，但是并不是空，
// 因此进入判断中后，给lastrow赋值为endRow-1，
// 也就是Sheet.getLastRowNum()变为了endRow-1
for(rowNum = endRow - 1; rowNum > endRow + n; --rowNum) {
if (this.getRow(rowNum) != null) {
this._lastrow = rowNum;
break;
}
}
}
}
---------------------------剩余源码省略-------------------------------------------------------
}
}结合源码，当从某一行至sheet最后一行整体向上移动时，shiftrows结束行入参用Sheet.getLastRowNum()+1，这样不会改变当前sheet最后一行的索引值。
完整代码：
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* excel合并通用工具类——xls类型
*
* @date 2022/10/25 16:57
*/
public class POIUtilsForMergeExcel {
public class HSSFDateUtil extends DateUtil {
}
/**
* 多份excel合并成一份
*
* @param originFilePath 合并后的文件地址，也是合并目标文件路径
* @param filePathList 待合并文件路径集合
* @param titleRowNums 表头结束行号
* @throws IOException
*/
public static void mergeExcel(String originFilePath, List<String> filePathList, int titleRowNums) throws IOException {
FileInputStream newExcelInputstream = null;
InputStream in = null;
FileOutputStream fileOut = null;
try {
// 初始化celltype，生成当前批次的唯一主键id
String cellTypeKey = uuid();
CellStyleCache.Initialized(cellTypeKey);
newExcelInputstream = new FileInputStream(originFilePath);
HSSFWorkbook newExcelCreat = new HSSFWorkbook(newExcelInputstream);
List<XSSFSheet> oldSheets = new ArrayList<XSSFSheet>();
// 创建sheet
HSSFSheet newSheet = newExcelCreat.getSheetAt(0);
int deleteHeadRowRatio = 0;
// 遍历每个源excel文件，filePathList为源文件的名称集合
for (int j = 0; j < filePathList.size(); j++) {
String fromExcelPath = filePathList.get(j);
// 屏蔽sheet网格线
newSheet.setDisplayGridlines(false);
in = new FileInputStream(fromExcelPath);
// 得到每个源excel对象
HSSFWorkbook fromExcel = new HSSFWorkbook(in);
// 开始遍历excel中的sheet
for (int i = 0; i < fromExcel.getNumberOfSheets(); i++) {
HSSFSheet oldSheet = fromExcel.getSheetAt(i);
// 删除源sheet表头 行上移，不保留行
// 先清除合并单元格（如果上移或下移的行与要覆盖的行同时包含相同位置处的合并单元格，需要先把合并单元格去掉再shiftRows,否则会报因单元格合并冲突）
removeMergedColRegion(oldSheet,0,titleRowNums);
System.out.println(fromExcelPath + "---****--" +oldSheet.getLastRowNum()+ "---****--" +
oldSheet.getPhysicalNumberOfRows());
// 再移动行 起始行号索引到截止行号索引，索引从0开始 ps：向上移动，若是到最后一行，建议用getLastRowNum+1,否则根据源码计算移动后的
// 建议用getLastRowNum会比实际小1,源码位置HSSFSheet 905
oldSheet.shiftRows(titleRowNums, oldSheet.getLastRowNum()+1, -titleRowNums);
// 获得目标sheet总行数，合并完第一份文件后，新文件会多出因移动多出的空白行，因此从第二份文件合并开始，每次都要从新文件的结束行索引减去空白行
int newRowNums = newSheet.getLastRowNum()-(titleRowNums*deleteHeadRowRatio);
System.out.println(fromExcelPath + "-----" + titleRowNums + "-----" + oldSheet.getLastRowNum()+ "-----" + oldSheet.getPhysicalNumberOfRows()
+ "-----" + newRowNums);
// 进行复制操作
POIUtilsForMergeExcel.copySheet(newRowNums+1,
newExcelCreat, oldSheet, newSheet, titleRowNums, cellTypeKey);
}
in.close();
// 从第二份开始
deleteHeadRowRatio = 1;
}
newExcelInputstream.close();
// 合并后的文件写入生成
fileOut = new FileOutputStream(originFilePath);
newExcelCreat.write(fileOut);
fileOut.flush();
fileOut.close();
// 清空cellstyle缓存数据
CellStyleCache.destroyCellStyle(cellTypeKey);
// 删除各个源文件
Iterator iterator = filePathList.listIterator();
while (iterator.hasNext()) {
File file = new File(String.valueOf(iterator.next()));
if (file.exists()) {
file.delete();
}
iterator.remove();
}
}catch (Exception e){
throw e;
} finally {
// 关闭资源
try {
if (newExcelInputstream != null) {
newExcelInputstream.close();
newExcelInputstream = null;
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (in != null) {
in.close();
in = null;
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (fileOut != null) {
fileOut.close();
fileOut = null;
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 清除合并的单元格(删除纵列)
* @param sheet
* @param startRow 开始行 从0开始
* @param endRow 结束行
*/
public static void removeMergedColRegion(HSSFSheet sheet, int startRow, int endRow) {
// 获取所有的单元格
int sheetMergeCount = sheet.getNumMergedRegions();
// 用于保存要移除的那个单元格序号
int index = 0;
for (int i = 0; i < sheetMergeCount; i++) {
// 获取第i个单元格
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (firstRow >= startRow && lastRow <= endRow) {
index = i;
// 移除合并单元格
sheet.removeMergedRegion(index);
break;
}
}
}
/**
* 删除合并单元格（横向合并）,删除行内所有合并
*
* @param sheet
* @param startRow 开始行
* @param endRow 开始行
* @param column 合并开始列
*/
public static void removeMergedRowRegion(HSSFSheet sheet, int startRow, int endRow, int column) {
for (int j = startRow; j <= endRow; j++) {
// 获取所有的合并单元格
int sheetMergeCount = sheet.getNumMergedRegions();
// 用于保存要移除的那个单元格序号
int index = 0;
for (int i = 0; i < sheetMergeCount; i++) {
// 获取第i个单元格
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (firstRow >= startRow && lastRow <= endRow) {
if (column >= firstColumn && column <= lastColumn) {
index = i;
// 移除合并单元格
sheet.removeMergedRegion(index);
break;
}
}
}
}
}
/**
* copyCell单元格样式复制
*
* @param wb 目标excel对象
* @param fromStyle 源单元格样式对象
* @param toStyle 目标单元格样式对象
* @return
* @throws
*/
public static void copyCellStyle(HSSFWorkbook wb, HSSFCellStyle fromStyle,
HSSFCellStyle toStyle) {
/**
* cloneStyleFrom 说明:如果各位的源文件数据量小,并且没有过多样式设定,可以使用此方法 优点:效率高,代码少
* 缺点:不知道是POI自身BUG
* ,或者是这次使用的版本低,截止目前,使用的POI版本也比较新,数据量大情况下,使用cloneStyleFrom后
* ,目标文件不可进行编辑,打开目标文件提示:本工作簿不能再使用其它字体.只能使用以下方法得以解决
*/
toStyle.cloneStyleFrom(fromStyle);
/**
* 以下代码,缺点:效率慢,如果数据量过大,也容易报错,因为createCellStyle次数太多
*/
// 对齐方式
/*toStyle.setAlignment(fromStyle.getAlignmentEnum());
// 边框和边框颜色
toStyle.setBorderBottom(fromStyle.getBorderBottomEnum());
toStyle.setBorderLeft(fromStyle.getBorderLeftEnum());
toStyle.setBorderRight(fromStyle.getBorderRightEnum());
toStyle.setBorderTop(fromStyle.getBorderTopEnum());
toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
toStyle.setDataFormat(fromStyle.getDataFormat()); // 数据格式
XSSFFont font = wb.createFont();
font.setFontHeightInPoints(fromStyle.getFont().getFontHeightInPoints());// 字体大小
font.setBold(fromStyle.getFont().getBold());// 加粗
font.setFontName(fromStyle.getFont().getFontName());// 字体
toStyle.setFont(font);
toStyle.setHidden(fromStyle.getHidden());// 是否隐藏
toStyle.setIndention(fromStyle.getIndention());// 首行缩进
toStyle.setLocked(fromStyle.getLocked()); // 是否锁定
toStyle.setRotation(fromStyle.getRotation());// 旋转
toStyle.setVerticalAlignment(fromStyle.getVerticalAlignmentEnum()); // 垂直对齐
toStyle.setWrapText(fromStyle.getWrapText()); // 文本换行
*/
}
/**
* mergeSheetAllRegion
*
* @description 确定需要合并单元格的位置并进行合并
* @param newRowNums 目标sheet总行数
* @param fromSheet 源sheet对象
* @param toSheet 目标sheet对象
* @return
* @throws
*/
public static void mergeSheetAllRegion(int fromRowNums, HSSFSheet fromSheet,
HSSFSheet toSheet, int titleRowNums) {
// 获取合并的单元格个数
int num = fromSheet.getNumMergedRegions();
CellRangeAddress cellR = null;
for (int i = 0; i < num; i++) {
// 获取第一个合并的单元格
cellR = fromSheet.getMergedRegion(i);
// 源位置确定
int firstRow = cellR.getFirstRow();
int lastTow = cellR.getLastRow();
// 跳过表头行号
if (firstRow > titleRowNums) {
// 目标位置确定
cellR.setFirstRow(firstRow + fromRowNums);
cellR.setLastRow(lastTow + fromRowNums);
// 合并
toSheet.addMergedRegion(cellR);
}
}
}
/**
* copyCell
* @description 复制单元格
* @param wb 目标excel对象
* @param fromCell 源单元格对象
* @param toCell 目标单元格对象
* @param cellTypeKey 缓存的celltype map key
* @return
* @throws
*/
public static void copyCell(HSSFWorkbook wb, HSSFCell fromCell,
HSSFCell toCell, String cellTypeKey) {
// 创建样式对象
HSSFCellStyle newstyle = CellStyleCache.getCellStyle(fromCell.getCellStyle(), cellTypeKey);
if (HxUtils.isEmpty(newstyle)) {
newstyle = wb.createCellStyle();
// 样式复制,样式这块效率最慢,一旦数据量大,样式多,容易出错
copyCellStyle(wb, fromCell.getCellStyle(), newstyle);
CellStyleCache.addCellStyle(newstyle, cellTypeKey);
}
// 设置自动换行，这时行高会自适应，但是不能设置行高，如果设置行高，就会导致自适应失效
newstyle.setWrapText(true);
// 给定样式
toCell.setCellStyle(newstyle);
// 评论
if (fromCell.getCellComment() != null) {
toCell.setCellComment(fromCell.getCellComment());
}
// 不同数据类型处理
CellType fromCellType = fromCell.getCellTypeEnum();
toCell.setCellType(fromCellType);
if (fromCellType == CellType.NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(fromCell)) {
toCell.setCellValue(fromCell.getDateCellValue());
} else {
toCell.setCellValue(fromCell.getNumericCellValue());
}
} else if (fromCellType == CellType.STRING) {
toCell.setCellValue(fromCell.getRichStringCellValue());
} else if (fromCellType == CellType.BLANK) {
} else if (fromCellType == CellType.BOOLEAN) {
toCell.setCellValue(fromCell.getBooleanCellValue());
} else if (fromCellType == CellType.ERROR) {
toCell.setCellErrorValue(fromCell.getErrorCellValue());
} else if (fromCellType == CellType.FORMULA) {
toCell.setCellFormula(fromCell.getCellFormula());
} else {
}
}
/**
* copyRow
*
* @description 复制行
* @param wb 目标excel对象
* @param oldRow 源行对象
* @param toRow 目标行对象
* @param cellTypeKey cellType map key
* @return
* @throws
*/
public static void copyRow(HSSFWorkbook wb, HSSFRow oldRow, HSSFRow toRow, String cellTypeKey) {
for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
HSSFCell tmpCell = (HSSFCell) cellIt.next();
// 创建单元格
HSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
// 单元格复制
copyCell(wb, tmpCell, newCell, cellTypeKey);
}
}
/**
* copySheet
* @description sheet复制
* @param newRowNums 目标sheet总行数
* @param wb 目标excel对象
* @param fromSheet 源sheet对象
* @param toSheet 目标sheet对象
* @param cellTypeKey cellType map Key
* @return
* @throws
*/
public static void copySheet(int newRowNums, HSSFWorkbook wb,
HSSFSheet fromSheet, HSSFSheet toSheet,
int titleRowNums, String cellTypeKey) {
// 先确定需要合并单元格的位置,并进行合并
mergeSheetAllRegion(newRowNums, fromSheet, toSheet, titleRowNums);
// 设置列宽,可以获取最后一行读取所有列
for (int i = 0; i <= fromSheet.getRow(fromSheet.getLastRowNum())
.getLastCellNum(); i++) {
toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
}
// 开始复制每行数据及样式
// int startLineNum = 1;
for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
HSSFRow oldRow = (HSSFRow) rowIt.next();
/** // 也可以在复制行的时候，跳过表头
if (titleRowNums >= startLineNum){
startLineNum ++;
continue;
}**/
// 创建行
HSSFRow newRow = toSheet.createRow(oldRow.getRowNum() + newRowNums);
// 行复制开始
copyRow(wb, oldRow, newRow, cellTypeKey);
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
