工作总结—poi读取与生成excel文件（复杂表头读取与生成）

请严格按照 pom 文件中 各依赖

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>top.mindse</groupId>
    <artifactId>dynamicxls</artifactId>
    <version>1.0.0</version>


    <properties>
        <java.version>1.8</java.version>
        <encoding>UTF-8</encoding>
    </properties>

    <dependencies>

        <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>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
        </dependency>

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

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>${java.version}</source>
                    <target>${java.version}</target>
                    <encoding>${encoding}</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

show me the code

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

public class ExcelTool {

    private Workbook workbook;
    /**
     * 表格数据
     * key位sheet名称
     * value位sheet数据
     */
    private Map<String, String[][]> data;

    /**
     * 生成文件路径
     */
    private String filePath;

    public ExcelTool(Workbook workbook, String filePath, Map<String, String[][]> data) {
        this.workbook = workbook;
        this.filePath = filePath;
        this.data = data;
    }


    public File create() throws IOException {
        if (Objects.nonNull(data) && !data.isEmpty()) {
            data.forEach((k, v) -> {
                Sheet sheet = workbook.createSheet(k);
                int maxTitleRow = getMaxTitleRow(v);
                createHead(sheet, v, maxTitleRow);
                createBody(sheet, v, maxTitleRow);
            });
            File file = save();
            return file;
        }
        return null;
    }

    public File save() throws IOException {
        File file = new File(filePath);
        if (!file.exists()) {
            file.createNewFile();
        }
        try (OutputStream out = new FileOutputStream(file)) {
            this.workbook.write(out);
            out.flush();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return file;
    }

    public void createBody(Sheet sheet, String[][] excelValues, int maxTitleRow) {
        int rowCount = excelValues.length;
        int colCount = excelValues[0].length;
        boolean[][] handled = new boolean[rowCount][colCount];

        for (int i = maxTitleRow; i < rowCount; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < colCount; j++) {
                if (!handled[i][j]) {
                    Cell cell = row.createCell(j, CellType.STRING);
                    cell.setCellValue(excelValues[i][j]);
                    int mergeMaxRow = getMergeMaxRow(i, j, excelValues.length, excelValues, handled);
                    if (!(i == mergeMaxRow)) {
                        CellRangeAddress cra = new CellRangeAddress(i, mergeMaxRow, j, j);
                        sheet.addMergedRegion(cra);
                    }
                }
            }

        }


    }

    public void createHead(Sheet sheet, String[][] excelValues, int maxTitleRow) {
        int colLength = excelValues[0].length;
        boolean[][] handled = new boolean[maxTitleRow][colLength];
        for (int i = 0; i < maxTitleRow; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < colLength; j++) {
                if (!handled[i][j]) {
                    Cell cell = row.createCell(j, CellType.STRING);
                    cell.setCellValue(excelValues[i][j]);
                    int mergeMaxRow = getMergeMaxRow(i, j, maxTitleRow, excelValues, handled);
                    int mergeMaxCol = getMergeMaxCol(i, j, colLength, excelValues, handled);
                    if (!(i == mergeMaxRow && j == mergeMaxCol)) {
                        CellRangeAddress cra = new CellRangeAddress(i, mergeMaxRow, j, mergeMaxCol);
                        sheet.addMergedRegion(cra);
                    }
                }
            }
        }

    }

    public int getMergeMaxCol(int i, int j, int colLength, String[][] excelValues, boolean[][] handled) {
        String current = excelValues[i][j];
        if (j == colLength - 1) {
            return j;
        }
        String next = excelValues[i][++j];
        if (Objects.equals(current, next)) {
            handled[i][j - 1] = true;
            if (j == colLength) {
                return colLength;
            } else {
                return getMergeMaxCol(i, j, colLength, excelValues, handled);
            }
        } else {
            return j - 1;
        }
    }

    public int getMergeMaxRow(int i, int j, int maxTitleRow, String[][] excelValues, boolean[][] handled) {
        String current = excelValues[i][j];
        if (i == maxTitleRow - 1) {
            return i;
        }
        String next = excelValues[++i][j];
        if (Objects.equals(current, next)) {
            handled[i - 1][j] = true;
            if (i == maxTitleRow) {
                return maxTitleRow;
            } else {
                return getMergeMaxRow(i, j, maxTitleRow, excelValues, handled);
            }
        } else {
            return i - 1;
        }
    }

    public int getMaxTitleRow(String[][] excelValues) {
        int rowCount = excelValues.length;
        int colCount = excelValues[0].length;
        int[] max = new int[colCount];
        for (int i = 0; i < colCount; i++) {
            max[i] = 1;
            for (int j = 0; j < rowCount; j++) {
                if (j == rowCount - 1) {
                    return rowCount;
                }
                if (Objects.equals(excelValues[j][i], excelValues[j + 1][i])) {
                    max[i] = max[i] + 1;
                } else {
                    break;
                }
            }
        }
        int maxTitleRow = Arrays.stream(max).max().orElse(1);
        return maxTitleRow;
    }

    public static Map<String, String[][]> getExcelValues(InputStream inputStream) throws IOException {
        if (Objects.isNull(inputStream)) {
            return new HashMap<>();
        }
        Workbook workbook = WorkbookFactory.create(inputStream);
        int sheetCount = workbook.getNumberOfSheets();
        Map<String, String[][]> orionData = new HashMap<>(sheetCount);
        for (int k = 0; k < sheetCount; k++) {
            Sheet sheet = workbook.getSheetAt(k);
            int rowNum = sheet.getLastRowNum();
            int colCount = sheet.getRow(rowNum ).getLastCellNum();
            String[][] values = new String[rowNum + 1][colCount];
            for (int i = 0; i <= rowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                for (int j = 0; j < colCount; j++) {
                    Cell cell = row.getCell(j);
                    String cellValue;
                    boolean isMerge = false;
                    if (cell != null) {
                        isMerge = isMergedRegion(sheet, i, cell.getColumnIndex());
                    }
                    //判断是否具有合并单元格
                    if (isMerge) {
                        cellValue = getMergedRegionValue(sheet, row.getRowNum(), cell.getColumnIndex(), workbook);
                    } else {
                        cellValue = getStringCellValue(cell, workbook);
                    }
                    values[i][j] = cellValue;
                }
            }
            orionData.put(sheet.getSheetName(), values);
        }
        return orionData;
    }


    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    private static String getMergedRegionValue(Sheet sheet, int row, int column, Workbook workbook) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getStringCellValue(fCell, workbook);
                }
            }
        }
        return "";
    }

    private static String getStringCellValue(Cell cell, Workbook workbook) {
        String strCell = "";
        if (cell == null) {
            return strCell;
        }
        switch (cell.getCellType()) {
            case STRING:
                strCell = cell.getStringCellValue().trim();
                break;
            case NUMERIC:
                strCell = String.valueOf(cell.getNumericCellValue()).trim();
                break;
            case BOOLEAN:
                strCell = String.valueOf(cell.getBooleanCellValue()).trim();
                break;
            case BLANK:
                strCell = "";
                break;
            case FORMULA:
                try {
                    strCell = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
                    strCell = formulaEvaluator.evaluate(cell).getStringValue();
                }
                break;

            default:
                strCell = "";
                break;
        }
        if (strCell.equals("")) {
            return "";
        }
        return strCell;
    }


    public static void main(String[] args) throws IOException {
        InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("1654076399908.xls");
        Map<String, String[][]> data = ExcelTool.getExcelValues(inputStream);
        String fileName = System.currentTimeMillis() + ".xlsx";
        ExcelTool excelTool = new ExcelTool(new XSSFWorkbook(), fileName, data);
        File excelFile = excelTool.create();
        System.out.println(excelFile.getAbsolutePath());
    }

}