List of usage examples for org.apache.poi.ss.usermodel DataFormatter formatCellValue
public String formatCellValue(Cell cell)
Returns the formatted value of a cell as a String regardless of the cell type.
From source file:org.wso2.security.tool.adapter.ExcelInputAdapter.java
License:Open Source License
/** * Converts the data in the files with .xlsx extension to the JSON format. * A workbook is created from the the excel file (.xlsx) and while iterating through the sheets in the workbook; * the data is read and set in to a JSONObject. The JSONObject returned by the method contains an array of * row objects corresponding to each row in the workbook. A row object contains values of each cell in a given row, * with key values starting from letter 'A'. * * @param dataFilePath The path where the data file uploaded is saved. * @return returns the JSON object that contains all the data in the .xlsx file. * @throws FeedbackToolException If the .xlsx file is not found in the given path or due to an error in * parsing the data in the data file. */// w ww. j a v a 2 s . com @Override public JSONObject convert(String dataFilePath) throws FeedbackToolException { // JSONObject to hold the array of row objects JSONObject dataJSONObject = new JSONObject(); try { Workbook workbook = WorkbookFactory.create(new File(dataFilePath)); logInfo = "Workbook has " + workbook.getNumberOfSheets() + " sheets"; log.info(logInfo); Iterator<Sheet> sheetIterator = workbook.sheetIterator(); // JSONArray to hold all the row objects JSONArray rowsJSONArray = new JSONArray(); while (sheetIterator.hasNext()) { Sheet sheet = sheetIterator.next(); logInfo = "Sheet: " + sheet.getSheetName() + " has " + sheet.getNumMergedRegions() + " merged regions"; log.info(logInfo); DataFormatter dataFormatter = new DataFormatter(); logInfo = "Iterating over Rows and Columns using for-each loop"; log.info(logInfo); for (Row row : sheet) { // JSONObject to hold the data in the cells of a given row JSONObject rowJSONObject = new JSONObject(); char keyLetter = 'A'; for (Cell cell : row) { String cellValue = dataFormatter.formatCellValue(cell); rowJSONObject.put(keyLetter, cellValue); ++keyLetter; } rowsJSONArray.add(rowJSONObject); } } dataJSONObject.put(Constants.JSON_DATA_OBJECT, rowsJSONArray); } catch (InvalidFormatException e) { throw new FeedbackToolException("Error in parsing the data file uploaded", e); } catch (IOException e) { throw new FeedbackToolException("Data file was not found in the specified location", e); } return dataJSONObject; }
From source file:PlacementFormatter.Controller.FileController.java
/** * * @param filepath/* w w w.ja v a 2 s . c om*/ * @throws IOException */ public static void formatFile(PlacementFile filepath) throws IOException { //Creates instance for reading xls, workbook,sheet, FileInputStream InputStream ExcelFileToRead = new FileInputStream(filepath.getFilepath()); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFSheet sheet = wb.getSheetAt(0); //Creates instances for writing output to xls format. String sheetName = "Import";//name of sheet HSSFWorkbook outWorkbook = new HSSFWorkbook(); HSSFSheet outSheet = outWorkbook.createSheet(sheetName); //Variables to hold the data without ' and r for the row counter String cellReplace; int r = 0; //Outer and Inner loop for iterating through the workbook for (Row row : sheet) { HSSFRow outRow = outSheet.createRow(r); for (int cn = 0; cn < row.getLastCellNum(); cn++) { // If the cell is missing from the file, generate a blank one // (Works by specifying a MissingCellPolicy) org.apache.poi.ss.usermodel.Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK); //NumberToTextConverter nc = new NumberToTextConverter.toText(); //or DataFormatter df = new DataFormatter(); String dataCell = df.formatCellValue(cell); //Replaces the single dash located in the data cellReplace = dataCell.replace("'", ""); HSSFCell outCell = outRow.createCell(cn); outCell.setCellValue(cellReplace); //System.out.println("CELL: " + cn + " --> " + cellReplace); } //ends inner loop r++; } //ends outer loop FileOutputStream fileOut = new FileOutputStream(filepath.getFilepath().replace("xlsx", "xls")); outWorkbook.write(fileOut); fileOut.flush(); System.out.print("File Exported Correclty"); }
From source file:ro.dabuno.office.integration.Xlsx2Word.java
public static void main(String[] args) throws Exception { log.info("starting app"); // Workbook wb = new XSSFWorkbook(new FileInputStream(args[0])); Workbook wb = new XSSFWorkbook(new FileInputStream("office-files/Input.xlsx")); DataFormatter formatter = new DataFormatter(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i);/*from w w w .ja v a 2 s . co m*/ System.out.println(wb.getSheetName(i)); int j = 4; for (Row row : sheet) { System.out.println("rownum: " + row.getRowNum()); for (Cell cell : row) { CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex()); System.out.print(cellRef.formatAsString()); System.out.print(" - "); // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc) String text = formatter.formatCellValue(cell); System.out.println(text); System.out.println("------------"); // Alternatively, get the value and format it yourself switch (cell.getCellTypeEnum()) { case STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { System.out.println(cell.getDateCellValue()); } else { System.out.print(cellRef.formatAsString()); System.out.print(" - "); System.out.println((long) cell.getNumericCellValue()); } break; case BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case FORMULA: System.out.println(cell.getCellFormula()); break; case BLANK: System.out.println(); break; default: System.out.println(); } } j--; if (j == 0) { break; } } } XWPFDocument doc = new XWPFDocument(); XWPFParagraph p0 = doc.createParagraph(); XWPFRun r0 = p0.createRun(); r0.setBold(false); r0.setText("Domnule"); XWPFRun r00 = p0.createRun(); r00.setBold(true); r00.setText(" Ionescu Ion"); FileOutputStream out = new FileOutputStream("out/xlsx2word.docx"); doc.write(out); out.close(); }
From source file:rpt.GUI.ProgramStrategist.CyclePlans.CyclePlansController.java
public void importButtonClicked(ActionEvent event) throws IOException { //List with all variants read from the imported Excel file List<TableVariant> variants = new ArrayList(); //Create File Chooser window FileChooser fileChooser = new FileChooser(); fileChooser.setTitle("Import Excel File"); //Set filter to allow only Excel files ExtensionFilter filter = new ExtensionFilter("Excel Files", "*.xls", "*.xlsx"); fileChooser.getExtensionFilters().addAll(filter); //Show File Selector File selectedFile = fileChooser.showOpenDialog(null); //import Excel file if a file has been selected, if not, do nothing //based on good example on: //http://www.codejava.net/coding/how-to-read-excel-files-in-java-using-apache-poi if (selectedFile != null) { //open dialog box and show available sheets in the file // the dialog box will then process the file and add data into the table FileInputStream inputStream = new FileInputStream(new File(selectedFile.getPath())); Workbook workbook;//from w ww . j a v a2s . c om if (selectedFile.getPath().endsWith("xlsx")) { workbook = new XSSFWorkbook(inputStream); } else { workbook = new HSSFWorkbook(inputStream); } //Use Sheet iterator to extract all sheet names Iterator<Sheet> sheetIterator = workbook.sheetIterator(); //Iterate over all sheets and populate a checkboxfield and let user select on of the sheets sheetsInFile = new ArrayList(); //reset just in case while (sheetIterator.hasNext()) { String nextSheet = sheetIterator.next().getSheetName(); sheetsInFile.add(nextSheet); //add found sheet into list of available sheets. } selectedSheet = null; //Show dialog box presenting all the available sheets for the user to select from Stage stage; Parent root; stage = new Stage(); root = FXMLLoader .load(getClass().getResource("/rpt/GUI/ProgramStrategist/CyclePlans/dialogSelectSheet.fxml")); stage.setScene(new Scene(root)); stage.setTitle("Select Sheet"); stage.initModality(Modality.APPLICATION_MODAL); stage.showAndWait(); // pause until the user has selected a sheet // If user has selected sheet to read, show next dialog box allowing // user to set the name of the imported cycle plan if (selectedSheet != null) { //preset the file name to the file name part before the . sign importedCyclePlanName = selectedFile.getName().split("\\.")[0]; //Create dialog stage = new Stage(); root = FXMLLoader .load(getClass().getResource("/rpt/GUI/ProgramStrategist/CyclePlans/dialogSetName.fxml")); stage.setScene(new Scene(root)); stage.setTitle("Set Cycle Plan Name"); stage.initModality(Modality.APPLICATION_MODAL); stage.showAndWait(); // pause until the user has selected a sheet } // only continue if a sheet was selected (=abort if used presses cancel) // AND if the cycle plan name is unique, i.e. not already imported if (selectedSheet != null && importedCyclePlanName != null) { // Add new cycleplan into Database try { Statement statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); query = "INSERT INTO CYCLEPLANS (Name, Version) VALUES (\'" + importedCyclePlanName + "\', 1)"; statement.executeUpdate(query); //set sheet to the sheet selected Sheet firstSheet = workbook.getSheet(selectedSheet); Iterator<Row> rowIterator = firstSheet.iterator(); //find first row //TODO //set first row keyword into application settings Boolean firstRowFound = false; Cell nextCell; Iterator<Cell> cellIterator; //dictionary using HashMaps Map<Integer, String> dictionary = new HashMap<Integer, String>(); do { Row nextRow = rowIterator.next(); cellIterator = nextRow.cellIterator(); nextCell = cellIterator.next(); if (getCellValue(nextCell) != null) {//blank cells return null if (getCellValue(nextCell).equals("Plant")) { //dictionary.put(nextCell.getColumnIndex(), (String) getCellValue(nextCell)); firstRowFound = true; } } } while (!firstRowFound && rowIterator.hasNext()); //First row is now found, loop through entire row and build a while (cellIterator.hasNext()) { if (getCellValue(nextCell) != null) { //blank cells return null dictionary.put(nextCell.getColumnIndex(), getCellValue(nextCell).toString()); } nextCell = cellIterator.next(); } // Since Excel stores numbers as floats and as an example 1 will turn into 1.0 this will be a problem // Mainly because the cycle plan tends to mix text and numbers in Excel. // Create a Dataformatter which will be used to solve this DataFormatter fmt = new DataFormatter(); //loop through all rows in the file while (rowIterator.hasNext()) { Row nextRow = rowIterator.next(); cellIterator = nextRow.cellIterator(); TableVariant aVariant = new TableVariant(); //loop through all columns in the row while (cellIterator.hasNext()) { nextCell = cellIterator.next(); int columnIndex = nextCell.getColumnIndex(); if (getCellValue(nextCell) != null) { aVariant.setValue(dictionary.get(nextCell.getColumnIndex()), fmt.formatCellValue(nextCell)); //aVariant.setValue(dictionary.get(nextCell.getColumnIndex()), getCellValue(nextCell).toString()); } else { } } variants.add(aVariant); } } catch (Exception e) { System.err.println("CyclePlansController line 343: " + e.getMessage()); } //remove current selection and add the new variants data.clear(); int index = 1; for (TableVariant variant : variants) { TableVariant entry = new TableVariant(variant.getPlant(), variant.getPlatform(), variant.getVehicle(), variant.getPropulsion(), variant.getDenomination(), variant.getFuel(), variant.getEngineFamily(), variant.getGeneration(), variant.getEngineName(), variant.getEngineCode(), variant.getDisplacement(), variant.getEnginePower(), variant.getElMotorPower(), variant.getTorque(), variant.getTorqueOverBoost(), variant.getGearboxType(), variant.getGears(), variant.getGearbox(), variant.getDriveline(), variant.getTransmissionCode(), variant.getCertGroup(), variant.getEmissionClass(), variant.getStartOfProd(), variant.getEndOfProd()); add(entry); index++; try { Statement statement = RPT.conn.createStatement(); statement.setQueryTimeout(30); String variantID = variant.getVehicle() + variant.getEngineCode() + variant.getTransmissionCode() + variant.getEmissionClass() + variant.getStartOfProd(); query = "SELECT COUNT(VariantID) FROM VARIANTS WHERE VariantID = '" + variantID + "'"; ResultSet rs = statement.executeQuery(query); //check count of previous query, 0 = new variant, 1 = it already exists Integer count = rs.getInt(1); // add variant if it does not exist if (count == 0) { // entry did not existbefore query = "INSERT INTO VARIANTS (" + "Plant, Platform, Vehicle, Propulsion, Denomination, Fuel, EngineFamily, Generation, EngineCode, Displacement, " + "EnginePower, ElMotorPower, Torque, TorqueOverBoost, GearboxType, Gears, Gearbox, Driveline, TransmissionCode, " + "CertGroup, EmissionClass, StartOfProd, EndOfProd, VariantID" + ")" + "" + " VALUES (\'" + variant.getPlant() + "\', \'" + variant.getPlatform() + "\', \'" + variant.getVehicle() + "\', \'" + variant.getPropulsion() + "\', \'" + variant.getDenomination() + "\', \'" + variant.getFuel() + "\', \'" + variant.getEngineFamily() + "\', \'" + variant.getGeneration() + "\', \'" + variant.getEngineCode() + "\', \'" + variant.getDisplacement() + "\', \'" + variant.getEnginePower() + "\', \'" + variant.getElMotorPower() + "\', \'" + variant.getTorque() + "\', \'" + variant.getTorqueOverBoost() + "\', \'" + variant.getGearboxType() + "\', \'" + variant.getGears() + "', '" + variant.getGearbox() + "\', \'" + variant.getDriveline() + "\', \'" + variant.getTransmissionCode() + "\', \'" + variant.getCertGroup() + "\', \'" + variant.getEmissionClass() + "\', \'" + variant.getStartOfProd() + "\', \'" + variant.getEndOfProd() + "\', \'" + variantID + "\')"; statement.executeUpdate(query); } // Add relation between cycle plan and variant query = "INSERT INTO VariantBelongsToCyclePlan (VariantID, CyclePlanID) VALUES (\'" + variantID + "\', \'" + importedCyclePlanName + "\')"; statement.executeUpdate(query); } catch (Exception e) { System.out.println("Query: " + query); System.err.println("CyclePlansController line 394: " + e.getMessage()); } } cyclePlanList.add(importedCyclePlanName); cyclePlanSelector.getSelectionModel().select(importedCyclePlanName); } // end of reading file after user has selected sheet and name of cycle plan inputStream.close(); } }
From source file:swift.selenium.Automation.java
License:Open Source License
/** Loads the Config sheet into HashMap **/ public static void LoadConfigData(String configFile) throws IOException { try {/*w w w . j a va 2 s . com*/ Date initialDate = new Date(); String strInitialDate = dtFormat.format(initialDate); TransactionMapping.report.setFromDate(strInitialDate); DataFormatter format = new DataFormatter(); //String projectPath = System.getProperty("user.dir"); //String configPath = projectPath + "\\CommonResources\\Config.xls"; HSSFSheet configSheet = ExcelUtility.GetSheet(configFile, "Config"); for (int rowIndex = 1; rowIndex <= configSheet.getLastRowNum(); rowIndex++) { HSSFRow rowActual = configSheet.getRow(rowIndex); String parameterName = format.formatCellValue(rowActual.getCell(0)); String value = format.formatCellValue(rowActual.getCell(1)); // Following 'if' is replacement of above, checks if parameterName and value are neither null nor Blank if (StringUtils.isNotBlank(parameterName) || StringUtils.isNotBlank(value)) { configHashMap.put(parameterName, value); } } if (configHashMap.containsKey("MOBILETEST")) { if (configHashMap.get("MOBILETEST").toString().equalsIgnoreCase("true")) { String appiumconfigPath = configHashMap.get("APPIUM_CONFIG_PATH").toString(); HSSFSheet appiumconfigSheet = ExcelUtility.GetSheet(appiumconfigPath, "Config"); int arowCount = appiumconfigSheet.getLastRowNum() + 1; for (int rowIndex = 1; rowIndex < arowCount; rowIndex++) { HSSFRow rowActual = appiumconfigSheet.getRow(rowIndex); String parameterName = format.formatCellValue(rowActual.getCell(0)); String value = format.formatCellValue(rowActual.getCell(1)); if (StringUtils.isNotBlank(parameterName) || StringUtils.isNotBlank(value)) { appiumConfigMap.put(parameterName, value); } } } } } catch (NullPointerException npe) { TransactionMapping.pauseFun("Null Values Found in Config Sheet"); npe.printStackTrace(); } catch (Exception e) { e.printStackTrace(); TransactionMapping.pauseFun(e.getMessage() + " From LoadConfig Function"); } }
From source file:swift.selenium.ExcelUtility.java
License:Open Source License
public static Reporter CompareExcel(HSSFSheet actualSheet, HSSFSheet expectedSheet, List<String> columns, List<String> columnsData, String testCaseID, String transactionType, int actualSheetRowCount) throws IOException { boolean isrowFound = false; int expSheetRowCount = getRowCount(expectedSheet); //expectedSheet.getPhysicalNumberOfRows(); Reporter report = new Reporter(); report.setReport(report);/*from w w w.jav a 2 s. c o m*/ int passCount = 0, failCount = 0, colCount = 0, finalRowCount = 0; //TM-28/09/2015: if expected sheet row count is greater than actual sheet row count than comparison should be on basis of expected sheet row count else vice-versa if (expSheetRowCount >= actualSheetRowCount) finalRowCount = expSheetRowCount; else finalRowCount = actualSheetRowCount; for (int rowIndex = firstRow; rowIndex < firstRow + finalRowCount; rowIndex++) { passCount = 0; failCount = 0; int currentRow = ++WebVerification.currentRowIndex; HSSFRow actualRow = actualSheet.getRow(currentRow); HSSFRow expectedRow = expectedSheet.getRow(rowIndex); //TM-28/09/2015: if actual and expected sheet row count does not match then break after following the steps in this code block. if (actualRow == null || expectedRow == null) { status.clear(); report.strStatus = "FAIL"; report.setStrStatus(report.strStatus); rowStatus.add(report.strStatus); failCount += 1; //TM-28/09/2015: if expected sheet row count is greater than actual sheet row count else vice-versa if (actualRow == null) report.strActualValue = "Expected No. of rows are greater than Actual No. of rows."; else report.strActualValue = "Actual No. of rows are greater than Expected No. of rows."; actualValue = new ArrayList<String>(); actualValue.add(report.strActualValue); actualRows.add(actualValue); PassCount.add(passCount); FailCount.add(failCount); report.setReport(report); break; } if (actualRow.getCell(0).toString().equals(expectedRow.getCell(0).toString()) && actualRow.getCell(1).toString().equals(expectedRow.getCell(1).toString())) { isrowFound = true; actualValue = new ArrayList<String>(); //TM:6/08/15-This is unreachable code /*if(actualRow == null || expectedRow == null) { break; }*/ colCount = expectedRow.getPhysicalNumberOfCells(); for (int columnIndex = 3; columnIndex < colCount; columnIndex++) { HSSFCell actualCell = actualRow.getCell(columnIndex); DataFormatter fmt = new DataFormatter(); HSSFCell expectedCell = expectedRow.getCell(columnIndex); //TM: commented the code to find replacement of continue /*if(actualCell == null || expectedCell == null) { continue; }*/ //TM: Following 'if' is replacement of the above if (actualCell != null || expectedCell != null) { String expectedValue = fmt.formatCellValue(expectedCell); if (!actualCell.toString().equalsIgnoreCase(expectedValue)) { report.strStatus = "FAIL"; report.setStrStatus(report.strStatus); failCount += 1; report.strActualValue = "FAIL |" + expectedValue + "|" + actualCell.toString(); } else { passCount += 1; report.strStatus = "PASS"; report.setStrStatus(report.strStatus); report.strActualValue = actualCell.toString(); //System.out.println(actualCell.toString()); } status.add(report.strStatus); actualValue.add(report.strActualValue); } } if (status.contains("FAIL")) { report.strStatus = "FAIL"; } else { report.strStatus = "PASS"; } status.clear(); rowStatus.add(report.strStatus); PassCount.add(passCount); FailCount.add(failCount); actualRows.add(actualValue); report.setReport(report); } else if (isrowFound == false) { continue; /*MainController.pauseFun("No Rows Found For Comparision"); break;*/ } } if (rowStatus.contains("FAIL")) { report.strStatus = "FAIL"; } WriteToDetailResults(testCaseID, transactionType, columns, actualRows, passCount, failCount, expSheetRowCount, colCount, report, rowStatus); PassCount.clear(); FailCount.clear(); return report; }
From source file:swift.selenium.ExcelUtility.java
License:Open Source License
public static Reporter CompareExcelRowWise(HSSFSheet actualSheet, HSSFSheet expectedSheet, List<String> columns, List<String> columnsData, String testCaseID, String transactionType) throws IOException { boolean isrowFound = false; int expSheetRowCount = getRowCount(expectedSheet);//expectedSheet.getPhysicalNumberOfRows(); int effectivefirstRow = firstRow; int actualSheetRowCount = ExcelUtility.getRowCount(actualSheet); Reporter report = new Reporter(); report.setReport(report);/*from ww w. j av a2 s. c om*/ int passCount = 0, failCount = 0, colCount = 0, finalRowCount = 0; WebVerification.currentRowIndex = 0; //TM-28/09/2015: if expected sheet row count not matching actual sheet row count than Come out if (expSheetRowCount != actualSheetRowCount) { TransactionMapping.report.strStatus = "FAIL"; TransactionMapping.report.strMessage = "Expected No. of rows does not match Actual No. of rows, Please check Expected and Actual sheets"; } else { for (int rowIndex = effectivefirstRow; rowIndex < effectivefirstRow + expSheetRowCount; rowIndex++) { passCount = 0; failCount = 0; int currentRow = ++WebVerification.currentRowIndex; HSSFRow actualRow = actualSheet.getRow(currentRow); HSSFRow expectedRow = expectedSheet.getRow(rowIndex); //TM-28/09/2015: if actual and expected sheet row count does not match then break after following the steps in this code block. if (actualRow == null || expectedRow == null) { status.clear(); report.strStatus = "FAIL"; report.setStrStatus(report.strStatus); rowStatus.add(report.strStatus); failCount += 1; //TM-28/09/2015: if expected sheet row count is greater than actual sheet row count else vice-versa if (actualRow == null) report.strActualValue = "Expected No. of rows are greater than Actual No. of rows."; else report.strActualValue = "Actual No. of rows are greater than Expected No. of rows."; actualValue = new ArrayList<String>(); actualValue.add(report.strActualValue); actualRows.add(actualValue); PassCount.add(passCount); FailCount.add(failCount); report.setReport(report); break; } if (actualRow.getCell(0).toString().equals(expectedRow.getCell(0).toString()) && actualRow.getCell(1).toString().equals(expectedRow.getCell(1).toString())) { isrowFound = true; actualValue = new ArrayList<String>(); colCount = expectedRow.getPhysicalNumberOfCells(); for (int columnIndex = 3; columnIndex < colCount; columnIndex++) { HSSFCell actualCell = actualRow.getCell(columnIndex); DataFormatter fmt = new DataFormatter(); HSSFCell expectedCell = expectedRow.getCell(columnIndex); //TM: Following 'if' is replacement of the above if (actualCell != null || expectedCell != null) { String expectedValue = fmt.formatCellValue(expectedCell); if (!actualCell.toString().equalsIgnoreCase(expectedValue)) { report.strStatus = "FAIL"; report.setStrStatus(report.strStatus); failCount += 1; report.strActualValue = "FAIL |" + expectedValue + "|" + actualCell.toString(); } else { passCount += 1; report.strStatus = "PASS"; report.setStrStatus(report.strStatus); report.strActualValue = actualCell.toString(); System.out.println(actualCell.toString()); } status.add(report.strStatus); actualValue.add(report.strActualValue); } } if (status.contains("FAIL")) { report.strStatus = "FAIL"; TransactionMapping.report.strStatus = "FAIL"; TransactionMapping.report.strMessage = "Please refer to Detailed Results for more info.."; } else { report.strStatus = "PASS"; } status.clear(); rowStatus.add(report.strStatus); PassCount.add(passCount); FailCount.add(failCount); actualRows.add(actualValue); report.setReport(report); } else if (isrowFound == false) { continue; } } if (rowStatus.contains("FAIL")) { report.strStatus = "FAIL"; } WriteToDetailResults(testCaseID, transactionType, columns, actualRows, passCount, failCount, expSheetRowCount, colCount, report, rowStatus); } PassCount.clear(); FailCount.clear(); return report; }
From source file:swift.selenium.TransactionMapping.java
License:Open Source License
@SuppressWarnings("null") public static String getCellData(String reqValue, HSSFSheet reqSheet, int rowIndex, HashMap<String, Object> inputHashTable) throws IOException { HSSFCell reqCell = null;//from w w w . ja v a 2 s .c om Object actualvalue = null; String req = ""; DataFormatter fmt = new DataFormatter(); if (inputHashTable.isEmpty() == true) { inputHashTable = getValueFromHashMap(reqSheet); } HSSFRow rowActual = reqSheet.getRow(rowIndex); if (inputHashTable.get(reqValue) == null) { report.setStrMessage("Column " + reqValue + " not Found. Please Check input Sheet"); pauseFun("Column " + reqValue + " not Found. Please Check input Sheet"); } else { actualvalue = inputHashTable.get(reqValue);// rowHeader.getCell(colIndex).toString(); if (actualvalue != null) { int colIndex = Integer.parseInt(actualvalue.toString()); reqCell = rowActual.getCell(colIndex); if (reqCell == null) { System.out.println(reqValue + " is Null"); } else { int type = reqCell.getCellType(); switch (type) { case HSSFCell.CELL_TYPE_BLANK: req = ""; break; case HSSFCell.CELL_TYPE_NUMERIC: req = fmt.formatCellValue(reqCell); break; case HSSFCell.CELL_TYPE_STRING: req = reqCell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: req = Boolean.toString(reqCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: req = "error"; break; case HSSFCell.CELL_TYPE_FORMULA: req = reqCell.getCellFormula(); break; } } } else { req = reqCell.getStringCellValue(); System.out.println("null"); } } return req; }
From source file:SwiftSeleniumWeb.Automation.java
License:Open Source License
/**Loads the Config sheet into HashMap**/ public static void LoadConfigData() throws IOException, SQLException, ClassNotFoundException, URISyntaxException { try {/*from ww w .j a va 2s .c om*/ Date initialDate = new Date(); String strInitialDate = dtFormat.format(initialDate); SwiftSeleniumWeb.WebDriver.report.setFromDate(strInitialDate); DataFormatter format = new DataFormatter(); String projectPath = System.getProperty("user.dir"); String configPath = projectPath + "\\CommonResources\\Config.xls"; HSSFSheet configSheet = ExcelUtility.GetSheet(configPath, "Config"); int rowCount = configSheet.getLastRowNum() + 1; for (int rowIndex = 1; rowIndex < rowCount; rowIndex++) { HSSFRow rowActual = configSheet.getRow(rowIndex); String parameterName = format.formatCellValue(rowActual.getCell(0)); String value = format.formatCellValue(rowActual.getCell(1)); if (StringUtils.isNotBlank(parameterName) || StringUtils.isNotBlank(value)) { configHashMap.put(parameterName, value); } } } catch (NullPointerException npe) { MainController.pauseFun("Null Values Found in Config Sheet"); } catch (Exception e) { MainController.pauseFun(e.getMessage() + " From LoadConfig Function"); } }
From source file:SwiftSeleniumWeb.WebHelper.java
License:Open Source License
@SuppressWarnings("null") public static String getCellData(String reqValue, HSSFSheet reqSheet, int rowIndex, HashMap<String, Object> inputHashTable) throws IOException { HSSFCell reqCell = null;/*w ww . j av a 2 s . c o m*/ Object actualvalue = null; String req = ""; DataFormatter fmt = new DataFormatter(); if (inputHashTable.isEmpty() == true) { inputHashTable = getValueFromHashMap(reqSheet); } HSSFRow rowActual = reqSheet.getRow(rowIndex); if (inputHashTable.get(reqValue) == null) { SwiftSeleniumWeb.WebDriver.report .setStrMessage("Column " + reqValue + " not Found. Please Check input Sheet"); MainController.pauseFun("Column " + reqValue + " not Found. Please Check input Sheet"); } else { actualvalue = inputHashTable.get(reqValue);//rowHeader.getCell(colIndex).toString(); if (actualvalue != null) { int colIndex = Integer.parseInt(actualvalue.toString()); reqCell = rowActual.getCell(colIndex); if (reqCell == null) { System.out.println(reqValue + " is Null"); } else { int type = reqCell.getCellType(); switch (type) { case HSSFCell.CELL_TYPE_BLANK: req = ""; break; case HSSFCell.CELL_TYPE_NUMERIC: req = fmt.formatCellValue(reqCell); break; case HSSFCell.CELL_TYPE_STRING: req = reqCell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: req = Boolean.toString(reqCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: req = "error"; break; case HSSFCell.CELL_TYPE_FORMULA: req = reqCell.getCellFormula(); break; } } } else { req = reqCell.getStringCellValue(); System.out.println("null"); } } return req; }