List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
From source file:poi.hssf.usermodel.examples.HSSFReadWrite.java
License:Apache License
/** * Method main/* w w w .ja v a 2 s .c om*/ * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (args.length == 2) { if (args[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); } } else if (args.length == 3 && args[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:poi.hssf.view.SViewerPanel.java
License:Apache License
protected JComponent makeSheetView(HSSFSheet sheet) { JTable sheetView = new JTable(new SVTableModel(sheet)); sheetView.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); sheetView.setDefaultRenderer(HSSFCell.class, cellRenderer); if (allowEdits) sheetView.setDefaultEditor(HSSFCell.class, cellEditor); JTableHeader header = sheetView.getTableHeader(); //Dont allow column reordering header.setReorderingAllowed(false);//from w w w .j av a 2s .com //Only allow column resizing if editing is allowed header.setResizingAllowed(allowEdits); //Set the columns the correct size TableColumnModel columns = sheetView.getColumnModel(); for (int i = 0; i < columns.getColumnCount(); i++) { TableColumn column = columns.getColumn(i); int width = sheet.getColumnWidth(i); //256 is because the width is in 256ths of a character column.setPreferredWidth(width / 256 * magicCharFactor); } //Set the rows to the correct size int rows = sheet.getPhysicalNumberOfRows(); Insets insets = cellRenderer.getInsets(); //Need to include the insets in the calculation of the row height to use. int extraHeight = insets.bottom + insets.top; for (int i = 0; i < rows; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { sheetView.setRowHeight(i, (int) sheet.getDefaultRowHeightInPoints() + extraHeight); } else { sheetView.setRowHeight(i, (int) row.getHeightInPoints() + extraHeight); } } //Add the row header to the sheet SVRowHeader rowHeader = new SVRowHeader(sheet, sheetView, extraHeight); JScrollPane scroll = new JScrollPane(sheetView); scroll.setRowHeaderView(rowHeader); return scroll; }
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * Method main//ww w . j a va2 s .com * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } // wb.close(); } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); // wb.close(); } } else if (args.length == 3 && args[2].toLowerCase(Locale.ROOT).equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); // wb.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:projekt.servise.impl.ReadDataFromExcelServiceImpl.java
@Override public void getData() { try {/*w ww . j a v a2 s.c o m*/ POIFSFileSystem fs = null; try { fs = new POIFSFileSystem(new FileInputStream( "C:/Users/Lenovo/Documents/NetBeansProjects/SoftwareArchitectureProject-master/src/main/java/projekt/Koormused_test.xls")); } catch (FileNotFoundException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } HSSFWorkbook wb = null; try { wb = new HSSFWorkbook(fs); } catch (IOException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; int rows = sheet.getPhysicalNumberOfRows(); int cols = 0; int tmp = 0; for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } Connection conn = DriverManager.getConnection( "jdbc:postgresql://dev.vk.edu.ee:5432/GroupWork?currentSchema=project", "t131566", "t131566"); for (int r = 11; r < rows + 11; r++) { row = sheet.getRow(r); if (row != null) { PreparedStatement preparedStatementRoles; ResultSet resultRoles = null; try { preparedStatementRoles = conn.prepareStatement("SELECT * FROM project.role"); resultRoles = preparedStatementRoles.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (!resultRoles.next()) { conn.setAutoCommit(false); PreparedStatement preparedStatementRole = conn .prepareStatement("INSERT INTO project.role (rolename) VALUES (?)"); preparedStatementRole.setString(1, "teacher"); preparedStatementRole.addBatch(); preparedStatementRole.setString(1, "student"); preparedStatementRole.addBatch(); preparedStatementRole.setString(1, "admin"); preparedStatementRole.addBatch(); preparedStatementRole.executeBatch(); conn.commit(); } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } PreparedStatement preparedStatementLanguages = null; try { preparedStatementLanguages = conn .prepareStatement("SELECT * FROM project.language where name=?"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementLanguages.setString(1, StringUtils.trim(row.getCell(13).getStringCellValue())); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } ResultSet resultLanguages = null; try { resultLanguages = preparedStatementLanguages.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (!resultLanguages.next()) { PreparedStatement preparedStatementLanguage = conn .prepareStatement("INSERT INTO project.language (name) VALUES (?)"); preparedStatementLanguage.setString(1, StringUtils.trim(row.getCell(13).getStringCellValue())); preparedStatementLanguage.executeUpdate(); } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } PreparedStatement preparedStatementTeacher = null; try { preparedStatementTeacher = conn .prepareStatement("SELECT * FROM project.person where lastname=? and firstname=?"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } String firstname = row.getCell(12).getStringCellValue().split("\\.")[0]; String lastname = row.getCell(12).getStringCellValue().split("\\.")[1]; try { preparedStatementTeacher.setString(1, lastname); preparedStatementTeacher.setString(2, firstname); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } ResultSet resultTeacher = null; try { resultTeacher = preparedStatementTeacher.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (!resultTeacher.next()) { PreparedStatement preparedStatementNewTeacher = conn.prepareStatement( "INSERT INTO project.person (firstname,lastname,roleid) VALUES (?,?,?)"); PreparedStatement preparedStatementTeacherRole = conn .prepareStatement("SELECT * FROM project.role where rolename='teacher'"); int roleId = 0; ResultSet resultTeacherRole = preparedStatementTeacherRole.executeQuery(); if (resultTeacherRole.next()) { roleId = resultTeacherRole.getInt(1); } preparedStatementNewTeacher.setString(1, firstname); preparedStatementNewTeacher.setString(2, lastname); preparedStatementNewTeacher.setInt(3, roleId); preparedStatementNewTeacher.executeUpdate(); } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } String courseCode = row.getCell(2).getStringCellValue(); Course course = null; try { course = courseService.getByCode(courseCode); } catch (Exception e) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, e); } int courseId = 0; if (course == null) { PreparedStatement preparedStatementCourse = null; try { preparedStatementCourse = conn.prepareStatement( "INSERT INTO project.course (code,name,lectureship) VALUES (?,?,?)"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementCourse.setString(1, row.getCell(2).getStringCellValue()); preparedStatementCourse.setString(2, row.getCell(3).getStringCellValue()); preparedStatementCourse.setString(3, row.getCell(1).getStringCellValue()); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementCourse.executeUpdate(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } } else { courseId = course.getId(); } if (courseId != 0) { PreparedStatement preparedStatementLanguageId = null; try { preparedStatementLanguageId = conn .prepareStatement("SELECT * FROM project.language where name=?"); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { preparedStatementLanguageId.setString(1, row.getCell(13).getStringCellValue()); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } int languageId = 0; ResultSet resultLanguageId = null; try { resultLanguageId = preparedStatementLanguageId.executeQuery(); } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } try { if (resultLanguageId.next()) { languageId = resultLanguageId.getInt(1); PreparedStatement preparedStatementTeacherId = conn.prepareStatement( "SELECT * FROM project.person where firstname=? and lastname=?"); preparedStatementTeacherId.setString(1, firstname); preparedStatementTeacherId.setString(2, lastname); int teacherId = 0; ResultSet resultTeacherId = preparedStatementTeacherId.executeQuery(); if (resultTeacherId.next()) { teacherId = resultTeacherId.getInt(1); PreparedStatement preparedStatementExistingCourseData = conn .prepareStatement("SELECT * FROM project.coursedata where courseid=? " + "and practice=? and excercise=? and lecture=? and languageid=? and teacherid=?"); preparedStatementExistingCourseData.setInt(1, courseId); int practice = (int) (row.getCell(6) != null ? row.getCell(6).getNumericCellValue() : 0); int lecture = (int) (row.getCell(5) != null ? row.getCell(5).getNumericCellValue() : 0); int exercise = (int) (row.getCell(7) != null ? row.getCell(7).getNumericCellValue() : 0); preparedStatementExistingCourseData.setInt(2, practice); preparedStatementExistingCourseData.setInt(3, exercise); preparedStatementExistingCourseData.setInt(4, lecture); preparedStatementExistingCourseData.setInt(5, languageId); preparedStatementExistingCourseData.setInt(6, teacherId); ResultSet resultExistingCourseData = preparedStatementExistingCourseData .executeQuery(); if (!resultExistingCourseData.next()) { PreparedStatement preparedStatementCourseData = conn .prepareStatement("INSERT INTO project.coursedata " + "(courseid,practice,lecture,excercise,lecturesperweek,languageid,semester,teacherid) " + "VALUES (?,?,?,?,?,?,?,?)"); preparedStatementCourseData.setInt(1, courseId); preparedStatementCourseData.setInt(2, practice); preparedStatementCourseData.setInt(3, lecture); preparedStatementCourseData.setInt(4, exercise); preparedStatementCourseData.setFloat(5, (float) row.getCell(9).getNumericCellValue()); preparedStatementCourseData.setInt(6, (int) languageId); preparedStatementCourseData.setString(7, row.getCell(14).getStringCellValue()); preparedStatementCourseData.setInt(8, (int) teacherId); preparedStatementCourseData.executeUpdate(); int courseDataId = 0; PreparedStatement preparedStatementLastCourseData = conn .prepareStatement("SELECT id FROM project.coursedata where " + "courseid=? and practice=? and lecture=? and excercise=? and lecturesperweek=? and languageid=? and semester=? and teacherid=?"); preparedStatementLastCourseData.setInt(1, courseId); preparedStatementLastCourseData.setInt(2, practice); preparedStatementLastCourseData.setInt(3, lecture); preparedStatementLastCourseData.setInt(4, exercise); preparedStatementLastCourseData.setFloat(5, (float) row.getCell(9).getNumericCellValue()); preparedStatementLastCourseData.setInt(6, (int) languageId); preparedStatementLastCourseData.setString(7, row.getCell(14).getStringCellValue()); preparedStatementLastCourseData.setInt(8, (int) teacherId); ResultSet resultLastCourseData = preparedStatementLastCourseData .executeQuery(); if (resultLastCourseData.next()) { courseDataId = resultLastCourseData.getInt(1); String[] groupCodes = row.getCell(4).getStringCellValue().split(" "); for (int i = 0; i < groupCodes.length; i++) { PreparedStatement preparedStatementGroupCode = conn .prepareStatement( "SELECT * FROM project.group where groupcode=?"); preparedStatementGroupCode.setString(1, groupCodes[i]); ResultSet resultGroupCode = preparedStatementGroupCode .executeQuery(); if (!resultGroupCode.next()) { PreparedStatement preparedStatementGroup = conn .prepareStatement( "INSERT INTO project.group (groupcode) VALUES (?)"); preparedStatementGroup.setString(1, groupCodes[i]); preparedStatementGroup.executeUpdate(); PreparedStatement preparedStatementLastGroup = conn .prepareStatement( "SELECT id FROM project.group where groupcode=?"); preparedStatementLastGroup.setString(1, groupCodes[i]); int groupId = 0; ResultSet resultLastGroup = preparedStatementLastGroup .executeQuery(); if (resultLastGroup.next()) { groupId = resultLastGroup.getInt(1); PreparedStatement preparedStatementGroupCourseData = conn .prepareStatement( "INSERT INTO project.groupcoursedata (groupid,goursedataid) VALUES (?,?)"); preparedStatementGroupCourseData.setInt(1, groupId); preparedStatementGroupCourseData.setInt(2, courseDataId); preparedStatementGroupCourseData.executeUpdate(); } } } } } } } } catch (SQLException ex) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ex); } } } } } catch (SQLException ioe) { Logger.getLogger(ReadDataFromExcelServiceImpl.class.getName()).log(Level.SEVERE, null, ioe); } }
From source file:QuickExcel.ExcelData.java
public void loadData() { try {//from ww w . j a va 2 s. c o m File file = new File("excel.xls"); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) { cols = tmp; } } } for (int r = 0; r < rows; r++) { row = sheet.getRow(r); if (row != null) { for (int c = 0; c < 2; c++) { cell = row.getCell(c); if (c == 0) { if (cell != null) { addQuestion(cell.toString()); } } else { if (cell != null) { addAnswer(cell.toString()); } } } } } } catch (Exception ioe) { ioe.printStackTrace(); } }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsFile(String fileName) { try {//w w w.ja v a2s .c om HSSFWorkbook wb = HSSFReadWrite.readxlsFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:swift.selenium.WebHelper.java
License:Open Source License
public static String ReadFromExcel(String controlValue) throws IOException { HSSFSheet uniqueNumberSheet = null; String uniqueTestcaseID = ""; HashMap<String, Object> uniqueValuesHashMap = null; //HSSFRow uniqueRow = null; String uniqueNumber = null;//from w w w . java 2 s . co m try { uniqueNumberSheet = ExcelUtility.GetSheet(Automation.configHashMap.get("TRANSACTION_INFO").toString(), "DataSheet"); uniqueValuesHashMap = getValueFromHashMap(uniqueNumberSheet); int rowCount = uniqueNumberSheet.getPhysicalNumberOfRows(); for (int rIndex = 1; rIndex < rowCount; rIndex++) { //uniqueRow = uniqueNumberSheet.getRow(rIndex); if (controlValue.equals("")) { uniqueTestcaseID = getCellData("TestCaseID", uniqueNumberSheet, rIndex, uniqueValuesHashMap); } else { uniqueTestcaseID = controlValue; } //String uniqueTransactionType = getCellData("TransactionType", uniqueNumberSheet, rIndex, uniqueValuesHashMap); if (TransactionMapping.testCaseID.toString().equals(uniqueTestcaseID))//&& MainController.controllerTransactionType.toString().equals(uniqueTransactionType) { return uniqueNumber = getCellData(columnName, uniqueNumberSheet, rIndex, uniqueValuesHashMap); } } } catch (Exception e) { TransactionMapping.pauseFun(e.getMessage() + " from ReadFromExcel Function"); } return uniqueNumber; }
From source file:swift.selenium.WebHelper.java
License:Open Source License
public static Boolean writeToExcel(String ctrlValue, WebElement webElement, String controlId, String controlType, String controlName, String rowNo, String colNo) throws Exception { try {//ww w .ja v a 2 s . c o m FileInputStream in = new FileInputStream(Automation.configHashMap.get("TRANSACTION_INFO").toString()); HSSFWorkbook uniqueWB = new HSSFWorkbook(in); HSSFSheet uniqueNumberSheet = uniqueWB.getSheet("DataSheet"); HashMap<String, Object> uniqueValuesHashMap = getValueFromHashMap(uniqueNumberSheet); HSSFRow uniqueRow = null; int rowNum = uniqueNumberSheet.getPhysicalNumberOfRows(); for (int rIndex = 0; rIndex < rowNum; rIndex++) { uniqueRow = uniqueNumberSheet.getRow(rIndex); String uniqueTestcaseID = getCellData("TestCaseID", uniqueNumberSheet, rIndex, uniqueValuesHashMap); if (TransactionMapping.testCaseID.toString().equals(uniqueTestcaseID))//&& MainController.controllerTransactionType.toString().equals(uniqueTransactionType) { uniqueRow = uniqueNumberSheet.getRow(rIndex); break; } else if (rIndex == rowNum - 1) { uniqueRow = uniqueNumberSheet.createRow(rowNum); } } if (controlType.equalsIgnoreCase("WebTable")) { //TM:commented and updated the following 'if' statement //if(Integer.valueOf(rowNo).equals(null)||Integer.valueOf(colNo).equals(null)) if (StringUtils.isBlank(rowNo) || StringUtils.isBlank(colNo)) { TransactionMapping.pauseFun("RowNumber or ColumnNumber is Missing"); return false; } else { //ctrlValue = Automation.selenium.getTable(controlName+"."+ Integer.parseInt(rowNo) +"." + Integer.parseInt(colNo)); ctrlValue = Automation.driver .findElement(By.xpath(controlName + "/tr[" + rowNo + "]/td[" + colNo + "]")).getText(); } } else if (controlType.equalsIgnoreCase("ListBox") || controlType.equalsIgnoreCase("WebList")) { ctrlValue = new Select(webElement).getFirstSelectedOption().toString(); } else if (controlType.equalsIgnoreCase("DB")) { System.out.println(ctrlValue); } else { ctrlValue = webElement.getText(); } HSSFCell uniqueTestCaseID = uniqueRow .createCell(Integer.parseInt(uniqueValuesHashMap.get("TestCaseID").toString())); HSSFCell uniqueCell = uniqueRow .createCell(Integer.parseInt(uniqueValuesHashMap.get(columnName).toString())); uniqueTestCaseID.setCellValue(TransactionMapping.testCaseID.toString()); uniqueCell.setCellValue(ctrlValue); in.close(); FileOutputStream out = new FileOutputStream( Automation.configHashMap.get("TRANSACTION_INFO").toString()); uniqueWB.write(out); } catch (FileNotFoundException e) { throw e; } catch (Exception e) { throw e; } return true; }
From source file:SwiftSeleniumWeb.WebHelper.java
License:Open Source License
public static String ReadFromExcel(String controlValue) throws IOException { HSSFSheet uniqueNumberSheet = null; String uniqueTestcaseID = ""; HashMap<String, Object> uniqueValuesHashMap = null; //HSSFRow uniqueRow = null; String uniqueNumber = null;//from www .j a va2s . c om try { uniqueNumberSheet = ExcelUtility.GetSheet(Automation.configHashMap.get("TRANSACTION_INFO").toString(), "DataSheet"); uniqueValuesHashMap = getValueFromHashMap(uniqueNumberSheet); int rowCount = uniqueNumberSheet.getPhysicalNumberOfRows(); for (int rIndex = 1; rIndex < rowCount; rIndex++) { //uniqueRow = uniqueNumberSheet.getRow(rIndex); if (controlValue.equals("")) { uniqueTestcaseID = getCellData("TestCaseID", uniqueNumberSheet, rIndex, uniqueValuesHashMap); } else { uniqueTestcaseID = controlValue; } //String uniqueTransactionType = getCellData("TransactionType", uniqueNumberSheet, rIndex, uniqueValuesHashMap); if (MainController.controllerTestCaseID.toString().equals(uniqueTestcaseID))//&& MainController.controllerTransactionType.toString().equals(uniqueTransactionType) { return uniqueNumber = getCellData(columnName, uniqueNumberSheet, rIndex, uniqueValuesHashMap); } } } catch (Exception e) { MainController.pauseFun(e.getMessage() + " from ReadFromExcel Function"); } return uniqueNumber; }
From source file:SwiftSeleniumWeb.WebHelper.java
License:Open Source License
public static Boolean writeToExcel(String ctrlValue, WebElement webElement, String controlId, String controlType, String controlName, String rowNo, String colNo) throws Exception { try {//www .j a va2 s .c om FileInputStream in = new FileInputStream(Automation.configHashMap.get("TRANSACTION_INFO").toString()); HSSFWorkbook uniqueWB = new HSSFWorkbook(in); HSSFSheet uniqueNumberSheet = uniqueWB.getSheet("DataSheet"); HashMap<String, Object> uniqueValuesHashMap = getValueFromHashMap(uniqueNumberSheet); HSSFRow uniqueRow = null; int rowNum = uniqueNumberSheet.getPhysicalNumberOfRows(); for (int rIndex = 0; rIndex < rowNum; rIndex++) { uniqueRow = uniqueNumberSheet.getRow(rIndex); String uniqueTestcaseID = getCellData("TestCaseID", uniqueNumberSheet, rIndex, uniqueValuesHashMap); if (MainController.controllerTestCaseID.toString().equals(uniqueTestcaseID))//&& MainController.controllerTransactionType.toString().equals(uniqueTransactionType) { uniqueRow = uniqueNumberSheet.getRow(rIndex); break; } else if (rIndex == rowNum - 1) { uniqueRow = uniqueNumberSheet.createRow(rowNum); } } if (controlType.equalsIgnoreCase("WebTable")) { //TM:commented and updated the following 'if' statement //if(Integer.valueOf(rowNo).equals(null)||Integer.valueOf(colNo).equals(null)) if (StringUtils.isBlank(rowNo) || StringUtils.isBlank(colNo)) { MainController.pauseFun("RowNumber or ColumnNumber is Missing"); return false; } else { //ctrlValue = Automation.selenium.getTable(controlName+"."+ Integer.parseInt(rowNo) +"." + Integer.parseInt(colNo)); ctrlValue = Automation.driver .findElement(By.xpath(controlName + "/tr[" + rowNo + "]/td[" + colNo + "]")).getText(); } } else if (controlType.equalsIgnoreCase("ListBox") || controlType.equalsIgnoreCase("WebList")) { ctrlValue = new Select(webElement).getFirstSelectedOption().toString(); } else if (controlType.equalsIgnoreCase("DB")) { System.out.println(ctrlValue); } else { ctrlValue = webElement.getText(); } HSSFCell uniqueTestCaseID = uniqueRow .createCell(Integer.parseInt(uniqueValuesHashMap.get("TestCaseID").toString())); HSSFCell uniqueCell = uniqueRow .createCell(Integer.parseInt(uniqueValuesHashMap.get(columnName).toString())); uniqueTestCaseID.setCellValue(MainController.controllerTestCaseID.toString()); uniqueCell.setCellValue(ctrlValue); in.close(); FileOutputStream out = new FileOutputStream( Automation.configHashMap.get("TRANSACTION_INFO").toString()); uniqueWB.write(out); } catch (FileNotFoundException e) { throw e; } catch (Exception e) { throw e; } return true; }