Example usage for org.apache.poi.ss.usermodel Sheet getRow

List of usage examples for org.apache.poi.ss.usermodel Sheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getRow

Introduction

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

Prototype

Row getRow(int rownum);

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

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 w w  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.ExcelImporter.java

License:Open Source License

private boolean isValidSheet(Sheet sheet, String sheetName) {
    if (!sheetName.equals(ERROR_SHEET)) {
        Row row = sheet.getRow(0);

        if (row != null) {
            Cell cell = row.getCell(0);/*from www.  j  a  v a  2  s  .c  o m*/
            String type = ExcelUtil.getString(cell);

            try {
                return (type != null && MdTypeDAO.getMdTypeDAO(type) != null);
            } catch (Exception e) {
                return false;
            }
        }
    }

    return false;
}

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);//from w  w  w.  jav  a2  s. c o  m

    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);/*from w w  w  .  j  av  a2 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);/*from   w ww . j  a va 2  s. co  m*/

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

From source file:com.shiyq.poi.HSSFTest.java

private static void setSheet(Sheet sheet, int startRow, int endRow, int startColumn, int endColumn,
        String describe, CellStyle descStyle) {
    Row row = sheet.getRow(startRow);
    if (Objects.isNull(row)) {
        row = sheet.createRow(startRow);
    }/*ww  w  .j a va2  s  .c o  m*/
    Cell cell = row.createCell(startColumn);
    cell.setCellStyle(descStyle);
    cell.setCellValue(describe);
    sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startColumn, endColumn));
}

From source file:com.simopuve.helper.ExcelFiller.java

public static void fillRows(Sheet sheet, List<PDVRow> PDVRows) {
    try {// w w w. j  ava2  s.c  om
        int rowLocation = UsefulConstants.FIRST_ROW;
        Map rowMap = PropertiesMap.getPDVRowPropertiesCoordinatesByName();
        Row tmpRow = sheet.getRow(rowLocation);
        BeanInfo beanInfo = Introspector.getBeanInfo(PDVRow.class);
        for (PDVRow PDVrow : PDVRows) {
            for (PropertyDescriptor propertyDesc : beanInfo.getPropertyDescriptors()) {
                if (!UsefulConstants.T_CLASS.equals(propertyDesc.getName())
                        && !"rowNumber".equals(propertyDesc.getName())) {
                    PropertyCoordinates tmpCoordinate = (PropertyCoordinates) rowMap
                            .get(propertyDesc.getName());
                    Object value = propertyDesc.getReadMethod().invoke(PDVrow);
                    Cell cell = tmpRow.getCell(tmpCoordinate.getCellY());
                    if (null != value.getClass().getName()) {
                        switch (value.getClass().getName()) {
                        case UsefulConstants.T_STRING:
                            cell.setCellValue((String) value);
                            break;
                        case UsefulConstants.T_INTEGER:
                            cell.setCellValue((Integer) value);
                            break;
                        case UsefulConstants.T_BOOLEAN:
                            cell.setCellValue((boolean) value);
                            break;
                        default:
                            break;
                        }
                    }
                }
            }
            rowLocation++;
            tmpRow = sheet.getRow(rowLocation);
        }
    } catch (IntrospectionException | IllegalAccessException | IllegalArgumentException
            | InvocationTargetException ex) {
        Logger.getLogger(ExcelFiller.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.simopuve.helper.ExcelFiller.java

public static void fillHeader(Sheet sheet) {
    Map headerMap = PropertiesMap.getPDVHeaderPropertiesCoordinatesByName();
    try {/*ww  w  . j  a v a2s  .  co m*/
        PDVHeader bean = getFilledHeader();
        BeanInfo beanInfo = Introspector.getBeanInfo(PDVHeader.class);
        DataFormatter formatter = new DataFormatter();
        for (PropertyDescriptor propertyDesc : beanInfo.getPropertyDescriptors()) {
            String propertyName = propertyDesc.getName();
            if (!UsefulConstants.T_CLASS.equals(propertyName)) {
                PropertyCoordinates tmpCoordinate = (PropertyCoordinates) headerMap.get(propertyName);
                Object value = propertyDesc.getReadMethod().invoke(bean);
                Row row = sheet.getRow(tmpCoordinate.getRowX());
                Cell cell = row.getCell(tmpCoordinate.getCellY());

                Class valueClass = value.getClass();
                valueClass.cast(value);

                if (null != valueClass.getName()) {
                    switch (valueClass.getName()) {
                    case UsefulConstants.T_STRING:
                        cell.setCellValue((String) value);
                        break;
                    case UsefulConstants.T_INTEGER:
                        cell.setCellValue((Integer) value);
                        break;
                    case UsefulConstants.T_DATE:
                        Date date = (Date) value;
                        Calendar cal = Calendar.getInstance();
                        cal.setTime(date);
                        int year = cal.get(Calendar.YEAR);
                        int month = cal.get(Calendar.MONTH);
                        int day = cal.get(Calendar.DAY_OF_MONTH);
                        cell.setCellValue((Integer) day);
                        cell = row.getCell(tmpCoordinate.getCellY() + 1);
                        cell.setCellValue((Integer) month);
                        cell = row.getCell(tmpCoordinate.getCellY() + 2);
                        cell.setCellValue((Integer) year);
                        break;
                    default:
                        break;
                    }
                }

            }
        }
    } catch (IntrospectionException | IllegalAccessException | IllegalArgumentException
            | InvocationTargetException ex) {
        Logger.getLogger(ExcelFiller.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.simopuve.helper.ExcelFiller.java

public static void fillHeader(PDVHeader header, Sheet sheet) {
    Map headerMap = PropertiesMap.getPDVHeaderPropertiesCoordinatesByName();
    try {/*from   ww  w .  j a va 2 s .  c  o m*/
        PDVHeader bean = header;
        BeanInfo beanInfo = Introspector.getBeanInfo(PDVHeader.class);
        DataFormatter formatter = new DataFormatter();
        for (PropertyDescriptor propertyDesc : beanInfo.getPropertyDescriptors()) {
            String propertyName = propertyDesc.getName();
            if (!UsefulConstants.T_CLASS.equals(propertyName)) {
                PropertyCoordinates tmpCoordinate = (PropertyCoordinates) headerMap.get(propertyName);
                if (tmpCoordinate == null)
                    continue;
                Object value = propertyDesc.getReadMethod().invoke(bean);
                Row row = sheet.getRow(tmpCoordinate.getRowX());
                Cell cell = row.getCell(tmpCoordinate.getCellY());

                Class valueClass = value.getClass();
                valueClass.cast(value);

                if (null != valueClass.getName()) {
                    switch (valueClass.getName()) {
                    case UsefulConstants.T_STRING:
                        cell.setCellValue((String) value);
                        break;
                    case UsefulConstants.T_INTEGER:
                        cell.setCellValue((Integer) value);
                        break;
                    case UsefulConstants.T_DATE:
                        Date date = (Date) value;
                        Calendar cal = Calendar.getInstance();
                        cal.setTime(date);
                        int year = cal.get(Calendar.YEAR);
                        int month = cal.get(Calendar.MONTH) + 1;
                        int day = cal.get(Calendar.DAY_OF_MONTH);
                        cell.setCellValue((Integer) day);
                        cell = row.getCell(tmpCoordinate.getCellY() + 1);
                        cell.setCellValue((Integer) month);
                        cell = row.getCell(tmpCoordinate.getCellY() + 2);
                        cell.setCellValue((Integer) year);
                        break;
                    case UsefulConstants.T_BOOLEAN:
                        cell.setCellValue(((Boolean) value) ? "SI" : "NO");
                    default:
                        break;
                    }
                }

            }
        }
    } catch (IntrospectionException | IllegalAccessException | IllegalArgumentException
            | InvocationTargetException ex) {
        Logger.getLogger(ExcelFiller.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:com.simopuve.helper.ReadPVDFromFile.java

private static String getTextFromCell(Integer rowX, Integer cellY, DataFormatter formatter, Sheet sheet,
        Row row) {//from  ww w . j a  v a  2 s .c o  m
    Row rowL = (row == null) ? sheet.getRow(rowX) : row;
    Cell cell = rowL.getCell(cellY);
    return formatter.formatCellValue(cell);
}