List of usage examples for org.apache.poi.ss.usermodel CellValue getNumberValue
public double getNumberValue()
From source file:de.bund.bfr.knime.pmmlite.io.XlsReader.java
License:Open Source License
private String getData(Cell cell) { if (cell == null) { return null; }/*w w w .j av a2s . c o m*/ if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { CellValue value = wb.getCreationHelper().createFormulaEvaluator().evaluate(cell); switch (value.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(value.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: return String.valueOf(value.getNumberValue()); case Cell.CELL_TYPE_STRING: return Strings.emptyToNull(Strings.nullToEmpty(value.getStringValue()).trim()); default: return null; } } else { return Strings.emptyToNull(cell.toString().trim()); } }
From source file:de.escnet.ExcelTable.java
License:Open Source License
private String getCellValue(XSSFCell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_BOOLEAN: return Boolean.toString(cell.getBooleanCellValue()); case Cell.CELL_TYPE_ERROR: return cell.getErrorCellString(); case Cell.CELL_TYPE_FORMULA: CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_BOOLEAN: return Boolean.toString(cellValue.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: return getNumericValue(cellValue.getNumberValue()); case Cell.CELL_TYPE_STRING: return cellValue.getStringValue(); case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_FORMULA: default:/*from w ww .j av a2s . c o m*/ throw new IllegalStateException("Illegal cell type: " + cell.getCellType()); } case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { return cell.getDateCellValue().toString(); } return getNumericValue(cell.getNumericCellValue()); case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); default: throw new IllegalStateException("Illegal cell type: " + cell.getCellType()); } }
From source file:dk.cubing.liveresults.uploader.parser.WcaParser.java
License:Open Source License
/** * @param row/*w ww . ja v a 2s.c om*/ * @param i * @return * @throws IllegalStateException */ private int parseResultCell(Row row, int i) throws IllegalStateException { int result = 0; Cell cell = row.getCell(3 + i); if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { String cellStr = null; switch (cell.getCellType()) { // result values case Cell.CELL_TYPE_NUMERIC: // seconds if ("0.00".equals(cell.getCellStyle().getDataFormatString())) { result = new Double(cell.getNumericCellValue() * 100).intValue(); // minutes } else if ("M:SS.00".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())) { try { result = resultTimeFormat.formatDateToInt(cell.getDateCellValue()); } catch (ParseException e) { log.error("[{}] " + e.getLocalizedMessage(), e); } // number } else if ("0".equals(cell.getCellStyle().getDataFormatString())) { result = new Double(cell.getNumericCellValue()).intValue(); // unsupported } else { log.warn("[{}] Unsupported cell format: {}. Row/Column ({}, {})", new Object[] { row.getSheet().getSheetName(), cell.getCellStyle().getDataFormatString(), cell.getRowIndex(), cell.getColumnIndex() }); } break; // Penalties case Cell.CELL_TYPE_STRING: cellStr = cell.getStringCellValue(); if (cellStr != null) { if (cellStr.equalsIgnoreCase(Result.Penalty.DNF.toString())) { result = Result.Penalty.DNF.getValue(); } else if (cellStr.equalsIgnoreCase(Result.Penalty.DNS.toString())) { result = Result.Penalty.DNS.getValue(); } } break; // best / worst case Cell.CELL_TYPE_FORMULA: CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { // calculated value case Cell.CELL_TYPE_NUMERIC: // seconds if ("0.00".equals(cell.getCellStyle().getDataFormatString())) { result = new Double(cellValue.getNumberValue() * 100).intValue(); // minutes } else if ("M:SS.00".equalsIgnoreCase(cell.getCellStyle().getDataFormatString())) { try { result = resultTimeFormat.formatDateToInt(cell.getDateCellValue()); } catch (ParseException e) { log.error("[{}] " + e.getLocalizedMessage(), e); } // number } else if ("0".equals(cell.getCellStyle().getDataFormatString()) || "GENERAL".equals(cell.getCellStyle().getDataFormatString())) { result = new Double(cell.getNumericCellValue()).intValue(); // unsupported } else { log.warn("[{}] Unsupported cell format: {}. Row/Column ({}, {})", new Object[] { row.getSheet().getSheetName(), cell.getCellStyle().getDataFormatString(), cell.getRowIndex(), cell.getColumnIndex() }); } break; // Penalties case Cell.CELL_TYPE_STRING: cellStr = cellValue.getStringValue(); if (cellStr != null) { if (cellStr.equalsIgnoreCase(Result.Penalty.DNF.toString())) { result = Result.Penalty.DNF.getValue(); } else if (cellStr.equalsIgnoreCase(Result.Penalty.DNS.toString())) { result = Result.Penalty.DNS.getValue(); } } break; } break; } } return result; }
From source file:eu.esdihumboldt.hale.app.bgis.ade.common.AbstractAnalyseTable.java
License:Open Source License
/** * Extract the text from a given cell. Formulas are evaluated, for blank or * error cells <code>null</code> is returned * /* ww w .j ava 2s .c om*/ * @param cell the cell * @return the cell text */ protected String extractText(Cell cell) { if (cell == null) return null; if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { // do this check here as the evaluator seems to return null on a // blank return null; } CellValue value = evaluator.evaluate(cell); switch (value.getCellType()) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(value.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: // number formatting double number = value.getNumberValue(); if (number == Math.floor(number)) { // it's an integer return String.valueOf((int) number); } return String.valueOf(value.getNumberValue()); case Cell.CELL_TYPE_STRING: return value.getStringValue(); case Cell.CELL_TYPE_FORMULA: // will not happen as we used the evaluator case Cell.CELL_TYPE_ERROR: // fall through default: return null; } }
From source file:eu.esdihumboldt.hale.io.xls.XLSUtil.java
License:Open Source License
/** * Extract the text from a given cell. Formulas are evaluated, for blank or * error cells <code>null</code> is returned * /*from www . j ava 2s . c om*/ * @param cell the cell * @param evaluator the formula evaluator * @return the cell text */ public static String extractText(Cell cell, FormulaEvaluator evaluator) { if (cell == null) return null; if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { // do this check here as the evaluator seems to return null on a // blank return null; } CellValue value = evaluator.evaluate(cell); switch (value.getCellType()) { case Cell.CELL_TYPE_BLANK: return null; case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(value.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: // number formatting double number = value.getNumberValue(); if (number == Math.floor(number)) { // it's an integer return String.valueOf((int) number); } return String.valueOf(value.getNumberValue()); case Cell.CELL_TYPE_STRING: return value.getStringValue(); case Cell.CELL_TYPE_FORMULA: // will not happen as we used the evaluator case Cell.CELL_TYPE_ERROR: // fall through default: return null; } }
From source file:excel.Reader.java
public void print() { System.out.println("START PRINT"); SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd"); int columnWidth = 15; FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); Sheet sheet = wb.getSheetAt(0);//from w w w .ja va 2 s. co m for (Row row : sheet) { //System.out.print("r"); for (Cell cell : row) { //CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); //System.out.print(cellRef.formatAsString()); //System.out.print(" - "); // System.out.print("c"); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: //System.out.print("s"); System.out.printf("%-" + columnWidth + "s", cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print("d"); if (DateUtil.isCellDateFormatted(cell)) { System.out.printf("%-" + columnWidth + "s", df.format(cell.getDateCellValue())); } else { if ((cell.getNumericCellValue() % 1.0) != 0.0) System.out.printf("%-" + columnWidth + ".2f", cell.getNumericCellValue()); else System.out.printf("%-" + columnWidth + ".0f", cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print("b"); System.out.printf("%-" + columnWidth + "s", cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: CellValue val = evaluator.evaluate(cell); //System.out.print("f"); switch (val.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.printf("%-" + columnWidth + "s", val.getStringValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.printf("%-" + columnWidth + ".2f", val.getNumberValue()); break; case Cell.CELL_TYPE_BOOLEAN: System.out.printf("%-" + columnWidth + "s", val.getBooleanValue()); break; default: System.out.printf("%-" + columnWidth + "s", ""); } break; default: System.out.print(""); } } System.out.println(); } }
From source file:fyp.POI.POI.java
public static void main(String[] args) throws Exception { FileInputStream fis = new FileInputStream(new File("FA_AAX.xlsx")); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0);//w w w. java 2 s. c o m FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); CellReference cr = new CellReference("C4"); Row row = sheet.getRow(cr.getRow()); Cell cell = row.getCell(cr.getCol()); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: boolean truefalse = cellValue.getBooleanValue(); System.out.println("Boolean" + truefalse); break; case Cell.CELL_TYPE_NUMERIC: double NumericCheck = cellValue.getNumberValue(); if (NumericCheck % 1 == 0) { int Integer = (int) NumericCheck; System.out.println("Integer" + Integer); } else { double Dbl = NumericCheck; System.out.println("Double" + Dbl); } break; case Cell.CELL_TYPE_STRING: String str = cellValue.getStringValue(); System.out.println("String" + str.substring(3)); break; case Cell.CELL_TYPE_BLANK: System.out.println("Blank"); break; case Cell.CELL_TYPE_ERROR: break; } fis.close(); }
From source file:fyp.POI.POIFunction.java
public double poiToGetDbl(String FileAddress, String CellDetails, int SheetNum) throws NullPointerException { double result = 0.0; try {/* ww w . ja v a2 s .c o m*/ FileInputStream fis = new FileInputStream(new File(FileAddress)); XSSFWorkbook wb = null; try { wb = new XSSFWorkbook(fis); } catch (IOException e) { System.out.print("Error detected: " + e); } XSSFSheet sheet = wb.getSheetAt(SheetNum); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); try { CellReference cr = new CellReference(CellDetails); Row row = sheet.getRow(cr.getRow()); Cell cell = row.getCell(cr.getCol()); //System.out.println("CELL "+ cell); checkNullNumeric(cell); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BLANK: result = 0.0; break; case Cell.CELL_TYPE_ERROR: result = 0.0; break; case Cell.CELL_TYPE_NUMERIC: result = cellValue.getNumberValue(); break; } } catch (NullPointerException n) { result = 0.0; } fis.close(); } catch (IOException e) { System.out.print("Error detected: " + e); } return result; }
From source file:fyp.POI.POIFunction.java
public double poiToGetInt(String FileAddress, String CellDetails, int SheetNum) throws FileNotFoundException { double result = 0.0; try {//from www . j av a 2s . c o m FileInputStream fis = new FileInputStream(new File(FileAddress)); XSSFWorkbook wb = null; try { wb = new XSSFWorkbook(fis); } catch (IOException e) { System.out.print("Error detected: " + e); } XSSFSheet sheet = wb.getSheetAt(SheetNum); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); try { CellReference cr = new CellReference(CellDetails); Row row = sheet.getRow(cr.getRow()); Cell cell = row.getCell(cr.getCol()); checkNullNumeric(cell); CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_NUMERIC: result = cellValue.getNumberValue(); break; case Cell.CELL_TYPE_BLANK: result = 0; break; case Cell.CELL_TYPE_ERROR: result = 0; break; } } catch (NullPointerException n) { result = 0; } fis.close(); } catch (IOException e) { System.out.print("Error detected: " + e); } return (int) result; }
From source file:gov.nih.nci.evs.browser.utils.ResolvedValueSetIteratorHolder.java
License:Open Source License
/** * (Each Excel sheet cell becomes an HTML table cell) Generates an HTML * table cell which has the same font styles, alignments, colours and * borders as the Excel cell.//from ww w . j ava 2 s. c om * * @param cell * The Excel cell. */ private void td(final HSSFCell cell) { 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; mergeEnd = -1; return; } else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart && colIndex < mergeEnd) { // Within the merging region - skip the cell. return; } //KLO 05022018 //out.append("<td "); out.append("<td height=\"15\" "); if (colspan > 1) { out.append("colspan='").append(colspan).append("' "); } if (cell == null) { out.append("/>\n"); return; } out.append("style='"); final HSSFCellStyle style = cell.getCellStyle(); // Text alignment switch (style.getAlignment()) { case CellStyle.ALIGN_LEFT: out.append("text-align: left; "); break; case CellStyle.ALIGN_RIGHT: out.append("text-align: right; "); break; case CellStyle.ALIGN_CENTER: out.append("text-align: center; "); break; default: break; } // Font style, size and weight final HSSFFont font = style.getFont(book); if (font == null) return; if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) { out.append("font-weight: bold; "); } if (font.getItalic()) { out.append("font-style: italic; "); } if (font.getUnderline() != HSSFFont.U_NONE) { out.append("text-decoration: underline; "); } out.append("font-size: ").append(Math.floor(font.getFontHeightInPoints() * 0.8)).append("pt; "); // Cell background and font colours final short[] backRGB = style.getFillForegroundColorColor().getTriplet(); final HSSFColor foreColor = palette.getColor(font.getColor()); if (foreColor != null) { final short[] foreRGB = foreColor.getTriplet(); if (foreRGB[0] != 0 || foreRGB[1] != 0 || foreRGB[2] != 0) { out.append("color: rgb(").append(foreRGB[0]).append(',').append(foreRGB[1]).append(',') .append(foreRGB[2]).append(");"); } } if (backRGB[0] != 0 || backRGB[1] != 0 || backRGB[2] != 0) { out.append("background-color: rgb(").append(backRGB[0]).append(',').append(backRGB[1]).append(',') .append(backRGB[2]).append(");"); } // Border if (style.getBorderTop() != HSSFCellStyle.BORDER_NONE) { out.append("border-top-style: solid; "); } if (style.getBorderRight() != HSSFCellStyle.BORDER_NONE) { out.append("border-right-style: solid; "); } if (style.getBorderBottom() != HSSFCellStyle.BORDER_NONE) { out.append("border-bottom-style: solid; "); } if (style.getBorderLeft() != HSSFCellStyle.BORDER_NONE) { out.append("border-left-style: solid; "); } out.append("'>"); 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; 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 = ""; } if (pix.containsKey(rowIndex)) { if (pix.get(rowIndex).containsKey(colIndex)) { for (final HSSFPictureData pic : pix.get(rowIndex).get(colIndex)) { out.append("<img alt='Image in Excel sheet' src='data:"); out.append(pic.getMimeType()); out.append(";base64,"); try { out.append(new String(Base64.encodeBase64(pic.getData()), "US-ASCII")); } catch (final UnsupportedEncodingException e) { throw new RuntimeException(e); } out.append("'/>"); } } } if (isCode(val) && this.URL != null) { val = getHyperLink(val); } out.append(val); out.append("</td>\n"); }