下面列出了org.apache.poi.ss.usermodel.Workbook#getNumberOfSheets ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* 读取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;
}
/**
* 获取表单名称
*
* @param filePath Excel完整路径
* @return
*/
public static List<String> getSheetNames(String filePath) throws Exception {
List<String> sheetNames = new ArrayList<>();
try {
Workbook workBook = getWorkBook(filePath);
if (workBook == null) {
return null;
}
//String[] sheetNames = new String[workBook.getNumberOfSheets()];
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
sheetNames.add(workBook.getSheetAt(i).getSheetName());
}
workBook.close();
} catch (Exception e) {
LOGGER.error("获取sheet名称", e);
}
return sheetNames;
}
public ExcelReader(String base64Content) {
byte[] decoded = Base64.getDecoder().decode(base64Content);
InputStream input = new ByteArrayInputStream(decoded);
try {
Workbook workbook = WorkbookFactory.create(input);
if (workbook.getNumberOfSheets() == 0) {
workbook.createSheet();
}
sheet = workbook.getSheetAt(0);
header = sheet.getPhysicalNumberOfRows() == 0 ? null : readRow(0);
} catch (IOException | InvalidFormatException e) {
throw new IllegalArgumentException(e);
}
}
private Sheet getSheet(Workbook workbook) {
Sheet sheet;
int numberOfSheets = workbook.getNumberOfSheets();
if ((getXslSheetNumber() != null) && (!(getXslSheetNumber().isEmpty()))) {
int sheetNumber = Integer.parseInt(getXslSheetNumber()) - 1;
if (sheetNumber > numberOfSheets) {
logger.error("Wrong sheet number, using first sheet as default");
// if not specified take first sheet
sheet = workbook.getSheetAt(0);
}
sheet = workbook.getSheetAt(sheetNumber);
} else {
// if not specified take first sheet
sheet = workbook.getSheetAt(0);
}
return sheet;
}
private boolean validateExcel(File excelFile, FileOutputStream out)
throws FileNotFoundException, IOException, AxelorException {
Workbook workBook = new XSSFWorkbook(new FileInputStream(excelFile));
boolean flag = true;
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
Sheet sheet = workBook.getSheetAt(i);
StringBuilder errorList = new StringBuilder();
errorList.append("\n" + "Sheet : " + sheet.getSheetName());
if (!this.validateSheet(sheet, errorList)) {
out.write(errorList.toString().getBytes());
flag = false;
out.write("\n".getBytes());
}
}
return flag;
}
public void read(Workbook workbook, SheeDataModelConsumer consumer){
Assert.notNull(workbook, "workbook can not be null");
try {
int sheetCount = workbook.getNumberOfSheets();
Sheet sheet = null;
for(int sheetIndex=0; sheetIndex<sheetCount; sheetIndex++){
sheet = workbook.getSheetAt(sheetIndex);
if(sheet.getPhysicalNumberOfRows()<1)
continue;
for(SheetStreamReader<?> reader : sheetReaders) {
if (reader.match(sheetIndex)) {
Object dataInst = reader.onRead(sheet, sheetIndex);
if (consumer!=null && dataInst!=null) {
consumer.onSheetReadCompleted(dataInst);
}
}
}
}
}catch (Exception e) {
throw ExcelUtils.wrapAsUnCheckedException("read excel file error.", e);
}
}
public void populateData() throws SQLException {
Workbook workbook = ((TExcelConnection) getConnection()).getWorkbook();
int noOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < noOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
String sheetName = sheet.getSheetName();
ColumnInfo[] headers = this.extractColumnHeaders(sheet);
DataTable dataTable = new FixedDataTable(sheetName, headers);
Iterator<Row> rowItr = sheet.rowIterator();
while (rowItr.hasNext()) {
Row row = rowItr.next();
if (row.getRowNum() != 0) {
DataRow dataRow = new DataRow(row.getRowNum() - 1);
Iterator<Cell> cellItr = row.cellIterator();
int cellIndex = 0;
while (cellItr.hasNext()) {
Cell cell = cellItr.next();
DataCell dataCell =
new DataCell(cellIndex + 1, cell.getCellType(), extractCellValue(cell));
dataRow.addCell(dataCell.getColumnId(), dataCell);
cellIndex++;
}
dataTable.addRow(dataRow);
}
}
this.getData().put(dataTable.getTableName(), dataTable);
}
}
/**
* 描述：获取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 void openWorkbook(Workbook workbook) throws IOException {
try {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
worksheetSelector.getItems().add(workbook.getSheetName(i));
}
sheetReader = (String sheetName) -> readSheet(workbook.getSheet(sheetName));
Platform.runLater(() -> worksheetSelector.getSelectionModel().selectFirst());
} finally {
workbook.close();
}
}
private Sheet getSheetToProcess(Workbook workbook, PoijiOptions options, String sheetName) {
int nonHiddenSheetIndex = 0;
int requestedIndex = options.sheetIndex();
Sheet sheet = null;
if (options.ignoreHiddenSheets()) {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
if (!workbook.isSheetHidden(i) && !workbook.isSheetVeryHidden(i)) {
if (sheetName == null) {
if (nonHiddenSheetIndex == requestedIndex) {
return workbook.getSheetAt(i);
}
} else {
if (workbook.getSheetName(i).equalsIgnoreCase(sheetName)) {
return workbook.getSheetAt(i);
}
}
nonHiddenSheetIndex++;
}
}
} else {
if (sheetName == null) {
sheet = workbook.getSheetAt(requestedIndex);
} else {
sheet = workbook.getSheet(sheetName);
}
}
return sheet;
}
protected static void evaluateAllFormulaCells(Workbook wb, FormulaEvaluator evaluator) {
for(int i=0; i<wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
for(Row r : sheet) {
for (Cell c : r) {
if (c.getCellTypeEnum() == CellType.FORMULA) {
evaluator.evaluateFormulaCellEnum(c);
}
}
}
}
}
private void freezeTitles(Workbook workbook) {
if (fixedTitles && titles != null) {
for (int i = 0, size = workbook.getNumberOfSheets(); i < size; i++) {
workbook.getSheetAt(i).createFreezePane(0, titles.size());
}
}
}
/**
* 读取excel指定页的数据
* @param path
* @param sheetNum
* @return
*/
public static List<List<String>> readExcel(String path, int sheetNum) {
List<List<String>> listSheet = null;
Workbook wb = getWorkbook(path, false);
int sheets = wb.getNumberOfSheets();
if(sheetNum <= sheets && sheetNum >=0){
//获取sheet
Sheet sheet = wb.getSheetAt(sheetNum);
System.out.println(sheet.getLastRowNum());
listSheet = getSheet(sheet);
}
return listSheet;
}
private File createZIPFromExcel(File excelFile)
throws IOException, ParseException, AxelorException {
Workbook workBook = new XSSFWorkbook(new FileInputStream(excelFile));
File zipFile = File.createTempFile("demo", ".zip");
List<String> entries = new ArrayList<>();
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
Sheet sheet = workBook.getSheetAt(i);
File csvFile =
new File(excelFile.getParent() + File.separator + this.getFileNameFromSheet(sheet));
try (ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(zipFile)); ) {
excelToCSV.writeTOCSV(csvFile, sheet, 3, 1);
if (entries.contains(csvFile.getName())) {
throw new AxelorException(
TraceBackRepository.CATEGORY_INCONSISTENCY,
I18n.get(IExceptionMessage.DUPLICATE_CSV_FILE_NAME_EXISTS));
}
entries.add(csvFile.getName());
this.writeToZip(csvFile, zos);
} finally {
csvFile.delete();
}
}
entries.clear();
return zipFile;
}
public Workbook createExcleByTemplate(TemplateExportParams params, Class<?> pojoClass,
Collection<?> dataSet, Map<String, Object> map) {
// step 1. 判断模板的地址
if (params == null || map == null || StringUtils.isEmpty(params.getTemplateUrl())) {
throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
}
Workbook wb = null;
// step 2. 判断模板的Excel类型,解析模板
try {
this.teplateParams = params;
wb = getCloneWorkBook();
// 创建表格样式
setExcelExportStyler((IExcelExportStyler) teplateParams.getStyle()
.getConstructor(Workbook.class).newInstance(wb));
// step 3. 解析模板
for (int i = 0, le = params.isScanAllsheet() ? wb.getNumberOfSheets() : params
.getSheetNum().length; i < le; i++) {
if (params.getSheetName() != null && params.getSheetName().length > i
&& StringUtils.isNotEmpty(params.getSheetName()[i])) {
wb.setSheetName(i, params.getSheetName()[i]);
}
tempCreateCellSet.clear();
parseTemplate(wb.getSheetAt(i), map);
}
if (dataSet != null) {
// step 4. 正常的数据填充
dataHanlder = params.getDataHanlder();
if (dataHanlder != null) {
needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields());
}
addDataToSheet(pojoClass, dataSet, wb.getSheetAt(params.getDataSheetNum()), wb);
}
} catch (Exception e) {
LOGGER.error(e.getMessage(), e);
return null;
}
return wb;
}
private static <T> List<T> doParse(Map<String, String> reverseHeaderMap, InputStream inputStream,
Class<T> recordClassIfJavaBean, SepRecordType recordType, List<CellError> cellErrors)
throws InvalidFormatException, InvalidHeaderRowException {
validateReverseHeaderMap(reverseHeaderMap);
Workbook workbook = toWorkbook(inputStream);
if (workbook.getNumberOfSheets() <= 0) {
return new ArrayList<T>();
}
Sheet sheet = workbook.getSheetAt(0);
// key = columnIndex, value= {propName, headerText}
Map<Short, ColumnMeta> columnMetaMap = parseHeader(reverseHeaderMap,
sheet.getRow(0));
if (columnMetaMap.isEmpty()) {
throw new InvalidHeaderRowException();
}
// now do the data rows
List<T> records = new ArrayList<T>();
for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
T record = (T) parseDataRow(columnMetaMap, row, rowIndex, recordType, recordClassIfJavaBean,
cellErrors);
records.add(record);
}
return records;
}
@Override
public Sheet selectSheetFrom(Workbook workbook) throws SheetNotFoundException {
if (workbook.getNumberOfSheets() > index) {
return workbook.getSheetAt(index);
}
throw new SheetNotFoundException(ERROR_MESSAGE, index + 1, workbook.getNumberOfSheets());
}
private static Workbook createSheetInUserModel2FileByTemplate(
TemplateExportParams params, Class<?> pojoClass,
Collection<?> dataSet, Map<String, Object> map) {
// step 1. 判断模板的地址
if (StringUtils.isEmpty(params.getTemplateUrl())) {
return null;
}
Workbook wb = null;
// step 2. 判断模板的Excel类型,解析模板
try {
wb = getCloneWorkBook(params);
// step 3. 删除其他的sheet
for (int i = wb.getNumberOfSheets()-1; i >= 0; i--) {
if (i != params.getSheetNum()) {
wb.removeSheetAt(i);
}
}
if(StringUtils.isNotEmpty(params.getSheetName())){
wb.setSheetName(0, params.getSheetName());
}
// step 4. 解析模板
parseTemplate(wb.getSheetAt(0), map);
if(dataSet != null){
// step 5. 正常的数据填充
addDataToSheet(params, pojoClass, dataSet, wb.getSheetAt(0), wb);
}
} catch (Exception e) {
e.printStackTrace();
return null;
}
return wb;
}
private int sheetCirculation(Workbook wb) {
int sheetCount = -1;
sheetCount = wb.getNumberOfSheets();
return sheetCount;
}
/**
* 通用读取Excel
*
* @Title: readExcel
* @Date : 2014-9-11 上午11:26:53
* @param wb
* @return
*/
public List<List<Row>> readExcel(Workbook wb) {
List<List<Row>> list = new ArrayList<>();
List<Row> rowList = new ArrayList<>();
int sheetCount = 1;// 需要操作的sheet数量
Sheet sheet = null;
if (onlyReadOneSheet) { // 只操作一个sheet
// 获取设定操作的sheet(如果设定了名称，按名称查，否则按索引值查)
sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
} else { // 操作多个sheet
sheetCount = wb.getNumberOfSheets();// 获取可以操作的总数量
}
// 获取sheet数目
for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) {
// 获取设定操作的sheet
if (!onlyReadOneSheet) {
sheet = wb.getSheetAt(t);
}
// 获取最后行号
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum > 0) { // 如果>0，表示有数据
out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：");
}
Row row = null;
// 循环读取
for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) {
row = sheet.getRow(i);
if (row != null) {
rowList.add(row);
out("第" + (i + 1) + "行：", false);
// 获取每一单元格的值
for (int j = 0; j < row.getLastCellNum(); j++) {
String value = getCellValue(row.getCell(j));
if (!value.equals("")) {
out(value + " | ", false);
}
}
out("");
}
}
list.add(rowList);
}
return list;
}