List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets
int getNumberOfSheets();
From source file:io.konig.spreadsheet.WorkbookProcessorImpl.java
License:Apache License
private List<WorkbookSheet> listSheets(Workbook workbook) throws SpreadsheetException { List<WorkbookSheet> list = new ArrayList<>(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); int bestRank = 0; SheetProcessor best = null;// w ww . ja v a 2s.co m for (SheetProcessor p : sheetProcessors) { int rank = rank(sheet, p); if (rank > bestRank) { bestRank = rank; best = p; } } if (best != null) { list.add(new WorkbookSheet(sheet, best)); } } Collections.sort(list); return list; }
From source file:it.greenvulcano.excel.reader.BaseReader.java
License:Open Source License
public void processExcel(Workbook workbook) throws ExcelException { cleanUp();/*from www . j a va 2s . co m*/ try { startProcess(); // Discover how many sheets there are in the workbook.... int numSheets = workbook.getNumberOfSheets(); // and then iterate through them. for (int i = 0; i < numSheets; i++) { // Get a reference to a sheet and check to see if it contains // any rows. Sheet sheet = workbook.getSheetAt(i); if (processSheet(sheet, i)) { // Note down the index number of the bottom-most row and // then iterate through all of the rows on the sheet starting // from the very first row - number 1 - even if it is missing. // Recover a reference to the row and then call another method // which will strip the data from the cells and build lines // for inclusion in the resulting object. int lastRowNum = sheet.getLastRowNum(); for (int j = 0; j <= lastRowNum; j++) { if (j % 10 == 0) { ThreadUtils.checkInterrupted(getClass().getSimpleName(), "ExcelFile", null); } Row row = sheet.getRow(j); processRow(row, i, j); } } } endProcess(); } catch (Exception exc) { throw new ExcelException("Error parsing Excel", exc); } }
From source file:itpreneurs.itp.report.archive.CellStyleDetails.java
License:Apache License
public static void main(String[] args) throws Exception { // if(args.length == 0) { // throw new IllegalArgumentException("Filename must be given"); // }//from w w w . j av a2s . c om String filename = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/Workbook1.xlsx"; Workbook wb = WorkbookFactory.create(new File(filename)); DataFormatter formatter = new DataFormatter(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { Sheet sheet = wb.getSheetAt(sn); System.out.println("Sheet #" + sn + " : " + sheet.getSheetName()); for (Row row : sheet) { System.out.println(" Row " + row.getRowNum()); for (Cell cell : row) { CellReference ref = new CellReference(cell); System.out.print(" " + ref.formatAsString()); System.out.print(" (" + cell.getColumnIndex() + ") "); CellStyle style = cell.getCellStyle(); System.out.print("Format=" + style.getDataFormatString() + " "); System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " "); System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " "); Font font = wb.getFontAt(style.getFontIndex()); System.out.print("Font=" + font.getFontName() + " "); System.out.print("FontColor="); if (font instanceof HSSFFont) { System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb))); } if (font instanceof XSSFFont) { System.out.print(renderColor(((XSSFFont) font).getXSSFColor())); } System.out.println(); System.out.println(" " + formatter.formatCellValue(cell)); } } System.out.println(); } }
From source file:jp.qpg.ExcelTo.java
License:Apache License
/** * excel to pdf//w ww .j a va2s. co m * * @param book excel workbook * @param out output to pdf * @param documentSetup document setup * @throws IOException I/O exception */ public static void pdf(Workbook book, OutputStream out, Consumer<PDFPrinter> documentSetup) throws IOException { Objects.requireNonNull(book); Objects.requireNonNull(out); try (PDFPrinter printer = new PDFPrinter()) { printer.documentSetup = Optional.ofNullable(documentSetup); for (int i = 0, end = book.getNumberOfSheets(); i < end; i++) { Sheet sheet = book.getSheetAt(i); int rowCount = sheet.getPhysicalNumberOfRows(); if (rowCount <= 0) { logger.info(sheet.getSheetName() + ": empty"); continue; /* skip blank sheet */ } logger.info(sheet.getSheetName() + ": " + rowCount + " rows"); printer.println("sheet name: " + sheet.getSheetName()); printer.println("max row index: " + sheet.getLastRowNum()); printer.println("max column index: " + Tool.stream(sheet.rowIterator(), rowCount).mapToInt(Row::getLastCellNum).max().orElse(0)); eachCell(sheet, (cell, range) -> Tool.cellValue(cell).ifPresent(value -> printer.println( '[' + (range == null ? new CellReference(cell).formatAsString() : range.formatAsString()) + "] " + value))); eachShape(sheet, shapeText(text -> printer.println("[shape text] " + text))); printer.newPage(); } printer.getDocument().save(out); } }
From source file:jp.qpg.ExcelTo.java
License:Apache License
/** * excel to text// w ww . j a v a2s. c o m * * @param book excel workbook * @param out output to text */ public static void text(Workbook book, OutputStream out) { Objects.requireNonNull(book); Objects.requireNonNull(out); try (PrintStream printer = Try.to(() -> new PrintStream(out, true, System.getProperty("file.encoding"))) .get()) { for (int i = 0, end = book.getNumberOfSheets(); i < end; i++) { Sheet sheet = book.getSheetAt(i); int rowCount = sheet.getPhysicalNumberOfRows(); if (rowCount <= 0) { logger.info(sheet.getSheetName() + ": empty"); continue; /* skip blank sheet */ } logger.info(sheet.getSheetName() + ": " + rowCount + " rows"); printer.println("sheet name: " + sheet.getSheetName()); printer.println("max row index: " + sheet.getLastRowNum()); printer.println("max column index: " + Tool.stream(sheet.rowIterator(), rowCount).mapToInt(Row::getLastCellNum).max().orElse(0)); eachCell(sheet, (cell, range) -> Tool.cellValue(cell).ifPresent(value -> printer.println( '[' + (range == null ? new CellReference(cell).formatAsString() : range.formatAsString()) + "] " + value))); sheet.getCellComments().entrySet().forEach(entry -> { printer.println("[comment " + entry.getKey() + "] " + entry.getValue().getString()); }); eachShape(sheet, shapeText(text -> printer.println("[shape text] " + text))); printer.println("--------"); } } }
From source file:kaflib.utils.FileUtils.java
License:Open Source License
/** * Reads a spreadsheet to a set of matrices (one per worksheet). * @param file/* ww w .ja v a 2 s.c o m*/ * @return * @throws Exception */ public static Map<String, Matrix<String>> readXLSX(final File file, final boolean columnTitles) throws Exception { Map<String, Matrix<String>> matrices = new HashMap<String, Matrix<String>>(); Workbook workbook = null; workbook = new XSSFWorkbook(new FileInputStream(file)); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { String name = workbook.getSheetName(i); Sheet sheet = workbook.getSheetAt(i); if (sheet == null) { continue; } Matrix<String> matrix = new Matrix<String>(); int start = 0; if (columnTitles) { Row row = sheet.getRow(0); if (row != null) { List<String> labels = new ArrayList<String>(); for (int k = 0; k < row.getLastCellNum(); k++) { labels.add(row.getCell(k).toString()); } matrix.setColumnLabels(labels); } start = 1; } for (int j = start; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row == null) { continue; } for (int k = 0; k <= row.getLastCellNum(); k++) { Cell cell = row.getCell(k); if (cell != null) { matrix.set(j - start, k, cell.toString()); } } } matrices.put(name, matrix); } workbook.close(); return matrices; }
From source file:net.bafeimao.umbrella.support.data.entity.ExcelEntityParser.java
License:Apache License
/** * Excel?Sheet???,?clazz//from w w w .j a v a 2 s . c om * <p/> * TODO {@link PrintExecutionTime}?AOP? */ @Override @PrintExecutionTime public <E extends DataEntity> LinkedList<E> parse(Class<E> entityClass) throws EntityParseException { LinkedList<E> entities = null; InputStream inputStream = null; try { ExcelMapping mapping = entityClass.getAnnotation(ExcelMapping.class); String fileName = "e:\\" + mapping.file(); String sheetName = mapping.sheet(); inputStream = new FileInputStream(fileName); // FileInputStream?Excel? Workbook wb = WorkbookFactory.create(inputStream); // Workbook,?excelsheet if (wb != null && wb.getNumberOfSheets() > 0) { Sheet sheet = wb.getSheet(sheetName); // ??? if (sheet.getRow(0) != null) { entities = parse0(entityClass, sheet); } } } catch (Exception e) { LOGGER.error("{}", e); } finally { try { if (inputStream != null) inputStream.close(); } catch (IOException e) { LOGGER.error("{}", e); } } return entities; }
From source file:net.ceos.project.poi.annotated.core.CascadeTest.java
License:Apache License
/** * Test cascade type CASCADE_BASE/*from w ww .ja v a2 s . c om*/ * * @throws WorkbookException */ @Test public void checkCascadeTypeBase() throws WorkbookException { CascadeObject cascadeObj = CascadeObjectBuilder.buildCascadeObject(); XConfigCriteria criteria = new XConfigCriteria(); criteria.overrideCascadeLevel(CascadeType.CASCADE_BASE); IEngine en = new Engine(); Workbook wb = en.marshalToWorkbook(criteria, cascadeObj); assertNotNull(wb); assertNotNull(wb.getSheet("Cascade analyser")); assertEquals(wb.getNumberOfSheets(), 1); }
From source file:net.ceos.project.poi.annotated.core.CascadeTest.java
License:Apache License
/** * Test cascade type CASCADE_L1//from ww w . j av a2 s . c o m * * @throws WorkbookException */ @Test public void checkCascadeTypeLevelOne() throws WorkbookException { CascadeObject cascadeObj = CascadeObjectBuilder.buildCascadeObject(); XConfigCriteria criteria = new XConfigCriteria(); criteria.overrideCascadeLevel(CascadeType.CASCADE_L1); IEngine en = new Engine(); Workbook wb = en.marshalToWorkbook(criteria, cascadeObj); assertNotNull(wb); assertNotNull(wb.getSheet("Cascade analyser")); assertNotNull(wb.getSheet("Basic object")); assertNotNull(wb.getSheet("Decorators")); assertNotNull(wb.getSheet("Cascade L1")); assertEquals(wb.getNumberOfSheets(), 4); }
From source file:net.ceos.project.poi.annotated.core.CascadeTest.java
License:Apache License
/** * Test cascade type CASCADE_L2//from w w w.j av a2s .co m * * @throws WorkbookException */ @Test public void checkCascadeTypeLevelTwo() throws WorkbookException { CascadeObject cascadeObj = CascadeObjectBuilder.buildCascadeObject(); XConfigCriteria criteria = new XConfigCriteria(); criteria.overrideCascadeLevel(CascadeType.CASCADE_L2); IEngine en = new Engine(); Workbook wb = en.marshalToWorkbook(criteria, cascadeObj); assertNotNull(wb); assertNotNull(wb.getSheet("Cascade analyser")); assertNotNull(wb.getSheet("Basic object")); assertNotNull(wb.getSheet("Decorators")); assertNotNull(wb.getSheet("Cascade L1")); assertNotNull(wb.getSheet("Cascade L2")); assertEquals(wb.getNumberOfSheets(), 5); }