package com.deloitte.tms.eit.neweit.utils; import java.text.SimpleDateFormat; import java.util.Arrays; import java.util.Date; import java.util.List; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; import com.deloitte.tms.base.srv.util.StringUtil; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; public class ReadMergeRegionExcel { /** * 读取excel文件 * @param * @param sheetIndex sheet页下标：从0开始 * @param startReadLine 开始读取的行:从0开始 * @param tailLine 去除最后读取的行 */ public static void readExcelSheet( Sheet sheet, int startReadLine) { Row row = null; for(int i=startReadLine; i<=sheet.getLastRowNum(); i++) { row = sheet.getRow(i); for(Cell c : row) { c.setCellType(Cell.CELL_TYPE_STRING); boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex()); //判断是否具有合并单元格 if(isMerge) { /* String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex()); System.out.print(rs + " "); */ }else { System.out.print(c.getRichStringCellValue()+" "); } } System.out.println(); } } /** * 获取合并单元格的值 * @param sheet * @param row * @param column * @return */ public static String getMergedRegionValue(Sheet sheet ,int row , int column,FormulaEvaluator eva){ 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,(FormulaEvaluator)eva) ; } } } return null ; } /** * 获取单元格的值 * @param cell * @return */ public static String getCellValue(Cell cell) { if (cell == null) return ""; if (cell.getCellType() == Cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return cell.getCellFormula(); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); } return ""; } public static String getCellValue(Cell cell, FormulaEvaluator eva) { if (cell == null) { return ""; } else { switch (cell.getCellType()) { case 0: boolean b1 = HSSFDateUtil.isCellDateFormatted(cell); if (b1) { Date date = cell.getDateCellValue(); SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); return df.format(date); } cell.setCellType(1); return cell.getStringCellValue(); case 1: return cell.getStringCellValue(); case 2: if (eva == null) { return cell.getCellFormula(); } else { // String cellFormula = cell.; if(cell instanceof XSSFCell) { XSSFCell temp = (XSSFCell)cell; CTCell ctcell = temp.getCTCell(); if(ctcell!=null) { String v = ctcell.getV(); if(StringUtil.isNotEmpty(v)) { return v; } } } CellValue b = eva.evaluate(cell); if (b.getCellType() == 0) { return String.valueOf(b.getNumberValue()); } return b.getStringValue(); } case 3: default: return ""; case 4: return String.valueOf(cell.getBooleanCellValue()); } } }/** * 判断指定的单元格是否是合并单元格 * * @param sheet * @param row * 行下标 * @param column * 列下标 * @return */ @SuppressWarnings("unused") public static boolean isMergedRegion(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { org.apache.poi.ss.util.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页中是否含有合并单元格 * @param sheet * @return * @return */ public static boolean hasMerged(Sheet sheet) { return sheet.getNumMergedRegions() > 0 ? true : false; } }
上面的方法是一个bug，循环的次数太多，导致效率太慢，我导入了6000行数据，做检验的过程中，就花费了8分钟左右。这个也是大部分网上给出的建议。。。
我当时也在想能不能用数组或是集合的方式将合并的单元格缓存起来，然后进行对比判断，这样的话就会效率很高。
以下是官网上的方法：
//首先传进来sheet，会将sheet中合并单元格的放在二维数组中，如果没有单元格的数组中是null，如果这一行没有合并单元格，则这一行的数组为null。
public static CellRangeAddress[][] buildMergedRangesMap( Sheet sheet ) { CellRangeAddress[][] mergedRanges = new CellRangeAddress[1][]; for ( final CellRangeAddress cellRangeAddress : sheet.getMergedRegions() ) { final int requiredHeight = cellRangeAddress.getLastRow() + 1; if ( mergedRanges.length < requiredHeight ) { CellRangeAddress[][] newArray = new CellRangeAddress[requiredHeight][]; System.arraycopy( mergedRanges, 0, newArray, 0, mergedRanges.length ); mergedRanges = newArray; } for ( int r = cellRangeAddress.getFirstRow(); r <= cellRangeAddress .getLastRow(); r++ ) { final int requiredWidth = cellRangeAddress.getLastColumn() + 1; CellRangeAddress[] rowMerged = mergedRanges[r]; if ( rowMerged == null ) { rowMerged = new CellRangeAddress[requiredWidth]; mergedRanges[r] = rowMerged; } else { final int rowMergedLength = rowMerged.length; if ( rowMergedLength < requiredWidth ) { final CellRangeAddress[] newRow = new CellRangeAddress[requiredWidth]; System.arraycopy( rowMerged, 0, newRow, 0, rowMergedLength ); mergedRanges[r] = newRow; rowMerged = newRow; } } Arrays.fill( rowMerged, cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn() + 1, cellRangeAddress ); } } return mergedRanges; }
下面是具体使用：
CellRangeAddress[][] cellRangeAddress=null;
//是否为合并单元格标志，判断取单元格中的值的逻辑
boolean isMerge=false;
//判断这个sheet存不存在合并单元格，如果不存在，在下面的每个单元格就不再进行判断是不是单元格
boolean isSheetMerge=false;
//将合并单元格全部放在数组中
cellRangeAddress=ExcelToHtmlUtils.buildMergedRangesMap(sheetAt);
//是否为合并单元格标志，判断取单元格中的值的逻辑
isMerge=false;
//判断这个sheet存不存在合并单元格，如果不存在，在下面的每个单元格就不再进行判断是不是单元格
isSheetMerge =(cellRangeAddress!=null)?true:false;
//如果sheet页存在单元格的话，对判断每个单元格是否 是合并单元格，否则不判断 if(isSheetMerge){ //判断是否为合并单元格，这里的逻辑如果不理解跟一下代码就明白了。因为数组中只有合并单元格 if(rowIndex<cellRangeAddress.length){ if(!(cellRangeAddress[rowIndex]==null)){ if(cellIndex<cellRangeAddress[rowIndex].length){ if(!(cellRangeAddress[rowIndex][cellIndex]==null)){ isMerge=(cellRangeAddress[rowIndex][cellIndex]!=null); } } } } if(isMerge){ fRow = sheetAt.getRow(cellRangeAddress[rowIndex][cellIndex].getFirstRow()); fCell = fRow.getCell(cellRangeAddress[rowIndex][cellIndex].getFirstColumn()); cellvalue=this.getCellValue(fCell,(FormulaEvaluator)eva) ; //因为每次都需要进行判断，所以默认设置为false isMerge=false; }else{ cellvalue = this.getCellValue(row.getCell(cellIndex), (FormulaEvaluator) eva); } }else{
//getCellValue方法在上面代码中有，获取单元格的值
cellvalue = this.getCellValue(row.getCell(cellIndex), (FormulaEvaluator) eva); }