List of usage examples for org.apache.poi.ss.usermodel FormulaEvaluator evaluateInCell
Cell evaluateInCell(Cell cell);
From source file:org.eclipse.lyo.samples.excel.adapter.dao.internal.ExcelDaoImpl.java
License:Open Source License
private String getCellValue(Cell cell) { if (cell != null) { String value = null;/*from w w w.j a v a 2 s . com*/ int type = cell.getCellType(); if (type == Cell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } else if (type == Cell.CELL_TYPE_NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); value = FastDateFormat.getInstance(DEFAULT_OUTPUT_DATE_FORMAT).format(date); } else { double d = cell.getNumericCellValue(); if (d == Math.floor(d)) { // need to consider when d is negative value = "" + (int) d; } else { value = "" + cell.getNumericCellValue(); } } } else if (type == Cell.CELL_TYPE_FORMULA) { // get calculated value if the cell type is formula Workbook wb = cell.getSheet().getWorkbook(); CreationHelper crateHelper = wb.getCreationHelper(); FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator(); // get recursively if the value is still formula value = getCellValue(evaluator.evaluateInCell(cell)); } return value; } return null; }
From source file:servlets.UploadAccountingServlet.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { System.out.println("TEST"); InputStream inputStream = null; Part filePart = request.getPart("file"); inputStream = filePart.getInputStream(); POIFSFileSystem fs = new POIFSFileSystem(inputStream); // FileInputStream fichier = new FileInputStream(new File("countries.xls")); //create workbook instance that refers to xlsx file HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0);//w ww . j a v a 2 s . c o m FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); ArrayList<Accounting> arrAccounting = new ArrayList<>(); ReportDB reportDB = new ReportDB(); reportDB.addReport(new Report("Accounting", "Pending")); ArrayList<Report> reportList = new ArrayList(); reportList = reportDB.getAllReports(); int ReportRef = reportList.size(); for (Row ligne : sheet) {//iterate rows Accounting accounting = new Accounting(); for (Cell cell : ligne) {//iterate columns //cell type /* Sales sales; (lagay sa sys out) sales.setSalesID(cell.getNumericCellValue()); --- WALA TO */ switch (formulaEvaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: // sales.setName(cell.getNumericCellValue()); System.out.print("cell 1 " + cell.getNumericCellValue() + " \t"); if (cell.getColumnIndex() == 1) { accounting.setPoNo((int) cell.getNumericCellValue()); } if (cell.getColumnIndex() == 4) { accounting.setAgingDays((int) cell.getNumericCellValue()); } if (cell.getColumnIndex() == 5) { accounting.setCustomerID((int) cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: System.out.print("cell 2 " + cell.getStringCellValue() + " \t"); if (cell.getColumnIndex() == 0) { accounting.setStartDate(cell.getStringCellValue()); } if (cell.getColumnIndex() == 2) { accounting.setTerms(cell.getStringCellValue()); } if (cell.getColumnIndex() == 3) { accounting.setDueDate(cell.getStringCellValue()); } break; } } accounting.setReportRef(ReportRef); arrAccounting.add(accounting); System.out.println(); } request.setAttribute("arrAccounting", arrAccounting); AccountingDB accountingDB = new AccountingDB(); request.setAttribute("arrAccounting", arrAccounting); for (int j = 0; j < arrAccounting.size(); j++) { accountingDB.addAccounting(arrAccounting.get(j)); } ServletContext context = getServletContext(); RequestDispatcher rd = context.getRequestDispatcher("/isrAfteruploadAccounting.jsp"); rd.forward(request, response); processRequest(request, response); }
From source file:servlets.UploadInventoryServlet.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { System.out.println("TEST"); InputStream inputStream = null; Part filePart = request.getPart("file"); inputStream = filePart.getInputStream(); POIFSFileSystem fs = new POIFSFileSystem(inputStream); // FileInputStream fichier = new FileInputStream(new File("countries.xls")); //create workbook instance that refers to xlsx file HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0);//from w w w. ja va 2 s . c o m FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); ArrayList<Inventory> arrInventory = new ArrayList<>(); ReportDB reportDB = new ReportDB(); reportDB.addReport(new Report("Inventory", "Pending")); ArrayList<Report> reportList = new ArrayList(); reportList = reportDB.getAllReports(); int ReportRef = reportList.size(); for (Row ligne : sheet) {//iterate rows Inventory inventory = new Inventory(); for (Cell cell : ligne) {//iterate columns //cell type /* Sales sales; (lagay sa sys out) sales.setSalesID(cell.getNumericCellValue()); --- WALA TO */ switch (formulaEvaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: // sales.setName(cell.getNumericCellValue()); System.out.print("cell 1 " + cell.getNumericCellValue() + " \t"); if (cell.getColumnIndex() == 1) { inventory.setQuantityOnHand((int) cell.getNumericCellValue()); } if (cell.getColumnIndex() == 2) { inventory.setGrandTotal((int) cell.getNumericCellValue()); } if (cell.getColumnIndex() == 3) { inventory.setBatchNo((int) cell.getNumericCellValue()); } if (cell.getColumnIndex() == 6) { inventory.setAvemonTO(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: System.out.print("cell 2 " + cell.getStringCellValue() + " \t"); if (cell.getColumnIndex() == 0) { inventory.setBrandName(cell.getStringCellValue()); } if (cell.getColumnIndex() == 4) { inventory.setExpDate(cell.getStringCellValue()); } if (cell.getColumnIndex() == 5) { inventory.setShelfLife(cell.getStringCellValue()); } if (cell.getColumnIndex() == 7) { inventory.setInventoryMonths(cell.getStringCellValue()); } break; } } inventory.setReportRef(ReportRef); arrInventory.add(inventory); System.out.println(); } request.setAttribute("arrInventory", arrInventory); InventoryDB inventoryDB = new InventoryDB(); request.setAttribute("arrInventory", arrInventory); for (int j = 0; j < arrInventory.size(); j++) { inventoryDB.addInventory(arrInventory.get(j)); } ServletContext context = getServletContext(); RequestDispatcher rd = context.getRequestDispatcher("/isrAfteruploadInventory.jsp"); rd.forward(request, response); processRequest(request, response); }
From source file:servlets.UploadSalesServlet.java
/** * Handles the HTTP <code>POST</code> method. * * @param request servlet request/*from ww w .j a v a2s . c o m*/ * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { System.out.println("TEST"); InputStream inputStream = null; Part filePart = request.getPart("file"); inputStream = filePart.getInputStream(); POIFSFileSystem fs = new POIFSFileSystem(inputStream); // FileInputStream fichier = new FileInputStream(new File("countries.xls")); //create workbook instance that refers to xlsx file HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator(); ArrayList<Sales> arrSales = new ArrayList<>(); ReportDB reportDB = new ReportDB(); reportDB.addReport(new Report("Sales", "Pending")); ArrayList<Report> reportList = new ArrayList(); reportList = reportDB.getAllReports(); int ReportRef = reportList.size(); for (Row ligne : sheet) {//iterate rows Sales sales = new Sales(); for (Cell cell : ligne) {//iterate columns //cell type switch (formulaEvaluator.evaluateInCell(cell).getCellType()) { case Cell.CELL_TYPE_NUMERIC: // sales.setName(cell.getNumericCellValue()); System.out.print("cell 1 " + cell.getNumericCellValue() + " \t"); if (cell.getColumnIndex() == 0) { sales.setSalesAmmount(cell.getNumericCellValue()); } if (cell.getColumnIndex() == 1) { sales.setCreatedBy((int) cell.getNumericCellValue()); } if (cell.getColumnIndex() == 2) { sales.setLocation((int) cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: System.out.print("cell 2 " + cell.getStringCellValue() + " \t"); break; } } sales.setReportRef(ReportRef); arrSales.add(sales); System.out.println(); } System.out.println("\t"); System.out.println("Size: " + arrSales.size()); System.out.println("\t"); for (int i = 0; i < arrSales.size(); i++) { System.out.println("Amount: " + arrSales.get(i).getSalesAmmount()); System.out.println("Created By: " + arrSales.get(i).getCreatedBy()); System.out.println("Location: " + arrSales.get(i).getLocation()); System.out.println("ReportRef: " + arrSales.get(i).getReportRef()); System.out.println("\t"); } request.setAttribute("arrSales", arrSales); SalesDB salesdb = new SalesDB(); request.setAttribute("arrSales", arrSales); for (int j = 0; j < arrSales.size(); j++) { salesdb.addSales(arrSales.get(j)); } ServletContext context = getServletContext(); RequestDispatcher rd = context.getRequestDispatcher("/isrAfteruploadSales.jsp"); rd.forward(request, response); processRequest(request, response); }