Java Code Examples for org.apache.poi.ss.usermodel.Sheet#getFirstRowNum()

/**
 * 获取Sheet页内容
 *
 * @param sheet
 * @return
 */
public static final List<Object[]> listFromSheet(Sheet sheet) {

    List<Object[]> list = new ArrayList<Object[]>();
    for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        Row row = sheet.getRow(r);
        if (row == null || row.getPhysicalNumberOfCells() == 0) continue;
        Object[] cells = new Object[row.getLastCellNum()];
        for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell == null) continue;
            //判断是否为日期类型
            cells[c] = getValueFromCell(cell);
        }
        list.add(cells);
    }
    return list;
}
 

@Override
@SuppressWarnings("unchecked")
public R read(Sheet sheet) {
    if (sheet == null) {
        return null;
    }
    R result = getResultSupplier().get();
    if (result == null) {
        return null;
    }
    BiConverter<? super R, Object, ? extends R> rowParser = getRowParser();
    if (rowParser == null) {
        return result;
    }
    for (int rowIndex = sheet.getFirstRowNum(), lastRowIndex = sheet.getLastRowNum(); rowIndex <= lastRowIndex; rowIndex++) {
        Object rowValue = null;
        Row row = sheet.getRow(rowIndex);
        IRowReader rowReader = getRowReader(rowIndex);
        if (rowReader != null) {
            rowValue = rowReader.read(row);
        }
        result = rowParser.apply(result, new Object[]{row, rowIndex, rowValue});

    }
    return result;
}
 

/**
 * Determine the version of an existing workbook
 * @param workbook
 * @param originSheetName
 * @return
 * @throws SpreadsheetException 
 */
private String readVersion(Workbook workbook, String originSheetName) throws SpreadsheetException {
	Sheet sheet = workbook.getSheet(originSheetName);
	if (sheet == null) {
		throw new SpreadsheetException("Invalid SPDX spreadsheet.  Sheet "+originSheetName+" does not exist.");
	}
	int firstRowNum = sheet.getFirstRowNum();
	Row dataRow = sheet.getRow(firstRowNum + DocumentInfoSheet.DATA_ROW_NUM);
	if (dataRow == null) {
		return UNKNOWN_VERSION;
	}
	Cell versionCell = dataRow.getCell(DocumentInfoSheet.SPREADSHEET_VERSION_COL);
	if (versionCell == null) {
		return UNKNOWN_VERSION;
	}
	return versionCell.getStringCellValue();
}
 

/**
 * シートの最大列数を取得する。
 * <p>{@literal jxl.Sheet.getColumns()}</p>
 * @param sheet シートオブジェクト
 * @return 最大列数
 * @throws IllegalArgumentException {@literal sheet == null.}
 */
public static int getColumns(final Sheet sheet) {
    ArgUtils.notNull(sheet, "sheet");

    int minRowIndex = sheet.getFirstRowNum();
    int maxRowIndex = sheet.getLastRowNum();
    int maxColumnsIndex = 0;
    for(int i = minRowIndex; i <= maxRowIndex; i++) {
        final Row row = sheet.getRow(i);
        if(row == null) {
            continue;
        }

        final int column = row.getLastCellNum();
        if(column > maxColumnsIndex) {
            maxColumnsIndex = column;
        }
    }

    return maxColumnsIndex;
}
 

/**
 * 读取Excel内容
 *
 * @param wb
 * @param sheetIndex
 * @return
 */
private static ExcelHelper _readExcel(Workbook wb, int sheetIndex) {
    // 遍历Excel Sheet， 依次读取里面的内容
    if (sheetIndex > wb.getNumberOfSheets()) {
        return null;
    }
    Sheet sheet = wb.getSheetAt(sheetIndex);
    // 遍历表格的每一行
    int rowStart = sheet.getFirstRowNum();
    // 最小行数为1行
    int rowEnd = sheet.getLastRowNum();
    // 读取EXCEL标题栏
    ExcelHelper eh = new ExcelHelper();
    eh._parseExcelHeader(sheet.getRow(0));
    // 读取EXCEL数据区域内容
    eh._parseExcelData(sheet, rowStart + 1, rowEnd);
    return eh;
}
 

/**
 * 描述：获取IO流中的数据，组装成List<List<Object>>对象
 * @param in,fileName
 * @return
 * @throws IOException
 */
public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
    List<List<Object>> list = null;

    //创建Excel工作薄
    Workbook work = this.getWorkbook(in,fileName);
    if(null == work){
        throw new Exception("创建Excel工作薄为空！");
    }
    Sheet sheet = null;  //页数
    Row row = null;  //行数
    Cell cell = null;  //列数
    list = new ArrayList<List<Object>>();
    //遍历Excel中所有的sheet
    for (int i = 0; i < work.getNumberOfSheets(); i++) {
        sheet = work.getSheetAt(i);
        if(sheet==null){continue;}

        //遍历当前sheet中的所有行
        for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if(row==null||row.getFirstCellNum()==j){continue;}
            //遍历所有的列
            List<Object> li = new ArrayList<Object>();
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                cell = row.getCell(y);
                li.add(this.getValue(cell));
            }
            list.add(li);
        }
    }

    return list;

}
 

private static List<com.minsx.framework.common.excel.Sheet> initialExcelData(InputStream inputStream) {
    Workbook workbook = getWorkbook(inputStream);
    List<com.minsx.framework.common.excel.Sheet> sheets = new ArrayList<>();
    if (workbook != null) {
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            if (sheet == null || sheet.getRow(0) == null) {
                continue;
            }
            List<String> header = getHeader(sheet);

            List<com.minsx.framework.common.excel.Row> rows = new ArrayList<>();
            for (int rowNum = sheet.getFirstRowNum() + 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                List<com.minsx.framework.common.excel.Cell> cells = new ArrayList<>();
                Row row = sheet.getRow(rowNum);
                for (int i = 0; i < header.size(); i++) {
                    Map<String, String> result = getCellData(sheet, row, i);
                    com.minsx.framework.common.excel.Cell cell = new com.minsx.framework.common.excel.Cell();
                    cell.setColumnName(header.get(i));
                    cell.setValue(result.get("value"));
                    cell.setMRValue(result.get("MRValue"));
                    cell.setType(result.get("type"));
                    cells.add(cell);
                }
                rows.add(new com.minsx.framework.common.excel.Row(cells));
            }
            sheets.add(new com.minsx.framework.common.excel.Sheet(rows));
        }
        try {
            workbook.close();
        } catch (IOException e) {
            throw new ExcelReadException(e);
        }
    }
    return sheets;
}
 

private static int getBigestCellNum(Sheet sheet) {
    int bigestCellNum = 0;
    for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
        Row row = sheet.getRow(rowNum);
        if (row != null && row.getLastCellNum() > bigestCellNum) {
            bigestCellNum = row.getLastCellNum();
        }
    }
    return bigestCellNum;
}
 

public PersonSheetConfigurator(XSSFWorkbook workbook, Sheet sheet) {
	this.sheetIndex = workbook.getSheetIndex(sheet);
	Row row = sheet.getRow(sheet.getFirstRowNum());
	this.firstRow = sheet.getFirstRowNum() + 1;
	this.lastRow = sheet.getLastRowNum();
	memoColumn = row.getLastCellNum() + 1;
	for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
		Cell cell = row.getCell(i);
		if (null != cell) {
			String str = this.getCellStringValue(cell);
			if (StringUtils.isNotEmpty(str)) {
				if (nameItems.contains(str)) {
					this.nameColumn = i;
				} else if (uniqueItems.contains(str)) {
					this.uniqueColumn = i;
				} else if (employeeItems.contains(str)) {
					this.employeeColumn = i;
				} else if (mobileItems.contains(str)) {
					this.mobileColumn = i;
				} else if (mailItems.contains(str)) {
					this.mailColumn = i;
				} else if (genderTypeItems.contains(str)) {
					this.genderTypeColumn = i;
				} else {
					Matcher matcher = attributePattern.matcher(str);
					if (matcher.matches()) {
						String attribute = matcher.group(1);
						this.attributes.put(attribute, new Integer(i));
					}
				}
			}
		}
	}
}
 

private List<T> getDataFromFile(Sheet sheet, Map<Integer, Field> fieldMap) {
    long startTime = System.currentTimeMillis();
    final int firstRowNum = sheet.getFirstRowNum();
    final int lastRowNum = sheet.getLastRowNum();
    log.info("FirstRowNum:{},LastRowNum:{}", firstRowNum, lastRowNum);
    if (lastRowNum < 0) {
        log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
        return Collections.emptyList();
    }
    DataFormatter formatter = new DataFormatter();
    List<T> result = new LinkedList<>();
    for (int i = firstRowNum; i <= lastRowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            log.info("Row of {} is null,it will be ignored.", i);
            continue;
        }
        boolean noMatchResult = rowFilter.negate().test(row);
        if (noMatchResult) {
            log.info("Row of {} does not meet the filtering criteria, it will be ignored.", i);
            continue;
        }
        int lastColNum = row.getLastCellNum();
        if (lastColNum < 0) {
            continue;
        }
        T obj = instanceObj(fieldMap, formatter, row);
        if (beanFilter.test(obj)) {
            result.add(obj);
        }
    }
    log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
    return result;
}
 

private void readThenConsume(Sheet sheet, Map<Integer, Field> fieldMap, Consumer<T> consumer, Function<T, Boolean> function) {
    long startTime = System.currentTimeMillis();
    final int firstRowNum = sheet.getFirstRowNum();
    final int lastRowNum = sheet.getLastRowNum();
    log.info("FirstRowNum:{},LastRowNum:{}", firstRowNum, lastRowNum);
    if (lastRowNum < 0) {
        log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
        return;
    }
    DataFormatter formatter = new DataFormatter();
    for (int i = firstRowNum; i <= lastRowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            log.info("Row of {} is null,it will be ignored.", i);
            continue;
        }
        boolean noMatchResult = rowFilter.negate().test(row);
        if (noMatchResult) {
            log.info("Row of {} does not meet the filtering criteria, it will be ignored.", i);
            continue;
        }
        int lastColNum = row.getLastCellNum();
        if (lastColNum < 0) {
            continue;
        }
        T obj = instanceObj(fieldMap, formatter, row);
        if (beanFilter.test(obj)) {
            if (consumer != null) {
                consumer.accept(obj);
            } else if (function != null) {
                Boolean noStop = function.apply(obj);
                if (!noStop) {
                    break;
                }
            }
        }
    }
    log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
}
 

public static Map<String, String> deleteCell(@NotNull final DeleteCellInputs deleteCellInputs) {
    try {
        final String excelFileName = deleteCellInputs.getCommonInputs().getExcelFileName();
        final Workbook excelDoc = getExcelDoc(excelFileName);
        final Sheet worksheet = getWorksheet(excelDoc, deleteCellInputs.getCommonInputs().getWorksheetName());

        final int firstRowIndex = worksheet.getFirstRowNum();
        final int firstColumnIndex = 0;
        final int lastRowIndex = worksheet.getLastRowNum();
        final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);

        final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
        final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
        final String rowIndex = defaultIfEmpty(deleteCellInputs.getRowIndex(), rowIndexDefault);
        final String columnIndex = defaultIfEmpty(deleteCellInputs.getColumnIndex(), columnIndexDefault);

        final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
        final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);

        if (rowIndexList.size() != 0 && columnIndexList.size() != 0) {
            final int deleteCellResult = deleteCell(worksheet, rowIndexList, columnIndexList);
            //update formula cells
            final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
            for (Row r : worksheet) {
                for (Cell c : r) {
                    if (c.getCellType() == CellType.FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
            updateWorkbook(excelDoc, excelFileName);
            return getSuccessResultsMap(String.valueOf(deleteCellResult));

        } else {
            return getSuccessResultsMap("0");
        }
    } catch (Exception e) {
        return getFailureResultsMap(e.getMessage());
    }
}
 

/**
 * We store (cell types per row) per column.
 *
 * @param sheet key is the column number, value is a Map with key row number and value Type
 * @return A Map<colId, Map<rowId, type>>
 */
private SortedMap<Integer, SortedMap<Integer, String>> collectSheetTypeMatrix(Sheet sheet,
        FormulaEvaluator formulaEvaluator) {

    int firstRowNum = sheet.getFirstRowNum();
    int lastRowNum = sheet.getLastRowNum();

    LOGGER.debug("firstRowNum: {}, lastRowNum: {}", firstRowNum, lastRowNum);

    SortedMap<Integer, SortedMap<Integer, String>> cellsTypeMatrix = new TreeMap<>();

    // we start analysing rows
    for (int rowCounter = firstRowNum; rowCounter <= lastRowNum; rowCounter++) {

        int cellCounter = 0;

        Row row = sheet.getRow(rowCounter);
        if (row == null) {
            continue;
        }

        Iterator<Cell> cellIterator = row.cellIterator();

        String currentType;

        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            CellType xlsType = CellType.STRING;

            try {
                xlsType = cell.getCellType() == CellType.FORMULA ? //
                        formulaEvaluator.evaluate(cell).getCellType() : cell.getCellType();
            } catch (Exception e) {
                // ignore formula error evaluation get as a String with the formula
            }
            switch (xlsType) {
            case BOOLEAN:
                currentType = BOOLEAN.getName();
                break;
            case NUMERIC:
                currentType = getTypeFromNumericCell(cell);
                break;
            case BLANK:
                currentType = BLANK;
                break;
            case FORMULA:
            case STRING:
                currentType = STRING.getName();
                break;
            case ERROR:
                // we cannot really do anything with an error
            default:
                currentType = ANY.getName();
            }

            SortedMap<Integer, String> cellInfo = cellsTypeMatrix.get(cellCounter);

            if (cellInfo == null) {
                cellInfo = new TreeMap<>();
            }
            cellInfo.put(rowCounter, currentType);

            cellsTypeMatrix.put(cellCounter, cellInfo);
            cellCounter++;
        }
    }

    LOGGER.trace("cellsTypeMatrix: {}", cellsTypeMatrix);
    return cellsTypeMatrix;
}
 

@NotNull
public static Map<String, String> modifyCell(@NotNull final ModifyCellInputs modifyCellInputs) {
    try {
        final String excelFileName = modifyCellInputs.getCommonInputs().getExcelFileName();
        final Workbook excelDoc = getExcelDoc(excelFileName);
        final Sheet worksheet = getWorksheet(excelDoc, modifyCellInputs.getCommonInputs().getWorksheetName());

        final int firstRowIndex = worksheet.getFirstRowNum();
        final int lastRowIndex = worksheet.getLastRowNum();
        final int firstColumnIndex = 0;
        final int lastColumnIndex = getLastColumnIndex(worksheet, firstRowIndex, lastRowIndex);
        final String columnDelimiter = modifyCellInputs.getColumnDelimiter();
        final String newValue = modifyCellInputs.getNewValue();

        final String rowIndexDefault = firstRowIndex + ":" + lastRowIndex;
        final String columnIndexDefault = firstColumnIndex + ":" + lastColumnIndex;
        final String rowIndex = defaultIfEmpty(modifyCellInputs.getRowIndex(), rowIndexDefault);
        final String columnIndex = defaultIfEmpty(modifyCellInputs.getColumnIndex(), columnIndexDefault);

        final List<Integer> rowIndexList = validateIndex(processIndex(rowIndex), firstRowIndex, lastRowIndex, true);
        final List<Integer> columnIndexList = validateIndex(processIndex(columnIndex), firstColumnIndex, lastColumnIndex, false);

        final List<String> dataList = getDataList(newValue, columnIndexList, columnDelimiter);

        incompleted = false;
        final int modifyCellDataResult = modifyCellData(worksheet, rowIndexList, columnIndexList, dataList);

        if (modifyCellDataResult != 0) {
            //update formula cells
            final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
            for (Row row : worksheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == CellType.FORMULA) {
                        evaluator.evaluateFormulaCell(cell);
                    }
                }
            }
            updateWorkbook(excelDoc, excelFileName);
        }

        if (modifyCellDataResult == rowIndexList.size() && !incompleted) {
            return getSuccessResultsMap(String.valueOf(modifyCellDataResult));
        } else {
            return getFailureResultsMap(String.valueOf(modifyCellDataResult));
        }
    } catch (Exception e) {
        return getFailureResultsMap(e.getMessage());
    }
}