Example usage for org.apache.poi.ss.usermodel WorkbookFactory create

List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. WorkbookFactory
  6. create

Introduction

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

Prototype

public static Workbook create(File file) throws IOException, EncryptedDocumentException

Source Link

Document

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

Usage

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5180IT.java

License:Open Source License

private void validateExcelSheet(final ByteArrayOutputStream boutSlow, final TableModel data)
        throws IOException, InvalidFormatException {
    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutSlow.toByteArray()));
    Sheet sheet = workbook.getSheetAt(0);
    Assert.assertEquals(0, sheet.getFirstRowNum());
    Assert.assertEquals(data.getRowCount() - 1, sheet.getLastRowNum());

    for (int r = 0; r < data.getRowCount(); r += 1) {
        Row row = sheet.getRow(r);/* w w  w. ja  va2  s  .c  o m*/
        for (int c = 0; c < data.getColumnCount(); c += 1) {
            Cell cell = row.getCell(c);

            Object valueAt = data.getValueAt(r, c);
            if (valueAt == null) {
                if (cell != null) {
                    // excel cells never return null
                    Assert.assertEquals("", cell.getStringCellValue());
                }
            } else {
                Assert.assertEquals(valueAt, cell.getStringCellValue());
            }
        }

    }

}

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5268IT.java

License:Open Source License

@Test
public void testSheetNames() throws Exception {
    MasterReport report = createReport();
    Assert.assertFalse(new ReportStructureValidator().isValidForFastProcessing(report));

    ByteArrayOutputStream boutFast = new ByteArrayOutputStream();
    FastExcelReportUtil.processXlsx(report, boutFast);

    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutFast.toByteArray()));
    Assert.assertEquals(3, workbook.getNumberOfSheets());
    Assert.assertEquals("FIRST REPORT", workbook.getSheetName(0));
    Assert.assertEquals("SECOND REPORT", workbook.getSheetName(1));
    Assert.assertEquals("SECOND REPORT 2", workbook.getSheetName(2));
}

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5268IT.java

License:Open Source License

@Test
public void testSheetContent() throws Exception {
    MasterReport report = createReport();
    Assert.assertFalse(new ReportStructureValidator().isValidForFastProcessing(report));

    ByteArrayOutputStream boutFast = new ByteArrayOutputStream();
    FastExcelReportUtil.processXlsx(report, boutFast);

    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutFast.toByteArray()));
    Assert.assertEquals(3, workbook.getNumberOfSheets());
    assertSheetNotEmpty(workbook.getSheetAt(0));
    assertSheetNotEmpty(workbook.getSheetAt(1));
    assertSheetNotEmpty(workbook.getSheetAt(2));
}

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5268Test.java

License:Open Source License

@Test
public void testSheetNamesInFastMode() throws Exception {
    MasterReport report = createReport();
    Assert.assertTrue(new ReportStructureValidator().isValidForFastProcessing(report));

    ByteArrayOutputStream boutFast = new ByteArrayOutputStream();
    FastExcelReportUtil.processXlsx(report, boutFast);

    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutFast.toByteArray()));
    Assert.assertEquals(3, workbook.getNumberOfSheets());
    Assert.assertEquals("FIRST REPORT", workbook.getSheetName(0));
    Assert.assertEquals("SECOND REPORT", workbook.getSheetName(1));
    Assert.assertEquals("SECOND REPORT 2", workbook.getSheetName(2));
}

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd5268Test.java

License:Open Source License

@Test
public void testSheetContent() throws Exception {
    MasterReport report = createReport();
    Assert.assertTrue(new ReportStructureValidator().isValidForFastProcessing(report));

    ByteArrayOutputStream boutFast = new ByteArrayOutputStream();
    FastExcelReportUtil.processXlsx(report, boutFast);

    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(boutFast.toByteArray()));
    Assert.assertEquals(3, workbook.getNumberOfSheets());
    assertSheetNotEmpty(workbook.getSheetAt(0));
    assertSheetNotEmpty(workbook.getSheetAt(1));
    assertSheetNotEmpty(workbook.getSheetAt(2));
}

From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelPrinterBase.java

License:Open Source License

protected Workbook createWorkbook() {
    // Not opened yet. Lets do this now.
    if (templateInputStream != null) {
        // do some preprocessing ..
        try {//from   www .j  a v a  2  s. c om
            final Workbook workbook = WorkbookFactory.create(templateInputStream);

            // OK, we have a workbook, but we can't stop here..
            final int sheetCount = workbook.getNumberOfSheets();
            for (int i = 0; i < sheetCount; i++) {
                final String sheetName = workbook.getSheetName(i);
                // make sure that that name is marked as used ..
                makeUnique(sheetName);
            }

            return workbook;
        } catch (IOException e) {
            logger.warn("Unable to read predefined xls-data.", e);
        } catch (InvalidFormatException e) {
            logger.warn("Unable to read predefined xls-data.", e);
        }
    }
    if (isUseXlsxFormat()) {
        return new XSSFWorkbook();
    } else {
        return new HSSFWorkbook();
    }
}

From source file:org.pentaho.reporting.ui.datasources.table.ImportFromFileTask.java

License:Open Source License

private void importFromFile(final File file, final boolean firstRowIsHeader) {
    final ByteArrayOutputStream bout = new ByteArrayOutputStream(Math.max(8192, (int) file.length()));
    try {/*w w w . j av  a2 s.co  m*/
        final InputStream fin = new FileInputStream(file);
        try {
            IOUtils.getInstance().copyStreams(new BufferedInputStream(fin), bout);
        } finally {
            fin.close();
        }

        if (Thread.currentThread().isInterrupted()) {
            return;
        }

        final Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray()));
        int sheetIndex = 0;
        if (workbook.getNumberOfSheets() > 1) {
            final SheetSelectorDialog selectorDialog = new SheetSelectorDialog(workbook, parent);
            if (selectorDialog.performSelection()) {
                sheetIndex = selectorDialog.getSelectedIndex();
            } else {
                return;
            }
        }

        final TypedTableModel tableModel = new TypedTableModel();
        final Sheet sheet = workbook.getSheetAt(sheetIndex);
        final Iterator rowIterator = sheet.rowIterator();

        if (firstRowIsHeader) {
            if (rowIterator.hasNext()) {
                final Row headerRow = (Row) rowIterator.next();
                final short cellCount = headerRow.getLastCellNum();
                for (short colIdx = 0; colIdx < cellCount; colIdx++) {
                    final Cell cell = headerRow.getCell(colIdx);
                    if (cell != null) {
                        while (colIdx > tableModel.getColumnCount()) {
                            tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
                                    String.valueOf(tableModel.getColumnCount())), Object.class);
                        }

                        final RichTextString string = cell.getRichStringCellValue();
                        if (string != null) {
                            tableModel.addColumn(string.getString(), Object.class);
                        } else {
                            tableModel.addColumn(
                                    Messages.getString("TableDataSourceEditor.Column", String.valueOf(colIdx)),
                                    Object.class);
                        }
                    }
                }
            }
        }

        Object[] rowData = null;
        while (rowIterator.hasNext()) {
            final Row row = (Row) rowIterator.next();
            final short cellCount = row.getLastCellNum();
            if (cellCount == -1) {
                continue;
            }
            if (rowData == null || rowData.length != cellCount) {
                rowData = new Object[cellCount];
            }

            for (short colIdx = 0; colIdx < cellCount; colIdx++) {
                final Cell cell = row.getCell(colIdx);

                final Object value;
                if (cell != null) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        final RichTextString string = cell.getRichStringCellValue();
                        if (string != null) {
                            value = string.getString();
                        } else {
                            value = null;
                        }
                    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        final CellStyle hssfCellStyle = cell.getCellStyle();
                        final short dataFormat = hssfCellStyle.getDataFormat();
                        final String dataFormatString = hssfCellStyle.getDataFormatString();
                        if (isDateFormat(dataFormat, dataFormatString)) {
                            value = cell.getDateCellValue();
                        } else {
                            value = cell.getNumericCellValue();
                        }
                    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                        value = cell.getBooleanCellValue();
                    } else {
                        value = cell.getStringCellValue();
                    }
                } else {
                    value = null;
                }

                if (value != null && "".equals(value) == false) {
                    while (colIdx >= tableModel.getColumnCount()) {
                        tableModel.addColumn(Messages.getString("TableDataSourceEditor.Column",
                                String.valueOf(tableModel.getColumnCount())), Object.class);
                    }
                }

                rowData[colIdx] = value;
            }

            if (Thread.currentThread().isInterrupted()) {
                return;
            }

            tableModel.addRow(rowData);
        }

        final int colCount = tableModel.getColumnCount();
        final int rowCount = tableModel.getRowCount();
        for (int col = 0; col < colCount; col++) {
            Class type = null;
            for (int row = 0; row < rowCount; row += 1) {
                final Object value = tableModel.getValueAt(row, col);
                if (value == null) {
                    continue;
                }
                if (type == null) {
                    type = value.getClass();
                } else if (type != Object.class) {
                    if (type.isInstance(value) == false) {
                        type = Object.class;
                    }
                }
            }

            if (Thread.currentThread().isInterrupted()) {
                return;
            }

            if (type != null) {
                tableModel.setColumnType(col, type);
            }
        }

        parent.importComplete(tableModel);
    } catch (Exception e) {
        parent.importFailed(e);
        logger.error("Failed to import spreadsheet", e); // NON-NLS
    }
}

From source file:org.pharmgkb.ItpcSheet.java

License:LGPL

/**
 * Constructor for an ITPC data file/* w ww . jav  a  2 s. co m*/
 * <br/>
 * Expectations for <code>file</code> parameter:
 * <ol>
 * <li>file is an Excel .XLS formatted spreadsheet</li>
 * <li>there is a sheet in the file called "Combined_Data"</li>
 * <li>the sheet has the first row as column headers</li>
 * <li>the sheet has the second row as column legends</li>
 * </ol>
 * After this has been initialized, samples can be gathered by using the <code>getSampleIterator</code> method
 * @param file an Excel .XLS file
 * @param doHighlighting highlight changed cells in the output file
 * @throws Exception can occur from file I/O
 */
public ItpcSheet(File file, boolean doHighlighting) throws Exception {
    if (file == null || !(file.getName().endsWith(".xls") || file.getName().endsWith(".xlsx"))) {
        throw new Exception("File not in right format: " + file);
    }

    inputFile = file;
    InputStream inputFileStream = null;
    sf_logger.info("Using input file: " + inputFile);

    try {
        inputFileStream = new FileInputStream(inputFile);
        Workbook inputWorkbook = WorkbookFactory.create(inputFileStream);
        Sheet inputSheet = inputWorkbook.getSheet(SHEET_NAME);
        if (inputSheet == null) {
            throw new Exception("Cannot find worksheet named " + SHEET_NAME);
        }

        m_dataSheet = inputSheet;
        if (doHighlighting) {
            doHighlighting();
        }

        parseColumnIndexes();

        PoiWorksheetIterator sampleIterator = new PoiWorksheetIterator(m_dataSheet);
        setSampleIterator(sampleIterator);
        skipNext(); // skip header row
        skipNext(); // skip legend row
    } catch (Exception ex) {
        throw new Exception("Error initializing ITPC Sheet", ex);
    } finally {
        if (inputFileStream != null) {
            IOUtils.closeQuietly(inputFileStream);
        }
    }
}