POI读取Excel合并单元格并储存

package com.cn.excel; import java.io.File; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.List; 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.Row.MissingCellPolicy; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; public class Test { public static void main(String[] args) { List<TestPo> testList = load("src/main/resources/test.xlsx", "工作表1", TestPo.class); for (int i = 0; i < testList.size(); i++) { System.out.print("【" + testList.get(i).getNo() + "】"); System.out.print("【" + testList.get(i).getName() + "】"); System.out.print("【" + testList.get(i).getValue() + "】"); System.out.println(); } } /** * 解析Excel表 * * @param excelPath * 文件路径 * @param sheetName * 工作表名 * @param clazz * 实体类 * @return */ public static <T> List<T> load(String excelPath, String sheetName, Class<T> clazz) { List<T> list = new ArrayList<T>(); try { // 创建workbook对象 Workbook workbook = WorkbookFactory.create(new File(excelPath)); // 获取表单sheetName Sheet sheet = workbook.getSheet(sheetName); // 获取第一行 Row titleRow = sheet.getRow(0); // 获取最后一列列号(列数) int lastCellNum = titleRow.getLastCellNum(); String[] fields = new String[lastCellNum]; // 循环处理每一列,取出每一列的字段名保存到数组（处理表头） for (int column = 0; column < lastCellNum; column++) { Cell cell = titleRow.getCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell.setCellType(CellType.STRING); String title = cell.getStringCellValue(); title = title.substring(0, title.indexOf("(")); fields[column] = title; } // 获取最后一列行号 int lastRowIndex = sheet.getLastRowNum(); // 循环处理每一个数据行 for (int row = 0; row <= lastRowIndex; row++) { T obj = clazz.newInstance(); // 拿到一个数据行 Row dataRow = sheet.getRow(row); if (dataRow == null || isEmptyRow(dataRow)) { continue; } // 拿到此数据行上的每一列 for (int column = 0; column < lastCellNum; column++) { Cell cell = dataRow.getCell(column, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell.setCellType(CellType.STRING); String value = null; // 获取单元格的值 value = getCellValue(cell); if (isMergedRegion(sheet, row, column)) { value = getMergedRegionValue(sheet, row, column); } // 获取要反射的方法 String methodName = "set" + fields[column]; // 获取要反射的方法对象 Method method = clazz.getMethod(methodName, String.class); // 完成反射调用 method.invoke(obj, value); } list.add(obj); } } catch (Exception e) { e.printStackTrace(); } return list; } /** * 判断指定的单元格是否是合并单元格 * * @param sheet 表单 * @param row 行 * @param column 列 * @return */ public static boolean isMergedRegion(Sheet sheet, int row, int column) { //获取sheet中合并单元格的总数 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 sheet * @param row * @param column * @return */ public static 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, MissingCellPolicy.CREATE_NULL_AS_BLANK); fCell.setCellType(CellType.STRING); return getCellValue(fCell); } } } return null; } /** * 获取单元格的值 * * @param cell * @return */ public static String getCellValue(Cell cell) { return cell.getStringCellValue(); } /** * * @param dataRow * @return */ private static boolean isEmptyRow(Row dataRow) { int lastCellNum = dataRow.getLastCellNum(); for (int i = 0; i < lastCellNum; i++) { Cell cell = dataRow.getCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK); cell.setCellType(CellType.STRING); String value = cell.getStringCellValue(); if (value != null && value.trim().length() > 0) { return false; } } return true; } }