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

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

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

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

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  va 2s.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);//www.jav a 2  s .  c  om

    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  w  w.  j  av  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);/*from   w  ww .  j  a v  a 2  s  . com*/
    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);/*from   www.j a  va2 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());

    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);/*w  w  w.j  a  va 2  s. c om*/
    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.ExcelImporter.java

License:Open Source License

/**
 * Opens the stream, parses the types from the sheets and set up context objects for them
 * /*from  ww  w .  j a v a  2  s. com*/
 * @param stream
 * @return
 * @throws IOException
 */
private void openStream(InputStream stream) {
    try {
        Workbook workbook = ExcelUtil.getWorkbook(stream);

        this.errorWorkbook = ExcelUtil.createWorkbook(workbook);

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            String sheetName = workbook.getSheetName(i);

            // Skip the error sheet
            if (this.isValidSheet(sheet, sheetName)) {
                Row row = sheet.getRow(0);
                Cell cell = row.getCell(0);
                String type = ExcelUtil.getString(cell);

                contexts.add(builder.createContext(sheet, sheetName, errorWorkbook, type));
            }
        }

        errorWorkbook.createSheet(ERROR_SHEET);
    } catch (IOException e) {
        throw new SystemException(e);
    }
}

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

License:Open Source License

public void testError() throws IOException {
    BusinessDAO business = BusinessDAO.newInstance(mdBusiness.definesType());
    business.setValue("testDouble", "10");
    business.setValue("testInteger", "-1");

    ExcelExporter exporter = new ExcelExporter();

    ExcelExportSheet excelSheet = exporter.addTemplate(mdBusiness.definesType());
    excelSheet.addRow(business);/*ww  w .  ja  v a 2 s  .  c  om*/

    byte[] bytes = exporter.write();

    ExcelImporter importer = new ExcelImporter(new ByteArrayInputStream(bytes));
    byte[] results = importer.read();

    assertFalse(results.length == 0);

    ExcelExporterTest.writeFile(results);

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

    assertEquals(2, workbook.getNumberOfSheets());

    Sheet importSheet = workbook.getSheetAt(0);

    Row typeRow = importSheet.getRow(0);
    Row attributeRow = importSheet.getRow(1);
    Row labelRow = importSheet.getRow(2);
    Row row = importSheet.getRow(3);

    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();
        String value = ExcelUtil.getString(row.getCell(i));

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

    Sheet errorSheet = workbook.getSheetAt(1);

    Row errorRow = errorSheet.getRow(1);

    assertEquals(4, ExcelUtil.getInteger(errorRow.getCell(0)).intValue());
    assertEquals(mdBusiness.getTypeName(), ExcelUtil.getString(errorRow.getCell(1)));
}

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

License:Open Source License

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

    BusinessDAO invalid = BusinessDAO.newInstance(mdBusiness.definesType());
    invalid.setValue("testDouble", "10");
    invalid.setValue("testInteger", "-1");

    ExcelExporter exporter = new ExcelExporter();

    ExcelExportSheet excelSheet = exporter.addTemplate(mdBusiness.definesType());
    excelSheet.addRow(valid);//  ww  w .  ja v  a  2  s . c  o m
    excelSheet.addRow(invalid);

    byte[] bytes = exporter.write();

    ExcelImporter importer = new ExcelImporter(new ByteArrayInputStream(bytes));
    byte[] results = importer.read();

    assertFalse(results.length == 0);

    ExcelExporterTest.writeFile(results);

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

    assertEquals(2, workbook.getNumberOfSheets());

    Sheet importSheet = workbook.getSheetAt(0);

    Row typeRow = importSheet.getRow(0);
    Row attributeRow = importSheet.getRow(1);
    Row labelRow = importSheet.getRow(2);
    Row row = importSheet.getRow(3);

    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();
        String value = ExcelUtil.getString(row.getCell(i));

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

    Sheet errorSheet = workbook.getSheetAt(1);

    Row errorRow = errorSheet.getRow(1);

    assertEquals(4, ExcelUtil.getInteger(errorRow.getCell(0)).intValue());
    assertEquals(mdBusiness.getTypeName(), ExcelUtil.getString(errorRow.getCell(1)));

    List<String> ids = BusinessDAO.getEntityIdsFromDB(mdBusiness);

    assertEquals(1, ids.size());

    BusinessDAOIF test = BusinessDAO.get(ids.get(0));

    try {
        assertEquals(valid.getValue(TestFixConst.ATTRIBUTE_CHARACTER),
                test.getValue(TestFixConst.ATTRIBUTE_CHARACTER));
        assertEquals(valid.getValue("testDouble"), test.getValue("testDouble"));
        assertEquals(valid.getValue("testInteger"), test.getValue("testInteger"));
    } finally {
        TestFixtureFactory.delete(test);
    }
}

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

License:Open Source License

public void testMultipleSheetsWithErrors() throws IOException {
    BusinessDAO business = BusinessDAO.newInstance(mdBusiness.definesType());
    business.setValue("testDouble", "10");
    business.setValue("testInteger", "-1");

    BusinessDAO business2 = BusinessDAO.newInstance(mdBusiness2.definesType());
    business2.setValue(TestFixConst.ATTRIBUTE_BOOLEAN, "true");

    ExcelExporter exporter = new ExcelExporter();

    ExcelExportSheet mdBusinessSheet = exporter.addTemplate(mdBusiness.definesType());
    mdBusinessSheet.addRow(business);/* w ww . ja v  a  2  s  .  c om*/

    ExcelExportSheet mdBusinessSheet2 = exporter.addTemplate(mdBusiness2.definesType());
    mdBusinessSheet2.addRow(business2);

    byte[] bytes = exporter.write();

    ExcelImporter importer = new ExcelImporter(new ByteArrayInputStream(bytes));
    byte[] results = importer.read();

    assertFalse(results.length == 0);

    ExcelExporterTest.writeFile(results);

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

    assertEquals(3, workbook.getNumberOfSheets());

    Sheet importSheet = workbook.getSheetAt(0);

    Row typeRow = importSheet.getRow(0);
    Row attributeRow = importSheet.getRow(1);
    Row labelRow = importSheet.getRow(2);
    Row row = importSheet.getRow(3);

    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();
        String value = ExcelUtil.getString(row.getCell(i));

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

    Sheet errorSheet = workbook.getSheetAt(2);

    Row errorRow = errorSheet.getRow(1);

    assertEquals(4, ExcelUtil.getInteger(errorRow.getCell(0)).intValue());
    assertEquals(mdBusiness.getTypeName(), ExcelUtil.getString(errorRow.getCell(1)));
}