List of usage examples for org.apache.poi.ss.usermodel CellValue getStringValue
public String getStringValue()
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
private String getCellData(final HSSFCell cell) { if (cell == null) return null; int colspan = 1; if (colIndex == mergeStart) { // First cell in the merging region - set colspan. colspan = mergeEnd - mergeStart + 1; } else if (colIndex == mergeEnd) { // Last cell in the merging region - no more skipped cells. mergeStart = -1;//from w ww . j a va 2 s.c om mergeEnd = -1; return null; } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) { // Within the merging region - skip the cell. return null; } //StringBuffer buf = new StringBuffer(); String val = ""; try { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: // POI does not distinguish between integer and double, thus: final double original = cell.getNumericCellValue(), rounded = Math.round(original); if (Math.abs(rounded - original) < 0.00000000000000001) { val = String.valueOf((int) rounded); } else { val = String.valueOf(original); } break; case HSSFCell.CELL_TYPE_FORMULA: final CellValue cv = evaluator.evaluate(cell); if (cv == null) return null; switch (cv.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: out.append(cv.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: out.append(cv.getNumberValue()); break; case Cell.CELL_TYPE_STRING: out.append(cv.getStringValue()); break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } break; default: // Neither string or number? Could be a date. try { val = sdf.format(cell.getDateCellValue()); } catch (final Exception e1) { } } } catch (final Exception e) { val = e.getMessage(); } if ("null".equals(val)) { val = ""; } return val; }
From source file:hu.webhejj.commons.io.table.excel.ExcelRowValueConverter.java
License:Apache License
public <T> T getValue(Row row, int column, Class<T> valueType) { if (row == null) { return null; }//from w w w . j av a2 s. c om Cell cell = row.getCell(column); if (cell == null) { return null; } CellValue cellValue = getCellValue(row, cell, column); if (cellValue == null) { return null; } switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: if (String.class.isAssignableFrom(valueType)) { return (T) Boolean.toString(cellValue.getBooleanValue()); } else if (Integer.class.isAssignableFrom(valueType)) { return (T) Integer.valueOf(cellValue.getBooleanValue() ? 1 : 0); } else if (Long.class.isAssignableFrom(valueType)) { return (T) Long.valueOf(cellValue.getBooleanValue() ? 1L : 0L); } else { throw new ClassCastException( "Can't convert " + cellValue.getBooleanValue() + " to " + valueType.getName()); } case Cell.CELL_TYPE_STRING: String stringValue = cellValue.getStringValue(); if (CompareUtils.isEmpty(stringValue)) { return null; } if ("null".equals(stringValue)) { return null; } if (String.class.isAssignableFrom(valueType)) { return (T) stringValue; } else if (Integer.class.isAssignableFrom(valueType)) { return (T) Integer.valueOf(stringValue); } else if (Long.class.isAssignableFrom(valueType)) { return (T) Long.valueOf(stringValue); } else if (valueType.isEnum()) { return (T) Enum.valueOf((Class<? extends Enum>) valueType, stringValue); } else if (BigDecimal.class.isAssignableFrom(valueType)) { return (T) (CompareUtils.isEmpty(stringValue) ? null : new BigDecimal(stringValue)); } else if (Boolean.class.isAssignableFrom(valueType)) { return (T) Boolean.valueOf("true".equalsIgnoreCase(stringValue) || (!CompareUtils.isEmpty(stringValue) && !"0".equals(stringValue))); } else { throw new ClassCastException("Can't convert " + stringValue + " to " + valueType.getName()); } case Cell.CELL_TYPE_NUMERIC: if (String.class.isAssignableFrom(valueType)) { Format format = formatter.createFormat(cell); if (format == null) { // TODO: do this without creating a BigDecimal each time return (T) new BigDecimal(cellValue.getNumberValue()).toString(); } else { return (T) format.format(cellValue.getNumberValue()); } } else if (Integer.class.isAssignableFrom(valueType)) { return (T) Integer.valueOf((int) cellValue.getNumberValue()); } else if (Long.class.isAssignableFrom(valueType)) { return (T) Long.valueOf((int) cellValue.getNumberValue()); } else { throw new ClassCastException( "Can't convert " + cellValue.getNumberValue() + " to " + valueType.getName()); } case Cell.CELL_TYPE_ERROR: FormulaError error = FormulaError.forInt(cell.getErrorCellValue()); if (FormulaError.NA.equals(error)) { return null; } else { // System.err.format(" Cell[%d,%d] error code %s\n", r.getRowNum(), column, error); return null; // throw new RuntimeException(String.format("Cell[%d,%d] error code %s", r.getRowNum(), column, error)); } } throw new IllegalArgumentException("Don't know how to convert cell of type " + cellValue.getCellType()); }
From source file:mw.sqlitetool.ExcelHelper.java
public Object getCellValue(Cell cell) { if (cell == null || cell.getCellType() == cell.CELL_TYPE_BLANK) { return ""; } else if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) { return cell.getBooleanCellValue(); } else if (cell.getCellType() == cell.CELL_TYPE_ERROR) { return cell.getErrorCellValue(); } else if (cell.getCellType() == cell.CELL_TYPE_FORMULA) { FormulaEvaluator evaluator = _workbook.getCreationHelper().createFormulaEvaluator(); CellValue val = evaluator.evaluate(cell); if (val.getCellType() == cell.CELL_TYPE_BOOLEAN) { return val.getBooleanValue(); } else if (val.getCellType() == cell.CELL_TYPE_NUMERIC) { return val.getNumberValue(); } else if (val.getCellType() == cell.CELL_TYPE_STRING) { return val.getStringValue(); } else if (val.getCellType() == cell.CELL_TYPE_ERROR) { return val.getErrorValue(); }// w w w. j a va2s . c om } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue(); } else { return cell.getNumericCellValue(); } } else if (cell.getCellType() == cell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } return ""; }
From source file:opn.greenwebs.FXMLDocumentController.java
private Object getCellData(File fSheet, int row, int coll) { try {//from w ww .j a va 2s . c o m XSSFWorkbook book = new XSSFWorkbook(fSheet); FormulaEvaluator eval = book.getCreationHelper().createFormulaEvaluator(); XSSFSheet xSheet = book.getSheet("Digital Version"); Cell celled = xSheet.getRow(row).getCell(coll); if (celled != null) { CellValue cellval = eval.evaluate(celled); if (cellval == null) { //System.out.println("cellval is null at line 918 " + fSheet.getAbsolutePath() + " " + row + " " + coll); return ""; } else { switch (cellval.getCellType()) { case Cell.CELL_TYPE_BLANK: logger.info("got a blank"); return ""; case Cell.CELL_TYPE_BOOLEAN: logger.info("got a boolean"); return cellval.getBooleanValue(); case Cell.CELL_TYPE_ERROR: return cellval.getErrorValue(); case Cell.CELL_TYPE_FORMULA: return cellval.getStringValue(); case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(celled)) { return HSSFDateUtil.getJavaDate(cellval.getNumberValue()); } else { return cellval.getNumberValue(); } case Cell.CELL_TYPE_STRING: return cellval.getStringValue(); default: return ""; } } } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } return ""; }
From source file:opn.greenwebs.FXMLDocumentController.java
private List<ItemDB> createListFromTable(File fStock) { List<ItemDB> lstMain = new ArrayList<>(); logger.log(Level.INFO, "into the breach {0}", fStock.getAbsolutePath()); try (FileInputStream fis = new FileInputStream(fStock); XSSFWorkbook book = new XSSFWorkbook(fis)) { XSSFSheet sheet = book.getSheet("Digital Version"); logger.log(Level.INFO, "we have a sheet {0}", sheet.getSheetName()); FormulaEvaluator evaluator2 = book.getCreationHelper().createFormulaEvaluator(); List lstItem;// www .ja v a 2 s . c om List<XSSFTable> lstTables = sheet.getTables(); logger.log(Level.SEVERE, "we have a table? {0}", lstTables.size()); if (!lstTables.isEmpty()) { logger.log(Level.INFO, "the table is not empty"); for (int j = lstTables.get(0).getStartCellReference().getRow(); j <= lstTables.get(0) .getEndCellReference().getRow(); j++) { ItemDB itmLine = new ItemDB(); lstItem = new ArrayList(); logger.log(Level.INFO, "we have some rows"); if (j > lstTables.get(0).getStartCellReference().getRow()) { Row row = sheet.getRow(j); if (row != null) { Iterator<Cell> cellIt = row.cellIterator(); logger.log(Level.INFO, "we have some cells {0}", cellIt.hasNext()); while (cellIt.hasNext()) { Cell cell = cellIt.next(); CellValue cellvalue = evaluator2.evaluate(cell); if (cellvalue == null) { lstItem.add(""); } else { switch (cellvalue.getCellType()) { case Cell.CELL_TYPE_STRING: lstItem.add(cellvalue.getStringValue()); break; case Cell.CELL_TYPE_NUMERIC: lstItem.add(cellvalue.getNumberValue()); break; case Cell.CELL_TYPE_BOOLEAN: lstItem.add(cellvalue.getBooleanValue()); case Cell.CELL_TYPE_BLANK: lstItem.add(""); break; case Cell.CELL_TYPE_FORMULA: lstItem.add("You found a formula"); break; default: } } } logger.log(Level.ALL, "lstItem is {0} and {1}", new Object[] { lstItem.size(), lstItem.toString() }); itmLine.setDblQty(Double.parseDouble(lstItem.get(0).toString())); logger.log(Level.INFO, "setting the quantity to {0}", lstItem.get(0).toString()); itmLine.setStrMfr(lstItem.get(1).toString()); itmLine.setStrSKU(lstItem.get(2).toString()); itmLine.setStrDescrip(lstItem.get(3).toString()); itmLine.setStrSupplier(lstItem.get(4).toString()); itmLine.setStrSupPart(lstItem.get(5).toString()); logger.log(Level.INFO, "setting the suppart to {0}", lstItem.get(5)); if (lstItem.size() > 6) { if (!lstItem.get(6).toString().isEmpty()) { itmLine.setDblSalePrice(Double.parseDouble(lstItem.get(6).toString())); } } //System.out.println("Added item " + itmLine.getStrDescrip()); lstMain.add(itmLine); } } } } } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } logger.log(Level.INFO, "returning {0}", lstMain.size()); return lstMain; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetGetCellValue.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; int rowNo, columnNo; /*//from www.ja va2s. c o m * Collect up the parameters */ spreadsheet = (cfSpreadSheetData) parameters.get(2); rowNo = parameters.get(1).getInt() - 1; columnNo = parameters.get(0).getInt() - 1; if (rowNo < 0) throwException(_session, "row must be 1 or greater (" + rowNo + ")"); if (columnNo < 0) throwException(_session, "column must be 1 or greater (" + columnNo + ")"); /* * Find the cell in question */ Sheet sheet = spreadsheet.getActiveSheet(); Row row = sheet.getRow(rowNo); if (row == null) row = sheet.createRow(rowNo); Cell cell = row.getCell(columnNo); if (cell == null) cell = row.createCell(columnNo); FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator(); if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) return cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) return new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return cfStringData.EMPTY_STRING; else if (cell.getCellType() == Cell.CELL_TYPE_STRING) return new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: return new cfNumberData(cellValue.getNumberValue()); case Cell.CELL_TYPE_STRING: return new cfStringData(cellValue.getStringValue()); default: return cfStringData.EMPTY_STRING; } } else return cfStringData.EMPTY_STRING; }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetQueryRead.java
License:Open Source License
public cfData execute(cfSession _session, cfArgStructData argStruct) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = (cfSpreadSheetData) getNamedParam(argStruct, "spreadsheet", null); if (spreadsheet == null) throwException(_session, "spreadsheet object not given"); //How many columns are we using int[] columnsToUse; String columns = getNamedStringParam(argStruct, "columns", null); if (columns != null) { int x = 0; List<Integer> numbers = tagUtils.getNumberListSorted(columns); columnsToUse = new int[numbers.size()]; Iterator<Integer> numbersIT = numbers.iterator(); while (numbersIT.hasNext()) columnsToUse[x++] = (numbersIT.next() - 1); } else {/* w w w . ja v a2s. c om*/ int maxColumns = SheetUtility.getMaxColumn(spreadsheet.getActiveSheet()); columnsToUse = new int[maxColumns]; for (int x = 0; x < maxColumns; x++) columnsToUse[x] = x; } //Figure out the columns String columnLabels[] = null; int startRow = 0; String columnnames = getNamedStringParam(argStruct, "columnnames", null); int headerRow = getNamedIntParam(argStruct, "headerrow", -1); FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator(); if (columnnames != null) { columnLabels = (columnnames.split("\\s*,\\s*")); if (columnLabels.length != columnsToUse.length) throwException(_session, "The COLUMNNAMES does not match the number of columns"); } else if (headerRow > -1) { headerRow = headerRow - 1; Row row = spreadsheet.getActiveSheet().getRow(headerRow); if (row == null) throwException(_session, "The HEADERROW does not exist"); columnLabels = new String[columnsToUse.length]; for (int c = 0; c < columnsToUse.length; c++) { Cell cell = row.getCell(columnsToUse[c]); if (cell == null) columnLabels[c] = ""; else columnLabels[c] = cell.toString(); } startRow = headerRow + 1; } else { columnLabels = new String[columnsToUse.length]; for (int x = 0; x < columnLabels.length; x++) columnLabels[x] = "Column " + (columnsToUse[x] + 1); } //Create the query cfQueryResultData queryData = new cfQueryResultData(columnLabels, "SpreadSheet"); List<Map<String, cfData>> vResults = new ArrayList<Map<String, cfData>>(); int sheetNo = getNamedIntParam(argStruct, "sheet", -1); if (sheetNo != -1) spreadsheet.setActiveSheet(sheetNo); Sheet sheet = spreadsheet.getActiveSheet(); Row row; Cell cell; cfData cfdata; String rowsSt = getNamedStringParam(argStruct, "rows", null); if (rowsSt != null) { List<Integer> rows = tagUtils.getNumberListSorted(rowsSt); Iterator<Integer> rowsIT = rows.iterator(); while (rowsIT.hasNext()) { int r = rowsIT.next() - 1; Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: cfdata = new cfNumberData(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: cfdata = new cfStringData(cellValue.getStringValue()); break; default: cfdata = cfStringData.EMPTY_STRING; break; } } else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } else { /* * Read __ALL__ the rows associated with this spreadsheet */ for (int r = startRow; r < sheet.getLastRowNum() + 1; r++) { Map<String, cfData> hm = new FastMap<String, cfData>(); if ((row = sheet.getRow(r)) == null) continue; for (int c = 0; c < columnsToUse.length; c++) { cell = row.getCell(columnsToUse[c]); if (cell == null) cfdata = new cfStringData(""); else { if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) cfdata = cfBooleanData.getcfBooleanData(cell.getBooleanCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) cfdata = new cfNumberData(cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) cfdata = new cfStringData(""); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) cfdata = new cfStringData(cell.getStringCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cfdata = cfBooleanData.getcfBooleanData(cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: cfdata = new cfNumberData(cellValue.getNumberValue()); break; case Cell.CELL_TYPE_STRING: cfdata = new cfStringData(cellValue.getStringValue()); break; default: cfdata = cfStringData.EMPTY_STRING; break; } } else cfdata = new cfStringData(""); } hm.put(columnLabels[c], cfdata); } vResults.add(hm); } } queryData.populateQuery(vResults); return queryData; }
From source file:org.databene.formats.xls.XLSUtil.java
License:Open Source License
@SuppressWarnings({ "unchecked", "rawtypes" })
private static Object convertString(CellValue cellValue, String emptyMarker,
Converter<?, ?> stringPreprocessor) {
String content = cellValue.getStringValue();
if (content != null && (content.equals(emptyMarker) || content.equals("'")))
content = "";
return (stringPreprocessor != null ? ((Converter) stringPreprocessor).convert(content) : content);
}
From source file:org.drools.decisiontable.parser.xls.ExcelParser.java
License:Apache License
private String getCellValue(final CellValue cv) { switch (cv.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return Boolean.toString(cv.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: return String.valueOf(cv.getNumberValue()); }/*from w w w . ja v a2 s . c o m*/ return cv.getStringValue(); }
From source file:org.geotools.data.excel.ExcelFeatureSource.java
License:Open Source License
/** * create a FeatureSource with the specified Query * //from ww w.j a v a2 s .c om * @param entry * @param query * - a query containing a filter that will be applied to the data */ public ExcelFeatureSource(ContentEntry entry, Query query) { super(entry, query); Date beginingOfExcelTime = HSSFDateUtil.getJavaDate(0); dataStore = (ExcelDataStore) entry.getDataStore(); sheet = dataStore.getSheet(); latCol = dataStore.getLatColumnIndex(); lonCol = dataStore.getLonColumnIndex(); int rows = sheet.getPhysicalNumberOfRows(); int start = dataStore.getHeaderRowIndex() + 1; latCol = dataStore.getLatColumnIndex(); lonCol = dataStore.getLonColumnIndex(); features = new ArrayList<SimpleFeature>(); filteredFeatures = new ArrayList<SimpleFeature>(); evaluator = dataStore.workbook.getCreationHelper().createFormulaEvaluator(); if (schema == null) { schema = getSchema(); } GeometryFactory geometryFactory = dataStore.getGeometryFactory(); SimpleFeatureBuilder builder = new SimpleFeatureBuilder(schema); Row header = sheet.getRow(dataStore.getHeaderRowIndex()); for (int i = start; i < rows; i++) { Row data = sheet.getRow(i); double x = 0.0; double y = 0.0; for (int col = data.getFirstCellNum(); col < data.getLastCellNum(); col++) { final Cell cell = data.getCell(col); CellValue value = evaluator.evaluate(cell); if (col == latCol) { if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) { y = value.getNumberValue(); } } else if (col == lonCol) { if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) { x = value.getNumberValue(); } } else { // cast and handle final String name = header.getCell(col).getStringCellValue().trim(); switch (value.getCellType()) { case Cell.CELL_TYPE_NUMERIC: AttributeType type = schema.getType(name); Class<?> clazz = type.getBinding(); if (clazz == Double.class) { builder.set(name, value.getNumberValue()); } else if (clazz == java.sql.Date.class) { final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue()); final Calendar cal = Calendar.getInstance(); cal.clear(); cal.setTime(javaDate); java.sql.Date date = new java.sql.Date(cal.getTimeInMillis()); builder.set(name, date); } else if (clazz == java.util.Date.class) { final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue()); builder.set(name, javaDate); } else if (clazz == Time.class) { final java.util.Date javaDate = HSSFDateUtil.getJavaDate(value.getNumberValue()); final Calendar cal = Calendar.getInstance(); cal.clear(); cal.setTime(javaDate); cal.set(0, 0, 0); Time time = new Time(cal.getTimeInMillis()); builder.set(name, time); } break; case Cell.CELL_TYPE_STRING: builder.set(name, value.getStringValue().trim()); break; case Cell.CELL_TYPE_BOOLEAN: builder.set(name, value.getBooleanValue()); break; default: System.out.println("We don't handle " + cell.getCellType() + " type cells " + cell.getStringCellValue()); } } } Point p = geometryFactory.createPoint(new Coordinate(x, y)); builder.set("the_geom", p); SimpleFeature feature = builder.buildFeature(null); features.add(feature); } filterFeatures(query); }