List of usage examples for org.apache.poi.ss.usermodel Cell getRichStringCellValue
RichTextString getRichStringCellValue();
For numeric cells we throw an exception.
From source file:org.dbunit.dataset.excel.MyXlsTable.java
License:Open Source License
static ITableMetaData createMetaData(String tableName, Row sampleRow) { logger.debug("createMetaData(tableName={}, sampleRow={}) - start", tableName, sampleRow); List columnList = new ArrayList(); for (int i = 0;; i++) { Cell cell = sampleRow.getCell(i); if (cell == null) { break; }// w w w . j ava2 s .c om String columnName = cell.getRichStringCellValue().getString(); if (columnName != null) { columnName = columnName.trim(); } // Bugfix for issue ID 2818981 - if a cell has a formatting but no name also ignore it if (columnName.length() <= 0) { logger.debug( "The column name of column # {} is empty - will skip here assuming the last column was reached", String.valueOf(i)); break; } Column column = new Column(columnName, DataType.UNKNOWN); columnList.add(column); } Column[] columns = (Column[]) columnList.toArray(new Column[0]); return new DefaultTableMetaData(tableName, columns); }
From source file:org.dbunit.dataset.excel.MyXlsTable.java
License:Open Source License
public Object getValue(int row, String column) throws DataSetException { if (logger.isDebugEnabled()) logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column); assertValidRowIndex(row);//from w w w . ja va 2 s .c o m int columnIndex = getColumnIndex(column); Row rowObject = _sheet.getRow(row + 1); if (rowObject == null) { return null; } Cell cell = rowObject.getCell(columnIndex); if (cell == null) { return null; } int type = cell.getCellType(); switch (type) { case Cell.CELL_TYPE_NUMERIC: CellStyle style = cell.getCellStyle(); if (DateUtil.isCellDateFormatted(cell)) { return getDateValue(cell); } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) { // The special dbunit date format return getDateValueFromJavaNumber(cell); } else { return getNumericValue(cell); } case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_FORMULA: throw new DataTypeException("Formula not supported at row=" + row + ", column=" + column); case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE; case Cell.CELL_TYPE_ERROR: throw new DataTypeException("Error at row=" + row + ", column=" + column); default: throw new DataTypeException("Unsupported type at row=" + row + ", column=" + column); } }
From source file:org.deegree.igeo.dataadapter.LinkedExcelTable.java
License:Open Source License
/** * //from w w w. ja v a2 s.c om * @param linkedTableType * @param file * @param sheetName * @throws IOException */ public LinkedExcelTable(LinkedFileTableType linkedTableType, File file) throws IOException { super(linkedTableType); if (file.getAbsolutePath().toLowerCase().endsWith(".xls")) { workbook = new HSSFWorkbook(new FileInputStream(file)); } else { workbook = new XSSFWorkbook(new FileInputStream(file)); } sheet = workbook.getSheetAt(0); LOG.logDebug("load first excel sheet"); Iterator<Row> rowIter = sheet.rowIterator(); Row firstRow = rowIter.next(); List<String> headerNames = new ArrayList<String>(); List<Integer> headerTypes = new ArrayList<Integer>(); for (Iterator<Cell> cit = firstRow.cellIterator(); cit.hasNext();) { Cell cell = cit.next(); String cellValue = cell.getRichStringCellValue().getString(); headerNames.add(cellValue); headerTypes.add(getCellType(cell.getCellType())); } columnNames = headerNames.toArray(new String[headerNames.size()]); types = new int[headerTypes.size()]; for (int i = 0; i < types.length; i++) { types[i] = headerTypes.get(i); } }
From source file:org.eclipse.jubula.client.core.businessprocess.importfilter.ExcelImportFilter.java
License:Open Source License
/** * returns a String for an excel cell/*from w ww.j a v a 2s.co m*/ * @param cell * HSSFCell * @return * String */ private String getExcelCellString(Cell cell) { if (cell == null) { return null; } int type = cell.getCellType(); switch (type) { case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_NUMERIC: return getDoubleString(cell.getNumericCellValue()); case Cell.CELL_TYPE_FORMULA: switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_NUMERIC: return getDoubleString(cell.getNumericCellValue()); default: break; } default: break; } return null; }
From source file:org.generationcp.middleware.util.PoiUtil.java
License:Open Source License
private static Object getFormulaValue(final Cell cell) { switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: return cell.getNumericCellValue(); case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue(); default://from w w w.j av a 2 s . c o m return cell.getCellFormula(); } }
From source file:org.geoserver.wfs.response.ExcelOutputFormatTest.java
License:Open Source License
private void testExcelOutputFormat(Workbook wb) throws IOException { Sheet sheet = wb.getSheet("PrimitiveGeoFeature"); assertNotNull(sheet);/*from w w w .java 2 s. c o m*/ FeatureSource fs = getFeatureSource(MockData.PRIMITIVEGEOFEATURE); // check the number of rows in the output final int feautureRows = fs.getCount(Query.ALL); assertEquals(feautureRows + 1, sheet.getPhysicalNumberOfRows()); // check the header is what we expect final SimpleFeatureType schema = (SimpleFeatureType) fs.getSchema(); final Row header = sheet.getRow(0); assertEquals("FID", header.getCell(0).getRichStringCellValue().toString()); for (int i = 0; i < schema.getAttributeCount(); i++) { assertEquals(schema.getDescriptor(i).getLocalName(), header.getCell(i + 1).getRichStringCellValue().toString()); } // check some selected values to see if the content and data type is the one // we expect FeatureIterator fi = fs.getFeatures().features(); SimpleFeature sf = (SimpleFeature) fi.next(); fi.close(); // ... a string cell Cell cell = sheet.getRow(1).getCell(1); assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); assertEquals(sf.getAttribute(0), cell.getRichStringCellValue().toString()); // ... a geom cell cell = sheet.getRow(1).getCell(4); assertEquals(Cell.CELL_TYPE_STRING, cell.getCellType()); assertEquals(sf.getAttribute(3).toString(), cell.getRichStringCellValue().toString()); // ... a number cell cell = sheet.getRow(1).getCell(6); assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); assertEquals(((Number) sf.getAttribute(5)).doubleValue(), cell.getNumericCellValue()); // ... a date cell (they are mapped as numeric in xms?) cell = sheet.getRow(1).getCell(10); assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); assertEquals(sf.getAttribute(9), cell.getDateCellValue()); // ... a boolean cell (they are mapped as numeric in xms?) cell = sheet.getRow(1).getCell(12); assertEquals(Cell.CELL_TYPE_BOOLEAN, cell.getCellType()); assertEquals(sf.getAttribute(11), cell.getBooleanCellValue()); // ... an empty cell (original value is null -> no cell) cell = sheet.getRow(1).getCell(3); assertNull(cell); }
From source file:org.isource.util.CSVUtils.java
private static List<List> readWorkbook(HSSFWorkbook workbook) { List<List> lines = new ArrayList<List>(); workbook = evaluateFormulas(workbook); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();/*from w w w . jav a 2s . co m*/ List<String> line = new ArrayList<String>(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: line.add(new Boolean(cell.getBooleanCellValue()).toString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy"); line.add(dateFormat.format(cell.getDateCellValue())); } else { line.add(new Double(cell.getNumericCellValue()).toString()); } break; case Cell.CELL_TYPE_STRING: line.add(cell.getStringCellValue()); break; case Cell.CELL_TYPE_FORMULA: switch (cell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: line.add(new Double(cell.getNumericCellValue()).toString()); break; case Cell.CELL_TYPE_STRING: line.add(cell.getRichStringCellValue().toString()); break; } break; } } lines.add(line); } return lines; }
From source file:org.joeffice.spreadsheet.cell.CellUtils.java
License:Apache License
public static void copyCell(Cell oldCell, Cell newCell) { newCell.setCellStyle(oldCell.getCellStyle()); if (newCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); }/*w ww .ja va 2s . c om*/ if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } }
From source file:org.kopsox.spreadsheet.util.POIUtil.java
License:Open Source License
/** * Gets a Value from the given Cell//from ww w . ja v a2s .c o m * * @param cell * @return Value */ @SuppressWarnings("boxing") public static final Value getValueFormCell(Cell cell) { //NUMERIC if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { //Numeric can be a Date or a Double return getValueFromNumeric(cell, null); } //STRING else if (Cell.CELL_TYPE_STRING == cell.getCellType()) { return new StringValue(cell.getRichStringCellValue().getString()); } //FORMEL else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) { String formula = cell.getCellFormula(); return getValueFromFormula(formula, cell); } //BOOLEAN else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) { return new BooleanValue(cell.getBooleanCellValue()); } //BLANK else if (Cell.CELL_TYPE_BLANK == cell.getCellType()) { return new BlankValue(); } //ERROR else if (Cell.CELL_TYPE_ERROR == cell.getCellType()) { //TODO //NOT SUPPORTED AT THE MOMENT } return new BlankValue(); }
From source file:org.kopsox.spreadsheet.util.POIUtil.java
License:Open Source License
@SuppressWarnings("boxing") private static final Value getValueFromFormula(String formula, Cell cell) { Value ret = null;//from w w w . java 2 s .c om if (Cell.CELL_TYPE_NUMERIC == cell.getCachedFormulaResultType()) { ret = getValueFromNumeric(cell, formula); } else if (Cell.CELL_TYPE_STRING == cell.getCachedFormulaResultType()) { StringValue tmp = new StringValue(cell.getRichStringCellValue().getString()); tmp.setFormula(formula); ret = tmp; } else if (Cell.CELL_TYPE_BOOLEAN == cell.getCachedFormulaResultType()) { BooleanValue tmp = new BooleanValue(cell.getBooleanCellValue()); tmp.setFormula(formula); ret = tmp; } return ret; }