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

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

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

Introduction

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

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

From source file:org.openmrs.module.reporting.report.renderer.XlsReportRendererTest.java

License:Open Source License

public void testValue(Workbook wb, String sheetName, int rowNum, int colNum, String value) {
    Sheet sheet = wb.getSheet(sheetName);
    Row row = CellUtil.getRow(rowNum - 1, sheet);
    Cell cell = CellUtil.getCell(row, colNum - 1);
    Assert.assertEquals(value.toLowerCase(), cell.getStringCellValue().toLowerCase());
}

From source file:org.openmrs.module.spreadsheetimport.SpreadsheetImportUtil.java

License:Open Source License

public static File importTemplate(SpreadsheetImportTemplate template, MultipartFile file, String sheetName,
        List<String> messages, boolean rollbackTransaction) throws Exception {

    if (file.isEmpty()) {
        messages.add("file must not be empty");
        return null;
    }//  w  ww . j  a  v  a2  s .  com

    // Open file
    Workbook wb = WorkbookFactory.create(file.getInputStream());
    Sheet sheet;
    if (!StringUtils.hasText(sheetName)) {
        sheet = wb.getSheetAt(0);
    } else {
        sheet = wb.getSheet(sheetName);
    }

    // Header row
    Row firstRow = sheet.getRow(0);
    if (firstRow == null) {
        messages.add("Spreadsheet header row must not be null");
        return null;
    }

    List<String> columnNames = new Vector<String>();
    for (Cell cell : firstRow) {
        columnNames.add(cell.getStringCellValue());
    }
    if (log.isDebugEnabled()) {
        log.debug("Column names: " + columnNames.toString());
    }

    // Required column names
    List<String> columnNamesOnlyInTemplate = new Vector<String>();
    columnNamesOnlyInTemplate.addAll(template.getColumnNamesAsList());
    columnNamesOnlyInTemplate.removeAll(columnNames);
    if (columnNamesOnlyInTemplate.isEmpty() == false) {
        messages.add("required column names not present: " + toString(columnNamesOnlyInTemplate));
        return null;
    }

    // Extra column names?
    List<String> columnNamesOnlyInSheet = new Vector<String>();
    columnNamesOnlyInSheet.addAll(columnNames);
    columnNamesOnlyInSheet.removeAll(template.getColumnNamesAsList());
    if (columnNamesOnlyInSheet.isEmpty() == false) {
        messages.add(
                "Extra column names present, these will not be processed: " + toString(columnNamesOnlyInSheet));
    }

    // Process rows
    boolean skipThisRow = true;
    for (Row row : sheet) {
        if (skipThisRow == true) {
            skipThisRow = false;
        } else {
            boolean rowHasData = false;
            Map<UniqueImport, Set<SpreadsheetImportTemplateColumn>> rowData = template
                    .getMapOfUniqueImportToColumnSetSortedByImportIdx();

            for (UniqueImport uniqueImport : rowData.keySet()) {
                Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport);
                for (SpreadsheetImportTemplateColumn column : columnSet) {

                    int idx = columnNames.indexOf(column.getName());
                    Cell cell = row.getCell(idx);

                    Object value = null;
                    // check for empty cell (new Encounter)
                    if (cell == null) {
                        rowHasData = true;
                        column.setValue("");
                        continue;
                    }

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        value = new Boolean(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        value = new Byte(cell.getErrorCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            java.util.Date date = cell.getDateCellValue();
                            value = "'" + new java.sql.Timestamp(date.getTime()).toString() + "'";
                        } else {
                            value = cell.getNumericCellValue();
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        // Escape for SQL
                        value = "'" + cell.getRichStringCellValue() + "'";
                        break;
                    }
                    if (value != null) {
                        rowHasData = true;
                        column.setValue(value);
                    } else
                        column.setValue("");
                }
            }

            for (UniqueImport uniqueImport : rowData.keySet()) {
                Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport);
                boolean isFirst = true;
                for (SpreadsheetImportTemplateColumn column : columnSet) {

                    if (isFirst) {
                        // Should be same for all columns in unique import
                        //                     System.out.println("SpreadsheetImportUtil.importTemplate: column.getColumnPrespecifiedValues(): " + column.getColumnPrespecifiedValues().size());
                        if (column.getColumnPrespecifiedValues().size() > 0) {
                            Set<SpreadsheetImportTemplateColumnPrespecifiedValue> columnPrespecifiedValueSet = column
                                    .getColumnPrespecifiedValues();
                            for (SpreadsheetImportTemplateColumnPrespecifiedValue columnPrespecifiedValue : columnPrespecifiedValueSet) {
                                //                           System.out.println(columnPrespecifiedValue.getPrespecifiedValue().getValue());
                            }
                        }
                    }
                }
            }

            if (rowHasData) {
                Exception exception = null;
                try {
                    DatabaseBackend.validateData(rowData);
                    String encounterId = DatabaseBackend.importData(rowData, rollbackTransaction);
                    if (encounterId != null) {
                        for (UniqueImport uniqueImport : rowData.keySet()) {
                            Set<SpreadsheetImportTemplateColumn> columnSet = rowData.get(uniqueImport);
                            for (SpreadsheetImportTemplateColumn column : columnSet) {
                                if ("encounter".equals(column.getTableName())) {
                                    int idx = columnNames.indexOf(column.getName());
                                    Cell cell = row.getCell(idx);
                                    if (cell == null)
                                        cell = row.createCell(idx);
                                    cell.setCellValue(encounterId);
                                }
                            }
                        }
                    }
                } catch (SpreadsheetImportTemplateValidationException e) {
                    messages.add("Validation failed: " + e.getMessage());
                    return null;
                } catch (SpreadsheetImportDuplicateValueException e) {
                    messages.add("found duplicate value for column " + e.getColumn().getName() + " with value "
                            + e.getColumn().getValue());
                    return null;
                } catch (SpreadsheetImportSQLSyntaxException e) {
                    messages.add("SQL syntax error: \"" + e.getSqlErrorMessage()
                            + "\".<br/>Attempted SQL Statement: \"" + e.getSqlStatement() + "\"");
                    return null;
                } catch (Exception e) {
                    exception = e;
                }
                if (exception != null) {
                    throw exception;
                }
            }
        }
    }

    // write back Excel file to a temp location
    File returnFile = File.createTempFile("sim", ".xls");
    FileOutputStream fos = new FileOutputStream(returnFile);
    wb.write(fos);
    fos.close();

    return returnFile;
}

From source file:org.openpythia.plugin.worststatements.DeltaSnapshotWriter.java

License:Apache License

private boolean saveDeltaSnapshot() {
    try {//from   www.  ja  va2s . co m
        Workbook workbook = WorkbookFactory
                .create(this.getClass().getResourceAsStream(TEMPLATE_DELTA_V_SQL_AREA_XLSX));
        statementsSheet = workbook.getSheet("Delta V$SQLAREA");
        executionPlansSheet = workbook.getSheet("Execution Plans");
        waitEventsForStatementSheet = workbook.getSheet("Wait Events per SQL Statement");
        waitEventsForTimeSpanSheet = workbook.getSheet("All Wait Events");
        hyperlinkStyle = createHyperlinkStyle(workbook);

        writeDeltaSnapshotStatements();

        List<DeltaSQLStatementSnapshot> worstStatements = getWorstSQLStatements();

        try {
            writeExecutionPlansForWorstStatements(worstStatements);
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, e);
        }

        try {
            writeWaitEventsForWorstStatements(worstStatements);
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, e);
        }

        try {
            writeWaitEventsForTimeSpan();
        } catch (Exception e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null, e);
        }

        if (listener != null) {
            listener.informFinished();
        }

        OutputStream outputStream = new FileOutputStream(destination);
        workbook.write(outputStream);
        outputStream.close();

        return true;

    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, e);
        return false;
    }
}

From source file:org.opentestsystem.delivery.testreg.integration.DownloadTemplateIntegrationTest.java

License:Open Source License

private void assertExcelTemplate(InputStream inpustream) throws Exception {
    Workbook workbook = WorkbookFactory.create(inpustream);
    assertThat(workbook.getNumberOfSheets(), is(1));
    String sheetName = new String();
    if (formatType.name().length() > 32) {
        sheetName = formatType.name().substring(0, 31);
    } else {// w ww.j  a  v  a 2s.  c  o m
        sheetName = formatType.name();
    }
    Sheet sheet = workbook.getSheet(sheetName);

    assertThat(sheet, is(notNullValue()));

    String[] columns = StringUtils.splitByWholeSeparatorPreserveAllTokens(columnString, "|");
    assertThat(sheet.getRow(HEADER_ROW).getLastCellNum(), is((short) columns.length));

    int cellNo = 0;
    for (String columnName : columns) {
        assertThat(sheet.getRow(HEADER_ROW).getCell(cellNo++).getStringCellValue(), is(columnName));
    }
}

From source file:org.opentestsystem.delivery.testreg.upload.ExcelFileAppenderTest.java

License:Open Source License

@Test
public void testSuccess() throws IOException, InvalidFormatException {
    InputStream appendedStream = appender.insertAtTop("DISTRICT", createWorkbookWithNoFormatTypeString());

    Workbook workbook = WorkbookFactory.create(appendedStream);
    assertThat(workbook.getNumberOfSheets(), is(1));

    Sheet sheet = workbook.getSheet("Test");
    assertThat(sheet, is(notNullValue()));

    //FormatType Row
    assertThat(sheet.getRow(0).getLastCellNum(), is((short) 1));
    assertThat(sheet.getRow(0).getCell(0).getStringCellValue(), is("DISTRICT"));

    //Header Row//w  w w.  ja v  a 2 s. c om
    assertThat(sheet.getRow(1).getCell(0).getStringCellValue(), is("Column1"));
    assertThat(sheet.getRow(1).getCell(1).getStringCellValue(), is("Column2"));
    assertThat(sheet.getRow(1).getCell(2).getStringCellValue(), is("Column3"));
    assertThat(sheet.getRow(1).getCell(3).getStringCellValue(), is("Column4"));
}

From source file:org.opentestsystem.delivery.testreg.upload.parser.ExcelFileUploadParserTest.java

License:Open Source License

private void addBlankRows(final Workbook workbook) throws InvalidFormatException, IOException {
    final Sheet sheet = workbook.getSheet("Test");
    addBlankCells(sheet.createRow(3));/*from  w  w  w .  j a  v a  2  s  .c  o  m*/
    addBlankCells(sheet.createRow(4));
    addBlankCells(sheet.createRow(5));
    addBlankCells(sheet.createRow(6));
    addBlankCells(sheet.createRow(7));
    addBlankCells(sheet.createRow(8));
    addBlankCells(sheet.createRow(9));
}

From source file:org.opentestsystem.delivery.testreg.upload.parser.ExcelFileUploadParserTest.java

License:Open Source License

private void addSomeRecordsWithWhitespaces(final Workbook workbook) {
    final Sheet sheet = workbook.getSheet("Test");
    final Row dataRow4 = sheet.createRow(3);
    dataRow4.createCell(0).setCellValue(" China ");
    dataRow4.createCell(1).setCellValue("Asia");
    dataRow4.createCell(2).setCellValue(" 1.35B             ");
    dataRow4.createCell(3).setCellValue("200000                         ");
    dataRow4.createCell(4).setCellValue("                     Socialist-Communist");
    dataRow4.createCell(5).setCellValue("              President                ");
    dataRow4.createCell(6).setCellValue("      8 yrs");

    final Row dataRow5 = sheet.createRow(4);
    dataRow5.createCell(0).setCellValue("Singapore     ");
    dataRow5.createCell(1).setCellValue("              Asia");
    dataRow5.createCell(2).setCellValue("5.3M");
    dataRow5.createCell(3).setCellValue(" 10000                          ");
    dataRow5.createCell(4).setCellValue("            Capitalist                                   ");
    dataRow5.createCell(5).setCellValue("       President");
    dataRow5.createCell(6).setCellValue("4 yrs      ");

}

From source file:org.opentestsystem.delivery.testreg.upload.parser.ExcelFileUploadParserTest.java

License:Open Source License

private void addBlankFormulaRows(final Workbook workbook) throws InvalidFormatException, IOException {
    final Sheet sheet = workbook.getSheet("Test");
    addFormulaCells(sheet.createRow(3));
    addFormulaCells(sheet.createRow(4));
    addFormulaCells(sheet.createRow(5));
    addFormulaCells(sheet.createRow(6));
    addFormulaCells(sheet.createRow(7));
    addFormulaCells(sheet.createRow(8));
    addFormulaCells(sheet.createRow(9));
}

From source file:org.paxml.bean.excel.ReadExcelTag.java

License:Open Source License

protected Sheet getExcelSheet(boolean createIfNone) {

    Workbook wb = file.getWorkbook();
    Sheet s = null;/*from w w  w. j  av a  2s.  co  m*/
    int index = -1;
    if (wb.getNumberOfSheets() > 0) {
        if (StringUtils.isBlank(sheet)) {
            s = wb.getSheetAt(0);
        } else {
            s = wb.getSheet(sheet);
            if (s == null) {

                try {
                    index = Integer.parseInt(sheet.trim()) - 1;
                } catch (Exception e) {
                    throw new PaxmlRuntimeException(
                            "Please specify either an existing sheet name or a sheet index number. This is neither: "
                                    + sheet,
                            e);
                }
                if (index < 0) {
                    index = file.getWorkbook().getActiveSheetIndex();
                }
                if (index >= 0) {
                    s = wb.getSheetAt(index);
                }
            }
        }
    }
    if (s == null) {
        if (createIfNone) {
            if (sheet == null || index == 0) {
                s = wb.createSheet();
            } else {
                s = wb.createSheet(sheet);
            }
        } else {
            throw new PaxmlRuntimeException(
                    "No sheet found with index " + index + " in file: " + file.getFile().getAbsolutePath());
        }
    }
    return s;
}

From source file:org.pharmgkb.ItpcSheet.java

License:LGPL

/**
 * Constructor for an ITPC data file/*w  w w  .  ja v  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);
        }
    }
}