Java Code Examples for org.apache.poi.ss.usermodel.Workbook#getNumberOfSheets()

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);
	}
}
 

/**
 * 读取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;
}
 

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 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 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());
        }
    }
}
 

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;
}
 

@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 <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;
}
 

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 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;
}
 

/**
 * 读取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;
}
 

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);
    }
}
 

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 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;
}
 

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();
    }
}
 

/**
 * 描述：获取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;

}
 

/**
 * 通用读取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;
}
 

private int sheetCirculation(Workbook wb) {
	int sheetCount = -1;
	sheetCount = wb.getNumberOfSheets();
	return sheetCount;
}