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.jberet.support.io.ExcelUserModelItemWriter.java

License:Open Source License

@Override
public void open(final Serializable checkpoint) throws Exception {
    //if template is used, create workbook based on template resource, and try to get header from template
    if (templateResource != null) {
        InputStream templateInputStream = null;
        try {//from w w  w. j  av a2 s. c o  m
            templateInputStream = getInputStream(templateResource, false);

            //for SXSSF (streaming), the original templateWorkbook is wrapped inside this.workbook, and these 2
            // workbook instances are different.  For XSSF and HSSF, the two are the same.
            // SXSSF workbook does not support reading, so we have to use the original templateWorkbook to read
            // header, and then reassign sheet to that of this.workbook, which is SXSSFWorkbook
            final Workbook templateWorkbook = createWorkbook(templateInputStream);
            if (templateSheetName != null) {
                sheet = templateWorkbook.getSheet(templateSheetName);
            }
            if (sheet == null) {
                sheet = templateWorkbook.getSheetAt(templateSheetIndex);
            }
            //if header property is already injected from job.xml, use it and no need to check templateHeaderRow
            if (header == null) {
                if (templateHeaderRow == null) {
                    throw SupportMessages.MESSAGES.invalidReaderWriterProperty(null, null, "templateHeaderRow");
                }
                final Row headerRow = sheet.getRow(templateHeaderRow);
                if (headerRow == null) {
                    throw SupportMessages.MESSAGES.failToReadExcelHeader(templateResource, templateSheetName);
                }
                header = getCellStringValues(headerRow);
            }
            currentRowNum = sheet.getLastRowNum();
            if (workbook != templateWorkbook) {
                sheet = workbook.getSheet(sheet.getSheetName());
            }
            workbook.setActiveSheet(workbook.getSheetIndex(sheet));
        } finally {
            if (templateInputStream != null) {
                try {
                    templateInputStream.close();
                } catch (final Exception e) {
                    SupportLogger.LOGGER.tracef(e,
                            "Failed to close template InputStream %s for template resource %s%n",
                            templateInputStream, templateResource);
                }
            }
        }
    } else { // no template is specified
        createWorkbook(null);
        sheet = sheetName == null ? workbook.createSheet()
                : workbook.createSheet(WorkbookUtil.createSafeSheetName(sheetName));

        if (header == null) {
            throw SupportMessages.MESSAGES.invalidReaderWriterProperty(null, null, "header");
        }
        //write header row
        final Row headerRow = sheet.createRow(0);
        for (int i = 0, j = header.length; i < j; ++i) {
            headerRow.createCell(i, Cell.CELL_TYPE_STRING).setCellValue(header[i]);
        }
        currentRowNum = 0;
    }
    outputStream = getOutputStream(writeMode);
}

From source file:org.jplus.compare.excel.service.CompareService.java

public static List<OptionBean> getOptionBeansFromExcel(String excelPath, String sheetName) {
    Map<String, OptionBean> optionMap = getOptionMap();
    List<OptionBean> optionBeans = new ArrayList<OptionBean>();
    InputStream inputStream = null;
    try {//ww  w .  j  av  a  2  s.  c  o  m
        File file = new File(excelPath);
        inputStream = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheet(sheetName);
        Row row = BaseExcelService.getRow(sheet, 0);
        short lastCellNum = row.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            Cell cell = row.getCell(i);
            if (cell != null) {
                String string = BaseExcelService.getString(cell);
                if (!ObjectHelper.isNullOrEmptyString(string)) {
                    OptionBean optionBean = new OptionBean();
                    optionBean.setItemName(string);
                    OptionBean get = optionMap.get(string);
                    if (ObjectHelper.isNotEmpty(get)) {
                        optionBean.setCompare(get.getCompare());
                        optionBean.setThresholdValue(get.getThresholdValue());
                    }
                    optionBeans.add(optionBean);
                }
            }
        }

    } catch (IOException ex) {
        Logger.getLogger(CompareService.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (IOException ex) {
                Logger.getLogger(CompareService.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    return optionBeans;
}

From source file:org.jplus.compare.excel.service.CompareService.java

public static List<Map> getExcelContent(String excelPath, String sheetName) {
    InputStream inputStream = null;
    try {/*from  w  ww.jav  a 2s.  c o  m*/
        File file = new File(excelPath);
        inputStream = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheet(sheetName);
        ImportTableService tableService = new ImportTableService(sheet);
        tableService.setStartRow(1);
        tableService.doImport();
        return tableService.read(getExcelHeader(sheet), Map.class);
    } catch (Exception ex) {
        Logger.getLogger(CompareService.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            inputStream.close();
        } catch (IOException ex) {
            Logger.getLogger(CompareService.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return Collections.EMPTY_LIST;
}

From source file:org.nuclos.server.report.export.ExcelExport.java

License:Open Source License

private NuclosFile export(Workbook wb, String sheetname, ResultVO result, List<ReportFieldDefinition> fields,
        String name) throws NuclosReportException {
    sheetname = sheetname != null ? sheetname
            : SpringLocaleDelegate.getInstance().getMessage("XLSExport.2", "Daten aus Nucleus");
    Sheet s = wb.getSheet(sheetname);
    if (s == null) {
        s = wb.createSheet(sheetname);/*from  www.j a v a2 s.c o  m*/
    }

    int iRowNum = 0;
    int iColumnNum = 0;
    CreationHelper createHelper = wb.getCreationHelper();

    Row row = getRow(s, 0);

    Map<Integer, CellStyle> styles = new HashMap<Integer, CellStyle>();

    for (Iterator<ResultColumnVO> i = result.getColumns().iterator(); i.hasNext(); iColumnNum++) {
        i.next();
        Cell cell = getCell(row, iColumnNum);
        cell.setCellValue(fields.get(iColumnNum).getLabel());

        CellStyle style = wb.createCellStyle();
        String f = getFormat(fields.get(iColumnNum));
        if (f != null) {
            style.setDataFormat(createHelper.createDataFormat().getFormat(f));
        }
        styles.put(iColumnNum, style);
    }
    iRowNum++;

    // export data
    for (int i = 0; i < result.getRows().size(); i++, iRowNum++) {
        iColumnNum = 0;
        Object[] dataRow = result.getRows().get(i);
        row = getRow(s, iRowNum);
        for (int j = 0; j < result.getColumns().size(); j++, iColumnNum++) {
            Object value = dataRow[j];
            Cell c = getCell(row, iColumnNum);
            ReportFieldDefinition def = fields.get(j);

            if (value != null) {
                if (value instanceof List) {
                    final StringBuilder sb = new StringBuilder();
                    for (Iterator<?> it = ((List<?>) value).iterator(); it.hasNext();) {
                        final Object v = it.next();
                        sb.append(CollectableFieldFormat.getInstance(def.getJavaClass())
                                .format(def.getOutputformat(), v));
                        if (it.hasNext()) {
                            sb.append(", ");
                        }
                    }
                    c.setCellValue(sb.toString());
                } else {
                    if (Date.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Date) value);
                    } else if (Integer.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Integer) value);
                    } else if (Double.class.isAssignableFrom(def.getJavaClass())) {
                        c.setCellStyle(styles.get(iColumnNum));
                        c.setCellValue((Double) value);
                    } else {
                        c.setCellValue(String.valueOf(value));
                    }
                }
            } else {
                c.setCellValue("");
            }
        }
    }

    try {
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = wb.getSheetAt(sheetNum);
            for (Row r : sheet) {
                for (Cell c : r) {
                    if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        evaluator.evaluateFormulaCell(c);
                    }
                }
            }
        }
    } catch (Exception e) {
    } // ignore any Exception

    ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
    try {
        wb.write(baos);
        return new NuclosFile(name + format.getExtension(), baos.toByteArray());
    } catch (IOException e) {
        throw new NuclosReportException(e);
    } finally {
        try {
            baos.close();
        } catch (IOException e) {
        }
    }
}

From source file:org.omnaest.i18nbinder.internal.XLSFile.java

License:Apache License

/**
 * Loads the data from the disk into this object.
 *//*ww w .  ja  va 2s .c o m*/
public void load() {
    try {
        //
        InputStream inputStream = new BufferedInputStream(new FileInputStream(this.file));
        Workbook wb = this.newWorkbookFrom(inputStream);
        Sheet sheet = wb.getSheet(MAINSHEETPAGENAME);

        //
        this.clear();
        for (Row iRow : sheet) {
            //
            TableRow newTableRow = new TableRow();

            //
            for (Cell iCell : iRow) {
                newTableRow.add(iCell.getStringCellValue());
            }

            //
            this.tableRowList.add(newTableRow);
        }

        //
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:org.openmrs.module.reporting.common.ExcelUtilTest.java

License:Open Source License

@Test
public void shouldGetCellContents() throws Exception {
    Workbook wb = ExcelUtil.loadWorkbookFromResource("org/openmrs/module/reporting/common/ExcelUtilTest.xls");
    Sheet sheet = wb.getSheet("Testing");
    testCellContentsToTheRightOf(sheet, "String", "This is a String");
    testCellContentsToTheRightOf(sheet, "Bold String", "This is a bold String");
    testCellContentsToTheRightOf(sheet, "Integer", 100);
    testCellContentsToTheRightOf(sheet, "Number", 100.5);
    testCellContentsToTheRightOf(sheet, "Boolean", true);
    testCellContentsToTheRightOf(sheet, "Formula", "B5*2");
    testCellContentsToTheRightOf(sheet, "Date", DateUtil.getDateTime(2011, 10, 31));
    testCellContentsToTheRightOf(sheet, "Time", DateUtil.getDateTime(2011, 10, 31, 11, 32, 0, 0));
}

From source file:org.openmrs.module.reporting.common.ExcelUtilTest.java

License:Open Source License

@Test
public void shouldSetCellContents() throws Exception {
    Workbook wb = ExcelUtil.loadWorkbookFromResource("org/openmrs/module/reporting/common/ExcelUtilTest.xls");
    Sheet sheet = wb.getSheet("Testing");
    Date testDate = DateUtil.getDateTime(1999, 3, 17);
    int testDateExcel = (int) ExcelUtil.getDateAsNumber(testDate);
    testSettingCellContents(sheet, "String", "New String", Cell.CELL_TYPE_STRING, "New String");
    testSettingCellContents(sheet, "String", 100, Cell.CELL_TYPE_NUMERIC, 100);
    testSettingCellContents(sheet, "Integer", 20.2, Cell.CELL_TYPE_NUMERIC, 20.2);
    testSettingCellContents(sheet, "Boolean", Boolean.FALSE, Cell.CELL_TYPE_BOOLEAN, false);
    testSettingCellContents(sheet, "Date", testDate, Cell.CELL_TYPE_NUMERIC, testDate);
    testSettingCellContents(sheet, "String", testDate, Cell.CELL_TYPE_NUMERIC, testDateExcel);
    testSettingCellContents(sheet, "Formula", "B5*3", Cell.CELL_TYPE_FORMULA, "B5*3");
}

From source file:org.openmrs.module.reporting.common.ExcelUtilTest.java

License:Open Source License

@Test
public void shouldAddStyle() throws Exception {
    Workbook wb = ExcelUtil.loadWorkbookFromResource("org/openmrs/module/reporting/common/ExcelUtilTest.xls");
    Sheet sheet = wb.getSheet("Testing");

    // Test Fonts
    Cell cell = getCellToTheRightOf(sheet, "String");
    Assert.assertEquals("This is a String", ExcelUtil.getCellContents(cell));

    Assert.assertEquals(Font.BOLDWEIGHT_NORMAL, ExcelUtil.getFont(cell).getBoldweight());
    cell.setCellStyle(ExcelUtil.createCellStyle(wb, "bold"));
    Assert.assertEquals(Font.BOLDWEIGHT_BOLD, ExcelUtil.getFont(cell).getBoldweight());

    Assert.assertFalse(ExcelUtil.getFont(cell).getItalic());
    Assert.assertEquals(Font.U_NONE, ExcelUtil.getFont(cell).getUnderline());
    cell.setCellStyle(ExcelUtil.createCellStyle(wb, "italic,underline"));
    Assert.assertTrue(ExcelUtil.getFont(cell).getItalic());
    Assert.assertEquals(Font.U_SINGLE, ExcelUtil.getFont(cell).getUnderline());

    int fontSize = ExcelUtil.getFont(cell).getFontHeightInPoints() + 1;
    cell.setCellStyle(ExcelUtil.createCellStyle(wb, "size=" + fontSize));
    Assert.assertEquals((short) fontSize, ExcelUtil.getFont(cell).getFontHeightInPoints());

    // Test other styles
    Assert.assertFalse(cell.getCellStyle().getWrapText());
    Assert.assertEquals(CellStyle.ALIGN_GENERAL, cell.getCellStyle().getAlignment());
    Assert.assertEquals(CellStyle.BORDER_NONE, cell.getCellStyle().getBorderBottom());
    cell.setCellStyle(ExcelUtil.createCellStyle(wb, "wraptext,align=center,border=bottom"));
    Assert.assertTrue(cell.getCellStyle().getWrapText());
    Assert.assertEquals(CellStyle.ALIGN_CENTER, cell.getCellStyle().getAlignment());
    Assert.assertEquals(CellStyle.BORDER_THIN, cell.getCellStyle().getBorderBottom());

    // Test Date/*from  w ww  .j  a  v  a  2 s .  c o m*/
    Date date = DateUtil.getDateTime(2013, 10, 31);
    cell.setCellValue(date);
    ExcelUtil.formatAsDate(cell);
    Assert.assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType());
    Assert.assertTrue(ExcelUtil.isCellDateFormatted(cell));
    Assert.assertEquals(date, ExcelUtil.getCellContents(cell));
}

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

License:Mozilla Public License

public void testLocalization(String prefix, String locale, String emrIdVal, String genderVal, String dobVal)
        throws Exception {

    ReportDefinition rd = new ReportDefinition();
    SqlDataSetDefinition testDataSet = new SqlDataSetDefinition();
    testDataSet.setSqlQuery(/*ww w .  j  av  a 2s.  c  o  m*/
            "select p.patient_id as PID, n.gender as GENDER, n.birthdate as DOB from patient p, person n where p.patient_id = n.person_id and n.gender = 'M'");
    rd.addDataSetDefinition("dataset", testDataSet, null);

    // Next, we set up the ReportDesign and ReportDesignResource files for the renderer

    final ReportDesign design = new ReportDesign();
    design.setName("TestDesign");
    design.setReportDefinition(rd);
    design.setRendererType(ExcelTemplateRenderer.class);

    ReportDesignResource resource = new ReportDesignResource();
    resource.setName("template.xls");
    InputStream is = OpenmrsClassLoader.getInstance().getResourceAsStream(
            "org/openmrs/module/reporting/report/renderer/ExcelTemplateLocalizeLabelsTest.xls");
    resource.setContents(IOUtils.toByteArray(is));
    IOUtils.closeQuietly(is);
    design.addResource(resource);

    Properties props = new Properties();
    props.put("columnTranslationPrefix", prefix);
    props.put("columnTranslationLocale", locale);
    design.setProperties(props);

    // For now, we need this little magic to simulate what would happen if this were all stored in the database via the UI

    ExcelTemplateRenderer renderer = new ExcelTemplateRenderer() {
        public ReportDesign getDesign(String argument) {
            return design;
        }
    };

    // We construct an EvaluationContext (in this case the parameters aren't used, but included here for reference)

    EvaluationContext context = new EvaluationContext();
    ReportData data = Context.getService(ReportDefinitionService.class).evaluate(rd, context);

    MutableMessageSource messageSource = Context.getMessageSourceService().getActiveMessageSource();
    messageSource.addPresentation(new PresentationMessage("reporting.test.PID", Locale.ENGLISH, "EMR ID", ""));
    messageSource.addPresentation(
            new PresentationMessage("reporting.test.dataset.DOB", Locale.ENGLISH, "Date of Birth", ""));
    messageSource
            .addPresentation(new PresentationMessage("reporting.test.PID", Locale.FRENCH, "ID DE EMR", ""));
    messageSource.addPresentation(new PresentationMessage("reporting.test.GENDER", Locale.FRENCH, "Sexe", ""));
    messageSource.addPresentation(
            new PresentationMessage("reporting.test.dataset.DOB", Locale.FRENCH, "Date de naissance", ""));

    ByteArrayOutputStream reportBaos = new ByteArrayOutputStream(1024);
    renderer.render(data, "xxx:xls", reportBaos);
    IOUtils.closeQuietly(reportBaos);

    Workbook wb = ExcelUtil.loadWorkbookFromInputStream(new ByteArrayInputStream(reportBaos.toByteArray()));
    Sheet sheet = wb.getSheet("TestLabels");

    List<String> cellsFound = new ArrayList<String>();

    for (Iterator<Row> ri = sheet.rowIterator(); ri.hasNext();) {
        Row row = ri.next();
        for (Iterator<Cell> ci = row.cellIterator(); ci.hasNext();) {
            Cell cell = ci.next();
            Object contents = ExcelUtil.getCellContents(cell);
            if (!ObjectUtil.isNull(contents)) {
                cellsFound.add(contents.toString());
            }
        }
    }

    Assert.assertEquals(3, cellsFound.size());
    Assert.assertEquals(emrIdVal, cellsFound.get(0));
    Assert.assertEquals(genderVal, cellsFound.get(1));
    Assert.assertEquals(dobVal, cellsFound.get(2));
}

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

License:Open Source License

@Test
public void testXlsReportRenderingWithoutHeaders() throws Exception {
    Workbook wb = renderToXls(false);

    Assert.assertEquals(3, wb.getNumberOfSheets());
    Assert.assertNotNull(wb.getSheet("males"));
    Assert.assertNotNull(wb.getSheet("females"));
    Assert.assertNotNull(wb.getSheet("encounters"));

    testValue(wb, "males", 1, 1, "patient_id");
    testValue(wb, "males", 1, 2, "gender");
    testValue(wb, "males", 1, 3, "birthdate");
}