poi 多个excel合并

81 阅读 0 评论 54 点赞

我是靠谱客的博主勤劳香氛，最近开发中收集的这篇文章主要介绍poi 多个excel合并，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author wongH
 * @date 2020/6/9 14:16
 * 合并多个 Excel 文件 到一个sheet
 * 样式丢失
 */
public class POIUtils1 {

    private static final int XLS_MAX_ROWS = 65535;
    private static final int XLSX_MAX_ROWS = 1048575;

    /**
     * 合并多个 Excel 文件
     *
     * @param mergedFile 合并后的文件
     * @param files      待合并的文件
     * @param isXlsx     合并文件类型是否是 xlsx
     * @throws IOException 合并异常
     */
    public static void mergeExcel(File mergedFile, List<File> files, boolean isXlsx) throws IOException {
        if (mergedFile == null || files == null) {
            return;
        }
        try (Workbook mergedWorkbook = isXlsx ? new SXSSFWorkbook() : new HSSFWorkbook();
             FileOutputStream out = new FileOutputStream(mergedFile)) {
            Sheet newSheet = mergedWorkbook.createSheet("安全");
            int start = 0;
            for (File file : files) {
                if (file == null) {
                    continue;
                }
                try (Workbook oldWorkbook = isXlsx ? new XSSFWorkbook(new FileInputStream(file)) : new HSSFWorkbook(new FileInputStream(file))) {
                    /*int oldSheetSize = oldWorkbook.getNumberOfSheets();
                    for (int i = 0; i < oldSheetSize; i++) {
                        Sheet oldSheet = oldWorkbook.getSheetAt(i);
                        int oldRowSize = oldSheet.getLastRowNum();
                        for (int j = 0; j <= oldRowSize; j++) {
                            if (start == (isXlsx ? XLSX_MAX_ROWS : XLS_MAX_ROWS)) {
                                newSheet = mergedWorkbook.createSheet();
                                start = newSheet.getLastRowNum();
                            }
                            Row oldRow = oldSheet.getRow(j);
                            Row newRow = newSheet.createRow(start);
                            copyRow(oldRow, newRow);
                            start++;
                        }
                    }*/
                    Sheet oldSheet = oldWorkbook.getSheetAt(1);
                    int oldRowSize = oldSheet.getLastRowNum();
                    for (int j = 0; j <= oldRowSize; j++) {
                        if (start == (isXlsx ? XLSX_MAX_ROWS : XLS_MAX_ROWS)) {
                            start = newSheet.getLastRowNum() + 2;
                        }
                        Row oldRow = oldSheet.getRow(j);
                        Row newRow = newSheet.createRow(start);
                        copyRow(oldRow, newRow);
                        start++;
                    }

                }
            }
            mergedWorkbook.write(out);
            out.flush();
            out.close();
            System.out.println("复制成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void copyRow(Row oldRow, Row newRow) {
        newRow.setHeight(oldRow.getHeight());
        for (int i = oldRow.getFirstCellNum(); i <= oldRow.getLastCellNum(); i++) {
            Cell oldCell = oldRow.getCell(i);
            if (null != oldCell) {
                copyCell(oldCell, newRow.createCell(i));
            }
        }
    }

    private static void copyCell(Cell oldCell, Cell newCell) {
        newCell.getCellStyle().cloneStyleFrom(oldCell.getCellStyle());
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }
        switch (oldCell.getCellType()) {
            case FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case BLANK:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case ERROR:
                newCell.setCellValue(oldCell.getErrorCellValue());
                break;
            default:
                break;
        }
    }

    public static void main(String[] args) throws IOException {
        File file1 = new File("D:/test.xlsx");
        File file2 = new File("D:/test1.xlsx");
        List<File> list = new ArrayList<>();
        list.add(file1);
        list.add(file2);
        File file = new File("100.xlsx");
        mergeExcel(file, list, true);
    }

}


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author wongH
 * @date 2020/6/10 14:16
 * 合并多个 Excel 文件 到一个Excel 多个sheet
 * 样式保留
 */
public class POIUtils2 {

    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();
        newstyle.cloneStyleFrom(fromCell.getCellStyle());
        //样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        CellType cellType = fromCell.getCellType();

        switch (cellType) {
            case FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case BLANK:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            case BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            case ERROR:
                toCell.setCellValue(fromCell.getErrorCellValue());
                break;
            default:
                break;
        }
    }

    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);
        //设置列宽
        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);
        }
    }


    public static void main(String[] args) {
        List<String> pathList = new ArrayList<>();
        pathList.add("D:/test.xlsx");
        pathList.add("D:/test1.xlsx");
        //将所有类型的excel文件合并成一个excel文件
        XSSFWorkbook newExcelCreat = new XSSFWorkbook();
        try {
            for (int i = 0; i < pathList.size(); i++) {
                InputStream in = new FileInputStream(pathList.get(i));
                XSSFWorkbook fromExcel = new XSSFWorkbook(in);
                //模板文件Sheet位置
                XSSFSheet oldSheet = fromExcel.getSheetAt(1);
                XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName() + i);
                copySheet(newExcelCreat, oldSheet, newSheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            FileOutputStream fileOut = new FileOutputStream(new File("1001.xlsx"));
            newExcelCreat.write(fileOut);
            fileOut.flush();
            fileOut.close();
            System.out.println("复制成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}