Java Excel 复制单元格 poi Excel 复制单元格 Java Excel 复制行 Java Excel 复制 sheet 页 poi excel copy

        Java Excel 复制单元格 poi Excel 复制单元格 Java Excel 复制行 Java Excel 复制 sheet 页 

一、前言

        1、本文记录 poi excel 实现 单元格cell的复制 、 row行复制 、 sheet 页复制 ；一般应用的场景：excel 模板中 可能需要进行的复制操作。

        2、poi 版本：3.16

        3、本文参考代码：poi操作excel，复制sheet，复制行，复制单元格_PlayAround的博客-CSDN博客_poi 复制单元格

二、示例代码

        1、创建类：ExcelCellCopy ，主要有 copyCellStyle 、 copyCell 、copyRow 、copySheet ，分别对应作用是： 复制单元格样式、复制单元格、复制行 、 复制 sheet 页面 等功能。

        2、具体代码如下：

package poi.test;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import java.util.Iterator;

/**
 * Description: Excel 单元格 测试
 * @author w
 * @version 1.0
 * @date 2022/8/23 14:14
 * @see https://blog.csdn.net/wutbiao/article/details/8696446 【源码这里】
 * 
  */
public class ExcelCellCopy {


        /**
         * @Description:  复制单元格 cell 样式
         * @param workbook
         * @param fromStyle
         * @param toStyle
         * @return  void
         * @version v1.0
         * @author wu
         * @date 2022/8/24 22:39
         */
        public static void copyCellStyle(Workbook workbook, CellStyle fromStyle, CellStyle toStyle) {
            // 水平垂直对齐方式
            toStyle.setAlignment(fromStyle.getAlignment());
            toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());

            //边框和边框颜色
            toStyle.setBorderBottom(fromStyle.getBorderBottom());
            toStyle.setBorderLeft(fromStyle.getBorderLeft());
            toStyle.setBorderRight(fromStyle.getBorderRight());
            toStyle.setBorderTop(fromStyle.getBorderTop());
            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.getFillPattern());

//    toStyle.setFont(fromStyle.getFont(null)); // 没有提供get 方法
            if (fromStyle instanceof HSSFCellStyle) {
                // 处理字体获取：03版 xls
                HSSFCellStyle style = (HSSFCellStyle) fromStyle;
                toStyle.setFont(style.getFont(workbook));
            } else if (fromStyle instanceof XSSFCellStyle) {
                // 处理字体获取：07版以及之后 xlsx
                XSSFCellStyle style = (XSSFCellStyle) fromStyle;
                toStyle.setFont(style.getFont());
            }

            toStyle.setHidden(fromStyle.getHidden());
            toStyle.setIndention(fromStyle.getIndention());//首行缩进
            toStyle.setLocked(fromStyle.getLocked());
            toStyle.setRotation(fromStyle.getRotation());//旋转
            toStyle.setWrapText(fromStyle.getWrapText());
        }

        /**
         * @Description: 复制 sheet 页
         * @param wb
         * @param fromSheet
         * @param toSheet
         * @param copyValueFlag
         * @return  void
         * @version v1.0
         * @author wu
         * @date 2022/8/24 22:40
         */
        public static void copySheet(Workbook wb, Sheet fromSheet, Sheet toSheet,  boolean copyValueFlag) {
            //合并区域处理
            mergerRegion(fromSheet, toSheet);
            Iterator<Row> rowIterator = fromSheet.rowIterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Row newRow = toSheet.createRow(row.getRowNum());
                copyRow(wb, row, newRow, copyValueFlag);
            }
        }

        /**
         * @Description: 复制 行row 数据
         * @return  void
         * @version v1.0
         * @author wu
         * @date 2022/8/24 22:40
         */
        public static void copyRow(Workbook wb, Row fromRow, Row toRow, boolean copyValueFlag) {
            short lastCellNum = fromRow.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                Cell fromCel = fromRow.getCell(i);
                Cell  destCell = toRow.createCell(i);
                copyCell(wb, fromCel, destCell, copyValueFlag);
            }
        }


        /**
         * @Description: 复制原有sheet的合并单元格到新创建的sheet
         * <br> 处理 sheet 页的合并区域
         * @param fromSheet
         * @param toSheet
         * @return  void
         * @version v1.0
         * @author wu
         * @date 2022/8/24 22:43
         */
        public static void mergerRegion(Sheet fromSheet, Sheet toSheet) {
            int sheetMergerCount = fromSheet.getNumMergedRegions();
            for (int i = 0; i < sheetMergerCount; i++) {
                CellRangeAddress mergedRegion = fromSheet.getMergedRegion(i);
                toSheet.addMergedRegion(mergedRegion);
            }
        }

        /**
         * @Description: 复制 单元格 cell
         * @param wb
         * @param srcCell
         * @param distCell
         * @param copyValueFlag  是否包含内容 - true 包含内容复制
         * @return  void
         * @version v1.0
         * @author wu
         * @date 2022/8/24 22:43
         */
        public static void copyCell(Workbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {

            CellStyle newstyle= wb.createCellStyle();
            copyCellStyle(wb,srcCell.getCellStyle(), newstyle);
//            distCell.setEncoding(srcCell.getEncoding());
            // 复制样式
            distCell.setCellStyle(newstyle);
            //评论
            if (srcCell.getCellComment() != null) {
                distCell.setCellComment(srcCell.getCellComment());
            }

            // 不同数据类型处理
            int srcCellType = srcCell.getCellType();
            distCell.setCellType(srcCellType);
            if (copyValueFlag) {
                if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
                    if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
                        distCell.setCellValue(srcCell.getDateCellValue());
                    } else {
                        distCell.setCellValue(srcCell.getNumericCellValue());
                    }
                } else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
                    distCell.setCellValue(srcCell.getRichStringCellValue());
                } else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
                    // nothing21
                } else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
                    distCell.setCellValue(srcCell.getBooleanCellValue());
                } else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
                    distCell.setCellErrorValue(srcCell.getErrorCellValue());
                } else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
                    distCell.setCellFormula(srcCell.getCellFormula());
                } else { // nothing29
                }
            }
        }
}

        3、测试代码如下：

package poi.test;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

/**
 * Description: excel 单元复制
 * @author wu
 * @version 1.0
 * @date 2022/8/24 22:48
 * @see https://blog.csdn.net/wutbiao/article/details/8696446
 */
public class ExcelCopyTest {

    public static void main(String[] args) throws Exception {
          excelCellCopy(); // excel 单元格复制
//        excelRowCopy(); // excel 行数据复制
//        excelSheetCopy(); // excel sheet 页数据复制
    }


    /**
     * @Description: excel 单元格复制
     * @return  void
     * @version v1.0
     * @author wu
     * @date 2022/8/24 22:53
     */
    private static void excelCellCopy() throws IOException {
        Workbook workbook = readTemplate();
        Sheet sheet = workbook.getSheet("template");
        Sheet sheet2 = workbook.getSheet("第一页");
        // 复制 普通单元格
        Row srcRow = sheet.getRow(0);
        Cell srcCell = srcRow.getCell(0);

        Row destRow = sheet2.createRow(10);
        Cell destCell = destRow.createCell(0);

        ExcelCellCopy.copyCell(workbook,srcCell,destCell,true);

        // 复制 合并的单元格
        Row srcRow2 = sheet.getRow(2);
        Cell srcCell2 = srcRow2.getCell(0);

        sheet2.addMergedRegion(new CellRangeAddress(12,12,0,2));
        Row destRow2 = sheet2.createRow(12);
        Cell destCell2 = destRow2.createCell(0);

        // remark: 合并 背景色的单元格 有问题
        ExcelCellCopy.copyCell(workbook,srcCell2,destCell2,true);


        // 写出到文件
        String  fileName = "D:\\文件导出-cell单元格复制 " +System.currentTimeMillis()+".xlsx" ;
        FileOutputStream out = new FileOutputStream(new File(fileName));
        workbook.write(out);

    }

    private static Workbook readTemplate() {
        // 读取模板
        InputStream inputStream = ExcelCopyTest.class.getResourceAsStream("/excel模板.xlsx");
//        System.out.println("inputStream :" + inputStream);
        Workbook workbook = null ;
        try {
            workbook= WorkbookFactory.create(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return workbook ;
    }


    /**
     * @Description: excel row 行数据复制
     * @return  void
     * @version v1.0
     * @author wu
     * @date 2022/8/24 22:54
     */
    private static void excelRowCopy() throws IOException {
        Workbook workbook = readTemplate();
        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(0);
        int start = 4 ;
        for (int i = 0; i < 5; i++) {
            Row destRow = sheet.createRow(start + i);
            ExcelCellCopy.copyRow(workbook,row,destRow ,true);
        }

        // 写出到文件
        String  fileName = "D:\\文件导出-row 行复制 " +System.currentTimeMillis()+".xlsx" ;
        FileOutputStream out = new FileOutputStream(new File(fileName));
        workbook.write(out);
    }


    /**
     * @Description: excel sheet 页复制
     * @return  void
     * @version v1.0
     * @author wu
     * @date 2022/8/24 22:54
     */
    private static void excelSheetCopy() throws IOException {
        Workbook workbook = readTemplate();
        Sheet srcSheet = workbook.getSheet("第一页");
        Sheet destSheet = workbook.createSheet("第一个复制");

        // 复制普通 sheet 页
        ExcelCellCopy.copySheet(workbook,srcSheet,destSheet,true);

        // 复制带有合并单元格个sheet页
        Sheet srcSheet2 = workbook.getSheet("template");
        Sheet destSheet2 = workbook.createSheet("template-copy");
        ExcelCellCopy.copySheet(workbook,srcSheet2,destSheet2,true);

        // 写出到文件
        String  fileName = "D:\\文件导出- sheet 页复制 " +System.currentTimeMillis()+".xlsx" ;
        FileOutputStream out = new FileOutputStream(new File(fileName));
        workbook.write(out);
    }


}

 

        4、示例中 excel模板.xlsx 文件，参考如下图：

 

 

三、总结

        1、本文使用poi的相关api，实现 excel 单元格的复制功能，应用场景在 excel 模板相关的操作中 会使用到。

        2、示例中是全部代码，直接复制粘贴即可使用~ excel 模板，根据实际需求进行创建即可。

        3、复制后的效果如下 ---- 几乎一模一样~