Java读取Excel中的合并单元格

本文以Java示例展示读取Excel中的合并单元格的方法。

1、 Maven仓库下载导入

在pom.xml中配置maven路径，指定依赖，如下：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>

2、读取

2.1 代码

/**
	 * 读取excel数据 支持xls格式
	 * @param sheetIndex    sheet页下标：从0开始
	 * @param startReadLine 开始读取的行:从0开始
	 * @param tailLine      去除最后读取的行
	 */
	public static List<List<JSONObject>> readMergeExcel(String path, int sheetIndex, int startReadLine, int tailLine) {
		List<List<JSONObject>> results = new ArrayList<>();
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(new File(path));
			Sheet sheet = wb.getSheetAt(sheetIndex);
			Row row = null;
			for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
				row = sheet.getRow(i);
				if (row == null)
					continue;
				List<JSONObject> result = new ArrayList<>();
				for (Cell c : row) {
					JSONObject values = new JSONObject();
					boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());
					values.put("isMerge",isMerge);
					// 判断是否具有合并单元格
					if (isMerge) {
						JSONObject rs = getMergedRegionJsonValue(sheet, row.getRowNum(), c.getColumnIndex());
						values.putAll(rs);
					} else {
						values.put("cellValue",getCellValue(c));
					}
					result.add(values);
				}
				results.add(result);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return results;
	}

	/**
	 * 获取合并单元格的值
	 */
	public String getMergedRegionValue(Sheet sheet, int row, int column) {
		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 getCellValue(fCell);
				}
			}
		}
		return null;
	}

	public static JSONObject getMergedRegionJsonValue(Sheet sheet, int row, int column) {
		JSONObject values = new JSONObject();
		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);
					values.put("cellValue",getCellValue(fCell));
					values.put("firstColumn",firstColumn);
					values.put("lastColumn",lastColumn);
					values.put("firstRow",firstRow);
					values.put("lastRow",lastRow);
					return values;
				}
			}
		}
		return values;
	}

	/**
	 * 判断合并了行
	 */
	private boolean isMergedRow(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;
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * @param row    行下标
	 * @param column 列下标
	 */
	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;
	}

	/**
	 * 判断sheet页中是否含有合并单元格
	 */
	private boolean hasMerged(Sheet sheet) {
		return sheet.getNumMergedRegions() > 0 ? true : false;
	}

	/**
	 * 合并单元格
	 * @param firstRow 开始行
	 * @param lastRow  结束行
	 * @param firstCol 开始列
	 * @param lastCol  结束列
	 */
	private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}

	/**
	 * 获取单元格的值
	 */
	public static String getCellValue(Cell cell) {
		if (cell == null) {return "";}
		if (cell.getCellType() == CellType.STRING) {
			return cell.getStringCellValue();
		}
		if (cell.getCellType() == CellType.BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		}
		if (cell.getCellType() ==  CellType.FORMULA) {
			return cell.getCellFormula();
		}
		if (cell.getCellType() ==  CellType.NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		}
		return "";
	}

	public static void main(String[] args) {
		List<List<JSONObject>> listMerges = readMergeExcel("E:\\test.xlsx",0,0,0);
		for(List<JSONObject> result: listMerges){
			System.out.println(result.toString());
		}
	}

2.2 示例

风萧萧	兮	易水寒
壮士一去兮不复还		！
荆轲	刺	秦王
秦时明月	汉时关

2.3 运行结果

[
    [
        {
            "isMerge": false,
            "cellValue": "风萧萧"
        },
        {
            "isMerge": false,
            "cellValue": "兮"
        },
        {
            "isMerge": false,
            "cellValue": "易水寒"
        }
    ],
    [
        {
            "firstColumn": 0,
            "firstRow": 1,
            "lastRow": 1,
            "isMerge": true,
            "lastColumn": 1,
            "cellValue": "壮士一去兮不复还"
        },
        {
            "firstColumn": 0,
            "firstRow": 1,
            "lastRow": 1,
            "isMerge": true,
            "lastColumn": 1,
            "cellValue": "壮士一去兮不复还"
        },
        {
            "isMerge": false,
            "cellValue": "！"
        }
    ],
    [
        {
            "isMerge": false,
            "cellValue": "荆轲"
        },
        {
            "isMerge": false,
            "cellValue": "刺"
        },
        {
            "isMerge": false,
            "cellValue": "秦王"
        }
    ],
    [
        {
            "isMerge": false,
            "cellValue": "秦时明月"
        },
        {
            "firstColumn": 1,
            "firstRow": 3,
            "lastRow": 3,
            "isMerge": true,
            "lastColumn": 2,
            "cellValue": "汉时关"
        },
        {
            "firstColumn": 1,
            "firstRow": 3,
            "lastRow": 3,
            "isMerge": true,
            "lastColumn": 2,
            "cellValue": "汉时关"
        }
    ]
]