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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. 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;
}