Example usage for org.apache.poi.ss.usermodel Workbook getNumberOfSheets

List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getNumberOfSheets

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getNumberOfSheets.

Prototype

int getNumberOfSheets();

Source Link

Document

Get the number of spreadsheets in the workbook

Usage

From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java

License:Open Source License

public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception {
    assert wb != null;
    assert reportFile != null;

    //Precompute formula
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);//from  w  w  w . j a  v  a  2  s . c  o  m

        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    try {
                        evaluator.evaluateFormulaCell(c);
                    } catch (Exception e) {
                        System.err.println(e);
                    }
                }
            }
        }
    }

    File tmp = File.createTempFile("tmp_", ".xlsx");
    try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) {
        wb.write(out);
    }

    //Find page orientation
    int maxColumnsGlobal = 0;
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum());
        }
    }

    Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate();
    Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f);

    PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile));
    addHeader(writer, header);
    pdfDocument.open();
    //we have two columns in the Excel sheet, so we create a PDF table with two columns
    //Note: There are ways to make this dynamic in nature, if you want to.
    //Loop through sheets
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);

        //Loop through rows, to find number of columns
        int minColumns = 1000;
        int maxColumns = 0;
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            if (row.getFirstCellNum() >= 0)
                minColumns = Math.min(minColumns, row.getFirstCellNum());
            if (row.getLastCellNum() >= 0)
                maxColumns = Math.max(maxColumns, row.getLastCellNum());
        }
        if (maxColumns == 0)
            continue;

        //Loop through first rows, to find relative width
        float[] widths = new float[maxColumns];
        int totalWidth = 0;
        for (int c = 0; c < maxColumns; c++) {
            int w = sheet.getColumnWidth(c);
            widths[c] = w;
            totalWidth += w;
        }

        for (int c = 0; c < maxColumns; c++) {
            widths[c] /= totalWidth;
        }

        //Create new page and a new chapter with the sheet's name
        if (sheetNo > 0)
            pdfDocument.newPage();
        Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1);

        PdfPTable pdfTable = null;
        PdfPCell pdfCell = null;
        boolean inTable = false;

        //Loop through cells, to create the content
        //         boolean leftBorder = true;
        //         boolean[] topBorder = new boolean[maxColumns+1];
        for (int r = 0; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);

            //Check if we exited a table (empty line)
            if (row == null) {
                if (pdfTable != null) {
                    addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
                    pdfTable = null;
                }
                inTable = false;
                continue;
            }

            //Check if we start a table (>MIN_COL_IN_TABLE columns)
            if (row.getLastCellNum() >= MIN_COL_IN_TABLE) {
                inTable = true;
            }

            if (!inTable) {
                //Process the data outside table, just add the text
                boolean hasData = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        continue;
                    Chunk chunk = getChunk(wb, cell);
                    pdfSheet.add(chunk);
                    pdfSheet.add(new Chunk(" "));
                    hasData = true;
                }
                if (hasData)
                    pdfSheet.add(Chunk.NEWLINE);

            } else {
                //Process the data in table
                if (pdfTable == null) {
                    //Create table
                    pdfTable = new PdfPTable(maxColumns);
                    pdfTable.setWidths(widths);
                    //                  topBorder = new boolean[maxColumns+1];
                }

                int cellNumber = minColumns;
                //               leftBorder = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    for (; cellNumber < cell.getColumnIndex(); cellNumber++) {
                        pdfCell = new PdfPCell();
                        pdfCell.setBorder(0);
                        pdfTable.addCell(pdfCell);
                    }

                    Chunk phrase = getChunk(wb, cell);
                    pdfCell = new PdfPCell(new Phrase(phrase));
                    pdfCell.setFixedHeight(row.getHeightInPoints() - 3);
                    pdfCell.setNoWrap(!cell.getCellStyle().getWrapText());
                    pdfCell.setPaddingLeft(1);
                    pdfCell.setHorizontalAlignment(
                            cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER
                                    : cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT
                                            ? PdfPCell.ALIGN_RIGHT
                                            : PdfPCell.ALIGN_LEFT);
                    pdfCell.setUseBorderPadding(false);
                    pdfCell.setUseVariableBorders(false);
                    pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f);
                    pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0
                            : cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0
                            : cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0
                            : cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f);
                    String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null
                            : ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex();
                    if (color != null)
                        pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2))));
                    pdfTable.addCell(pdfCell);
                    cellNumber++;
                }
                for (; cellNumber < maxColumns; cellNumber++) {
                    pdfCell = new PdfPCell();
                    pdfCell.setBorder(0);
                    pdfTable.addCell(pdfCell);
                }
            }

            //Custom code to add all images on the first sheet (works for reporting)
            if (sheetNo == 0 && row.getRowNum() == 0) {
                for (PictureData pd : wb.getAllPictures()) {
                    try {
                        Image pdfImg = Image.getInstance(pd.getData());
                        pdfImg.scaleToFit(
                                pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft()
                                        - pageSize.getBorderWidthRight(),
                                pageSize.getHeight() * .8f - pageSize.getBorderWidthTop()
                                        - pageSize.getBorderWidthBottom());
                        pdfSheet.add(pdfImg);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        if (pdfTable != null) {
            addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
        }

        pdfDocument.add(pdfSheet);
    }
    pdfDocument.close();

}

From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServletTest.java

License:Apache License

/**
 * Generate a worksheet, just confirming that there are no blatant runtime errors and the data was recorded in the output
 *//*from   w w  w.  jav  a2  s . c  o m*/
@Test
public void createSpreadsheet() throws Exception {
    addFailure("path1", "error1", "stacktrace1");
    addFailure("path2", "error2", "stacktrace2");
    addFailure("path3", "error3", "stacktrace3");
    addFailure("path4", "error4", "stacktrace4");
    Workbook wb = servlet.createSpreadsheet(process);
    assertNotNull("Created workbook", wb);
    assertEquals("Created one sheet", 1, wb.getNumberOfSheets());
    assertEquals("Created correct number of rows", 5, wb.getSheetAt(0).getPhysicalNumberOfRows());
}

From source file:com.adobe.ags.curly.controller.DataImporterController.java

License:Apache License

private void openWorkbook(Workbook workbook) throws IOException {
    try {//  w  w w.  j  a  v  a2 s  .  c o m
        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();
    }
}

From source file:com.aurel.track.exchange.excel.ExcelFieldMatchBL.java

License:Open Source License

/**
 * Get the workbook and returns its sheets
 * @param workbook//from  w ww  . ja  v  a  2s  .com
 * @return Map<SheetID, SheetName>
 */
static List<IntegerStringBean> loadSheetNames(Workbook workbook) {
    List<IntegerStringBean> sheetList = new ArrayList<IntegerStringBean>();
    if (workbook != null) {
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            sheetList.add(new IntegerStringBean(workbook.getSheetName(i), Integer.valueOf(i)));
        }
    }
    return sheetList;
}

From source file:com.aurel.track.lucene.util.poi.XLSTextStripper.java

License:Open Source License

public XLSTextStripper(FileInputStream fis, String fileExtension) {
    try {//from ww  w. j ava2s.  co m
        StringBuffer sb = new StringBuffer();
        Workbook workbook = null;
        if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLS.equalsIgnoreCase(fileExtension)) {
            workbook = new HSSFWorkbook(fis);
        } else {
            if (LuceneFileExtractor.INDEXABLE_EXTENSIONS.XLSX.equalsIgnoreCase(fileExtension)) {
                workbook = new XSSFWorkbook(fis);
            }
        }
        if (workbook != null) {
            int numOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        String cellStringValue = null;
                        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                            boolean booleanValue = cell.getBooleanCellValue();
                            cellStringValue = Boolean.toString(booleanValue);
                        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                            double doubleValue = cell.getNumericCellValue();
                            cellStringValue = Double.toString(doubleValue);
                        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                            cellStringValue = cell.getStringCellValue();
                        }
                        if (cellStringValue != null) {
                            sb.append(cellStringValue);
                            sb.append("\t");
                        }
                    }
                    sb.append("\n");
                }
            }
        }
        _text = sb.toString();
    } catch (Exception e) {
        LOGGER.error(ExceptionUtils.getStackTrace(e));
    }
}

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelImportJob.java

License:Apache License

public static Set<Sheet> collectSheets(final Workbook workbook) {
    final Set<Sheet> sheets = Sets.newHashSet();
    final int numberOfSheets = workbook.getNumberOfSheets();

    for (int i = 0; i < numberOfSheets; i++) {
        final Sheet sheet = workbook.getSheetAt(i);

        if (!INDEX_SHEET.equalsIgnoreCase(sheet.getSheetName())) {
            sheets.add(sheet);/*from ww  w . j  a  v  a2s. co m*/
        }

    }

    return sheets;
}

From source file:com.b2international.snowowl.snomed.importer.net4j.SnomedSubsetImportUtil.java

License:Apache License

private boolean processExcelFile(final SubsetEntry entry) throws InvalidFormatException, IOException {

    final FileInputStream inputStream = createFileInputStream(entry);
    final Workbook workbook = WorkbookFactory.create(inputStream);
    final List<Integer> list = getSheetAndFirstRowNumber(workbook, workbook.getNumberOfSheets());

    if (null != list) {
        final int sheetNumber = list.get(0);
        final int firstRowNumber = list.get(1);
        final Sheet sheet = workbook.getSheetAt(sheetNumber);
        final List<String> row = collectRowValues(sheet.getRow(firstRowNumber));

        entry.setHeadings(row);/*from  ww w  .  ja  v  a2  s .  co m*/
        entry.setSheetNumber(sheetNumber);

        if (entry.isHasHeader()) {
            Optional<String> match = FluentIterable.from(row).firstMatch(new Predicate<String>() {
                @Override
                public boolean apply(String input) {
                    return input.contains("concept") && (input.contains("id") || input.contains("sctid"));
                }
            });
            entry.setIdColumnNumber(match.isPresent() ? row.indexOf(match.get()) : 0); // default to first?
        } else {
            for (int i = 0; i < row.size(); i++) {
                if (isConceptId(row.get(i).trim())) {
                    entry.setIdColumnNumber(i);
                }
            }
        }

        return true;
    } else {
        return false;
    }
}

From source file:com.ben12.reta.util.RETAAnalysis.java

License:Open Source License

public void writeExcel(Window parent) throws IOException, InvalidFormatException {
    logger.info("Start write excel output");

    Path outputFile = Paths.get(output);
    if (!outputFile.isAbsolute()) {
        Path root = config.getAbsoluteFile().getParentFile().toPath();
        outputFile = root.resolve(outputFile);
    }/* w w w.  j  a va  2 s  . c o m*/

    // test using template
    InputStream is = getClass().getResourceAsStream("/com/ben12/reta/resources/template/template.xlsx");
    ExcelTransformer transformer = new ExcelTransformer();
    List<String> sheetNames = new ArrayList<>();
    List<String> sheetTemplateNames = new ArrayList<>();
    for (InputRequirementSource requirementSource : requirementSources.values()) {
        sheetTemplateNames.add("DOCUMENT");
        sheetTemplateNames.add("COVERAGE");
        sheetNames.add(requirementSource.getName());
        sheetNames.add(requirementSource.getName() + " coverage");
    }

    List<Map<String, Object>> sheetValues = new ArrayList<>();
    for (InputRequirementSource source : requirementSources.values()) {
        Map<String, Object> values = new HashMap<>();
        values.put("source", source);
        values.put("null", null);
        values.put("line", "\n");

        Set<String> attributes = new LinkedHashSet<>();
        attributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            attributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        attributes.addAll(source.getAttributesGroup().keySet());
        attributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("attributes", attributes);

        Set<String> refAttributes = new LinkedHashSet<>();
        refAttributes.add(Requirement.ATTRIBUTE_ID);
        if (source.getRefAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) {
            refAttributes.add(Requirement.ATTRIBUTE_VERSION);
        }
        refAttributes.addAll(source.getRefAttributesGroup().keySet());
        refAttributes.remove(Requirement.ATTRIBUTE_TEXT);
        values.put("refAttributes", refAttributes);

        sheetValues.add(values);
        sheetValues.add(values);
    }

    Workbook wb = transformer.transform(is, sheetTemplateNames, sheetNames, sheetValues);
    int sheetCount = wb.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
        Sheet sheet = wb.getSheetAt(i);
        int columns = 0;
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row != null) {
                row.setHeight((short) -1);
                columns = Math.max(columns, row.getLastCellNum() + 1);
            }
        }
        for (int j = 0; j < columns; j++) {
            sheet.autoSizeColumn(j);
        }
    }

    try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
        wb.write(fos);
    } catch (FileNotFoundException e) {
        int confirm = MessageDialog.showQuestionMessage(null, "Excel output file must be closed.");

        if (confirm == MessageDialog.OK_OPTION) {
            try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
                wb.write(fos);
            } catch (IOException e2) {
                throw e2;
            }
        } else {
            throw e;
        }
    }

    logger.info("End write excel output");
}

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataTableWriterExcelManual.java

License:Apache License

@Test
public void testMultiSheet() throws Exception {
    RecordDef recordDef = createSimpleRecordDef();
    DataTable dataSet = new DataTable(recordDef);

    for (int i = 0; i < DataSetWriterExcel.EXCEL_MAX_ROWS; i++) {
        Record record = new Record(recordDef);
        for (FieldDef fieldDef : recordDef) {
            record.setFieldValue(fieldDef.getName(), fieldDef.getName() + " test value " + i);
        }/* w  ww  . j ava2s . c  o m*/
        dataSet.add(record);
    }

    DataSetWriterExcel writer = new DataSetWriterExcel(); // Pass a filename
    // if you want an
    // output file
    writer.write(dataSet);
    Workbook wb = writer.getWorkbook();
    assertEquals(2, wb.getNumberOfSheets());

    // Second sheet
    Sheet sheet = wb.getSheetAt(1);
    assertEquals(2, sheet.getLastRowNum());

    // Last row
    Row row = sheet.getRow(2);
    assertEquals("applicationVersion test value 1048575", row.getCell(1).getStringCellValue());
}

From source file:com.blackducksoftware.tools.commonframework.standard.workbook.CsvWriter.java

License:Apache License

@Override
public void write(Workbook wb) throws IOException {

    int numSheets = wb.getNumberOfSheets();
    for (int i = 0; i < numSheets; i++) {

        File curOutputFile = getCurrentOutputFile(filePath, numSheets, i);

        CSVWriter pw = new CSVWriter(new OutputStreamWriter(new FileOutputStream(curOutputFile)),
                CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, "\r\n");

        try {/*from   w  w  w  .j  av  a  2s.  c  om*/
            Sheet sheet = wb.getSheetAt(i);
            for (Row row : sheet) {
                List<String> cells = new ArrayList<String>();
                String cellValue = "";
                for (Cell cell : row) {
                    int cellType = cell.getCellType();
                    switch (cellType) {
                    case Cell.CELL_TYPE_BLANK:
                        cellValue = "";
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        boolean cellValueBoolean = cell.getBooleanCellValue();
                        cellValue = cellValueBoolean ? "true" : "false";
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cellValue = "<error: " + cell.getErrorCellValue() + ">";
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cellValue = cell.getCellFormula();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        double cellValueDouble = cell.getNumericCellValue();
                        cellValue = Double.toString(cellValueDouble);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    default:
                        break;
                    }

                    cells.add(cellValue);
                }
                String[] typeExample = new String[cells.size()];
                String[] cellArray = cells.toArray(typeExample);
                pw.writeNext(cellArray);
            }
        } finally {
            pw.close();
        }
    }
}