List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook setForceFormulaRecalculation
@Override public void setForceFormulaRecalculation(boolean value)
From source file:opn.greenwebs.FXMLDocumentController.java
@FXML private void handleInjectExcel(ActionEvent event) { XSSFWorkbook wb = createWorkbook(); FormulaEvaluator evaluator;/*from w ww . j ava 2 s . c o m*/ inject(wb, cmbSales.getValue(), 5, 14); Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault())); inject(wb, Date.from(instant), 3, 14); inject(wb, txtCustomer.getText(), 10, 2); inject(wb, txtAddress.getText(), 11, 2); inject(wb, txtCity.getText(), 12, 2); inject(wb, txtProvince.getText(), 13, 2); inject(wb, txtPhone.getText(), 14, 2); inject(wb, txtContact.getText(), 15, 2); inject(wb, txtFax.getText(), 14, 4); inject(wb, txtEmail.getText(), 16, 2); inject(wb, txtPO.getText(), 15, 4); if (chkGST.isSelected()) { inject(wb, "Y", 36, 8); } else { inject(wb, "N", 36, 8); } if (chkPST.isSelected()) { inject(wb, "Y", 37, 8); } else { inject(wb, "N", 37, 8); } int nRow = 21; for (Object obj : tblItems.getItems()) { if (obj instanceof Item) { Item item = (Item) obj; inject(wb, item.getSdpQty(), nRow, 0); inject(wb, item.getSspMfr(), nRow, 1); inject(wb, item.getSspSKU(), nRow, 2); inject(wb, item.getSspDescrip(), nRow, 3); inject(wb, item.getSspSupplier(), nRow, 5); inject(wb, item.getSspSupPart(), nRow, 6); inject(wb, item.getSspSerial(), nRow, 7); inject(wb, item.getSdpSalePrice(), nRow, 8); inject(wb, item.getSdpEOS(), nRow++, 9); } } evaluator = wb.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll(); wb.setForceFormulaRecalculation(true); try { File fTemp = createFilename("Q", txtCustomer.getText()); inject(wb, fTemp.getName().substring(0, 7), 46, 14); try (FileOutputStream fos = new FileOutputStream(fTemp)) { wb.write(fos); } Alert alert = new Alert(AlertType.INFORMATION, "Would you like to open the file? Choose no to print", ButtonType.YES, ButtonType.NO); alert.showAndWait().ifPresent(response -> { CustomerDB cust = new CustomerDB(); cust.setStrCustomer(txtCustomer.getText()); cust.setStrAddress(txtAddress.getText()); cust.setStrCity(txtCity.getText()); cust.setStrProvince(txtProvince.getText()); cust.setStrPhone(txtPhone.getText()); cust.setStrEmail(txtEmail.getText()); cust.setStrFax(txtFax.getText()); cust.setStrContact(txtContact.getText()); cust.setStrPO(txtPO.getText()); cust.setBooGST(chkGST.isSelected()); cust.setBooPST(chkPST.isSelected()); if ((aDataCust.query("txtCustomer", cust.getStrCustomer()).isEmpty()) && (aDataCust.query("txtPhone", cust.getStrPhone()).isEmpty())) { aDataCust.addItem(cust); CustData.add(cust.createCustomer()); } if (response == ButtonType.YES) { try { Desktop.getDesktop().open(fTemp); } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } else { try { Desktop.getDesktop().print(fTemp); } catch (IOException ex) { Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex); } } }); } catch (FileNotFoundException ex) { logger.info(ex.getLocalizedMessage()); } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } }
From source file:tan.jam.jsf.OrignalFileDownloadBean.java
public void downloadHandler(ActionEvent e) { System.out.println("download click handler"); try {/*from www . j a v a 2 s . c o m*/ InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext() .getContext()).getResourceAsStream("/resources/demo/file/Table.xlsx"); XSSFWorkbook wb = new XSSFWorkbook(OPCPackage.open(stream)); XSSFSheet sheet = wb.getSheetAt(0); wb.setForceFormulaRecalculation(true); /* */ String name; String acc; HashSet OnlineTransferToAccounts = new HashSet(); int RowForOnlineTransferT0 = 105; int RowForRecurringDailyPayment = 105; List<String> LenderDescription = new ArrayList<>(); List<String> LenderDate = new ArrayList<>(); List<Double> LenderAmount = new ArrayList<>(); List<Double> UniqueDailypaymentkeywordAmount = new ArrayList<>(); List<String> UniqueDailypaymentkeywordDate = new ArrayList<>(); List<String> UniqueDailypaymentkeywordDescription = new ArrayList<>(); for (int a = 0; a < companies.size(); a++) { Company c = companies.get(a); name = c.getCompanyName(); acc = c.getAccountNumber(); String month = c.getDate().toLowerCase(); int R = 3; if (month.startsWith(("jan"))) { R = 3; } else if (month.startsWith("feb")) { R = 4; } else if (month.startsWith("mar")) { R = 5; } else if (month.startsWith("apr")) { R = 6; } else if (month.startsWith("may")) { R = 7; } else if (month.startsWith("jun")) { R = 8; } else if (month.startsWith("jul")) { R = 9; } else if (month.startsWith("aug")) { R = 10; } else if (month.startsWith("sep")) { R = 11; } else if (month.startsWith("oct")) { R = 12; } else if (month.startsWith("nov")) { R = 13; } else if (month.startsWith("dec")) { R = 14; } else if (month.startsWith("02") | month.startsWith("2")) { R = 4; } else if (month.startsWith("03") | month.startsWith("3")) { R = 5; } else if (month.startsWith("04") | month.startsWith("4")) { R = 6; } else if (month.startsWith("05") | month.startsWith("5")) { R = 7; } else if (month.startsWith("06") | month.startsWith("6")) { R = 8; } else if (month.startsWith("07") | month.startsWith("7")) { R = 9; } else if (month.startsWith("08") | month.startsWith("8")) { R = 10; } else if (month.startsWith("09") | month.startsWith("9")) { R = 11; } else if (month.startsWith("10") | month.startsWith("10")) { R = 12; } else if (month.toUpperCase().startsWith("11")) { R = 13; } else if (month.toUpperCase().startsWith("12")) { R = 14; } ////%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%//// sheet.getRow(1).getCell(1).setCellValue("Bank Account**" + acc); if (a == 0) { sheet.getRow(1).getCell(7).setCellValue(name + "**" + acc); sheet.getRow(1).getCell(12).setCellValue(name + "**" + acc); sheet.getRow(R).getCell(7) .setCellFormula("sum(" + c.getTotalDeposits() + "-" + c.getSumOfReversalFrom() + "-" + c.getSumOfOnlineTransferFrom() + "-" + c.getKeywordSum() + ")"); // int Mov=Shifting.FindShift(wb, sheet); sheet.getRow(R).getCell(12).setCellValue(c.getAverageLedgerBalance()); sheet.getRow(R).getCell(13).setCellValue(c.getMinValue()); sheet.getRow(R).getCell(14).setCellValue(c.getNoOfNegativeValues()); // int Onl=Shifting.Online(wb, sheet); } else { int Decision = 0; String Name = name + "**" + acc; for (int s = 0; s < Shifting.FindShift(wb, sheet) + 1; s++) { if (sheet.getRow(1).getCell(7 + s).getStringCellValue().equals(Name)) { Decision = 1; sheet.getRow(R).getCell(7 + s) .setCellFormula("sum(" + c.getTotalDeposits() + "-" + c.getSumOfReversalFrom() + "-" + c.getSumOfOnlineTransferFrom() + "-" + c.getKeywordSum() + ")"); } } if (Decision == 0) { int Mov = Shifting.FindShift(wb, sheet); Shifting.InsertColumn(wb, sheet); sheet.getRow(1).getCell(8 + Mov).setCellValue(name + "**" + acc); sheet.getRow(R).getCell(8 + Mov) .setCellFormula("sum(" + c.getTotalDeposits() + "-" + c.getSumOfReversalFrom() + "-" + c.getSumOfOnlineTransferFrom() + "-" + c.getKeywordSum() + ")"); } int selectcolumn = Shifting.FindShift(wb, sheet) + 12; if (sheet.getRow(1).getCell(selectcolumn).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn).getStringCellValue().equals(Name)) { } else if (sheet.getRow(1).getCell(selectcolumn + 6).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 6).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 6).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 6; } else if (sheet.getRow(1).getCell(selectcolumn + 12).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 12).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 12).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 12; } else if (sheet.getRow(1).getCell(selectcolumn + 18).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 18).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 18).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 18; } else if (sheet.getRow(1).getCell(selectcolumn + 24).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 24).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 24).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 24; } else if (sheet.getRow(1).getCell(selectcolumn + 30).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 30).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 30).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 30; } else if (sheet.getRow(1).getCell(selectcolumn + 36).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 36).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 36).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 36; } else if (sheet.getRow(1).getCell(selectcolumn + 42).getCellType() == 3 || sheet.getRow(1).getCell(selectcolumn + 42).getStringCellValue().isEmpty() || sheet.getRow(1).getCell(selectcolumn + 42).getStringCellValue().equals(Name)) { selectcolumn = selectcolumn + 42; } sheet.getRow(1).getCell(selectcolumn).setCellValue(name + "**" + acc); sheet.getRow(R).getCell(selectcolumn).setCellValue(c.getAverageLedgerBalance()); sheet.getRow(R).getCell(selectcolumn + 1).setCellValue(c.getMinValue()); sheet.getRow(R).getCell(selectcolumn + 2).setCellValue(c.getNoOfNegativeValues()); } /* // int Mov=Shifting.FindShift(wb, sheet); sheet.getRow(R).getCell(12).setCellValue(c.getAverageLedgerBalance()); sheet.getRow(R).getCell(13).setCellValue(c.getMinValue()); sheet.getRow(R).getCell(14).setCellValue(c.getNoOfNegativeValues()); // int Onl=Shifting.Online(wb, sheet); */ ///%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%///// for (int b = 0; b < c.getOnlineTransferToDate().size(); b++) { sheet.getRow(RowForOnlineTransferT0).getCell(9) .setCellValue(c.getOnlineTransferToDate().get(b)); sheet.getRow(RowForOnlineTransferT0).getCell(10) .setCellValue(c.getOnlineTransferToDescription().get(b)); sheet.getRow(RowForOnlineTransferT0).getCell(12) .setCellValue(c.getOnlineTransferToAmount().get(b)); RowForOnlineTransferT0++; } for (int L = 0; L < c.getKeywordAmount().size(); L++) { int index = 0; if (LenderDescription.contains(c.getKeywordDescription().get(L))) { index = LenderDescription.indexOf(c.getKeywordDescription().get(L)); LenderDate.set(index, c.getKeywordDate().get(L)); LenderAmount.set(index, c.getKeywordAmount().get(L)); } else { LenderDescription.add(c.getKeywordDescription().get(L)); LenderDate.add(c.getKeywordDate().get(L)); LenderAmount.add(c.getKeywordAmount().get(L)); } } for (int TT = 0; TT < c.getOnlineTransferToCheckNoDuplicateRemoved().size(); TT++) { OnlineTransferToAccounts.add(c.getOnlineTransferToCheckNoDuplicateRemoved().get(TT)); } if (!name.equals("BlackHills")) { for (int U = 0; U < c.getDailypaymentKeywordDescription().size(); U++) { sheet.getRow(RowForRecurringDailyPayment).getCell(14) .setCellValue(c.getDailypaymentKeywordDate().get(U)); sheet.getRow(RowForRecurringDailyPayment).getCell(15) .setCellValue(c.getDailypaymentKeywordDescription().get(U)); sheet.getRow(RowForRecurringDailyPayment).getCell(17) .setCellValue(c.getDailypaymentKeywordAmount().get(U)); RowForRecurringDailyPayment++; if (!UniqueDailypaymentkeywordDescription .contains(c.getDailypaymentKeywordDescription().get(U))) { UniqueDailypaymentkeywordAmount.add(c.getDailypaymentKeywordAmount().get(U)); UniqueDailypaymentkeywordDate.add(c.getDailypaymentKeywordDate().get(U)); UniqueDailypaymentkeywordDescription.add(c.getDailypaymentKeywordDescription().get(U)); } } } } for (int LL = 0; LL < LenderDescription.size(); LL++) if (25 + LL < 31) { { // if(25+LL > 29) { //Shifting.InsertRow(wb, sheet,25+LL,26+LL); } sheet.getRow(LL + 25).getCell(1).setCellValue(LenderDate.get(LL)); sheet.getRow(LL + 25).getCell(2).setCellValue(LenderDescription.get(LL)); sheet.getRow(LL + 25).getCell(5).setCellValue(LenderAmount.get(LL)); } } List<String> OnlineTransferToUniqueAccounts = new ArrayList<>(OnlineTransferToAccounts); for (int TT = 0; TT < OnlineTransferToUniqueAccounts.size(); TT++) { int Mov = Shifting.FindShift(wb, sheet); Shifting.InsertColumn(wb, sheet); sheet.getRow(1).getCell(8 + Mov).setCellValue("**" + OnlineTransferToUniqueAccounts.get(TT)); } //************************** Daily Payment portion ******************************************// for (int D = 0; D < UniqueDailypaymentkeywordDescription.size(); D++) { for (int r = 25; r < 31; r++) { if (sheet.getRow(r).getCell(2).getCellType() == 3) { sheet.getRow(r).getCell(1).setCellValue(UniqueDailypaymentkeywordDate.get(D)); sheet.getRow(r).getCell(2).setCellValue(UniqueDailypaymentkeywordDescription.get(D)); sheet.getRow(r).getCell(9).setCellValue(UniqueDailypaymentkeywordAmount.get(D)); sheet.getRow(r).getCell(0).setCellValue("OPEN"); break; } else if (sheet.getRow(r).getCell(2).getStringCellValue() .equals(UniqueDailypaymentkeywordDescription.get(D))) { sheet.getRow(r).getCell(9).setCellValue(UniqueDailypaymentkeywordAmount.get(D)); sheet.getRow(r).getCell(0).setCellValue("OPEN"); break; } } } Shifting.InsertFormulas(wb, sheet); Shifting.MergeCells(wb, sheet); // Write output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xlsx"); wb.write(fileOut); fileOut.close(); InputStream inp = new FileInputStream("workbook.xlsx"); file = new DefaultStreamedContent(inp, "application/vnd.ms-excel", "Table_downloaded.xlsx"); } catch (IOException ex) { Logger.getLogger(OrignalFileDownloadBean.class.getName()).log(Level.SEVERE, null, ex); } catch (InvalidFormatException ex) { Logger.getLogger(OrignalFileDownloadBean.class.getName()).log(Level.SEVERE, null, ex); } }