Example usage for org.apache.poi.ss.usermodel Workbook 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:org.openmrs.module.mksreports.renderer.PatientHistoryExcelTemplateRenderer.java

License:Open Source License

/** 
 * @see ReportRenderer#render(ReportData, String, OutputStream)
 *//*from w ww  .  j  a v  a 2 s .co m*/
public void render(ReportData reportData, String argument, OutputStream out)
        throws IOException, RenderingException {

    try {
        log.debug("Attempting to render report with ExcelTemplateRenderer");
        ReportDesign design = getDesign(argument);
        Workbook wb = getExcelTemplate(design);

        if (wb == null) {
            XlsReportRenderer xlsRenderer = new XlsReportRenderer();
            xlsRenderer.render(reportData, argument, out);
        } else {
            //This should be changed to get the dataset name form a parameter 
            DataSet ds = reportData.getDataSets().get("patient");
            ArrayList<String> names = new ArrayList<String>();

            for (DataSetColumn dataSetRow : ds.getMetaData().getColumns()) {
                names.add(dataSetRow.getName());
            }
            Sheet s = wb.getSheetAt(0);

            //Trying to creat a row that has the replacement values pre-populated
            Row h = s.createRow(8);
            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setBold(true);
            style.setFont(font);

            for (String name : names) {
                Cell c = h.createCell(names.indexOf(name));
                String value = name.toUpperCase().replace("_", " ");
                c.setCellValue(value);
                c.setCellStyle(style);
            }

            Row r = s.getRow(9);

            for (String name : names) {
                Cell c = r.createCell(names.indexOf(name));
                String value = "#patient." + name + "#";
                c.setCellValue(value);
            }

            Map<String, String> repeatSections = getRepeatingSections(design);

            // Put together base set of replacements.  Any dataSet with only one row is included.
            Map<String, Object> replacements = getBaseReplacementData(reportData, design);

            // Iterate across all of the sheets in the workbook, and configure all those that need to be added/cloned
            List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>();

            Set<String> usedSheetNames = new HashSet<String>();
            int numberOfSheets = wb.getNumberOfSheets();

            for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {

                Sheet currentSheet = wb.getSheetAt(sheetNum);
                String originalSheetName = wb.getSheetName(sheetNum);

                String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections);
                if (dataSetName != null) {

                    DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements);
                    int dataSetRowNum = 0;
                    for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator(); rowIterator
                            .hasNext();) {
                        DataSetRow dataSetRow = rowIterator.next();
                        dataSetRowNum++;
                        Map<String, Object> newReplacements = getReplacementData(replacements, reportData,
                                design, dataSetName, dataSetRow, dataSetRowNum);
                        Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum));
                        sheetsToAdd.add(new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements));
                    }
                } else {
                    sheetsToAdd.add(new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements));
                }
            }

            // Then iterate across all of these and add them in
            for (int i = 0; i < sheetsToAdd.size(); i++) {
                addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections);
            }

            wb.write(out);
        }
    } catch (Exception e) {
        throw new RenderingException("Unable to render results due to: " + e, e);
    }
}

---

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

License:Open Source License

/** 
 * @see ReportRenderer#render(ReportData, String, OutputStream)
 *//* w  w w  .  j  av a 2  s. c o  m*/
public void render(ReportData reportData, String argument, OutputStream out)
        throws IOException, RenderingException {

    try {
        log.debug("Attempting to render report with ExcelTemplateRenderer");
        ReportDesign design = getDesign(argument);
        Workbook wb = getExcelTemplate(design);

        if (wb == null) {
            XlsReportRenderer xlsRenderer = new XlsReportRenderer();
            xlsRenderer.render(reportData, argument, out);
        } else {
            Map<String, String> repeatSections = getRepeatingSections(design);

            // Put together base set of replacements.  Any dataSet with only one row is included.
            Map<String, Object> replacements = getBaseReplacementData(reportData, design);

            // Iterate across all of the sheets in the workbook, and configure all those that need to be added/cloned
            List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>();

            Set<String> usedSheetNames = new HashSet<String>();
            int numberOfSheets = wb.getNumberOfSheets();

            for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {

                Sheet currentSheet = wb.getSheetAt(sheetNum);
                String originalSheetName = wb.getSheetName(sheetNum);

                String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections);
                if (dataSetName != null) {

                    DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements);
                    int dataSetRowNum = 0;
                    for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator(); rowIterator
                            .hasNext();) {
                        DataSetRow dataSetRow = rowIterator.next();
                        dataSetRowNum++;
                        Map<String, Object> newReplacements = getReplacementData(replacements, reportData,
                                design, dataSetName, dataSetRow, dataSetRowNum);
                        Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum));
                        sheetsToAdd.add(new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements));
                    }
                } else {
                    sheetsToAdd.add(new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements));
                }
            }

            // Then iterate across all of these and add them in
            for (int i = 0; i < sheetsToAdd.size(); i++) {
                addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections);
            }

            wb.write(out);
        }
    } catch (Exception e) {
        throw new RenderingException("Unable to render results due to: " + e, e);
    }
}

---

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");
}

---

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

License:Open Source License

@Test
public void testXlsReportRenderingWithHeaders() throws Exception {
    Workbook wb = renderToXls(true);
    Assert.assertEquals(3, wb.getNumberOfSheets());
    testValue(wb, "males", 1, 1, "Gender Data Set");
    testValue(wb, "females", 1, 1, "Gender Data Set");
    testValue(wb, "encounters", 1, 1, "encounters");
    testValue(wb, "males", 2, 1, "Gender:");
    testValue(wb, "males", 2, 2, "M");
    testValue(wb, "females", 2, 1, "Gender:");
    testValue(wb, "females", 2, 2, "F");
    testValue(wb, "encounters", 2, 1, "");
    testValue(wb, "encounters", 2, 1, "");
}

---

From source file:org.openmrs.module.rwandareports.renderer.PatientHistoryExcelTemplateRenderer.java

License:Open Source License

/** 
 * @see ReportRenderer#render(ReportData, String, OutputStream)
 *///  w w w .j a v  a  2 s.  c  o  m
public void render(ReportData reportData, String argument, OutputStream out)
        throws IOException, RenderingException {

    try {
        log.debug("Attempting to render report with ExcelTemplateRenderer");
        ReportDesign design = getDesign(argument);
        Workbook wb = getExcelTemplate(design);

        if (wb == null) {
            XlsReportRenderer xlsRenderer = new XlsReportRenderer();
            xlsRenderer.render(reportData, argument, out);
        } else {
            //This should be changed to get the dataset name form a parameter 
            DataSet ds = reportData.getDataSets().get("patient");
            ArrayList<String> names = new ArrayList<String>();

            for (DataSetColumn dataSetRow : ds.getMetaData().getColumns()) {
                names.add(dataSetRow.getName());
            }
            Sheet s = wb.getSheetAt(0);

            Row h = s.createRow(8);
            CellStyle style = wb.createCellStyle();
            Font font = wb.createFont();
            font.setFontName(HSSFFont.FONT_ARIAL);
            font.setBold(true);
            style.setFont(font);

            for (String name : names) {
                Cell c = h.createCell(names.indexOf(name));
                String value = name.toUpperCase().replace("_", " ");
                c.setCellValue(value);
                c.setCellStyle(style);
            }

            //Trying to creat a row that has the replacement values pre-populated
            Row r = s.getRow(9);

            for (String name : names) {
                Cell c = r.createCell(names.indexOf(name));
                String value = "#patient." + name + "#";
                c.setCellValue(value);

            }

            ExcelUtil.formatRow(r);
            Map<String, String> repeatSections = getRepeatingSections(design);

            // Put together base set of replacements.  Any dataSet with only one row is included.
            Map<String, Object> replacements = getBaseReplacementData(reportData, design);

            // Iterate across all of the sheets in the workbook, and configure all those that need to be added/cloned
            List<SheetToAdd> sheetsToAdd = new ArrayList<SheetToAdd>();

            Set<String> usedSheetNames = new HashSet<String>();
            int numberOfSheets = wb.getNumberOfSheets();

            for (int sheetNum = 0; sheetNum < numberOfSheets; sheetNum++) {

                Sheet currentSheet = wb.getSheetAt(sheetNum);
                String originalSheetName = wb.getSheetName(sheetNum);

                String dataSetName = getRepeatingSheetProperty(sheetNum, repeatSections);
                if (dataSetName != null) {

                    DataSet repeatingSheetDataSet = getDataSet(reportData, dataSetName, replacements);
                    int dataSetRowNum = 0;
                    for (Iterator<DataSetRow> rowIterator = repeatingSheetDataSet.iterator(); rowIterator
                            .hasNext();) {
                        DataSetRow dataSetRow = rowIterator.next();
                        dataSetRowNum++;
                        Map<String, Object> newReplacements = getReplacementData(replacements, reportData,
                                design, dataSetName, dataSetRow, dataSetRowNum);
                        Sheet newSheet = (dataSetRowNum == 1 ? currentSheet : wb.cloneSheet(sheetNum));
                        sheetsToAdd.add(new SheetToAdd(newSheet, sheetNum, originalSheetName, newReplacements));
                    }
                } else {
                    sheetsToAdd.add(new SheetToAdd(currentSheet, sheetNum, originalSheetName, replacements));
                }
            }

            // Then iterate across all of these and add them in
            for (int i = 0; i < sheetsToAdd.size(); i++) {
                addSheet(wb, sheetsToAdd.get(i), usedSheetNames, reportData, design, repeatSections);
            }

            wb.write(out);
        }
    } catch (Exception e) {
        throw new RenderingException("Unable to render results due to: " + e, e);
    }
}

---

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 {/*from  ww  w  .j a v a  2 s  . c om*/
        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/*from   w w  w . ja  v  a  2  s. c o m*/
    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.ExcelUtils.java

License:Open Source License

/**
 * Process an excel file using ExcelWorksheetProcessor iterating through individual spreadsheets.
 *
 * @param excelFile/*from   www .ja va2 s . co  m*/
 *        Excel File that needs to be processed.
 * @param worksheetProcessor
 *        A Worksheet processor.
 * @throws InvalidFormatException
 *         InvalidFormatException when parsing excel files.
 * @throws IOException
 *         When reading the excel file.
 */
public void processExcelFile(final InputStream excelFile, final ExcelWorksheetProcessor worksheetProcessor)
        throws InvalidFormatException, IOException {
    final Workbook workbook = WorkbookFactory.create(excelFile);

    // Find Iterate index
    final int iterateIndex = this.iterateAllSheets ? workbook.getNumberOfSheets() : DEFAULT_SHEET_INDEX;

    for (int i = 0; i < iterateIndex; i++) {
        worksheetProcessor.process(workbook.getSheetAt(i));
    }

}

---

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  a va 2s  .c o  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.pentaho.reporting.engine.classic.core.bugs.Prd3431IT.java

License:Open Source License

public void testAsExcelOutput() throws ResourceException, ReportProcessingException, IOException, SAXException,
        ParserConfigurationException, InvalidFormatException {
    final URL url = getClass().getResource("Prd-3431.prpt");
    assertNotNull(url);/*w  ww . j  a  v  a2 s  . com*/
    final ResourceManager resourceManager = new ResourceManager();
    resourceManager.registerDefaults();
    final Resource directly = resourceManager.createDirectly(url, MasterReport.class);
    final MasterReport report = (MasterReport) directly.getResource();
    final MemoryByteArrayOutputStream mbos = new MemoryByteArrayOutputStream();
    ExcelReportUtil.createXLS(report, new NoCloseOutputStream(mbos));

    final ByteArrayInputStream bin = new ByteArrayInputStream(mbos.getRaw(), 0, mbos.getLength());
    final Workbook workbook = WorkbookFactory.create(bin);
    assertEquals(4, workbook.getNumberOfSheets());
    assertEquals("Summary", workbook.getSheetAt(0).getSheetName());
    assertEquals("AuthorPublisher A", workbook.getSheetAt(1).getSheetName());
    assertEquals("AuthorPublisher B", workbook.getSheetAt(2).getSheetName());
    assertEquals("AuthorPublisher C", workbook.getSheetAt(3).getSheetName());
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• »