poi 多个excel合并

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

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);
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

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();
        }
    }
}