【Java / POI / Excel】使用POI实现Excel文档的拆分、合并、复制Sheet页面（包含样式）复制不成功你来砍我

坤你叽哇

已于 2024-01-10 09:53:14 修改

阅读量1.5k

点赞数 15

文章标签： excel java 开发语言 springboot

于 2024-01-09 20:43:36 首次发布

本文链接：https://blog.csdn.net/weixin_44470942/article/details/135488853

版权

我在网上找了很多poi的资料，很多教程只是复制了整个sheet页面的内容，但是样式没有进行复制，且excel的分割、合并又依赖sheet页面的复制，于是自行探索得到如下excel的工具类：

实现Excel文档的拆分，将指定Sheet页面拆分成单独的一个Excel文件
两个Excel文件合并成为一个Excel文件
完全复制Sheet页面

POI版本信息

我使用的POI的依赖及版本信息如下，不知道以后高版本会不会内置这些功能，如下工具类还需要引入hutool，不会还有人没有过吧？不会吧？

        <!-- excel操作工具poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.22</version>
        </dependency>

工具类代码（适用于.xlsx和xls）

package com.clewm.vilatools.tools.excel_split_merge_2.utils;

import cn.hutool.core.collection.ListUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
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;

public class ExcelSplitMergeUtil {

    /**
     * 获得所有sheet的名字
     * @param workbook
     * @return
     */
    public static List<String> getNameOfSheet(Workbook workbook){
        ArrayList<String> sheetNames = new ArrayList<>();
        try {
            workbook.sheetIterator().forEachRemaining(sheet -> {
                sheetNames.add(sheet.getSheetName());
            });
        }catch (Exception e){
            e.printStackTrace();
        }
        return sheetNames;
    }

    /**
     * 将两个excel表格
     * @param excel_A_FilePath 第1个excel的路径，例如 ./xxx.xlsx
     * @param excel_B_FilePath 第2个excel的路径
     * @param outputFilePath 合并后的文件路径
     */
    public static boolean merge(String excel_A_FilePath,String excel_B_FilePath,List<String> sheet_A_Name,List<String> sheet_B_Name,String outputFilePath){
        List<String> pathList = new ArrayList<>();
        pathList.add(excel_A_FilePath);
        pathList.add(excel_B_FilePath);
        //将所有类型的尽调excel文件合并成一个excel文件
        XSSFWorkbook newExcelCreat = new XSSFWorkbook();
        try {

            for (int i = 0; i < 2; i++) {//遍历每个源excel文件，fileNameList为源文件的名称集合
                if(i == 0){
                    InputStream in = new FileInputStream(pathList.get(i));
                    ZipSecureFile.setMinInflateRatio(-1.0d);
                    XSSFWorkbook fromExcel = new XSSFWorkbook(in);
                    for (String sheetName : sheet_A_Name) {
                        XSSFSheet oldSheet = fromExcel.getSheet(sheetName);
                        XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                        copySheet(newExcelCreat, oldSheet, newSheet);
                    }
                }

                if(i == 1){
                    InputStream in = new FileInputStream(pathList.get(i));
                    ZipSecureFile.setMinInflateRatio(-1.0d);
                    XSSFWorkbook fromExcel = new XSSFWorkbook(in);
                    for (String sheetName : sheet_B_Name) {
                        XSSFSheet oldSheet = fromExcel.getSheet(sheetName);
                        XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                        copySheet(newExcelCreat, oldSheet, newSheet);
                    }
                }
            }
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        } catch (IOException e1) {
            e1.printStackTrace();
        }

        String allFileName = outputFilePath;
        try {
            FileOutputStream fileOut = new FileOutputStream(allFileName);
            newExcelCreat.write(fileOut);
            fileOut.flush();
            fileOut.close();
            log.info("文件{},{}合并成功",excel_A_FilePath,excel_B_FilePath);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 将一个excel文件中的指定sheet页面单独拆分出来
     * @param splitSheetNames
     * @param excelPath
     */
    public static boolean split(List<String> splitSheetNames, String excelPath,String outputFilePath) {
        //将所有类型的尽调excel文件合并成一个excel文件
        XSSFWorkbook newExcelCreat = new XSSFWorkbook();
        try {
            InputStream in = new FileInputStream(excelPath);
            ZipSecureFile.setMinInflateRatio(-1.0d);
            XSSFWorkbook fromExcel = new XSSFWorkbook(in);

            for (String sheetName : splitSheetNames) {
                XSSFSheet oldSheet = fromExcel.getSheet(sheetName);
                XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                copySheet(newExcelCreat, oldSheet, newSheet);
            }
        } catch (Exception e1) {
            e1.printStackTrace();
        }

        try {
            FileOutputStream fileOut = new FileOutputStream(outputFilePath);
            newExcelCreat.write(fileOut);
            fileOut.flush();
            fileOut.close();
            log.info("文件{}拆分成功",excelPath);
            return true;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {
        toStyle.cloneStyleFrom(fromStyle);//此一行代码搞定
    }
    public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {//合并单元格
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }

    public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle=wb.createCellStyle();
        copyCellStyle(fromCell.getCellStyle(), newstyle);
        //toCell.setEncoding(fromCell.getEncoding());
        //样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        CellType fromCellType = fromCell.getCellType();

        // XSSF类型时不需要，HSSF时需要
//        toCell.setCellType(fromCellType);

        if (fromCellType == CellType.NUMERIC) {
            if (DateUtil.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 {
        }

    }

    public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow){
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext();) {
            XSSFCell tmpCell = (XSSFCell) cellIt.next();
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb,tmpCell, newCell);
        }
    }
    
    public static void copySheet(XSSFWorkbook wb,XSSFSheet fromSheet, XSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        //设置列宽
        if(fromSheet.getFirstRowNum() < 0) return;
        for (int i = 0; i <= fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum(); i++) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }
        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
            XSSFRow oldRow = (XSSFRow) rowIt.next();
            XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
            copyRow(wb, oldRow, newRow);
        }
    }


}

演示

文章篇幅有限，只演示excel的合并。

excel_a.xlsx中A页面的sheet的内容如下，已修改单元格的字体、大小、行高、列宽、边框、颜色等属性：

excel_b.xlsx中【B页面】sheet的内容如下：

单元测试写个代码：

    @Test
    public void testMerge() {
        boolean merge = merge(
                "/Users/clewm/Desktop/excel_a.xlsx",
                "/Users/clewm/Desktop/excel_b.xlsx",
                ListUtil.of("A页面"),
                ListUtil.of("B页面"),
                "/Users/clewm/Desktop/excel_merge.xlsx"
        );
        System.out.println(merge ? "合并成功！" : "合并失败！");
    }

打开合并后的文件，发现已完成合并，并且保留了各种属性和样式：