目录
1 Maven依赖
<!-- easyExcel Excel文档处理工具 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.8</version>
</dependency>
<!-- hutool工具包 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.5.7</version>
</dependency>2 实现代码
/**
* 行复制(多行)
*
* @param workbook 文档对象
* @param sheet sheet对象
* @param fromRowIndex
* @param toRowIndex
* @param copyRowNum 复制行数
*/
public static void copyRow(Workbook workbook, Sheet sheet, int fromRowIndex, int toRowIndex, int copyRowNum, boolean insertFlag
, Integer colNum) {
for (int i = 0; i < copyRowNum; i++) {
Row fromRow = sheet.getRow(fromRowIndex + i);
Row toRow = sheet.getRow(toRowIndex + i);
if (insertFlag) {
//复制行超出原有sheet页的最大行数时，不需要移动直接插入
if (toRowIndex + i <= sheet.getLastRowNum()) {
//先移动要插入的行号所在行及之后的行
sheet.shiftRows(toRowIndex + i, sheet.getLastRowNum(), 1, true, false);
}
//然后再插入行
toRow = sheet.createRow(toRowIndex + i);
//设置行高
toRow.setHeight(fromRow.getHeight());
}
for (int colIndex = 0; colIndex < (colNum != null ? colNum : fromRow.getLastCellNum()); colIndex++) {
Cell tmpCell = fromRow.getCell(colIndex);
if (tmpCell == null) {
tmpCell = fromRow.createCell(colIndex);
if (colIndex != 0) {
copyCell(workbook, fromRow.createCell(colIndex - 1), tmpCell);
}
}
Cell newCell = toRow.createCell(colIndex);
copyCell(workbook, tmpCell, newCell);
}
}
//获取合并单元格
List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
Map<Integer, List<CellRangeAddress>> rowCellRangeAddressMap = CollUtil.isNotEmpty(cellRangeAddressList) ? cellRangeAddressList.stream().collect(Collectors.groupingBy(x ->
x.getFirstRow())) : new HashMap<>();
//获取形状（线条）
XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();
List<XSSFShape> shapeList = new ArrayList<>();
Map<Integer, List<XSSFShape>> rowShapeMap = new HashMap<>();
if (drawing != null) {
shapeList = drawing.getShapes();
rowShapeMap = shapeList.stream().filter(x -> x.getAnchor() != null)
.collect(Collectors.groupingBy(x -> ((XSSFClientAnchor) x.getAnchor()).getRow1()));
}
List<XSSFShape> insertShapeList = new ArrayList<>();
for (int i = 0; i < copyRowNum; i++) {
Row toRow = sheet.getRow(toRowIndex + i);
//复制合并单元格
List<CellRangeAddress> rowCellRangeAddressList = rowCellRangeAddressMap.get(fromRowIndex + i);
if (CollUtil.isNotEmpty(rowCellRangeAddressList)) {
for (CellRangeAddress cellRangeAddress : rowCellRangeAddressList) {
CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(), (toRow.getRowNum() +
(cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress
.getFirstColumn(), cellRangeAddress.getLastColumn());
sheet.addMergedRegionUnsafe(newCellRangeAddress);
}
}
//复制形状（线条）
List<XSSFShape> rowShapeList = rowShapeMap.get(fromRowIndex + i);
if (CollUtil.isNotEmpty(rowShapeList)) {
for (XSSFShape shape : rowShapeList) {
//复制描点
XSSFClientAnchor fromAnchor = (XSSFClientAnchor) shape.getAnchor();
XSSFClientAnchor toAnchor = new XSSFClientAnchor();
toAnchor.setDx1(fromAnchor.getDx1());
toAnchor.setDx2(fromAnchor.getDx2());
toAnchor.setDy1(fromAnchor.getDy1());
toAnchor.setDy2(fromAnchor.getDy2());
toAnchor.setRow1(toRow.getRowNum());
toAnchor.setRow2(toRow.getRowNum() + fromAnchor.getRow2() - fromAnchor.getRow1());
toAnchor.setCol1(fromAnchor.getCol1());
toAnchor.setCol2(fromAnchor.getCol2());
//复制形状
if (shape instanceof XSSFConnector) {
copyXSSFConnector((XSSFConnector) shape, drawing, toAnchor);
} else if (shape instanceof XSSFSimpleShape) {
copyXSSFSimpleShape((XSSFSimpleShape) shape, drawing, toAnchor);
}
}
}
}
}
/**
* 复制XSSFSimpleShape类
*
* @param fromShape
* @param drawing
* @param anchor
* @return
*/
public static XSSFSimpleShape copyXSSFSimpleShape(XSSFSimpleShape fromShape, XSSFDrawing drawing, XSSFClientAnchor anchor) {
XSSFSimpleShape toShape = drawing.createSimpleShape(anchor);
CTShape ctShape = fromShape.getCTShape();
CTShapeProperties ctShapeProperties = ctShape.getSpPr();
CTLineProperties lineProperties = ctShapeProperties.isSetLn() ? ctShapeProperties.getLn() : ctShapeProperties.addNewLn();
CTPresetLineDashProperties dashStyle = lineProperties.isSetPrstDash() ? lineProperties.getPrstDash() : CTPresetLineDashProperties.Factory.newInstance();
STPresetLineDashVal.Enum dashStyleEnum = dashStyle.isSetVal() ? dashStyle.getVal() : STPresetLineDashVal.Enum.forInt(1);
CTSolidColorFillProperties fill = lineProperties.isSetSolidFill() ? lineProperties.getSolidFill() : lineProperties.addNewSolidFill();
CTSRgbColor rgb = fill.isSetSrgbClr() ? fill.getSrgbClr() : CTSRgbColor.Factory.newInstance();
// 设置形状类型
toShape.setShapeType(fromShape.getShapeType());
// 设置线宽
toShape.setLineWidth(lineProperties.getW() * 1.0 / Units.EMU_PER_POINT);
// 设置线的风格
toShape.setLineStyle(dashStyleEnum.intValue() - 1);
// 设置线的颜色
byte[] rgbBytes = rgb.getVal();
if (rgbBytes == null) {
toShape.setLineStyleColor(0, 0, 0);
} else {
toShape.setLineStyleColor(rgbBytes[0], rgbBytes[1], rgbBytes[2]);
}
return toShape;
}
/**
* 复制XSSFConnector类
*
* @param fromShape
* @param drawing
* @param anchor
* @return
*/
public static XSSFConnector copyXSSFConnector(XSSFConnector fromShape, XSSFDrawing drawing, XSSFClientAnchor anchor) {
XSSFConnector toShape = drawing.createConnector(anchor);
CTConnector ctConnector = fromShape.getCTConnector();
CTShapeProperties ctShapeProperties = ctConnector.getSpPr();
CTLineProperties lineProperties = ctShapeProperties.isSetLn() ? ctShapeProperties.getLn() : ctShapeProperties.addNewLn();
CTPresetLineDashProperties dashStyle = lineProperties.isSetPrstDash() ? lineProperties.getPrstDash() : CTPresetLineDashProperties.Factory.newInstance();
STPresetLineDashVal.Enum dashStyleEnum = dashStyle.isSetVal() ? dashStyle.getVal() : STPresetLineDashVal.Enum.forInt(1);
CTSolidColorFillProperties fill = lineProperties.isSetSolidFill() ? lineProperties.getSolidFill() : lineProperties.addNewSolidFill();
CTSRgbColor rgb = fill.isSetSrgbClr() ? fill.getSrgbClr() : CTSRgbColor.Factory.newInstance();
// 设置形状类型
toShape.setShapeType(fromShape.getShapeType());
// 设置线宽
toShape.setLineWidth(lineProperties.getW() * 1.0 / Units.EMU_PER_POINT);
// 设置线的风格
toShape.setLineStyle(dashStyleEnum.intValue() - 1);
// 设置线的颜色
byte[] rgbBytes = rgb.getVal();
if (rgbBytes == null) {
toShape.setLineStyleColor(0, 0, 0);
} else {
toShape.setLineStyleColor(rgbBytes[0], rgbBytes[1], rgbBytes[2]);
}
return toShape;
}
/**
* 复制单元格
*
* @param srcCell
* @param distCell
*/
public static void copyCell(Workbook workbook, Cell srcCell, Cell distCell) {
CellStyle newStyle = workbook.createCellStyle();
copyCellStyle(srcCell.getCellStyle(), newStyle, workbook);
//样式
distCell.setCellStyle(newStyle);
//设置内容
CellType srcCellType = srcCell.getCellTypeEnum();
distCell.setCellType(srcCellType);
if (srcCellType == NUMERIC) {
if (DateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == CellType.STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == CellType.BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == CellType.ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == CellType.FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else {
}
}
/**
* 复制一个单元格样式到目的单元格样式
*
* @param fromStyle
* @param toStyle
*/
public static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle, Workbook workbook) {
//水平垂直对齐方式
toStyle.setAlignment(fromStyle.getAlignmentEnum());
toStyle.setVerticalAlignment(fromStyle.getVerticalAlignmentEnum());
//边框和边框颜色
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());
//背景和前景
if (fromStyle instanceof XSSFCellStyle) {
XSSFCellStyle xssfToStyle = (XSSFCellStyle) toStyle;
xssfToStyle.setFillBackgroundColor(((XSSFCellStyle) fromStyle).getFillBackgroundColorColor());
xssfToStyle.setFillForegroundColor(((XSSFCellStyle) fromStyle).getFillForegroundColorColor());
} else {
toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
}
toStyle.setDataFormat(fromStyle.getDataFormat());
toStyle.setFillPattern(fromStyle.getFillPatternEnum());
if (fromStyle instanceof XSSFCellStyle) {
toStyle.setFont(((XSSFCellStyle) fromStyle).getFont());
} else if (fromStyle instanceof HSSFCellStyle) {
toStyle.setFont(((HSSFCellStyle) fromStyle).getFont(workbook));
}
toStyle.setHidden(fromStyle.getHidden());
//首行缩进
toStyle.setIndention(fromStyle.getIndention());
toStyle.setLocked(fromStyle.getLocked());
//旋转
toStyle.setRotation(fromStyle.getRotation());
toStyle.setWrapText(fromStyle.getWrapText());
}3 调试代码
/**
* 测试复制行（样式、合并单元格、形状）
*/
@Test
public void testCopyRow() {
try {
//读文件
ClassPathResource classPathResource = new ClassPathResource("/doc/excel/copyRowTemplate.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(classPathResource.getFile());
Sheet sheet = workbook.getSheetAt(0);
File file = new File("D:/easyexcel/testCopyRow.xlsx");
FileUtil.createNewFile(file);
//复制前两行
POIExcelUtil.copyRow(workbook,sheet,0,2,2,true,null);
workbook.write(new FileOutputStream(file));
} catch (Exception e) {
e.printStackTrace();
}
}4 模板内容
5 调试结果
注：
（1） 支持复制文本内容、单元格样式、合并单元格、形状信息。复制形状只支持形状类型的复制，不支持线条类型、宽度、颜色的复制。
（2）源码请查看Gitee。
xudongbase: 主要是项目中可以用到的共通方法
5866
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
