涉及到合并单元格的数据读取：
package com.util; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; public class ExcelUtil { /** * 判断指定的单元格是否是合并单元格 * * @param sheet * @param row 行下标 * @param column 列下标 * @return */ public 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; } /** * 获取合并单元格的值 * * @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); fCell.setCellType(CellType.STRING); return getCellValue(fCell); } } } return ""; } /** * 获取单元格的值 * * @param cell * @return */ public static String getCellValue(Cell cell) { if (cell == null) { return ""; } cell.setCellType(CellType.STRING); if (cell == null) return ""; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue().trim(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()).trim(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return cell.getCellFormula().trim(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()).trim(); } return ""; } public static List<List<String>> getValues(String fileUrl, int sheetNum) { List<List<String>> values = new ArrayList<List<String>>(); try { File file = new File(fileUrl); InputStream is = null; try { is = new FileInputStream(file); } catch (FileNotFoundException e) { e.printStackTrace(); } Workbook workbook = null; Sheet sheet = null; workbook = WorkbookFactory.create(is); int sheetCount = sheetNum - 1; //workbook.getNumberOfSheets();//sheet 数量,可以只读取手动指定的sheet页 // for(int s=0;s<sheetCount;s++){ sheet = workbook.getSheetAt(sheetCount); //读取第几个工作表sheet int rowNum = sheet.getLastRowNum();//有多少行 for (int i = 1; i <= rowNum; i++) { Row row = sheet.getRow(i);//第i行 if (row == null) {//过滤空行 continue; } List<String> list = new ArrayList<>(); int colCount = sheet.getRow(0).getLastCellNum();//用表头去算有多少列，不然从下面的行计算列的话，空的就不算了 // for (int j = 0; j < row.getLastCellNum(); j++) {//第j列://+1是因为最后一列是空 也算进去 for (int j = 0; j < colCount; j++) {//第j列://+1是因为最后一列是空 也算进去 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()); } else { cellValue = getCellValue(cell); } list.add(cellValue); } values.add(list); } // } System.out.println(values); } catch (Exception e) { e.printStackTrace(); } return values; } }
调用工具类方法：
@Test public void testReadOne() { String fileUrl = "E:\officedh\cai\docs\design\云彩游戏列表.xlsx"; List<List<String>> values = ExcelUtil.getValues(fileUrl,1); String firstValue = ""; String secondValue = ""; int firstId = 0; int secondId = 0; List<Game> games = new ArrayList<>(); List<GameGroup> gameGroups = new ArrayList<>(); Timestamp timestamp = new Timestamp(System.currentTimeMillis()); for (List<String> value : values) { for (int i = 0; i < value.size(); i++) { String v = value.get(i); switch (i) { case 0: if (!v.equals(firstValue)) { firstId++; secondId = 0; firstValue = v; // 插入PlayGroup{firstId,firstValue} GameGroup group = new GameGroup(); String gameGroupId = String.format("%02d", firstId); group.setGameGroupId(Long.valueOf(gameGroupId)); // group.setTags(Long.valueOf(id)); group.setCreateTime(timestamp); group.setName(firstValue); group.setDescription(""); group.setSn(""); gameGroups.add(group); } break; case 1: if (!v.equals(secondValue)) { secondId++; secondValue = v; //插入PlaySection{secondId,secondValue} Game game = new Game(); String gameGroupId = String.format("%02d", firstId); String gameId = String.format("%02d%02d", firstId, secondId); game.setGameGroupId(Long.valueOf(gameGroupId)); game.setGameId(Long.valueOf(gameId)); game.setName(secondValue); // game.setGameType(rank);//游戏类型，1：数字型；2：字符型；3：其他型 // game.setBeginTime(rank);//开奖时间 // game.setEndTime(rank);//闭奖时间 // game.setDescription(rank);// // game.setDuration(rank);//开奖间隔，单位：秒 // game.setLockTime(rank);//封存时间，单位：秒 // game.setElements(rank);//号码 // game.setLogo(rank);//LOGO // game.setSn(rank); // game.setStatus(rank); // game.setTags(rank); game.setCreateTime(timestamp); games.add(game); } break; } } } gameRepository.save(games); gameGroupRepository.save(gameGroups); }