List of usage examples for org.apache.poi.ss.usermodel CellValue formatAsString
public String formatAsString()
From source file:at.htlpinkafeld.beans.BenutzerkontoBean.java
/** * Method used to load stuff from predefined Excel not currently in use *//* w w w . j a v a 2 s . co m*/ 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:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
public Object getCellContent(Object cell) { if (cell != null) { HSSFCell cel = (HSSFCell) cell;// w ww . j a va 2s . c o m HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(this.book); CellValue cellValue = evaluator.evaluate(cel); switch (cel.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cel)) { return UtilDate.parseTime(cel.getDateCellValue(), "yyyy-MM-dd HH:mm:ss"); } else { return Double.valueOf(cellValue.getNumberValue()); } case HSSFCell.CELL_TYPE_STRING: return cellValue.getStringValue(); case HSSFCell.CELL_TYPE_BOOLEAN: return Boolean.valueOf(cellValue.getBooleanValue()); case HSSFCell.CELL_TYPE_ERROR: return Byte.valueOf(cellValue.getErrorValue()); case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_FORMULA: return cellValue.formatAsString(); default: return null; } } return null; }
From source file:com.github.crab2died.utils.Utils.java
License:Open Source License
/** * ?/* www . ja v a2 s . c o m*/ * * @param cell ??? * @return ?? ??String * @author QingMings * Email:1821063757@qq.com * date 2018-01-13 */ public static String calculationFormula(Cell cell) { CellValue cellValue = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator() .evaluate(cell); return cellValue.formatAsString(); }
From source file:com.jkoolcloud.tnt4j.streams.parsers.AbstractExcelParser.java
License:Apache License
/** * Evaluates and returns cell contained value. * * @param cell//from ww w.j ava2s.co m * cell instance to evaluate value * @return evaluated cell value */ protected Object getCellValue(Cell cell) { CellValue cellValue; synchronized (EVALUATOR_LOCK) { if (evaluator == null) { Workbook workbook = cell.getSheet().getWorkbook(); evaluator = workbook.getCreationHelper().createFormulaEvaluator(); } cellValue = evaluator.evaluate(cell); } if (cellValue == null) { return cell.toString(); } switch (cellValue.getCellTypeEnum()) { case BOOLEAN: return cellValue.getBooleanValue(); case NUMERIC: return cellValue.getNumberValue(); case STRING: return cellValue.getStringValue(); default: return cellValue.formatAsString(); } }
From source file:org.talend.dataprep.schema.xls.XlsUtils.java
License:Open Source License
/** * Return the numeric value.//from ww w.j a va 2 s .com * * @param cell the cell to extract the value from. * @return the numeric value from the cell. */ private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) { // Date is typed as numeric if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable?? DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH); return sdf.format(cell.getDateCellValue()); } // Numeric type (use data formatter to get number format right) DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH); if (cellValue == null) { return formatter.formatCellValue(cell); } return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell); }
From source file:org.testeditor.core.importer.ExcelFileImporter.java
License:Open Source License
/** * Iterates through the cells in a row an creates a {@link TestDataRow} * Object./*from w w w. ja v a2 s .co m*/ * * @param row * row in excel sheet * @return TestDataRow */ @SuppressWarnings("rawtypes") private TestDataRow getTestDataRow(HSSFRow row) { int id = 0; Iterator cells = row.cellIterator(); TestDataRow testDataRow = new TestDataRow(); while (cells.hasNext()) { HSSFCell cell = (HSSFCell) cells.next(); for (int i = id; i < cell.getColumnIndex(); i++) { testDataRow.add(""); } id = cell.getColumnIndex() + 1; if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { getTestDataNumericCell(testDataRow, cell); } else if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) { testDataRow.add(cell.getStringCellValue()); } else if (HSSFCell.CELL_TYPE_BOOLEAN == cell.getCellType()) { testDataRow.add(String.valueOf(cell.getBooleanCellValue())); } else if (HSSFCell.CELL_TYPE_FORMULA == cell.getCellType()) { HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(row.getSheet().getWorkbook()); CellValue cv = fe.evaluate(cell); testDataRow.add(cv.formatAsString()); } else if (HSSFCell.CELL_TYPE_BLANK == cell.getCellType()) { testDataRow.add(""); } else { if (LOGGER.isInfoEnabled()) { LOGGER.info("getTestData :: Unknown cell type"); } } } return testDataRow; }