JAVA解析Excel复杂表头

废话不多说，直接上源码。前后端都有哦～～～～～～～～

能帮到你记得点赞收藏哦～～～～～～～～～

后端： 

import org.apache.poi.ss.usermodel.Cell;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;/*** @Description:Excel工具类(复杂表头解析)* @Author: sh* @Date: 2025/1/2 09:29*/
public class ExcelComplexUtil {/*** 导入Excel文件，逐行读取数据，数据格式二维数组* @param filePath* @param sheetIndex* @return* @throws IOException*/public String[][] importExcel(String filePath, int sheetIndex) throws IOException {List<String[]> dataList = new ArrayList<>();try (FileInputStream fis = new FileInputStream(new File(filePath));Workbook workbook = new XSSFWorkbook(fis)) {Sheet sheet = workbook.getSheetAt(sheetIndex); // 获取第一个工作表// 获取表头行Row headerRow = checkHeaderRow(sheet);if (headerRow != null) {//封装表头数据warpHeaderData(headerRow, dataList);} else {throw new RuntimeException("Excel 文件中没有找到表头行,请修改表格");}// 读取数据行for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {Row row = sheet.getRow(rowIndex);if (row == null) {continue; // 跳过空行}//封装行数据warpRowData(headerRow, row, dataList);}}// 将 List<String[]> 转换为 String[][] 数组String[][] result = new String[dataList.size()][];for (int i = 0; i < dataList.size(); i++) {result[i] = dataList.get(i);}return result; // 返回二维数组}/*** 检查表头行,表头行必须在前10行中* @param sheet* @return*/private Row checkHeaderRow(Sheet sheet) {int i = 0;Row headerRow = null;while (i < 10) {headerRow = sheet.getRow(i);if (headerRow != null) {break;}i++;}return headerRow;}/*** 数据遍历* @param headerRow* @param dataList* @throws IOException*/public void warpHeaderData(Row headerRow, List<String[]> dataList) {int columnCount = headerRow.getPhysicalNumberOfCells();short lastCellNum = headerRow.getLastCellNum();String[] data = new String[columnCount]; // 创建一维数组存储表头数据for (int colIndex = 0; colIndex < columnCount; colIndex++) {Cell cell = headerRow.getCell(lastCellNum - columnCount + colIndex);String cellValue = (cell != null) ? cell.toString() : ""; // 处理空单元格data[colIndex] = cellValue; // 将单元格值放入表头数组中}dataList.add(data); // 将表头数组添加到列表中}public void warpRowData(Row headerRow, Row row, List<String[]> dataList) {int columnCount = headerRow.getPhysicalNumberOfCells();short lastCellNum = headerRow.getLastCellNum();String[] data = new String[columnCount]; // 创建一维数组存储表头数据for (int colIndex = 0; colIndex < columnCount; colIndex++) {Cell cell = row.getCell(lastCellNum - columnCount + colIndex);String cellValue = (cell != null) ? cell.toString() : ""; // 处理空单元格data[colIndex] = cellValue; // 将单元格值放入表头数组中}dataList.add(data); // 将表头数组添加到列表中}/*** 获取excel中所有合并单元格* @param filePath* @param sheetIndex* @throws IOException*/public List<MergedCell> checkMergedCells(String filePath, int sheetIndex) throws IOException {try (FileInputStream fis = new FileInputStream(new File(filePath));Workbook workbook = new XSSFWorkbook(fis)) {Sheet sheet = workbook.getSheetAt(sheetIndex); // 获取第一个工作表int numberOfMergedRegions = sheet.getNumMergedRegions(); // 获取合并单元格的数量List<MergedCell> mergedCellArray = new ArrayList<>();for (int i = 0; i < numberOfMergedRegions; i++) {MergedCell mergedCell = new MergedCell();CellRangeAddress range = sheet.getMergedRegion(i); // 获取合并单元格区域mergedCell.setRange(range.formatAsString());// 获取合并单元格区域的起始单元格int firstRow = range.getFirstRow();int firstCol = range.getFirstColumn();// 获取合并单元格的内容Row row = sheet.getRow(firstRow);Cell cell = row.getCell(firstCol);String cellValue = (cell != null) ? cell.toString() : ""; // 处理空单元格mergedCell.setValue(cellValue);mergedCellArray.add(mergedCell);}return mergedCellArray;}}/*** 检查特定单元格是否是合并单元格* @param sheet* @param row* @param col* @return*/private boolean isMergedCell(Sheet sheet, int row, int col) {int numberOfMergedRegions = sheet.getNumMergedRegions();for (int i = 0; i < numberOfMergedRegions; i++) {CellRangeAddress range = sheet.getMergedRegion(i);if (range.isInRange(row, col)) {return true; // 如果该单元格在合并区内，返回 true}}return false; // 如果不在任何合并区内，返回 false}class MergedCell {private String range;private String value;public String getRange() {return range;}public void setRange(String range) {this.range = range;}public String getValue() {return value;}public void setValue(String value) {this.value = value;}@Overridepublic String toString() {return "MergedCell{" +"range='" + range + '\'' +", value='" + value + '\'' +'}';}}//    public static void main(String[] args) {
//        String filePath = "/ceshi/ceshi.xlsx"; // Excel 文件路径
//        try {
//            String[][] strings = importExcel(filePath, 0);
//            for (String[] row : strings) {
//                System.out.println(String.join(", ", row)); // 以逗号为分隔符打印每一行
//            }
//            List<MergedCell> mergedCells = checkMergedCells(filePath, 0);
//            for (MergedCell row : mergedCells) {
//                System.out.println(row); // 以逗号为分隔符打印每一行
//            }
//
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//    }
}

前端：

<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><title>合并单元格查看器</title><style>table {border-collapse: collapse;width: 500px; /* 设置表格宽度 */}td {border: 1px solid black;padding: 0; /* 清除内边距 */text-align: center;vertical-align: middle; /* 垂直居中 */height: 50px; /* 设置每个单元格的高度 */}.merged {background-color: #f0f0f0; /* 合并单元格的背景颜色 */font-weight: bold; /* 合并单元格字体加粗 */}</style>
</head>
<body><div id="app"><h1>合并单元格查看器</h1><table><tr v-for="(row, rowIndex) in tableData" :key="rowIndex"><tdv-for="(cell, colIndex) in row":key="colIndex"v-if="!isCellOccupied(rowIndex, colIndex)":rowspan="getRowSpan(rowIndex, colIndex)":colspan="getColSpan(rowIndex, colIndex)":class="{ merged: isMergedCell(rowIndex, colIndex) }">{{ getCellValue(rowIndex, colIndex) }}</td></tr></table></div><script src="https://cdn.jsdelivr.net/npm/vue@2.6.14/dist/vue.js"></script><script>new Vue({el: '#app',data() {return {mergedCells: [{ range: "C1:D1", value: "合并单元格 1" },{ range: "B2:C2", value: "合并单元格 2" },{ range: "B3:C3", value: "合并单元格 " },{ range: "A2:A3", value: "合并单元格 " },{ range: "F2:F3", value: "合并单元格 " } ],normalData: [["监督员地区", "总数", "有效", "", "有效政治类", ""],["平谷区监督员", "244", "", "197", "28", "169"],["", "244", "", "197", "28", ""],["数据 16", "数据 17", "数据 18", "数据 19", "数据 20"],["数据 21", "数据 22", "数据 23", "数据 24", "数据 25"],["", "", "", "", ""]],occupiedCells: []};},computed: {// 生成完整表格数据tableData() {const rows = this.normalData.length;const cols = this.normalData[0].length;const emptyTable = Array.from({ length: rows }, () => Array(cols).fill(null));// 填充合并单元格this.mergedCells.forEach(({ range, value }) => {const [start, end] = range.split(':');const startRow = parseInt(start.match(/\d+/)[0]) - 1;const startCol = start.charCodeAt(0) - 65;const endRow = parseInt(end.match(/\d+/)[0]) - 1;const endCol = end.charCodeAt(0) - 65;// 填充合并单元格的位置for (let r = startRow; r <= endRow; r++) {for (let c = startCol; c <= endCol; c++) {if (r === startRow && c === startCol) {emptyTable[r][c] = value; // 合并单元格的值} else {this.occupiedCells[r] = this.occupiedCells[r] || [];this.occupiedCells[r][c] = true; // 标记占用}}}});// 填充普通单元格的数据this.normalData.forEach((row, r) => {row.forEach((cell, c) => {if (!this.occupiedCells[r] || !this.occupiedCells[r][c]) {emptyTable[r][c] = cell;}});});return emptyTable;}},methods: {isCellOccupied(rowIndex, colIndex) {return this.occupiedCells[rowIndex] && this.occupiedCells[rowIndex][colIndex];},getCellValue(rowIndex, colIndex) {return this.tableData[rowIndex][colIndex];},getRowSpan(rowIndex, colIndex) {let rowspan = 1;const firstValue = this.getCellValue(rowIndex, colIndex);const mergedCell = this.mergedCells.find(({ range }) => {const [start, end] = range.split(':');const startRow = parseInt(start.match(/\d+/)[0]) - 1;const startCol = start.charCodeAt(0) - 65;const endRow = parseInt(end.match(/\d+/)[0]) - 1;const endCol = end.charCodeAt(0) - 65;return rowIndex === startRow && colIndex === startCol;});if (mergedCell) {const [start, end] = mergedCell.range.split(':');const startRow = parseInt(start.match(/\d+/)[0]) - 1;const endRow = parseInt(end.match(/\d+/)[0]) - 1;rowspan = endRow - startRow + 1;}return rowspan;},getColSpan(rowIndex, colIndex) {let colspan = 1;const firstValue = this.getCellValue(rowIndex, colIndex);const mergedCell = this.mergedCells.find(({ range }) => {const [start, end] = range.split(':');const startRow = parseInt(start.match(/\d+/)[0]) - 1;const startCol = start.charCodeAt(0) - 65;const endRow = parseInt(end.match(/\d+/)[0]) - 1;const endCol = end.charCodeAt(0) - 65;return rowIndex === startRow && colIndex === startCol;});if (mergedCell) {const [start, end] = mergedCell.range.split(':');const startCol = start.charCodeAt(0) - 65;const endCol = end.charCodeAt(0) - 65;colspan = endCol - startCol + 1;}return colspan;},isMergedCell(rowIndex, colIndex) {return this.mergedCells.some(({ range }) => {const [start, end] = range.split(':');const startRow = parseInt(start.match(/\d+/)[0]) - 1;const startCol = start.charCodeAt(0) - 65;return rowIndex === startRow && colIndex === startCol;});}}});</script>
</body>
</html>