下面列出了org.apache.poi.ss.usermodel.Sheet#getFirstRowNum ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* 获取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());
}
}