List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:negocio.parser.ExcelReader.java
@Override public IExcelContent leerArchivo(String ruta) throws Exception { java.util.Date date = new java.util.Date(); Date entrada = new Date(date.getTime()); IExcelContent ec = ExcelContent.getInstantiateExcelContent(); try {// w w w. j a v a 2s .com LogDAO dao = new LogDAO(); LogDTO dto = new LogDTO("Leer archivo", "Comienzo de lectura de archivo", entrada.toString(), entrada.toString()); dao.registrarLog(dto); File archivo = new File(ruta); XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(archivo)); //crear un libro excel XSSFSheet sheet = workbook.getSheetAt(0); //acceder a la primera hoja Iterator<Row> rowIterator = sheet.iterator(); Row row; boolean sw = true; ArrayList<List<String>> datos = new ArrayList<>(); while (rowIterator.hasNext()) { row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); Cell celda; List<String> fila = new ArrayList<>(); while (cellIterator.hasNext()) { celda = cellIterator.next(); String dato = ""; switch (celda.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(celda)) { dato = celda.getDateCellValue().toString(); } else { dato = celda.getNumericCellValue() + ""; } break; case Cell.CELL_TYPE_STRING: dato = celda.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: dato = celda.getBooleanCellValue() + ""; break; } fila.add(dato); } if (sw) { sw = false; ec.setTitulos(fila); } else { datos.add(fila); } } ec.setDatos(datos); workbook.close(); return ec; } catch (IOException ex) { Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex); } return null; }
From source file:net.clementlevallois.gradingpics.io.Excel.java
public void writeFGradeForOneStudent(String name, String grade) throws FileNotFoundException, IOException { XSSFWorkbook workbook; FileInputStream file = null;// w ww .j a v a2 s . c o m if (Files.exists(Paths.get("grades_2018.xlsx"))) { file = new FileInputStream(new File("grades_2018.xlsx")); workbook = new XSSFWorkbook(file); } else { workbook = new XSSFWorkbook(); } while (workbook.getNumberOfSheets() < 4) { workbook.createSheet(String.valueOf(String.valueOf(workbook.getNumberOfSheets() + 1))); } XSSFSheet sheet = workbook.getSheetAt(1); int lastRowNumber = sheet.getLastRowNum(); //Create a new row in current sheet XSSFRow row = sheet.createRow(lastRowNumber + 1); //Create a new cell in current row XSSFCell cellName = row.createCell(0); //Set value to new value cellName.setCellValue(name); XSSFCell cellGrade = row.createCell(1); cellGrade.setCellValue(grade); //close the excel file when done if (file != null) { file.close(); } FileOutputStream fos = new FileOutputStream(new File("grades_2018.xlsx")); workbook.write(fos); fos.close(); }
From source file:net.creativeidesign.timekeeper_v1.util.TaskImport.java
public boolean doImport() { try {//from w w w. j a v a2 s . c om JFrame parentFrame = new JFrame(); FileFilter filter = new FileNameExtensionFilter("MS Excel .xlsx", "xlsx"); JFileChooser fileChooser = new JFileChooser(); fileChooser.setDialogTitle("Please select an excel file to import from"); fileChooser.setFileFilter(filter); int userSelection = fileChooser.showSaveDialog(parentFrame); File fileToSave = fileChooser.getSelectedFile(); if (fileToSave == null) return false; String filePathName = fileToSave.getAbsolutePath(); FileInputStream file = new FileInputStream(new File(filePathName)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); importedItems = new ArrayList<>(); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); rowIterator.next();//skip first row [it should be the header] while (rowIterator.hasNext()) { ToDoItemModel tmpItem = new ToDoItemModel(); int iCellCnt = 0; Row row = rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { String cellStr = null; double cellNum = 0; Cell cell = cellIterator.next(); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cellNum = cell.getNumericCellValue(); System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: cellStr = cell.getStringCellValue(); System.out.print(cell.getStringCellValue() + "\t"); break; }//end switch (cell.getCellType()) switch (iCellCnt) { case 0: tmpItem.setiId((int) cellNum); break; case 1: tmpItem.setStrTitle(cellStr); break; case 2: tmpItem.setStrDescription(cellStr); break; case 3: tmpItem.setDtDateUntil(cellStr); break; case 4: tmpItem.setiCategory((int) cellNum); break; }//end switch(iCellCnt) ++iCellCnt; } System.out.println(""); importedItems.add(tmpItem); } file.close(); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, "Wohoo, something went wrong, please try again later", "File Saved", JOptionPane.INFORMATION_MESSAGE); } return compareItemsInDB(); }
From source file:nl.architolk.ldt.processors.ExcelConverter.java
License:Open Source License
public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException { try {//from w w w. ja v a2s . c o m // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); // Create workbook XSSFWorkbook workbook = new XSSFWorkbook(bais); DataFormatter formatter = new DataFormatter(); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); contentHandler.startDocument(); contentHandler.startElement("", "workbook", "workbook", new AttributesImpl()); for (int s = 0; s < workbook.getNumberOfSheets(); s++) { XSSFSheet sheet = workbook.getSheetAt(s); AttributesImpl sheetAttr = new AttributesImpl(); sheetAttr.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); contentHandler.startElement("", "sheet", "sheet", sheetAttr); for (int r = 0; r <= sheet.getLastRowNum(); r++) { XSSFRow row = sheet.getRow(r); if (row != null) { AttributesImpl rowAttr = new AttributesImpl(); rowAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(r)); contentHandler.startElement("", "row", "row", rowAttr); for (int c = 0; c < row.getLastCellNum(); c++) { XSSFCell cell = row.getCell(c); if (cell != null) { try { String cellvalue = formatter.formatCellValue(cell, evaluator); if (cellvalue != "") { AttributesImpl columnAttr = new AttributesImpl(); columnAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(cell.getColumnIndex())); contentHandler.startElement("", "column", "column", columnAttr); contentHandler.characters(cellvalue.toCharArray(), 0, cellvalue.length()); contentHandler.endElement("", "column", "column"); } } catch (Exception e) { } } } contentHandler.endElement("", "row", "row"); } } contentHandler.endElement("", "sheet", "sheet"); } contentHandler.endElement("", "workbook", "workbook"); contentHandler.endDocument(); } catch (IOException e) { throw new OXFException(e); } }
From source file:nl.detoren.ijc.io.OutputExcel.java
License:Open Source License
/** * Create the Excel version of the sheet Original Empty file is stored in * Empty.xlsx Create version with round matches is stored in Indeling.xlsx * * @param wedstrijden/*from w w w. j a v a2 s . com*/ * The round to store in the Excel file */ public boolean export(Wedstrijden wedstrijden) { try { logger.log(Level.INFO, "Wedstrijden wegschrijven naar Excel"); int periode = wedstrijden.getPeriode(); int ronde = wedstrijden.getRonde(); String rpString = "Periode " + periode + ", Ronde " + ronde; String datum = new SimpleDateFormat("dd-MM-yyyy HH:mm").format(Calendar.getInstance().getTime()); // Open the empty schedule file, matches are stored in the // second sheet (id = 1) FileInputStream file = new FileInputStream("Template.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(file); ArrayList<Groepswedstrijden> gws = wedstrijden.getGroepswedstrijden(); for (Groepswedstrijden gw : gws) { int groepID = gw.getNiveau(); int nrSeries = gw.getSeries().size(); // Open sheet voor deze groep XSSFSheet sheet = workbook.getSheetAt(groepID); workbook.setSheetName(groepID, Groep.geefNaam(groepID)); updateCell(sheet, 2, 8, rpString); updateCell(sheet, 2, 4, datum); // Export Series int currentRow = 5; for (int s = 0; s < nrSeries; ++s) { // For each serie Serie serie = gw.getSerie(s); updateCell(sheet, currentRow, 2, "Serie " + (s + 1)); borderLeft(getCell(sheet, currentRow, 1)); borderLeft(getCell(sheet, currentRow + 1, 1)); borderRight(getCell(sheet, currentRow, 9)); borderRight(getCell(sheet, currentRow + 1, 9)); currentRow += 2; for (Wedstrijd w : serie.getWedstrijden()) { exportWedstrijd(sheet, w, currentRow); borderLeft(getCell(sheet, currentRow, 1)); borderLeft(getCell(sheet, currentRow + 1, 1)); borderRight(getCell(sheet, currentRow, 9)); borderRight(getCell(sheet, currentRow + 1, 9)); currentRow += 2; } } // Export trio ArrayList<Wedstrijd> trio = gw.getTriowedstrijden(); if (trio != null && trio.size() > 0) { updateCell(sheet, currentRow, 2, "Trio"); borderLeft(getCell(sheet, currentRow, 1)); borderLeft(getCell(sheet, currentRow + 1, 1)); borderRight(getCell(sheet, currentRow, 9)); borderRight(getCell(sheet, currentRow + 1, 9)); currentRow += 2; for (Wedstrijd w : trio) { exportWedstrijd(sheet, w, currentRow); borderLeft(getCell(sheet, currentRow, 1)); borderLeft(getCell(sheet, currentRow + 1, 1)); borderRight(getCell(sheet, currentRow, 9)); borderRight(getCell(sheet, currentRow + 1, 9)); currentRow += 2; } } currentRow--; for (int j = 2; j <= 8; j++) borderBottom(getCell(sheet, currentRow, j)); borderLeftBottom(getCell(sheet, currentRow, 1)); borderRightBottom(getCell(sheet, currentRow, 9)); } // Close input file file.close(); // Store Excel to new file String dirName = "R" + periode + "-" + ronde; new File(dirName).mkdirs(); String filename = dirName + File.separator + "Indeling " + periode + "-" + ronde + ".xlsx"; File outputFile = new File(filename); FileOutputStream outFile = new FileOutputStream(outputFile); workbook.write(outFile); // Close output file workbook.close(); outFile.close(); // And open it in the system editor Desktop.getDesktop().open(outputFile); return true; } catch (Exception e) { logger.log(Level.WARNING, "Error writing output: " + e.toString()); FoutMelding.melding("Fout bij opslaan Excel bestand: " + e.getMessage()); return false; } }
From source file:nl.rabobank.fixtures.aiep.testdata.traffic.LicensePlateDataLoader.java
public void fetchTestData() throws MalformedURLException, IOException { InputStream excelFileToRead = null; if (isTest) { excelFileToRead = getClass().getResourceAsStream("/aiep_testdata_fitnesse.xlsx"); } else {/*from w w w. j a v a 2 s . co m*/ excelFileToRead = new URL(TestDataLoader.TESTDATAPATH).openStream(); } XSSFWorkbook wb = new XSSFWorkbook(excelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rows = sheet.rowIterator(); boolean newRow = true; List<Row> myRowList = Lists.newArrayList(rows); if (this.testStubObject != null) { for (Row row : myRowList) { Iterator<Cell> cells = row.cellIterator(); List<Cell> myRowCellsList = Lists.newArrayList(cells); for (Cell cell : myRowCellsList) { cell.setCellType(Cell.CELL_TYPE_STRING); if (cell.getStringCellValue().equals(this.testStubObject) && cell.getColumnIndex() == 0) { newRow = false; } if (cell.getColumnIndex() == 1 && !newRow) { setLicenseplate(cell.getStringCellValue()); } if (cell.getColumnIndex() == 2 && !newRow) { setBrand(cell.getStringCellValue()); } if (cell.getColumnIndex() == 3 && !newRow) { setModel(cell.getStringCellValue()); } if (cell.getColumnIndex() == 4 && !newRow) { setType(cell.getStringCellValue()); } if (cell.getColumnIndex() == 5 && !newRow) { setGear(cell.getStringCellValue()); } if (cell.getColumnIndex() == 6 && !newRow) { setFuel(cell.getStringCellValue()); } if (cell.getColumnIndex() == 7 && !newRow) { setProductionYear(cell.getStringCellValue()); } if (cell.getColumnIndex() == 8 && !newRow) { setPurchaseYear(cell.getStringCellValue()); } if (cell.getColumnIndex() == 9 && this.testStubObject.startsWith("Motor") && !newRow) { setSideCarValue(cell.getStringCellValue()); } if (cell.getColumnIndex() == 10 && this.testStubObject.startsWith("Trailer")) { setChassisNumber(cell.getStringCellValue()); } if (cell.getColumnIndex() == 11 && !newRow) { setWeight(cell.getStringCellValue()); } if (cell.getColumnIndex() == 12 && !newRow) { setPurchaseValue(cell.getStringCellValue()); } if (cell.getColumnIndex() == 13 && !newRow) { setInsuredInventory(cell.getStringCellValue()); } if (cell.getColumnIndex() == 14 && !newRow) { setSameLicensePlateAsCar(cell.getStringCellValue()); } if (cell.getColumnIndex() == 15 && !newRow) { setCatalogueValue(cell.getStringCellValue()); } if (cell.getColumnIndex() == 16 && !newRow) { setBodyShape(cell.getStringCellValue()); } } if (!newRow) { break; } } } excelFileToRead.close(); }
From source file:no.hild1.excelsplit.ES.java
private void handleRow(Row row, int j, Row header, Map<String, XSSFWorkbook> header2types) throws IOException { int HEADER1 = 0, HEADER2 = 1, HEADER3 = 2, HEADER4 = 3; String header2forthisrow = text(row, HEADER2); XSSFWorkbook w = null; Sheet s = null;/* ww w. j a v a 2s . com*/ Row r = null; if (!header2types.containsKey(header2forthisrow)) { w = new XSSFWorkbook(); s = w.createSheet(); r = s.createRow(0); // insert "header" into "r" somehow header2types.put(header2forthisrow, w); } else { w = header2types.get(header2forthisrow); s = w.getSheetAt(0); } r = s.createRow(s.getLastRowNum() + 1); // insert data "row" into "r" somehow }
From source file:no.sintef.ict.splcatool.XLSXLib.java
License:Open Source License
public static String getCSV(File file) throws IOException { String filename = file.getAbsoluteFile().toString(); FileInputStream fis = new FileInputStream(filename); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); //System.out.println(" number of rows"+ sheet.getLastRowNum()); String csv = ""; int w = Integer.MAX_VALUE; outerloop: for (Row r : sheet) { int x = 0; XSSFRow row = (XSSFRow) r;//w w w .j av a 2 s .co m for (Cell c : row) { if (x >= w) break; XSSFCell cell = (XSSFCell) c; String v = ""; if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { v = cell.getStringCellValue(); } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { v = "" + (int) cell.getNumericCellValue(); } else { System.out.println("Unknown type " + cell.getCellType() + " " + cell.getRawValue()); System.exit(-1); } //System.out.println(v + ", " + x + ", " + w); if (x == 0 && v.equals("#end")) break outerloop; if (v.equals("#end")) { w = x; break; } csv += v + ";"; x++; } csv += "\n"; } //csv = csv.substring(0, csv.length()-1); //System.out.println(csv); return csv; }
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;/* w ww . ja v a 2s . com*/ 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:offishell.excel.Excel.java
License:MIT License
/** * <p>//from w w w. j a v a 2 s . c o m * Create {@link Excel} wrapper. * </p> * * @param path * @param book */ private Excel(Path path, XSSFWorkbook book) { this.path = path; this.book = book; this.excel = Locator.file(path); this.sheet = book.getSheetAt(0); this.baseStyle = book.createCellStyle(); this.dateStyle = book.createCellStyle(); CreationHelper helper = book.getCreationHelper(); DataFormat dateFormat = helper.createDataFormat(); Font font = book.createFont(); font.setFontName(" Medium"); font.setFontHeightInPoints((short) 10); baseStyle.setFont(font); baseStyle.setAlignment(HorizontalAlignment.CENTER); baseStyle.setVerticalAlignment(VerticalAlignment.CENTER); baseStyle.setShrinkToFit(true); baseStyle.setWrapText(true); dateStyle.cloneStyleFrom(baseStyle); dateStyle.setDataFormat(dateFormat.getFormat("yyyy/mm/dd")); }