Example usage for org.apache.poi.ss.usermodel Cell getStringCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue

Introduction

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

Prototype

String getStringCellValue();

Source Link

Document

Get the value of the cell as a string

For numeric cells we throw an exception.

Usage

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java

License:Open Source License

/**
 * @param cell//from w ww.j a v a  2s  . c o m
 * @return true if cell is empty, false if not.
 */
@SuppressWarnings({ "PMD.MissingBreakInSwitch" })
public static boolean isEmptyCell(Cell cell) {
    if (cell == null) {
        return true;
    }
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return true;
    case Cell.CELL_TYPE_BOOLEAN:
        return false;
    case Cell.CELL_TYPE_ERROR:
        return true;
    case Cell.CELL_TYPE_FORMULA:
        return StringUtils.isEmpty(cell.getCellFormula());
    case Cell.CELL_TYPE_NUMERIC:
        return false;
    case Cell.CELL_TYPE_STRING:
        return StringUtils.isEmpty(cell.getStringCellValue());
    default: // impossible.
        return true;
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelUtils.java

License:Open Source License

/**
 * get the attribute from the cell. Do basic type conversions.
 * //from   ww  w . j a  v  a  2s.  co  m
 * @param cell the cell to read
 * @param convertNumberToDate if true, AND if the cell type is numeric, convert the cell value to Date; use the 
 *        numeric value otherwise.
 * 
 * @return the value from the cell, or null if cell is empty.
 */
@SuppressWarnings({ "PMD.MissingBreakInSwitch", "boxing" })
public static Object getCellValue(Cell cell, boolean convertNumberToDate) {

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_NUMERIC:
        if (convertNumberToDate) {
            return cell.getDateCellValue();
        } else {
            return cell.getNumericCellValue();
        }
    case Cell.CELL_TYPE_STRING:
        return StringUtils.trim(cell.getStringCellValue());
    case Cell.CELL_TYPE_ERROR:
        LOGGER.error("Error in cell {0}: contains Error", ExcelUtils.getFullCellName(cell));
        return null;
    case Cell.CELL_TYPE_FORMULA:
        return cell.getCellFormula();
    default: // impossible.
        LOGGER.error("Error in cell {0}: contains unknown cell type.", ExcelUtils.getFullCellName(cell));
        return null;
    }
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.workbookdata.SheetContext.java

License:Open Source License

public short getColumnFullParentNameColumnNumber() {
    Row row = getSheet().getRow(getHeaderRowNumber());
    for (short cellnum = row.getFirstCellNum(); cellnum <= row.getLastCellNum(); cellnum++) {
        Cell cell = row.getCell(cellnum);
        if (SheetContext.FULL_PARENT_NAME_COLUMN.equals(cell.getStringCellValue())) {
            return cellnum;
        }//from  w  ww .j  ava  2  s.c  o m
    }
    return -1;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticmi.write.metamodel.impl.ExcelAttributeTypeReader.java

License:Open Source License

private boolean isValidStructuredTypeSheet(Sheet sheet) {
    Cell cell = sheet.getRow(structuredTypeSheetIdentifier.getRow())
            .getCell(structuredTypeSheetIdentifier.getColumn());
    boolean matchesStName = (cell != null
            && (cell.toString().endsWith(structuredTypeSheetIdentifier.getIdentifier())
                    || cell.toString().endsWith(":RelationshipTypeExpression")));
    if (!matchesStName) {
        return false;
    }/*from w  w  w.  ja va 2 s  .  c  o  m*/
    String stName = getStructuredTypeName(cell.getStringCellValue());
    if (!isValidStructuredType(stName)) {
        if (!NamedUtil.areSame("InformationFlow", stName)) {
            this.messageListener.onMessage(new LocalizedIteraplanMessage(Severity.WARNING,
                    MESSAGE_KEY_UNSUPPOERTED_STRUCTURED_TYPE, stName));
        }
        return false;
    }
    return true;
}

From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.exporter.ExportWorkbook.java

License:Open Source License

public void adjustDefaultSettingsSheet() {
    if (sheetOrder.isEmpty()) {
        LOGGER.error(/*from  w  w  w .j a  va2 s.c o  m*/
                "Can't find the default settings worksheet in the currently loaded workbook. Maybe the template workbook is broken?");
        return;
    }

    // move the sheet from first to last position in our internal list
    Sheet defaultSheet = sheetOrder.remove(0);
    sheetOrder.add(defaultSheet);
    // and eventually move the sheet in the POI workbook
    getWb().setSheetOrder(DEFAULT_SHEET_KEY, sheetOrder.size());
    getWb().setSelectedTab(0);

    // store the current locale on the configuration settings sheet
    // re-get the sheet, as the Sheet reference seems to become invalid when the sheet is moved around
    defaultSheet = getWb().getSheet(DEFAULT_SHEET_KEY);
    for (Row row : defaultSheet) {
        Cell firstCell = row.getCell(0);
        if (firstCell == null) {
            continue;
        }
        String firstCellContent;
        try {
            firstCellContent = firstCell.getStringCellValue();
        } catch (IllegalStateException poie) {
            LOGGER.error("Can't read this type of cell, skipping.", poie);
            continue;
        }

        if (!"Locale".equals(firstCellContent.trim())) {
            // not yet in the right row
            continue;
        }

        Cell valueCell = row.getCell(1);
        if (valueCell == null) {
            valueCell = row.createCell(1);
        }

        // write the language code to that cell
        valueCell.setCellValue(UserContext.getCurrentLocale().getLanguage());
        break;
    }

}

From source file:de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformerTest.java

License:Open Source License

/**
 * Test method for {@link de.iteratec.iteraplan.businesslogic.exchange.nettoExport.NettoExcelTransformer#transform(java.util.List, java.io.OutputStream, de.iteratec.iteraplan.model.TypeOfBuildingBlock)}.
 */// w w  w  .j a v a2 s .  co m
@Test
public void testTransform() {
    NettoTransformer inst2007op = NettoExcelTransformer.newInstance(createSimpleOverviewPageTableStructure(),
            NettoExcelTransformer.ExcelVersion.EXCEL_VERSION_2007);
    NettoTransformer inst2003sr = NettoExcelTransformer.newInstance(
            createSimpleSpreadsheetReportTableStructure(),
            NettoExcelTransformer.ExcelVersion.EXCEL_VERSION_2003);

    assertNotNull("Can't create netto transformer for overview page table structure with excel 2007",
            inst2007op);
    assertNotNull("Can't create netto transformer for spreadsheet report table structure with excel 2003",
            inst2003sr);

    List<BuildingBlock> sourceList = new ArrayList<BuildingBlock>();
    String firstInfstrElemName = "Infrastructure Element for UnitTest";
    String lastInfstrElemDesc = "Last comment";
    sourceList.add(testDataHelper.createInfrastructureElement(firstInfstrElemName, "Some comment"));
    sourceList.add(
            testDataHelper.createInfrastructureElement("Another Infrastructure Element", "Some more comment"));
    sourceList.add(testDataHelper.createInfrastructureElement("Yet another Infrastructure Element",
            "Even more comment"));
    sourceList
            .add(testDataHelper.createInfrastructureElement("Last Infrastructure Element", lastInfstrElemDesc));

    ByteArrayOutputStream bufferA = new ByteArrayOutputStream();
    ByteArrayOutputStream bufferB = new ByteArrayOutputStream();
    inst2007op.transform(sourceList, bufferA, TypeOfBuildingBlock.INFRASTRUCTUREELEMENT);
    inst2003sr.transform(sourceList, bufferB, TypeOfBuildingBlock.INFRASTRUCTUREELEMENT);

    InputStream in2007 = new ByteArrayInputStream(bufferA.toByteArray());
    InputStream in2003 = new ByteArrayInputStream(bufferB.toByteArray());

    // Excel version
    try {
        assertTrue("Generated excel file is not version 2007.", POIXMLDocument.hasOOXMLHeader(in2007));
        assertTrue("Generated excel file is not version 2003.", POIFSFileSystem.hasPOIFSHeader(in2003));
    } catch (IOException e1) {
        fail("Can't read excel header from buffers.");
    }

    Workbook workbook2007 = null;
    Workbook workbook2003 = null;
    try {
        workbook2007 = WorkbookFactory.create(in2007);
        workbook2003 = WorkbookFactory.create(in2003);
    } catch (Exception e) {
        fail("Can't open generated excel workbook.");
    }

    assertNotNull("Could not create excel workbook instance from generated output (excel 2007).", workbook2007);
    assertNotNull("Could not create excel workbook instance from generated output (excel 2003).", workbook2003);

    assertSame("Number of sheets is not equal 1  (excel 2007).",
            Integer.valueOf(workbook2007.getNumberOfSheets()), Integer.valueOf(1));
    assertSame("Number of sheets is not equal 1  (excel 2003).",
            Integer.valueOf(workbook2003.getNumberOfSheets()), Integer.valueOf(1));

    String stringCellValue2007 = null;
    String stringCellValue2003 = null;
    String stringCellValueDesc2007 = null;
    String stringCellValueDesc2003 = null;
    Sheet sheet2007;
    Sheet sheet2003;
    Row firstDataRow2007;
    Row firstDataRow2003;
    Row lastDataRow2007;
    Row lastDataRow2003;
    Cell nameCell2007;
    Cell nameCell2003;
    Cell descCell2007;
    Cell descCell2003;

    try {
        sheet2007 = workbook2007.getSheetAt(0);
        sheet2003 = workbook2003.getSheetAt(0);

        firstDataRow2007 = sheet2007.getRow(1);
        firstDataRow2003 = sheet2003.getRow(1);

        lastDataRow2007 = sheet2007.getRow(4);
        lastDataRow2003 = sheet2003.getRow(4);

        nameCell2007 = firstDataRow2007.getCell(0);
        nameCell2003 = firstDataRow2003.getCell(0);

        descCell2007 = lastDataRow2007.getCell(1);
        descCell2003 = lastDataRow2003.getCell(1);

        stringCellValue2007 = nameCell2007.getStringCellValue();
        stringCellValue2003 = nameCell2003.getStringCellValue();

        stringCellValueDesc2007 = descCell2007.getStringCellValue();
        stringCellValueDesc2003 = descCell2003.getStringCellValue();
    } catch (Exception e) {
        fail("Wrong structure inside workbook/sheet/row.");
    }

    assertEquals(
            "String in generated excel 2007 workbook does not match the string from the first element in the List of BuildingBlocks.",
            stringCellValue2007, firstInfstrElemName);
    assertEquals(
            "String in generated excel 2003 workbook does not match the string from the first element in the List of BuildingBlocks.",
            stringCellValue2003, firstInfstrElemName);

    assertEquals(
            "String in generated excel 2007 workbook does not match the string from the last element in the List of BuildingBlocks.",
            stringCellValueDesc2007, lastInfstrElemDesc);
    assertEquals(
            "String in generated excel 2003 workbook does not match the string from the last element in the List of BuildingBlocks.",
            stringCellValueDesc2003, lastInfstrElemDesc);
}

From source file:de.iteratec.iteraplan.businesslogic.service.legacyExcel.ExcelExportServiceImplTest.java

License:Open Source License

/**
 * @param wb the Workbook to be scanned//from w  ww .j av  a 2  s  .  c o m
 * @param expctContent the string we search for
 * 
 * @return true if one of the workbook's contains the searched string, false otherwise
 */
private boolean wbContainsString(ExportWorkbook wb, String expctContent) {

    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        for (Iterator<Row> rIt = sheet.rowIterator(); rIt.hasNext();) {
            Row r = rIt.next();

            for (Iterator<Cell> cIt = r.cellIterator(); cIt.hasNext();) {
                Cell c = cIt.next();
                try {
                    if (c.getStringCellValue().equals(expctContent)) {
                        return true;
                    }

                } catch (IllegalStateException e) {
                    // if numeric cells are encountered
                }
            }
        }
    }
    return false;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

private String getStringCellValue(Cell cell, int originalColumnIndex) throws Exception {
    String value = null;//w  ww. j  ava 2 s  .c o  m
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                value = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        if (returnURLInsteadOfName) {
                            Hyperlink link = cell.getHyperlink();
                            if (link != null) {
                                if (concatenateLabelUrl) {
                                    String url = link.getAddress();
                                    if (url == null) {
                                        url = "";
                                    }
                                    String label = link.getLabel();
                                    if (label == null) {
                                        label = "";
                                    }
                                    value = label + "|" + url;
                                } else {
                                    value = link.getAddress();
                                }
                            } else {
                                value = cell.getStringCellValue();
                            }
                        } else {
                            value = cell.getStringCellValue();
                        }
                    } else if (cellType == CellType.NUMERIC) {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            if (defaultDateFormat != null) {
                                Date d = cell.getDateCellValue();
                                if (d != null) {
                                    value = defaultDateFormat.format(d);
                                }
                            } else {
                                value = getDataFormatter().formatCellValue(cell);
                            }
                        } else {
                            if (overrideExcelNumberFormat) {
                                value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue());
                            } else {
                                value = getDataFormatter().formatCellValue(cell);
                            }
                        }
                    } else if (cellType == CellType.BOOLEAN) {
                        value = cell.getBooleanCellValue() ? "true" : "false";
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.STRING) {
            if (returnURLInsteadOfName) {
                Hyperlink link = cell.getHyperlink();
                if (link != null) {
                    if (concatenateLabelUrl) {
                        String url = link.getAddress();
                        if (url == null) {
                            url = "";
                        }
                        String label = link.getLabel();
                        if (label == null) {
                            label = "";
                        }
                        value = label + "|" + url;
                    } else {
                        value = link.getAddress();
                    }
                } else {
                    value = cell.getStringCellValue();
                }
            } else {
                value = cell.getStringCellValue();
            }
        } else if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                value = getDataFormatter().formatCellValue(cell);
            } else {
                if (overrideExcelNumberFormat) {
                    value = getNumberFormat(originalColumnIndex).format(cell.getNumericCellValue());
                } else {
                    value = getDataFormatter().formatCellValue(cell);
                }
            }
        } else if (cellType == CellType.BOOLEAN) {
            value = cell.getBooleanCellValue() ? "true" : "false";
        } else if (cellType == CellType.BLANK) {
            value = null;
        }
    }
    return value;
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

public boolean isCellValueEmpty(Cell cell) {
    if (cell == null) {
        return true;
    } else {/*from   www .  ja va  2  s  . c o m*/
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.BLANK) {
            return true;
        } else if (cellType == CellType.FORMULA) {
            try {
                String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
                if (s == null || s.trim().isEmpty()) {
                    return true;
                } else {
                    return false;
                }
            } catch (Exception e) {
                return true;
            }
        } else if (cellType == CellType.STRING) {
            String s = cell.getStringCellValue();
            if (s == null || s.trim().isEmpty()) {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }
}

From source file:de.jlo.talendcomp.excel.SpreadsheetInput.java

License:Apache License

private Double getDoubleCellValue(Cell cell) throws Exception {
    Double value = null;/*  w  w w.ja v  a  2 s  .c  om*/
    if (cell != null) {
        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            try {
                String s = getDataFormatter().formatCellValue(cell, getFormulaEvaluator());
                if (s != null && s.trim().isEmpty() == false) {
                    Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim());
                    value = n.doubleValue();
                }
            } catch (Exception e) {
                if (useCachedValuesForFailedEvaluations) {
                    cellType = cell.getCachedFormulaResultTypeEnum();
                    if (cellType == CellType.STRING) {
                        String s = cell.getStringCellValue();
                        if (s != null && s.trim().isEmpty() == false) {
                            Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim());
                            value = n.doubleValue();
                        }
                    } else if (cellType == CellType.NUMERIC) {
                        value = cell.getNumericCellValue();
                    }
                } else {
                    throw e;
                }
            }
        } else if (cellType == CellType.STRING) {
            String s = cell.getStringCellValue();
            if (s != null && s.trim().isEmpty() == false) {
                Number n = getNumberFormat(cell.getColumnIndex()).parse(s.trim());
                value = n.doubleValue();
            }
        } else if (cellType == CellType.NUMERIC) {
            value = cell.getNumericCellValue();
        }
    }
    return value;
}