List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
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; }