POI进行单行单行地导入的数据在网上有许多的文章,但是要导入一个具有合并单元格的excel貌似比较难找。刚好最近完成了这样的一个需求,要求导入具有合并单元格的excel:
1 /** 2 * 读取excel数据,调用这方法开始 3 * @param is 4 * @param indexNum 至少需要多少列数据 5 */ 6 public static List<Object[]> readExcelToObj(InputStream is,int indexNum) { 7 8 Workbook wb = null; 9 List<Object[]> objArrList = null;10 try {11 objArrList = new ArrayList<>();12 wb = WorkbookFactory.create(is);13 readExcel(wb, 0, 0, 0,objArrList,indexNum);14 } catch (InvalidFormatException e) {15 e.printStackTrace();16 } catch (IOException e) {17 e.printStackTrace();18 }19 return objArrList;20 }
1 /** 2 * 读取excel文件 3 * @param wb 4 * @param sheetIndex sheet页下标:从0开始 5 * @param startReadLine 开始读取的行:从0开始 6 * @param tailLine 去除最后读取的行 7 */ 8 public static void readExcel(Workbook wb,int sheetIndex, int startReadLine, int tailLine, List<Object[]> objArrList, int indexNum) { 9 Sheet sheet = wb.getSheetAt(sheetIndex);10 Row row = null;11 12 for(int i=startReadLine; i<sheet.getLastRowNum()-tailLine+1; i++) {13 row = sheet.getRow(i);14 List<Object> objList = new ArrayList<>();15 for(int j = 0 ; j<row.getLastCellNum();j++) {16 //for(Cell c : row) {17 Cell c = row.getCell(j);18 if(c==null){19 objList.add("");20 continue;21 }22 boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());23 //判断是否具有合并单元格24 if(isMerge) {25 String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());26 objList.add(rs);27 }else {28 objList.add(getCellValue(c));29 }30 31 }32 while(objList.size()<indexNum){33 objList.add("");34 }35 objArrList.add(objList.toArray());36 }37 }
1 /** 2 * 获取合并单元格的值 3 * @param sheet 4 * @param row 5 * @param column 6 * @return 7 */ 8 public static String getMergedRegionValue(Sheet sheet ,int row , int column){ 9 int sheetMergeCount = sheet.getNumMergedRegions();10 11 for(int i = 0 ; i < sheetMergeCount ; i++){12 CellRangeAddress ca = sheet.getMergedRegion(i);13 int firstColumn = ca.getFirstColumn();14 int lastColumn = ca.getLastColumn();15 int firstRow = ca.getFirstRow();16 int lastRow = ca.getLastRow();17 18 if(row >= firstRow && row <= lastRow){19 20 if(column >= firstColumn && column <= lastColumn){21 Row fRow = sheet.getRow(firstRow);22 Cell fCell = fRow.getCell(firstColumn);23 return getCellValue(fCell) ;24 }25 }26 }27 28 return null ;29 }
1 /** 2 * 判断指定的单元格是否是合并单元格 3 * @param sheet 4 * @param row 行下标 5 * @param column 列下标 6 * @return 7 */ 8 public static boolean isMergedRegion(Sheet sheet,int row ,int column) { 9 int sheetMergeCount = sheet.getNumMergedRegions();10 for (int i = 0; i < sheetMergeCount; i++) {11 CellRangeAddress range = sheet.getMergedRegion(i);12 int firstColumn = range.getFirstColumn();13 int lastColumn = range.getLastColumn();14 int firstRow = range.getFirstRow();15 int lastRow = range.getLastRow();16 if(row >= firstRow && row <= lastRow){17 if(column >= firstColumn && column <= lastColumn){18 return true;19 }20 }21 }22 return false;23 }
1 /** 2 * 获取单元格的值 3 * @param cell 4 * @return 5 */ 6 public static String getCellValue(Cell cell){ 7 8 if(cell == null) return ""; 9 10 if(cell.getCellType() == Cell.CELL_TYPE_STRING){11 12 return cell.getStringCellValue();13 14 }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){15 16 return String.valueOf(cell.getBooleanCellValue());17 18 }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){19 20 return cell.getCellFormula() ;21 22 }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){23 24 return String.valueOf(cell.getNumericCellValue());25 26 }27 return "";28 }
注意:这导入功能也适用于单行读取,直接调用 readExcelToObj() 方法即可;参数1:传入excel文件的输入流;参数2:指定你希望至少要读入多少列数据(比如传入个0,就代表:如果你有的行只有3列数据的话,那么获得的数组长度就只有3;如果你传入了10,那些只有3列的数据会自动填充空字符串给数组,使每个数组最小长度为10);
原标题:POI导入具有合并了单元格的Excel
关键词:excel