List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue
double getNumericCellValue();
From source file:adams.data.io.input.ExcelSpreadSheetReader.java
License:Open Source License
/** * Turns a numeric cell into a string. Tries to use "long" representation * if possible./*from w w w . j a v a 2 s.c o m*/ * * @param cell the cell to process * @return the string representation */ protected String numericToString(Cell cell) { Double dbl; long lng; dbl = cell.getNumericCellValue(); lng = dbl.longValue(); if (dbl == lng) return "" + lng; else return "" + dbl; }
From source file:adams.data.io.input.ExcelSpreadSheetReader.java
License:Open Source License
/** * Reads the spreadsheet content from the specified file. * * @param in the input stream to read from * @return the spreadsheets or null in case of an error *//*from w w w . jav a 2 s . co m*/ @Override protected List<SpreadSheet> doReadRange(InputStream in) { List<SpreadSheet> result; int[] indices; Workbook workbook; Sheet sheet; SpreadSheet spsheet; Row exRow; Cell exCell; adams.data.spreadsheet.Row spRow; int i; int n; int cellType; DateFormat dformat; boolean numeric; int dataRowStart; int firstRow; int lastRow; List<String> header; result = new ArrayList<>(); workbook = null; dformat = DateUtils.getTimestampFormatter(); try { workbook = WorkbookFactory.create(in); m_SheetRange.setMax(workbook.getNumberOfSheets()); indices = m_SheetRange.getIntIndices(); firstRow = m_FirstRow - 1; dataRowStart = getNoHeader() ? firstRow : firstRow + 1; for (int index : indices) { if (m_Stopped) break; spsheet = m_SpreadSheetType.newInstance(); spsheet.setDataRowClass(m_DataRowType.getClass()); result.add(spsheet); if (isLoggingEnabled()) getLogger().info("sheet: " + (index + 1)); sheet = workbook.getSheetAt(index); if (sheet.getLastRowNum() == 0) { getLogger().severe("No rows in sheet #" + index); return null; } spsheet.setName(sheet.getSheetName()); // header if (isLoggingEnabled()) getLogger().info("header row"); exRow = sheet.getRow(firstRow); if (exRow == null) { getLogger().warning("No data in sheet #" + (index + 1) + "?"); } else if (exRow != null) { spRow = spsheet.getHeaderRow(); m_TextColumns.setMax(exRow.getLastCellNum()); if (getNoHeader()) { header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders); for (i = 0; i < header.size(); i++) spRow.addCell("" + (i + 1)).setContent(header.get(i)); } else { if (!m_CustomColumnHeaders.trim().isEmpty()) { header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders); for (i = 0; i < header.size(); i++) spRow.addCell("" + (i + 1)).setContent(header.get(i)); } else { for (i = 0; i < exRow.getLastCellNum(); i++) { if (m_Stopped) break; exCell = exRow.getCell(i); if (exCell == null) { spRow.addCell("" + (i + 1)).setMissing(); continue; } numeric = !m_TextColumns.isInRange(i); switch (exCell.getCellType()) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: spRow.addCell("" + (i + 1)).setContent("column-" + (i + 1)); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(exCell)) spRow.addCell("" + (i + 1)).setContent(new DateTime( HSSFDateUtil.getJavaDate(exCell.getNumericCellValue()))); else if (numeric) spRow.addCell("" + (i + 1)).setContent(exCell.getNumericCellValue()); else spRow.addCell("" + (i + 1)).setContentAsString(numericToString(exCell)); break; default: spRow.addCell("" + (i + 1)).setContentAsString(exCell.getStringCellValue()); } } } } } // data if (spsheet.getColumnCount() > 0) { if (m_NumRows < 1) lastRow = sheet.getLastRowNum(); else lastRow = Math.min(firstRow + m_NumRows - 1, sheet.getLastRowNum()); for (i = dataRowStart; i <= lastRow; i++) { if (m_Stopped) break; if (isLoggingEnabled()) getLogger().info("data row: " + (i + 1)); spRow = spsheet.addRow("" + spsheet.getRowCount()); exRow = sheet.getRow(i); if (exRow == null) continue; for (n = 0; n < exRow.getLastCellNum(); n++) { // too few columns in header? if ((n >= spsheet.getHeaderRow().getCellCount()) && m_AutoExtendHeader) spsheet.insertColumn(spsheet.getColumnCount(), ""); m_TextColumns.setMax(spsheet.getHeaderRow().getCellCount()); exCell = exRow.getCell(n); if (exCell == null) { spRow.addCell(n).setMissing(); continue; } cellType = exCell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) cellType = exCell.getCachedFormulaResultType(); numeric = !m_TextColumns.isInRange(n); switch (cellType) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: if (m_MissingValue.isEmpty()) spRow.addCell(n).setMissing(); else spRow.addCell(n).setContent(""); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(exCell)) spRow.addCell(n).setContent( dformat.format(HSSFDateUtil.getJavaDate(exCell.getNumericCellValue()))); else if (numeric) spRow.addCell(n).setContent(exCell.getNumericCellValue()); else spRow.addCell(n).setContentAsString(numericToString(exCell)); break; default: if (m_MissingValue.isMatch(exCell.getStringCellValue())) spRow.addCell(n).setMissing(); else spRow.addCell(n).setContentAsString(exCell.getStringCellValue()); } } } } } } catch (Exception ioe) { getLogger().log(Level.SEVERE, "Failed to read range '" + m_SheetRange + "':", ioe); result = null; m_LastError = "Failed to read range '" + m_SheetRange + "' from stream!\n" + Utils.throwableToString(ioe); } return result; }
From source file:addCE.ExcelReader.java
public void read() { try {/*from w ww . j a v a2 s .com*/ FileInputStream excelFile = new FileInputStream(new File(excelFileName)); Workbook workbook = new XSSFWorkbook(excelFile); Sheet datatypeSheet = workbook.getSheetAt(0); Iterator<Row> iterator = datatypeSheet.iterator(); iterator.next(); iterator.next(); iterator.next(); while (iterator.hasNext()) { Row currentRow = iterator.next(); ArrayList<String> data = new ArrayList<String>(); Iterator<Cell> cellIterator = currentRow.iterator(); while (cellIterator.hasNext()) { Cell currentCell = cellIterator.next(); //getCellTypeEnum shown as deprecated for version 3.15 //getCellTypeEnum will be renamed to getCellType starting from version 4.0 if (currentCell == null || currentCell.getCellTypeEnum() == CellType.BLANK) { data.add("None"); } else if (currentCell.getCellTypeEnum() == CellType.STRING) { data.add(currentCell.getStringCellValue()); //System.out.print(currentCell.getStringCellValue() + "--"); } else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) { data.add(Double.toString(currentCell.getNumericCellValue())); //System.out.print(currentCell.getNumericCellValue() + "--"); } } this.attendees.add(data); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:ambit2.core.io.IteratingXLSReader.java
License:Open Source License
public Object next() { IAtomContainer mol = null;//from ww w. ja va2 s . c om Map properties = new Hashtable(); try { Row row = (Row) iterator.next(); for (int col = 0; col < getNumberOfColumns(); col++) { Cell cell = row.getCell(col); Object value = null; if (cell != null) switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: value = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BLANK: value = ""; break; case Cell.CELL_TYPE_ERROR: value = ""; break; case Cell.CELL_TYPE_FORMULA: try { value = cell.getStringCellValue(); break; } catch (Exception x) { try { value = cell.getNumericCellValue(); } catch (Exception z) { logger.log(Level.WARNING, x.getMessage(), x); } } } else value = ""; try { if (smilesIndex == col) { try { mol = sp.parseSmiles(value.toString()); properties.put(AmbitCONSTANTS.SMILES, value.toString()); } catch (InvalidSmilesException x) { logger.warning("Invalid SMILES!\t" + value); properties.put(AmbitCONSTANTS.SMILES, "Invalid SMILES"); } } else if (col < getNumberOfColumns()) properties.put(getHeaderColumn(col), value); } catch (Exception x) { logger.log(Level.WARNING, x.getMessage(), x); } } if (mol == null) mol = SilentChemObjectBuilder.getInstance().newInstance(IMolecule.class); mol.setProperties(properties); processRow(mol); } catch (Exception x) { logger.log(Level.SEVERE, x.getMessage(), x); } return mol; }
From source file:android_connector.ExcelReader.java
/** * Gibt eine vernnftige Darstellung einer Zelle als String zurck. * @param cell die Zelle/*from w ww . ja v a2 s.c o m*/ * @return z.B. bei Zelle, die eine Gleichung enthlt, deren Ergebnis */ private String differCellType(Cell cell) { String returnValue = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: returnValue = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: returnValue = String.valueOf(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: returnValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: FormulaEvaluator evaluator = this.wb.getCreationHelper().createFormulaEvaluator(); CellValue cellValue = evaluator.evaluate(cell); returnValue = cellValue.getStringValue(); break; case Cell.CELL_TYPE_ERROR: returnValue = String.valueOf(cell.getErrorCellValue()); break; case Cell.CELL_TYPE_BLANK: returnValue = ""; break; default: returnValue = "default value at (" + cell.getRowIndex() + ";" + cell.getColumnIndex() + ") !"; break; } return returnValue; }
From source file:apm.common.utils.excel.ImportExcel.java
License:Open Source License
/** * ??//from w ww .j a va 2 s.c o m * @param row ? * @param column ??? * @return ? */ public Object getCellValue(Row row, int column) { Object val = ""; try { Cell cell = row.getCell(column); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { val = cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { val = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { val = cell.getCellFormula(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { val = cell.getBooleanCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { val = cell.getErrorCellValue(); } } } catch (Exception e) { return val; } return val; }
From source file:at.htlpinkafeld.beans.BenutzerkontoBean.java
/** * Method used to load stuff from predefined Excel not currently in use *//*from w w w .ja va2 s.com*/ public void loadFromExcel(ActionEvent event) throws FileNotFoundException, IOException, ParserException { if (excel != null) { FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Successful", excel.getFileName() + " successfully uploaded!")); XSSFWorkbook workbook = new XSSFWorkbook(excel.getInputstream()); for (int i = 1; i <= 12; i++) { int min = 5; LocalDate date = LocalDate.of(2016, i, 1); int max = min + date.lengthOfMonth() - 1; XSSFSheet sheet = workbook.getSheetAt(i); for (int j = min; j <= max; j++) { Row row = sheet.getRow(j); LocalDateTime start = null; LocalDateTime end = null; LocalDate day = date.withDayOfMonth((int) row.getCell(1).getNumericCellValue()); // DataFormatter formatter = new DataFormatter(); // System.out.println(formatter.formatCellValue(row.getCell(2))); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); Cell soll = row.getCell(5); Cell ist = row.getCell(6); CellValue sollValue = null; CellValue istValue = null; if (soll != null && ist != null) { sollValue = evaluator.evaluate(soll); istValue = evaluator.evaluate(ist); } if (sollValue != null && istValue != null) { double dif = sollValue.getNumberValue() - istValue.getNumberValue(); if (istValue.getNumberValue() != 0.0) { Cell urlaub = row.getCell(10); if (urlaub != null && urlaub.getCellType() != Cell.CELL_TYPE_BLANK && urlaub.getNumericCellValue() != 1.0) { Cell cell = row.getCell(2); //for endtime = row 2 if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double time = cellValue.getNumberValue() * 24; String time2; DecimalFormat df = new DecimalFormat("00.00"); time2 = df.format(time); time2 = time2.replace(',', ':'); LocalTime localtime = LocalTime.parse(time2); start = LocalDateTime.of(day, localtime); } } cell = row.getCell(3); //for endtime = row 3 if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double time = cellValue.getNumberValue() * 24; String time2; DecimalFormat df = new DecimalFormat("00.00"); time2 = df.format(time); time2 = time2.replace(',', ':'); LocalTime localtime = LocalTime.parse(time2); end = LocalDateTime.of(day, localtime); } } int breaktime = 0; cell = row.getCell(4); if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double tempbreaktime = cellValue.getNumberValue() * 24 * 60; breaktime = (int) tempbreaktime; } } String bemerkung = ""; Cell comment = row.getCell(11); if (comment != null) { CellValue value = evaluator.evaluate(comment); if (value != null) { bemerkung = value.formatAsString(); double d; try { d = Double.valueOf(bemerkung); if (BigDecimal.valueOf(d).scale() > 2) { d = d * 24 * 60; LocalTime lt = LocalTime.MIN.plusMinutes((int) (d + 0.5)); bemerkung = lt.format(DateTimeFormatter.ofPattern("HH:mm")); } } catch (NumberFormatException e) { //Value is not castable to double and will be ignored -> best case scenario } } } if (start != null && end != null) { WorkTime worktime = new WorkTime(user, start, end, breaktime, bemerkung, ""); IstZeitService.addIstTime(worktime); if (dif > 0.0) { LocalDateTime absenceend = end.plusMinutes((int) ((dif * 24 * 60) + 0.5)); Absence a = new Absence(user, AbsenceTypeNew.TIME_COMPENSATION, end, absenceend, bemerkung); a.setAcknowledged(true); AbsenceService.insertAbsence(a); } } } else if (urlaub != null && urlaub.getCellType() != Cell.CELL_TYPE_BLANK && urlaub.getNumericCellValue() == 1.0) { start = LocalDateTime.of(day, LocalTime.MIN); end = start; Absence a = new Absence(user, AbsenceTypeNew.HOLIDAY, start, end); a.setAcknowledged(true); AbsenceService.insertAbsence(a); } } else { Cell cell = row.getCell(2); //for endtime = row 2 if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double time = cellValue.getNumberValue() * 24; String time2; DecimalFormat df = new DecimalFormat("00.00"); time2 = df.format(time); time2 = time2.replace(',', ':'); LocalTime localtime = LocalTime.parse(time2); start = LocalDateTime.of(day, localtime); } } cell = row.getCell(3); //for endtime = row 3 if (cell != null) { CellValue cellValue = evaluator.evaluate(cell); if (cellValue != null) { double time = cellValue.getNumberValue() * 24; String time2; DecimalFormat df = new DecimalFormat("00.00"); time2 = df.format(time); time2 = time2.replace(',', ':'); LocalTime localtime = LocalTime.parse(time2); end = LocalDateTime.of(day, localtime); } } String bemerkung = ""; Cell comment = row.getCell(11); if (comment != null) { CellValue value = evaluator.evaluate(comment); if (value != null) { bemerkung = value.formatAsString(); } } Absence a = new Absence(user, AbsenceTypeNew.TIME_COMPENSATION, start, end, bemerkung); a.setAcknowledged(true); AbsenceService.insertAbsence(a); } } } } } }
From source file:at.mukprojects.exclycore.dao.XLSXReader.java
License:Open Source License
private ExclyString readString(Cell cell, int type) throws Exception { ExclyString output = null;/* w ww .ja v a 2 s . co m*/ if (type == Cell.CELL_TYPE_STRING) { output = new ExclyString(cell.getStringCellValue()); } else if (type == Cell.CELL_TYPE_ERROR) { output = new ExclyStringError(); } else if (type == Cell.CELL_TYPE_FORMULA) { int formulaType = cell.getCachedFormulaResultType(); output = readString(cell, formulaType); } else if (type == Cell.CELL_TYPE_BLANK) { output = new ExclyString(""); } else if (type == Cell.CELL_TYPE_BOOLEAN) { Boolean data = cell.getBooleanCellValue(); if (data) { output = new ExclyString("WAHR"); } else { output = new ExclyString("FALSCH"); } } else if (DateUtil.isCellDateFormatted(cell)) { Date data = cell.getDateCellValue(); output = new ExclyString(data.toString()); } else if (type == Cell.CELL_TYPE_NUMERIC) { double cellValue = cell.getNumericCellValue(); String data = String.valueOf(cellValue); if (cellValue % 1 == 0 && data.endsWith(".0")) { data = data.substring(0, data.length() - 2); } output = new ExclyString(data); } else { log.warn("The reader was unable to find a valid parser for the cell [Row, Column] (" + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")"); output = new ExclyStringError(); } return output; }
From source file:at.mukprojects.exclycore.dao.XLSXReader.java
License:Open Source License
private ExclyDouble readDouble(Cell cell, int type) throws Exception { ExclyDouble output = null;/*from www . jav a 2 s .c o m*/ if (type == Cell.CELL_TYPE_STRING) { String data = cell.getStringCellValue(); if (isNumericGerman(data)) { Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data); output = new ExclyDouble(number.doubleValue()); } else if (isNumericUK(data)) { Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data); output = new ExclyDouble(number.doubleValue()); } else if (data.equals("") || data.equals(" ") || data.equals("-")) { output = new ExclyDoubleBlank(); } else { output = new ExclyDoubleError(); log.warn("The reader has expected a numeric value, but found a string value. [Row, Column] (" + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")"); } } else if (type == Cell.CELL_TYPE_BLANK) { output = new ExclyDoubleBlank(); } else if (type == Cell.CELL_TYPE_FORMULA) { int formulaType = cell.getCachedFormulaResultType(); output = readDouble(cell, formulaType); } else if (type == Cell.CELL_TYPE_BOOLEAN) { Boolean data = cell.getBooleanCellValue(); if (data) { output = new ExclyDouble(1); } else { output = new ExclyDouble(0); } } else if (type == Cell.CELL_TYPE_NUMERIC) { double data = cell.getNumericCellValue(); output = new ExclyDouble(data); } else if (type == Cell.CELL_TYPE_ERROR) { output = new ExclyDoubleError(); } else { log.warn("The reader was unable to find a valid parser for the cell [Row, Column] (" + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")"); output = new ExclyDoubleError(); } return output; }
From source file:at.mukprojects.exclycore.dao.XLSXReader.java
License:Open Source License
private ExclyInteger readInteger(Cell cell, int type) throws Exception { ExclyInteger output = null;//from www . j av a 2 s .c o m if (type == Cell.CELL_TYPE_STRING) { String data = cell.getStringCellValue(); if (isNumericGerman(data)) { Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data); output = new ExclyInteger(number.intValue()); } else if (isNumericUK(data)) { Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data); output = new ExclyInteger(number.intValue()); } else if (data.equals("") || data.equals(" ") || data.trim().equals("-")) { output = new ExclyIntegerBlank(); } else { output = new ExclyIntegerError(); log.warn("The reader has expected a numeric value, but found a string value. [Row, Column] (" + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")"); } } else if (type == Cell.CELL_TYPE_BLANK) { output = new ExclyIntegerBlank(); } else if (type == Cell.CELL_TYPE_FORMULA) { int formulaType = cell.getCachedFormulaResultType(); output = readInteger(cell, formulaType); } else if (type == Cell.CELL_TYPE_BOOLEAN) { Boolean data = cell.getBooleanCellValue(); if (data) { output = new ExclyInteger(1); } else { output = new ExclyInteger(0); } } else if (type == Cell.CELL_TYPE_NUMERIC) { double data = cell.getNumericCellValue(); output = new ExclyInteger(data); } else if (type == Cell.CELL_TYPE_ERROR) { output = new ExclyIntegerError(); } else { log.warn("The reader was unable to find a valid parser for the cell [Row, Column] (" + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")"); output = new ExclyIntegerError(); } return output; }