java poi excel判断表头单元格是否相同,相同则合并

可以使用Java的POI库来读取Excel文件，并对表头进行判断和合并。具体的代码实现如下：

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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 org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
    public static void main(String[] args) {
        String filePath = "test.xlsx";
        String sheetName = "Sheet1";

        List<List<String>> data = readExcel(filePath, sheetName);
        if (data != null) {
            List<Integer> mergeRows = getMergeRows(data);
            writeExcel(filePath, sheetName, data, mergeRows);
        }
    }

    /**
     * 读取Excel文件
     * 
     * @param filePath   Excel文件路径
     * @param sheetName  Sheet名称
     * @return           读取结果，每行数据都是一个List<String>，所有行数据则是一个List<List<String>>
     */
    public static List<List<String>> readExcel(String filePath, String sheetName) {
        List<List<String>> result = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(fis)) {
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                return null;
            }

            int lastRowNum = sheet.getLastRowNum();
            int lastCellNum = sheet.getRow(0).getLastCellNum();

            for (int i = 0; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                List<String> rowData = new ArrayList<>();

                for (int j = 0; j < lastCellNum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        rowData.add("");
                        continue;
                    }

                    if (cell.getCellType() == CellType.NUMERIC) {
                        rowData.add(String.valueOf(cell.getNumericCellValue()));
                    } else {
                        rowData.add(cell.getStringCellValue());
                    }
                }

                result.add(rowData);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return result;
    }

    /**
     * 获取表头相同的行数
     * 
     * @param data  Excel数据，每行数据都是一个List<String>，所有行数据则是一个List<List<String>>
     * @return      表头相同的行数
     */
    public static List<Integer> getMergeRows(List<List<String>> data) {
        List<Integer> result = new ArrayList<>();

        int rowCount = data.size();
        int colCount = data.get(0).size();
        Map<String, Integer> colMap = new TreeMap<>();

        for (int i = 0; i < colCount; i++) {
            String colName = data.get(0).get(i);
            if (colMap.containsKey(colName)) {
                colMap.put(colName, colMap.get(colName) + 1);
            } else {
                colMap.put(colName, 1);
            }
        }

        for (int i = 0; i < rowCount; i++) {
            boolean isSame = true;

            for (int j = 0; j < colCount; j++) {
                if (i == 0) {
                    continue;
                }

                String cellValue = data.get(i).get(j);
                String colName = data.get(0).get(j);

                if (!cellValue.equals(data.get(i - 1).get(j))) {
                    isSame = false;
                    break;
                }

                if (colMap.get(colName) > 1) {
                    CellRangeAddress mergedRegion = getMergedRegion(data, i, j);
                    if (mergedRegion != null) {
                        int firstRow = mergedRegion.getFirstRow();
                        int lastRow = mergedRegion.getLastRow();
                        if (lastRow > i) {
                            i = lastRow;
                            isSame = false;
                            break;
                        }
                    }
                }
            }

            if (isSame) {
                result.add(i);
            }
        }

        return result;
    }

    /**
     * 写入Excel文件
     * 
     * @param filePath   Excel文件路径
     * @param sheetName  Sheet名称
     * @param data       数据，每行数据都是一个List<String>，所有行数据则是一个List<List<String>>
     * @param mergeRows  需要合并的行数
     */
    public static void writeExcel(String filePath, String sheetName, List<List<String>> data, List<Integer> mergeRows) {
        try (FileInputStream fis = new FileInputStream(filePath);
             XSSFWorkbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                return;
            }

            XSSFCellStyle style = workbook.createCellStyle();
            XSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 255, 204)));

            for (int i = 0; i < mergeRows.size(); i++) {
                int rowNumber = mergeRows.get(i);

                Row row = sheet.getRow(rowNumber);
                if (row != null) {
                    int lastCellNum = row.getLastCellNum();
                    int startRow = rowNumber;
                    int endRow = rowNumber;

                    for (int j = rowNumber + 1; j < data.size(); j++) {
                        boolean isSame = true;

                        for (int k = 0; k < lastCellNum; k++) {
                            String cellValue = data.get(j).get(k);
                            String colName = data.get(0).get(k);

                            if (!cellValue.equals(data.get(startRow).get(k))) {
                                isSame = false;
                                break;
                            }

                            if (k == lastCellNum - 1 && isSame) {
                                endRow = j;
                            }

                            if (endRow > startRow) {
                                break;
                            }

                            if (mergeRows.contains(j)) {
                                CellRangeAddress mergedRegion = getMergedRegion(data, j, k);
                                if (mergedRegion != null) {
                                    int lastRow = mergedRegion.getLastRow();
                                    if (lastRow > endRow) {
                                        endRow = lastRow;
                                        break;
                                    }
                                }
                            }
                        }

                        if (endRow > startRow) {
                            break;
                        }
                    }

                    if (endRow > startRow) {
                        for (int j = 0; j < lastCellNum; j++) {
                            Cell cell = row.getCell(j);
                            if (cell == null) {
                                cell = row.createCell(j);
                            }

                            cell.setCellStyle(style);

                            if (j == 0) {
                                cell.setCellValue(data.get(startRow).get(j));
                            }
                        }

                        CellRangeAddress range = new CellRangeAddress(startRow, endRow, 0, lastCellNum - 1);
                        sheet.addMergedRegion(range);
                    }
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取合并单元格的区域
     * 
     * @param data   数据，每行数据都是一个List<String>，所有行数据则是一个List<List<String>>
     * @param rowNum 行号
     * @param colNum 列号
     * @return       合并单元格的区域
     */
    public static CellRangeAddress getMergedRegion(List<List<String>> data, int rowNum, int colNum) {
        Sheet sheet = null;
        Workbook workbook = null;
        try (FileInputStream fis = new FileInputStream("test.xlsx")) {
            workbook = WorkbookFactory.create(fis);
            sheet = workbook.getSheetAt(0);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        if (sheet == null) {
            return null;
        }

        int numMergedRegions = sheet.getNumMergedRegions();
        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress merged = sheet.getMergedRegion(i);
            if (merged.isInRange(rowNum, colNum)) {
                return merged;
            }
        }

        return null;
    }
}

这段代码会读取Excel文件中指定Sheet的数据，然后判断表头中相同的行数，并将相同的行合并。其中，`getMergeRows`方法用于获取需要合并的行数，`writeExcel`方法用于将数据写入到Excel文件中并合并相同的行，`getMergedRegion`方法用于获取合并单元格的区域。你可以根据实际情况修改代码，以适应不同的需求。