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:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptGoesReadOnly() throws Exception {
    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();

        Sheet s = wb.createSheet("Test");

        // Numeric formulas
        Row r1 = s.createRow(0);/* w w w .jav a  2  s.c  o m*/
        Cell c1 = r1.createCell(0);
        Cell c2 = r1.createCell(1);
        Cell c3 = r1.createCell(2);
        Cell c4 = r1.createCell(3);

        c1.setCellValue(1);
        c2.setCellValue(2);
        c3.setCellFormula("A1+B1");
        c4.setCellFormula("(A1+B1)*B1");

        // Strings, booleans and errors
        Row r2 = s.createRow(1);
        Cell c21 = r2.createCell(0);
        Cell c22 = r2.createCell(1);
        Cell c23 = r2.createCell(2);
        Cell c24 = r2.createCell(3);

        c21.setCellValue("Testing");
        c22.setCellFormula("CONCATENATE(A2,A2)");
        c23.setCellFormula("FALSE()");
        c24.setCellFormula("A1/0");

        // Ensure the formulas are current
        eval.evaluateAll();

        // Run the excerpt
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        excerpter.excerpt(new int[] { 0 }, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(1, newwb.getNumberOfSheets());

        s = newwb.getSheetAt(0);
        r1 = s.getRow(0);
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(3).getCellType());

        assertEquals(1.0, s.getRow(0).getCell(0).getNumericCellValue(), 0.001);
        assertEquals(2.0, s.getRow(0).getCell(1).getNumericCellValue(), 0.001);
        assertEquals(3.0, s.getRow(0).getCell(2).getNumericCellValue(), 0.001);
        assertEquals(6.0, s.getRow(0).getCell(3).getNumericCellValue(), 0.001);

        r2 = s.getRow(1);
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(0).getCellType());
        assertEquals(Cell.CELL_TYPE_STRING, r2.getCell(1).getCellType());
        assertEquals(Cell.CELL_TYPE_BOOLEAN, r2.getCell(2).getCellType());
        assertEquals(Cell.CELL_TYPE_BLANK, r2.getCell(3).getCellType());

        assertEquals("Testing", s.getRow(1).getCell(0).getStringCellValue());
        assertEquals("TestingTesting", s.getRow(1).getCell(1).getStringCellValue());
        assertEquals(false, s.getRow(1).getCell(2).getBooleanCellValue());
    }
}

---

From source file:com.quanticate.opensource.spreadsheetexcerpt.excerpt.TestPOIExcerpter.java

License:Apache License

@Test
public void excerptRemovesUnUsed() throws Exception {
    String[] names = new String[] { "a", "b", "ccc", "dddd", "e", "f", "gg" };

    for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }) {
        // Create some dummy content
        for (String sn : names) {
            Sheet s = wb.createSheet(sn);
            s.createRow(0).createCell(0).setCellValue(sn);
        }//from   w w  w  .j a va 2  s. c o  m

        // Excerpt by index
        File tmp = File.createTempFile("test", ".xls");
        wb.write(new FileOutputStream(tmp));

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        int[] excI = new int[] { 0, 1, 2, 4, 5 };
        excerpter.excerpt(excI, tmp, baos);

        // Check
        Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(5, newwb.getNumberOfSheets());
        assertEquals(names[excI[0]], newwb.getSheetName(0));
        assertEquals(names[excI[1]], newwb.getSheetName(1));
        assertEquals(names[excI[2]], newwb.getSheetName(2));
        assertEquals(names[excI[3]], newwb.getSheetName(3));
        assertEquals(names[excI[4]], newwb.getSheetName(4));

        assertEquals(names[excI[0]], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[1]], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[2]], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[3]], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());
        assertEquals(names[excI[4]], newwb.getSheetAt(4).getRow(0).getCell(0).getStringCellValue());

        // Excerpt by name
        String[] excN = new String[] { "b", "ccc", "f", "gg" };
        baos = new ByteArrayOutputStream();
        excerpter.excerpt(excN, tmp, baos);

        newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
        assertEquals(4, newwb.getNumberOfSheets());
        assertEquals(excN[0], newwb.getSheetName(0));
        assertEquals(excN[1], newwb.getSheetName(1));
        assertEquals(excN[2], newwb.getSheetName(2));
        assertEquals(excN[3], newwb.getSheetName(3));

        assertEquals(excN[0], newwb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[1], newwb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[2], newwb.getSheetAt(2).getRow(0).getCell(0).getStringCellValue());
        assertEquals(excN[3], newwb.getSheetAt(3).getRow(0).getCell(0).getStringCellValue());

        // Can't excerpt by invalid index
        try {
            excerpter.excerpt(new int[] { 0, 10 }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }

        // Can't excerpt by invalid name
        try {
            excerpter.excerpt(new String[] { "a", "invalid" }, tmp, null);
            fail();
        } catch (IllegalArgumentException e) {
        }
    }
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testExport() throws IOException {
    ExcelExporter exporter = new ExcelExporter();
    exporter.addTemplate(mdBusiness.definesType());
    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(1, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row typeRow = sheet.getRow(0);/*from   w  w w .  j a va2s .  com*/
    Row attributeRow = sheet.getRow(1);
    Row labelRow = sheet.getRow(2);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdAttributeDAOIF> attributes = ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
    }

    // Ensure there aren't any extra columns
    assertNull(attributeRow.getCell(attributes.size()));
    assertNull(labelRow.getCell(attributes.size()));
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testFormExport() throws IOException {
    ExcelExporter exporter = new FormExcelExporter(new MdWebAttributeFilter());
    exporter.addTemplate(mdForm.definesType());
    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(1, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row typeRow = sheet.getRow(0);/* w w  w. j a va2  s  .c  om*/
    Row attributeRow = sheet.getRow(1);
    Row labelRow = sheet.getRow(2);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdFieldDAOIF> fields = mdForm.getSortedFields();

    for (int i = 0; i < fields.size(); i++) {
        MdFieldDAOIF mdField = fields.get(i);
        MdAttributeDAOIF mdAttribute = ((MdWebAttributeDAOIF) mdField).getDefiningMdAttribute();

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdField.getDisplayLabel(Session.getCurrentLocale()), label);
    }

    // Ensure there aren't any extra columns
    assertNull(attributeRow.getCell(fields.size()));
    assertNull(labelRow.getCell(fields.size()));
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testExtraColumns() throws IOException {
    ExcelExporter exporter = new ExcelExporter();
    exporter.addListener(new MockExcelExportListener());
    exporter.addTemplate(mdBusiness.definesType());
    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(1, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row typeRow = sheet.getRow(0);/*from  w  w w  . j  a  v a2s  .  c o  m*/
    Row attributeRow = sheet.getRow(1);
    Row labelRow = sheet.getRow(2);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    int cellNum = ((List<? extends MdAttributeDAOIF>) ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter())).size();

    String attributeName = attributeRow.getCell(cellNum).getRichStringCellValue().toString();
    String label = labelRow.getCell(cellNum).getRichStringCellValue().toString();

    assertEquals(MockExcelExportListener.ATTRIBUTE_NAME, attributeName);
    assertEquals(MockExcelExportListener.DISPLAY_LABEL, label);
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testAddRow() throws IOException {
    BusinessDAO business = BusinessDAO.newInstance(mdBusiness.definesType());
    business.setValue(TestFixConst.ATTRIBUTE_CHARACTER, "Test Character Value");
    business.setValue("testDouble", "10");
    business.setValue("testInteger", "-1");

    ExcelExporter exporter = new ExcelExporter();

    ExcelExportSheet excelSheet = exporter.addTemplate(mdBusiness.definesType());
    excelSheet.addRow(business);/* w w  w  .  ja  v a2 s .c  o m*/

    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(1, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(3);

    List<? extends MdAttributeDAOIF> attributes = ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);
        String value = ExcelUtil.getString(row.getCell(i));

        assertEquals(business.getValue(mdAttribute.definesAttribute()), value);
    }
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testMultipleSheets() throws IOException {
    ExcelExporter exporter = new ExcelExporter();
    exporter.addTemplate(mdBusiness.definesType());
    exporter.addTemplate(mdBusiness2.definesType());
    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(2, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row typeRow = sheet.getRow(0);// w  ww.ja  v a  2 s  .  co m
    Row attributeRow = sheet.getRow(1);
    Row labelRow = sheet.getRow(2);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdAttributeDAOIF> attributes = ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
    }

    // Ensure there aren't any extra columns
    assertNull(attributeRow.getCell(attributes.size()));
    assertNull(labelRow.getCell(attributes.size()));

    sheet = workbook.getSheetAt(1);
    typeRow = sheet.getRow(0);
    attributeRow = sheet.getRow(1);
    labelRow = sheet.getRow(2);

    assertEquals(mdBusiness2.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    attributes = ExcelUtil.getAttributes(mdBusiness2, new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
    }

    // Ensure there aren't any extra columns
    assertNull(attributeRow.getCell(attributes.size()));
    assertNull(labelRow.getCell(attributes.size()));
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testMultipleSheetsWithDefaultListeners() throws IOException {
    ExcelExporter exporter = new ExcelExporter();
    exporter.addListener(new MockExcelExportListener());
    exporter.addTemplate(mdBusiness.definesType());
    exporter.addTemplate(mdBusiness2.definesType());
    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(2, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row typeRow = sheet.getRow(0);//www  .jav  a 2  s .c  om
    Row attributeRow = sheet.getRow(1);
    Row labelRow = sheet.getRow(2);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    int cellNum = ((List<? extends MdAttributeDAOIF>) ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter())).size();

    String attributeName = attributeRow.getCell(cellNum).getRichStringCellValue().toString();
    String label = labelRow.getCell(cellNum).getRichStringCellValue().toString();

    assertEquals(MockExcelExportListener.ATTRIBUTE_NAME, attributeName);
    assertEquals(MockExcelExportListener.DISPLAY_LABEL, label);

    sheet = workbook.getSheetAt(1);
    typeRow = sheet.getRow(0);
    attributeRow = sheet.getRow(1);
    labelRow = sheet.getRow(2);

    assertEquals(mdBusiness2.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    cellNum = ((List<? extends MdAttributeDAOIF>) ExcelUtil.getAttributes(mdBusiness2,
            new DefaultExcelAttributeFilter())).size();

    attributeName = attributeRow.getCell(cellNum).getRichStringCellValue().toString();
    label = labelRow.getCell(cellNum).getRichStringCellValue().toString();

    assertEquals(MockExcelExportListener.ATTRIBUTE_NAME, attributeName);
    assertEquals(MockExcelExportListener.DISPLAY_LABEL, label);
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testMultipleSheetsWithDifferentListeners() throws IOException {
    List<ExcelExportListener> listeners = new LinkedList<ExcelExportListener>();
    listeners.add(new MockExcelExportListener());

    ExcelExporter exporter = new ExcelExporter();
    exporter.addTemplate(mdBusiness.definesType());
    exporter.addTemplate(mdBusiness2.definesType(), listeners);
    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(2, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row typeRow = sheet.getRow(0);//  w  w  w.  j  a v  a2  s  . c  o m
    Row attributeRow = sheet.getRow(1);
    Row labelRow = sheet.getRow(2);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdAttributeDAOIF> attributes = ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
    }

    // Ensure there aren't any extra columns
    assertNull(attributeRow.getCell(attributes.size()));
    assertNull(labelRow.getCell(attributes.size()));

    sheet = workbook.getSheetAt(1);
    typeRow = sheet.getRow(0);
    attributeRow = sheet.getRow(1);
    labelRow = sheet.getRow(2);

    assertEquals(mdBusiness2.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    int cellNum = ((List<? extends MdAttributeDAOIF>) ExcelUtil.getAttributes(mdBusiness2,
            new DefaultExcelAttributeFilter())).size();

    String attributeName = attributeRow.getCell(cellNum).getRichStringCellValue().toString();
    String label = labelRow.getCell(cellNum).getRichStringCellValue().toString();

    assertEquals(MockExcelExportListener.ATTRIBUTE_NAME, attributeName);
    assertEquals(MockExcelExportListener.DISPLAY_LABEL, label);
}

---

From source file:com.runwaysdk.dataaccess.io.ExcelExporterTest.java

License:Open Source License

public void testInvalidSheetNames() throws IOException {
    ExcelExporter exporter = new ExcelExporter();
    exporter.addTemplate(mdBusiness.definesType());
    exporter.addTemplate(mdBusiness2.definesType());
    exporter.addTemplate(mdBusiness3.definesType());
    byte[] bytes = exporter.write();

    Workbook workbook = new HSSFWorkbook(new ByteArrayInputStream(bytes));

    assertEquals(3, workbook.getNumberOfSheets());

    Sheet sheet = workbook.getSheetAt(0);
    Row typeRow = sheet.getRow(0);//from   w ww. ja v  a 2s  . c o m
    Row attributeRow = sheet.getRow(1);
    Row labelRow = sheet.getRow(2);

    assertEquals(mdBusiness.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    List<? extends MdAttributeDAOIF> attributes = ExcelUtil.getAttributes(mdBusiness,
            new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
    }

    // Ensure there aren't any extra columns
    assertNull(attributeRow.getCell(attributes.size()));
    assertNull(labelRow.getCell(attributes.size()));

    sheet = workbook.getSheetAt(1);
    typeRow = sheet.getRow(0);
    attributeRow = sheet.getRow(1);
    labelRow = sheet.getRow(2);

    assertEquals(mdBusiness2.definesType(), typeRow.getCell(0).getRichStringCellValue().toString());

    attributes = ExcelUtil.getAttributes(mdBusiness2, new DefaultExcelAttributeFilter());

    for (int i = 0; i < attributes.size(); i++) {
        MdAttributeDAOIF mdAttribute = attributes.get(i);

        String attributeName = attributeRow.getCell(i).getRichStringCellValue().toString();
        String label = labelRow.getCell(i).getRichStringCellValue().toString();

        assertEquals(mdAttribute.definesAttribute(), attributeName);
        assertEquals(mdAttribute.getDisplayLabel(Session.getCurrentLocale()), label);
    }

    // Ensure there aren't any extra columns
    assertNull(attributeRow.getCell(attributes.size()));
    assertNull(labelRow.getCell(attributes.size()));
}

---

• «
• 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
• »