List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluate
CellValue evaluate(Cell cell);
From source file:ExcelConverter.java
public List<ScheduleClass> Converter() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0);/*from w ww .jav a 2 s . co m*/ Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add; int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(j); if (cell.getStringCellValue().contains("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); break outerloop; } } } outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } lt = LocalTime.parse(arrJam[0]); } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 5)) { subject = cell.getStringCellValue(); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colNoIdx + 6 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } } } } } for (int j = 0; j < dosen.size(); j++) { scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return Mergering(scheduleList); }
From source file:ExampleClass.java
public static void main(String[] args) throws Exception { File src = new File( "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx"); //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx"); FileInputStream fis = new FileInputStream(src); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet1 = wb.getSheetAt(0); // Iterator< Row> rowIterator = sheet1.iterator(); int colIndex = 0; int ex = 0;//from ww w . j a va 2 s . c o m int lastCol = sheet1.getLastRowNum(); int i = 0; int idx = 0; CellRangeAddress add; // while (rowIterator.hasNext()) { // row = (XSSFRow) rowIterator.next(); // Iterator< Cell> cellIterator = row.cellIterator(); // //System.out.println("i = "+i+", ex:"+ex); // // if (row.getRowNum() > 53) { // break; // } //// if(lastCol-(ex+1) == i) break; // while (cellIterator.hasNext()) { // Cell cell = cellIterator.next(); // for (int f = 0; f < sheet1.getNumMergedRegions(); f++) { // add = sheet1.getMergedRegion(f); // // int col = add.getFirstColumn(); // int rowNum = add.getFirstRow(); // if (rowNum != 0 && rowNum == cell.getRowIndex() && colIndex == cell.getColumnIndex()) { // System.out.println("col:"+col+" "+",row :"+rowNum); // String b = String.valueOf(sheet1.getRow(rowNum).getCell(col)); // System.out.println(b); // // } // // } // switch (cell.getCellType()) // { // case Cell.CELL_TYPE_FORMULA: // ex++; // switch (cell.getCachedFormulaResultType()) // { // case Cell.CELL_TYPE_NUMERIC: // i = (int)cell.getNumericCellValue(); // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // // // break; // } // break; // case Cell.CELL_TYPE_NUMERIC: // if (cell.getColumnIndex() >= 6) // { // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // } // break; // case Cell.CELL_TYPE_STRING: // add = sheet1.getMergedRegion(cell.getRowIndex()); // // if (cell.getStringCellValue().contentEquals("No.")) // { // colIndex = cell.getColumnIndex(); // } // if (cell.getColumnIndex() == 1) // { // System.out.print( // cell.getStringCellValue() + " \t\t " ); // } // break; // // } // } // // System.out.println(); // } for (int j = 0; j < sheet1.getLastRowNum(); j++) { row = sheet1.getRow(j); for (int k = 0; k < row.getLastCellNum(); k++) { Cell cell = row.getCell(k); // if (cell.getColumnIndex() == 1) // { // System.out.println(cell.getStringCellValue()); // } FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == 0 && j > 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { System.exit(k); } if (cell.getColumnIndex() >= 6 && cell.getColumnIndex() <= 11) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.print((int) cell.getNumericCellValue() + " "); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); System.out.print( cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet1.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { System.out.print( c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } } } System.out.println(""); } System.out.println(colIndex); System.out.println(idx); fis.close(); }
From source file:android_connector.ExcelReader.java
/** * Gibt eine vernnftige Darstellung einer Zelle als String zurck. * @param cell die Zelle/*from w w w . j av a 2 s .c om*/ * @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:at.htlpinkafeld.beans.BenutzerkontoBean.java
/** * Method used to load stuff from predefined Excel not currently in use *//* ww w.j a v a 2s.c o 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:au.com.onegeek.lambda.parser.XslxUtil.java
License:Apache License
public static Object evaluateCellFormula(final Workbook workbook, final Cell cell) { FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); CellValue cellValue = evaluator.evaluate(cell); Object result = null;/* w ww. j ava 2 s.co m*/ if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) { result = cellValue.getBooleanValue(); } else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) { result = cellValue.getNumberValue(); } else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) { result = cellValue.getStringValue(); } return result; }
From source file:br.com.tecsinapse.dataio.importer.ImporterUtils.java
License:LGPL
private static CellValue safeEvaluteFormula(FormulaEvaluator evaluator, Cell cell) { try {/*from w ww . j a va 2s. co m*/ return evaluator.evaluate(cell); } catch (Exception e) { log.warn("Formula evalute error (ignored)", e); } return null; }
From source file:br.com.tecsinapse.exporter.importer.ImporterUtils.java
License:LGPL
public static Object getValueOrEmptyAsObject(FormulaEvaluator evaluator, Cell cell) { final CellValue cellValue = evaluator.evaluate(cell); if (cellValue == null) { return ""; }/*from w w w. j av a 2 s . c om*/ switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return Boolean.valueOf(cellValue.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); return date; } BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(DECIMAL_PRECISION, BigDecimal.ROUND_HALF_UP); return bd.stripTrailingZeros(); case Cell.CELL_TYPE_STRING: return cellValue.getStringValue(); case Cell.CELL_TYPE_ERROR: return "ERRO"; default: return ""; } }
From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java
License:Apache License
private static JsonElement parseAsJsonElement(Cell cell, FormulaEvaluator evaluator) { switch (cell.getCellType()) { case NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { return new JsonPrimitive(DateFormatterHolder.FORMATTER.format(cell.getDateCellValue().toInstant())); } else {/*from w w w. j a va2s . c o m*/ return new JsonPrimitive(cell.getNumericCellValue()); } case STRING: return new JsonPrimitive(cell.getStringCellValue()); case FORMULA: CellValue cellValue = evaluator.evaluate(cell); switch (cellValue.getCellType()) { case NUMERIC: return new JsonPrimitive(cellValue.getNumberValue()); case STRING: return new JsonPrimitive(cellValue.getStringValue()); case BLANK: return new JsonPrimitive(""); case BOOLEAN: return new JsonPrimitive(cellValue.getBooleanValue()); case ERROR: default: return null; } case BLANK: return new JsonPrimitive(""); case BOOLEAN: return new JsonPrimitive(cell.getBooleanCellValue()); case ERROR: default: return null; } }
From source file:cn.study.innerclass.PoiUtil.java
License:Open Source License
public static Object getCellData(Cell cell, FormulaEvaluator formula) { if (cell == null) { return null; }/*from www .j av a2 s . c om*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println(cell.getRichStringCellValue().getString()); return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); return cell.getDateCellValue(); } else { System.out.println(cell.getNumericCellValue()); return cell.getNumericCellValue(); } case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); return cell.getBooleanCellValue(); case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getStringCellValue()); switch (formula.evaluate(cell).getCellType()) { case Cell.CELL_TYPE_STRING: System.out.println(formula.evaluate(cell).getStringValue()); return formula.evaluate(cell).getStringValue(); case Cell.CELL_TYPE_NUMERIC: System.out.println(formula.evaluate(cell).getNumberValue()); return formula.evaluate(cell).getNumberValue(); case Cell.CELL_TYPE_BOOLEAN: System.out.println(formula.evaluate(cell).getBooleanValue()); return formula.evaluate(cell); } default: return null; } }
From source file:com.b2international.snowowl.datastore.server.importer.ExcelUtilities.java
License:Apache License
private static String extractAsString(final Cell cell, final boolean formatNumber) { String value = ""; if (cell == null) { return value; }/*ww w .ja v a 2s . com*/ FormulaEvaluator formulaEvaluator = cell.getSheet().getWorkbook().getCreationHelper() .createFormulaEvaluator(); int type = cell.getCellType(); switch (type) { case Cell.CELL_TYPE_NUMERIC: if (formatNumber) { value = convertToString(cell.getNumericCellValue()); } else { value = convertToStringWithoutFormat(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: CellValue cellValue = formulaEvaluator.evaluate(cell); value = cellValue.getStringValue(); //type should be checked break; case Cell.CELL_TYPE_BOOLEAN: value = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_BLANK: //do nothing, sctId is null break; default: LOGGER.log(Level.SEVERE, "Unsupported cell type:" + type + " for cell: " + cell); break; } return null == value ? "" : value; }