List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write
@SuppressWarnings("resource") public final void write(OutputStream stream) throws IOException
From source file:ObjectRepository.EquipDown.java
public void TestScenario() throws InterruptedException, IOException { System.out.println("Starting Execution on location " + Constant.Location + " to Down Equipment"); //driver.manage().window().setPosition(new Point(1900, 0)); driver.manage().window().maximize(); //driver.manage().window().setPosition(new Point(1900, 0)); driver.findElement(By.xpath("//*[@id='nav']/div[2]/div[1]/div[3]/div[2]")).click(); //click on Personnel menu driver.findElement(By.xpath("//*[@id='nav']/div[2]/div[1]/div[4]/div[2]")).click(); //click on tasks menu //driver.manage().window().setPosition(new Point(-1900, 0)); // List<String> equipmentCategory = new ArrayList<String>(); // equipmentCategory.add("RearLoaders"); // equipmentCategory.add("DualBins"); // equipmentCategory.add("MechanicalBrooms"); // equipmentCategory.add("RoRos"); // equipmentCategory.add("EzPacks"); // equipmentCategory.add("Miscellaneous"); // equipmentCategory.add("Snow"); String[] equipmentCategory = new String[8]; equipmentCategory[0] = ("RearLoaders"); equipmentCategory[1] = ("DualBins"); equipmentCategory[2] = ("MechanicalBrooms"); equipmentCategory[3] = ("AlleyTrucks"); equipmentCategory[4] = ("RoRos"); equipmentCategory[5] = ("EzPacks"); equipmentCategory[6] = ("Miscellaneous"); equipmentCategory[7] = ("Snow"); //opening existing excel file call Data FileInputStream file = new FileInputStream( "C:\\Users\\skashem\\Desktop\\Excel_Selenium_Framework\\431_Down_Equipment.xlsx"); @SuppressWarnings("resource") XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = null;//from ww w . ja va2 s . co m int n = 1; SoftAssert softassert = new SoftAssert(); for (int itrCategory = 0; itrCategory < equipmentCategory.length; itrCategory++) { //equipmentCategory.get(itrCategory) //this.j = j; for (int j = 0; j <= Constant.equipIteration - 1; j++) //number of equipment to be down //this.j = j; { //Thread.sleep(300); if (driver .findElement(By.xpath("//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[2]/div")) .isDisplayed()) { String equipInnertext = driver .findElement( By.xpath("//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[2]/div")) .getText(); //this.variableName can be used if two elements have similar functioanlity and you want to split it and call the element from a specific category. That variable can be used outside of if statement and loop statement //String equipInnertext = driver.findElement(By.xpath("//*[@id='equipRearLoaders']/div[2]/div")).getText(); if (equipInnertext.length() > 3) { String[] Innertext = equipInnertext.split("\n"); int i = Constant.GetAllEquipmentCount(Innertext); //@SuppressWarnings("unchecked") List<String> equpNames = Constant.GetAllEquipmentNames(Innertext); // String random = (equpNames[new Random().nextInt(equpNames.length)]); Random rn = new Random(); int randomEqup = rn.nextInt(i); row = sheet.createRow(n); System.out.println("number of total equipment in " + equipmentCategory[itrCategory] + " Before Down is " + i); row.createCell(0).setCellValue(Constant.Location); //location sheet.autoSizeColumn(0); row.createCell(1).setCellValue(Constant.GetDateAndformat(0)); //date sheet.autoSizeColumn(1); String strEquipCount = driver.findElement(By.xpath( "//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[1]/h4/a/span[2]")) .getText(); String intEquipCount = Constant.EquipmentCount(strEquipCount); int intEquipCountBeforeDown = Double.valueOf(intEquipCount).intValue(); System.out.println("Equipment to down is " + equpNames.get(randomEqup)); row.createCell(2).setCellValue(equpNames.get(randomEqup)); //Equipment name from ops-board sheet.autoSizeColumn(2); row.createCell(3).setCellValue(equipmentCategory[itrCategory]); //Equipment category name sheet.autoSizeColumn(3); if (intEquipCountBeforeDown == i) { row.createCell(4).setCellValue(i); //number of equipment within a category Reporter.log( "Passed-Correct number of Equipment displayed on OPS Before down for category " + equipmentCategory[itrCategory] + " is " + i + " for iteration " + (j + 1)); //number of equipment within a category); sheet.autoSizeColumn(4); } else { row.createCell(4).setCellValue("Ops-Board category displays Count as: " + intEquipCountBeforeDown + " but actual equipment count is " + i); //number of equipment within a category //Assert.fail("Failed- InCorrect number of Equipment displayed on OPS after down for category " + equipmentCategory.get(itrCategory) + " is " + i + " for iteration " + (j + 1)); Reporter.log( "Failed- InCorrect number of Equipment displayed on OPS before down for category " + equipmentCategory[itrCategory] + " is " + intEquipCountBeforeDown + " for iteration " + (j + 1)); softassert.assertEquals("Equipment count before down should be " + i, " But equipment count before down is " + intEquipCountBeforeDown); sheet.autoSizeColumn(4); //driver.close(); } String downEquipInnertext = driver.findElement(By.xpath("//*[@id='equipDown']")).getText(); String[] downInnertext = downEquipInnertext.split("\n"); int downItr = Constant.GetAllEquipmentCount(downInnertext); String strDownCount = driver .findElement(By.xpath("//*[@id='allUnavailableEquipment']/div[3]/a/div/span[2]")) .getText(); String intDownCount = Constant.EquipmentCount(strDownCount); int intDownCountBeforeDown = Double.valueOf(intDownCount).intValue(); if (intDownCountBeforeDown == downItr) { row.createCell(5).setCellValue(downItr); //number of equipment within a down category Reporter.log( "Passed-Correct number of Equipment displayed on OPS Before down for Down Category is " + downItr + " for iteration " + (j + 1)); //number of equipment within Down category); System.out.println( "Passed-Correct number of Equipment displayed on OPS Before down for Down Category is " + downItr + " for iteration " + (j + 1)); //number of equipment within Down category); sheet.autoSizeColumn(5); } else { System.out.println( "Failed-Incorrect number of Equipment displayed on OPS Before down for Down Category is " + intDownCountBeforeDown + " for iteration " + (j + 1)); row.createCell(5).setCellValue("Ops-Board category displays Count as: " + intDownCountBeforeDown + " but actual equipment count is " + downItr); //number of equipment within Down category Reporter.log( "Failed- InCorrect number of Equipment displayed on OPS before down for Down category is " + intDownCountBeforeDown + " for iteration " + (j + 1)); softassert.assertEquals( "Equipment count before down in down category should be " + downItr, " But equipment count before down in Down category is " + intDownCountBeforeDown); sheet.autoSizeColumn(5); //driver.close(); } driver.findElement( By.xpath("//span[contains(@class,'piece-of-equipment') and contains(text(),'" + equpNames.get(randomEqup) + "')]")) .click(); String equipDown = driver .findElement(By.xpath("//*[@id='details']/div[1]/div/table/tbody/tr[1]/td[1]/h4")) .getText(); row.createCell(6).setCellValue(equipDown); //Equipment condition before down System.out.println("Equipment Condition is " + equipDown); if (equipDown == "Down") { //j--; //continue; row.createCell(6).setCellValue("Down"); //Equipment condition before down sheet.autoSizeColumn(6); } else { // Thread.sleep(350); driver.findElement( By.xpath("//*[@id='screen1Pane2']/div[2]/div[1]/div/div/span[3]/div/button[2]")) .click(); //click on action drop down if (driver .findElement(By.xpath( "//*[@id='screen1Pane2']/div[2]/div[1]/div/div/span[3]/div/ul/li[5]")) .isDisplayed()) //if down action button is displayed { driver.findElement(By.xpath( "//*[@id='screen1Pane2']/div[2]/div[1]/div/div/span[3]/div/ul/li[5]")) .click(); //clicking on Down action Thread.sleep(1800); String strEquipmentType = driver .findElement(By.xpath("//*[@id='modal']/div[2]/div[1]/div[1]")).getText(); //equipment type modal window row.createCell(7).setCellValue(strEquipmentType); //Equipment Repair Location sheet.autoSizeColumn(7); String strEquipmentOwner = driver .findElement(By.xpath("//*[@id='modal']/div[2]/div[1]/div[2]")).getText(); //equipment Owner modal window row.createCell(8).setCellValue(strEquipmentOwner); //Equipment Repair Location sheet.autoSizeColumn(8); String strEquipmentCurrentLocation = driver .findElement(By.xpath("//*[@id='modal']/div[2]/div[2]/div[1]")).getText(); //equipment Current Location modal window row.createCell(9).setCellValue(strEquipmentCurrentLocation); //Equipment Repair Location modal window sheet.autoSizeColumn(9); String strEquipmentLicense = driver .findElement(By.xpath("//*[@id='modal']/div[2]/div[2]/div[2]")).getText(); //equipment License modal window row.createCell(10).setCellValue(strEquipmentLicense); //Equipment Repair Location sheet.autoSizeColumn(10); //click and enter a down code 1 from drop down driver.findElement(By.xpath("//*[@id='s2id_downCode1']")).click(); String downCodesInnertext = driver .findElement(By.xpath("//div[@id='select2-drop']")).getText(); String[] arrayDownCodeInnertext = downCodesInnertext.split("\n"); List<String> downCodes = Constant.GetAllDropDowns(arrayDownCodeInnertext); int j1 = Constant.GetAllDropDownCount(arrayDownCodeInnertext); int randomDownCode = rn.nextInt(j1); System.out.println( "Down code selected for down code 1 is " + downCodes.get(randomDownCode)); row.createCell(11).setCellValue(downCodes.get(randomDownCode)); //Equipment down code sheet.autoSizeColumn(11); List<WebElement> a = driver.findElements(By .xpath("//input[contains(@id,'s2id_autogen') and contains(@id,'search')]")); Actions actions = new Actions(driver); actions.moveToElement(a.get(0)); actions.click(); actions.sendKeys(downCodes.get(randomDownCode)).perform(); driver.findElement(By.xpath("//*[contains(@id,'select2-results')]/li[1]")).click(); //click and enter a Repair Location from drop down driver.findElement(By.xpath("//*[@id='s2id_repairLocation1']")).click(); String repairLocationInnertext = driver .findElement(By.xpath("//div[@id='select2-drop']")).getText(); String[] arrayRepairLocation = repairLocationInnertext.split("\n"); List<String> repairLocation = Constant.GetAllDropDowns(arrayRepairLocation); //System.out.println("Down code " + repairLocation.get(0)); int k = Constant.GetAllDropDownCount(arrayRepairLocation); int randomRepairLocation = rn.nextInt(k); System.out.println("Repair Location selected for down code 1 is " + repairLocation.get(randomRepairLocation)); row.createCell(12).setCellValue((repairLocation.get(randomRepairLocation))); //Equipment Repair Location sheet.autoSizeColumn(12); actions.moveToElement(a.get(1)); actions.click(); actions.sendKeys(repairLocation.get(randomRepairLocation)).perform(); driver.findElement(By.xpath("//*[contains(@id,'select2-results')]/li[1]")).click(); //Enter Reporter driver.findElement( By.xpath("//*[@id='modal']/div[2]/fieldset[1]/div[2]/div[1]/input")) .sendKeys("Reporter1"); row.createCell(13).setCellValue("Reporter1"); //Equipment Reporter sheet.autoSizeColumn(13); //Thread.sleep(300); //Enter Mechanic driver.findElement( By.xpath("//*[@id='modal']/div[2]/fieldset[1]/div[2]/div[2]/input")) .sendKeys("Mechanic1"); row.createCell(14).setCellValue("Mechanic1"); //Equipment Mechanic sheet.autoSizeColumn(14); //Enter Remarks driver.findElement( By.xpath("//*[@id='modal']/div[2]/fieldset[1]/div[2]/div[3]/input")) .sendKeys("Test"); //Click on Down Button driver.findElement(By.xpath("//*[@id='modal']/div[3]/button[2]")).click(); Thread.sleep(4000); driver.findElement(By.xpath("//*[contains(@ng-click,'closeEquipmentDetailsPane')]")) .click(); //Thread.sleep(1200); } // end of if down action button is displayed } // if equipment is down //Thread.sleep(500); driver.findElement( By.xpath("//span[contains(@class,'piece-of-equipment') and contains(text(),'" + equpNames.get(randomEqup) + "')]")) .click(); Thread.sleep(300); String equipAfterDown = driver .findElement(By.xpath("//*[@id='details']/div[1]/div/table/tbody/tr[1]/td[1]/h4")) .getText(); row.createCell(17).setCellValue(equipAfterDown); //Equipment condition after down driver.findElement(By.xpath("//*[contains(@ng-click,'closeEquipmentDetailsPane')]")) .click(); driver.findElement(By.xpath("//*[@id='nav']/div[2]/div[1]/div[12]/div[2]")).click(); Thread.sleep(1000); String strDownRecentActivity0 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[1]")) .getText(); String strDownRecentActivity1 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[2]")) .getText(); String strDownRecentActivity2 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[3]")) .getText(); String strDownRecentActivity3 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[4]")) .getText(); String strDownRecentActivity4 = driver .findElement( By.xpath("//*[@id='board']/div[2]/div/div[2]/div[1]/div[1]/div[2]/span[6]")) .getText(); String strDownActivity = strDownRecentActivity0 + " " + strDownRecentActivity1 + " " + strDownRecentActivity2 + " " + strDownRecentActivity3 + " on " + strDownRecentActivity4; //System.out.println(strDownActivity); if (strDownActivity .matches("(?i).*Downed Equipment.*" + equpNames.get(randomEqup) + ".*")) { System.out.println("Equipment displays in recent activity for iteration " + (j + 1) + " after Down: " + strDownActivity); row.createCell(18).setCellValue(strDownActivity); //Recent Activity after down sheet.autoSizeColumn(18); } else { System.out.println("no records for Equipment appears in recent acitivity for iteration " + (j + 1) + " after Down"); row.createCell(18).setCellValue( "No records for Equipment appears in recent acitivity for iteration "); //Recent Activity after down sheet.autoSizeColumn(18); } Thread.sleep(200); driver.findElement(By.xpath("//*[@id='nav']/div[2]/div[1]/div[12]/div[2]")).click(); String equipInnertextAfter = driver .findElement(By .xpath("//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[2]/div")) .getText(); String[] InnertextAfter = equipInnertextAfter.split("\n"); int iAfter = Constant.GetAllEquipmentCount(InnertextAfter); System.out.println("number of total equipment in " + equipmentCategory[itrCategory] + " After Down is " + iAfter); String strEquipCountAfterDown = driver.findElement(By.xpath( "//*[@id='equip" + equipmentCategory[itrCategory] + "']/div[1]/h4/a/span[2]")) .getText(); String intEquipCountAfterDown = Constant.EquipmentCount(strEquipCountAfterDown); int intEquipCountAfter = Double.valueOf(intEquipCountAfterDown).intValue(); if (intEquipCountAfter != iAfter) { row.createCell(15).setCellValue("Ops-Board category displays Count as: " + intEquipCountAfter + " but actual equipment count is " + iAfter); //number of equipment within a category sheet.autoSizeColumn(15); //Assert.fail("Failed- InCorrect number of Equipment displayed on OPS after down for category " + equipmentCategory.get(itrCategory) + " is " + intEquipCountAfter + " for iteration " + (j + 1)); //driver.close(); // try // { // Softassert.assertTrue(false); softassert.assertEquals("Equipment count after down should be " + iAfter, " But equipment count after down is " + intEquipCountAfter); //intEquipCountAfter // catch(AssertionError e) // { Reporter.log( "Failed- incorrect number of Equipment displayed on OPS after down for category " + equipmentCategory[itrCategory] + " is " + intEquipCountAfter + " for iteration " + (j + 1)); //number of equipment within a category; // } } else { row.createCell(15).setCellValue(iAfter); //number of equipment within a category Reporter.log( "Passed- correct number of Equipment displayed on OPS after down for category " + equipmentCategory[itrCategory] + " is " + iAfter + " for iteration " + (j + 1)); //number of equipment within a category; sheet.autoSizeColumn(15); } String downEquipInnertextAfter = driver.findElement(By.xpath("//*[@id='equipDown']")) .getText(); String[] downInnertextAfter = downEquipInnertextAfter.split("\n"); int downItrAfter = Constant.GetAllEquipmentCount(downInnertextAfter); String strDownCountAfter = driver .findElement(By.xpath("//*[@id='allUnavailableEquipment']/div[3]/a/div/span[2]")) .getText(); String intDownCountAfter = Constant.EquipmentCount(strDownCountAfter); int intDownCountBeforeDownAfter = Double.valueOf(intDownCountAfter).intValue(); if (intDownCountBeforeDownAfter == downItrAfter) { row.createCell(16).setCellValue(downItrAfter); //number of equipment within a down category Reporter.log( "Passed-Correct number of Equipment displayed on OPS After down for Down Category is " + downItrAfter + " for iteration " + (j + 1)); //number of equipment within Down category); System.out.println( "Passed-Correct number of Equipment displayed on OPS After down for Down Category is " + downItrAfter + " for iteration " + (j + 1)); //number of equipment within Down category); sheet.autoSizeColumn(16); } else { System.out.println( "Failed-Incorrect number of Equipment displayed on OPS After down for Down Category is " + intDownCountBeforeDownAfter + " for iteration " + (j + 1)); row.createCell(16).setCellValue( "Ops-Board category displays Count as: " + intDownCountBeforeDownAfter + " but actual equipment count is " + downItrAfter); //number of equipment within Down category Reporter.log( "Failed- InCorrect number of Equipment displayed on OPS After down for Down category is " + intDownCountBeforeDownAfter + " for iteration " + (j + 1)); softassert.assertEquals( "Equipment count After down in down category should be " + downItrAfter, " But equipment count After down in Down category is " + intDownCountBeforeDownAfter); sheet.autoSizeColumn(16); //driver.close(); } Thread.sleep(500); n = n + 1; } else { break; } // end of equipment greater than 3 if } // end of is displayed } // End of number of equipment per category loop } // End of Category loop driver.close(); file.close(); //saving the file as different name FileOutputStream outFile = new FileOutputStream( "C:\\Users\\skashem\\Desktop\\Excel_Selenium_Framework\\431_Down_Equipment_" + Constant.Location + "_" + Constant.GetDateAndformat(0) + ".xlsx"); workbook.write(outFile); outFile.close(); }
From source file:Opm_Package.Excell_Dublicating.java
public void createExcel(ArrayList<Object[]> allobj, int number, String excelFilePath, String sheetName) throws IOException { FileOutputStream fos = null;/*from www.ja v a 2s . c om*/ try { XSSFWorkbook workbook = null; if (new File(excelFilePath).createNewFile()) { workbook = new XSSFWorkbook(); } else { FileInputStream pfs = new FileInputStream(new File(excelFilePath)); workbook = new XSSFWorkbook(pfs); } if (workbook.getSheet(sheetName) == null) { fos = new FileOutputStream(excelFilePath); sheet = workbook.createSheet(sheetName); /// int rowid2 = sheet.getLastRowNum(); int x; for (x = 0; x < allobj.size(); x++) {//Looping thru the array list to pick the objects... rows = sheet.createRow(rowid2++); Object[] objectArr = allobj.get(x); int cellid = 0; for (Object obj : objectArr) {//Looping inside the object... Cell cells = rows.createCell(cellid++); if (obj instanceof String) { cells.setCellValue((String) obj); } else if (obj instanceof Integer) { cells.setCellValue((int) obj); } else if (obj instanceof Double) { cells.setCellValue((double) obj); } } // End of for loop for object } //End of for loop for arraylist of object.... workbook.write(fos); System.out.println("Excel File Created is : " + excelFilePath + " With sheet name :" + sheetName); System.out.println("\n\n"); } } catch (IOException e) { throw e; } finally { if (fos != null) { fos.close(); } } }
From source file:opn.greenwebs.FXMLDocumentController.java
private File createStockFile(List<ItemDB> list) { int nSize = list.size(); XSSFWorkbook wbs = createStockWorkbook(); XSSFSheet sheetStock = wbs.getSheet("Digital Version"); List<XSSFTable> lTables = sheetStock.getTables(); // Create a FormulaEvaluator to use FormulaEvaluator mainWorkbookEvaluator = sheetStock.getWorkbook().getCreationHelper() .createFormulaEvaluator();/*from w w w. ja v a 2 s. co m*/ File fStock = createFilename("STK", ""); Instant instant = Instant.from(dteOrderDate.getValue().atStartOfDay(ZoneId.systemDefault())); Row rowed = sheetStock.getRow(6); Cell celled = rowed.getCell(10); CellStyle cellStyle = celled.getCellStyle(); XSSFFont font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); cellStyle.setFont(font); celled.setCellValue(Date.from(instant)); celled.setCellStyle(cellStyle); rowed = sheetStock.getRow(10); celled = rowed.getCell(2); celled.setCellValue(fStock.getName().substring(0, fStock.getName().length() - 5)); if (!lTables.isEmpty()) { XSSFTable table = lTables.get(0); table.getCTTable() .setRef(new CellRangeAddress(table.getStartCellReference().getRow(), table.getEndCellReference().getRow() + nSize, table.getStartCellReference().getCol(), table.getEndCellReference().getCol()).formatAsString()); XSSFRow row; XSSFCell cell; font = sheetStock.getWorkbook().createFont(); font.setFontHeight(14); int nCellRef = table.getStartCellReference().getRow() + 1; for (ItemDB itemdb : list) { row = sheetStock.createRow(nCellRef++); cell = row.createCell(0); cellStyle = cell.getCellStyle(); cell.setCellValue(itemdb.getDblQty()); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(itemdb.getStrMfr()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(itemdb.getStrSKU()); cell.setCellStyle(cellStyle); cell = row.createCell(3); cell.setCellValue(itemdb.getStrDescrip()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(itemdb.getStrSupplier()); cell.setCellStyle(cellStyle); cell = row.createCell(5); cell.setCellValue(itemdb.getStrSupPart()); cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(itemdb.getDblSalePrice()); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(itemdb.getDblCost()); cell.setCellStyle(cellStyle); /*cell = row.createCell(8); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("IF(A" + nCellRef + ">0,IF(G" + nCellRef + ">0,IF(H" + nCellRef + ">0,A" + nCellRef + "*G" + nCellRef + "-A" + nCellRef + "*H" + nCellRef + ",\"\"),\"\"),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); cell.setCellStyle(cellStyle); cell = row.createCell(9); cell.setCellFormula("IF(I" + nCellRef + "<>\"\",I" + nCellRef + "/(A" + nCellRef + "*G" + nCellRef + "),\"\")"); mainWorkbookEvaluator.evaluateFormulaCell(cell); CellStyle style = wbs.createCellStyle(); style.setDataFormat(wbs.createDataFormat().getFormat("0%")); cell.setCellStyle(style);*/ mainWorkbookEvaluator.evaluateAll(); } try { try (FileOutputStream fileOut = new FileOutputStream(fStock)) { wbs.write(fileOut); return fStock; } } catch (FileNotFoundException ex) { logger.info(ex.getLocalizedMessage()); } catch (IOException ex) { logger.info(ex.getLocalizedMessage()); } } return null; }
From source file:opn.greenwebs.FXMLDocumentController.java
@FXML private void handleInjectExcel(ActionEvent event) { XSSFWorkbook wb = createWorkbook(); FormulaEvaluator evaluator;/*from w ww .j a va 2 s . co 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:optrecursive_testbench.Save.java
public void save(Matrix matrice, String filename) throws FileNotFoundException, IOException { ///It is working String dirPath = OptRecursive_Testbench.excelFilePath + File.separator + filepath; setupDirectory(dirPath);//from w w w . j a v a 2 s . c o m String fileName = dirPath + File.separator + filename + ".xlsx"; System.out.println("Using filepath " + filepath + ", saving to address: " + fileName); try { FileOutputStream fileOut = new FileOutputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet worksheet = workbook.createSheet("POI Worksheet"); int lastvaluex = matrice.getRowDimension(); int lastvaluey = matrice.getColumnDimension(); int ih = 0; int jh = 0; while (ih < lastvaluex) { XSSFRow row = worksheet.createRow(ih); ih++; while (jh < lastvaluey) { XSSFCell cell = row.createCell(jh); jh++; cell.setCellValue(matrice.get(ih - 1, jh - 1)); } jh = 0; } workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:optrecursive_testbench.Save.java
public void savedouble(double matrice, String filename) throws FileNotFoundException, IOException { ///It is working String dirPath = OptRecursive_Testbench.excelFilePath + File.separator + filepath; setupDirectory(dirPath);//w w w. j a v a 2 s . co m String fileName = dirPath + File.separator + filename + ".xlsx"; try { FileOutputStream fileOut = new FileOutputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet worksheet = workbook.createSheet("POI Worksheet"); int lastvaluex = 1; int lastvaluey = 1; int ih = 0; int jh = 0; while (ih < lastvaluex) { XSSFRow row = worksheet.createRow(ih); ih++; while (jh < lastvaluey) { XSSFCell cell = row.createCell(jh); jh++; cell.setCellValue(matrice); } jh = 0; } workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:optrecursive_testbench.Save.java
public void save3D(Matrix matrice, String filename, int kj) throws FileNotFoundException, IOException { ///It is working String dirPath = OptRecursive_Testbench.excelFilePath + File.separator + filepath; setupDirectory(dirPath);/* w ww. j a va 2 s . c o m*/ String fileName = dirPath + File.separator + filename + kj + ".xlsx"; try { FileOutputStream fileOut = new FileOutputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet worksheet = workbook.createSheet("POI Worksheet"); int lastvaluex = matrice.getRowDimension(); int lastvaluey = matrice.getColumnDimension(); int ih = 0; int jh = 0; while (ih < lastvaluex) { XSSFRow row = worksheet.createRow(ih); while (jh < lastvaluey) { XSSFCell cell = row.createCell(jh); cell.setCellValue(matrice.get(ih, jh)); jh++; } ih++; jh = 0; } workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:optrecursive_testbench.Save.java
public void saveString(String[] matrice, String filename) throws FileNotFoundException, IOException { ///It is working String dirPath = OptRecursive_Testbench.excelFilePath + File.separator + filepath; setupDirectory(dirPath);//ww w. j a v a2 s . co m String fileName = dirPath + File.separator + filename + ".xlsx"; try { FileOutputStream fileOut = new FileOutputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet worksheet = workbook.createSheet("POI Worksheet"); int lastvaluex = matrice.length; int lastvaluey = 1; int ih = 0; int jh = 0; while (ih < lastvaluex) { XSSFRow row = worksheet.createRow(ih); while (jh < lastvaluey) { XSSFCell cell = row.createCell(jh); cell.setCellValue(matrice[ih]); jh++; } ih++; jh = 0; } workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:org.adimadim.kosu.MainForm.java
private void exportToExcel() throws Exception { List<RaceScore> raceScoreList = getRaceService().retrieveRaceScoresByRaceId(selectedRace.getRaceId()); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Sonuclar"); XSSFRow row = sheet.createRow(0);//ww w .ja v a 2 s .c om row.createCell(0).setCellValue("Id"); row.createCell(1).setCellValue("Gs No"); row.createCell(2).setCellValue("Ad"); row.createCell(3).setCellValue("Soyad"); row.createCell(4).setCellValue("Cinsiyet"); row.createCell(5).setCellValue("Sra"); int i = 1; for (RaceScore raceScore : raceScoreList) { XSSFRow tempRow = sheet.createRow(i); tempRow.createCell(0).setCellValue(raceScore.getAccount().getAccountId()); tempRow.createCell(1).setCellValue(raceScore.getAccount().getChestNumber()); tempRow.createCell(2).setCellValue(raceScore.getAccount().getName()); tempRow.createCell(3).setCellValue(raceScore.getAccount().getSurname()); tempRow.createCell(4).setCellValue(raceScore.getAccount().getGender()); tempRow.createCell(5).setCellValue(raceScore.getOrderNo()); i++; } String fileName = "sonuclar.xlsx"; try (FileOutputStream fos = new FileOutputStream(fileName)) { workbook.write(fos); } System.out.println(fileName + " written successfully"); }
From source file:org.alfresco.bm.api.v1.RestAPITest.java
License:Open Source License
/** * Checks the test application APIs for retrieving test results. */// www .jav a2 s.com @Test public synchronized void testScenario07() throws Exception { /* * In-memory byte[] are used because we know the size of the documents * will be manageable. */ executeTestRun("T07", "A test for scenario 07.", "01", "Scenario 07 - Run 01"); // Get report for test run that does not exist try { ResultsRestAPI resultsAPI = api.getTestRunResultsAPI("T07MISSING", "01"); resultsAPI.getReportCSV(); Assert.fail("Expected exception regarding missing test."); } catch (WebApplicationException e) { assertEquals(Status.NOT_FOUND.getStatusCode(), e.getResponse().getStatus()); } ResultsRestAPI resultsAPI = api.getTestRunResultsAPI("T07", "01"); // Get the results CSV StreamingOutput csvOutput = resultsAPI.getReportCSV(); ByteArrayOutputStream csvBos = new ByteArrayOutputStream(); csvBos.close(); csvOutput.write(csvBos); String csvResults = new String(csvBos.toByteArray()); // Check assertTrue(csvResults.contains("Data:,bm21-data.T07.01.results")); assertTrue(csvResults.contains("Started")); assertTrue(csvResults.contains("Finished")); assertTrue(csvResults.contains("Duration")); // Get the JSON results String chartJson = resultsAPI.getTimeSeriesResults(0L, "seconds", 1, 5, false); assertTrue(chartJson.startsWith("[ { \"time\" : ")); assertTrue(chartJson.contains("[ { \"time\" : ")); assertTrue(chartJson.contains("000 , \"name\" : \"start\" , \"mean\" : ")); assertTrue(chartJson.endsWith(" , \"fail\" : 0 , \"failPerSec\" : 0.0}]")); // Get the XLSX report StreamingOutput xlsxOutput = resultsAPI.getReportXLSX(); ByteArrayOutputStream xlsxBos = new ByteArrayOutputStream(); xlsxOutput.write(xlsxBos); xlsxBos.close(); ByteArrayInputStream xlsxBis = new ByteArrayInputStream(xlsxBos.toByteArray()); XSSFWorkbook xlsxWorkbook = new XSSFWorkbook(xlsxBis); xlsxBis.close(); // Write it to a temp file just for fun File xlsxFile = File.createTempFile(UUID.randomUUID().toString(), ".xlsx"); FileOutputStream xlsxFos = new FileOutputStream(xlsxFile); xlsxWorkbook.write(xlsxFos); xlsxFos.close(); logger.info("XLSX report found here: " + xlsxFile); // Check POIXMLProperties xlsxProperties = xlsxWorkbook.getProperties(); CoreProperties xlsxCoreProperties = xlsxProperties.getCoreProperties(); Assert.assertNotNull("No XLSX description: " + xlsxFile, xlsxCoreProperties.getDescription()); Assert.assertTrue("Title in XLSX must contain test run FQN: " + xlsxFile, xlsxCoreProperties.getTitle().contains("T07.01")); Assert.assertTrue("Description in XLSX must contain test name and description: " + xlsxFile, xlsxCoreProperties.getDescription().contains("A test for scenario 07.")); Assert.assertTrue("Description in XLSX must contain test name and description." + xlsxFile, xlsxCoreProperties.getDescription().contains("Scenario 07 - Run 01")); xlsxWorkbook.close(); }