List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter
public DataFormatter()
From source file:py.gov.datos.XlsToCsvConverter.java
License:GNU General Public License
/** * Convierte una celda de una planilla XLSX a un elemento de un archivo .csv. * @param cell celda a convertir./*from w w w .j ava2s . c o m*/ * @return elemento generado. */ private StringBuffer convertCell(Cell cell) { StringBuffer data = new StringBuffer(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: data.append(cell.getBooleanCellValue() + ";"); break; case Cell.CELL_TYPE_NUMERIC: data.append(new DataFormatter().formatCellValue(cell) + ";"); break; case Cell.CELL_TYPE_STRING: data.append(CustomStringEscapeUtils.escapeCsv(cell.getStringCellValue()) + ";"); break; case Cell.CELL_TYPE_BLANK: data.append("" + ";"); break; default: data.append(CustomStringEscapeUtils.escapeCsv(cell.getStringCellValue()) + ";"); } return data; }
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);//w ww . j av a 2 s. c om 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 a 2s . com*/ 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:service.XSSFSheetHandler.java
/** * Accepts objects needed while parsing. * /*from w w w . j av a2 s. c o m*/ * @param styles * Table of styles * @param strings * Table of shared strings * @param cols * Minimum number of columns to show * @param target * Sink for output */ public XSSFSheetHandler(StylesTable styles, ReadOnlySharedStringsTable strings, int cols, PrintStream target) { this.stylesTable = styles; this.sharedStringsTable = strings; this.minColumnCount = cols; this.output = target; this.value = new StringBuffer(); this.nextDataType = ReadExcelFile.xssfDataType.NUMBER; this.formatter = new DataFormatter(); }
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 {//ww w.j av a 2 s . co m 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);/* w w w . ja va2 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);/*w w w.j a va 2 s. c o m*/ 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 www . j a va2s . 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) { 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:swift.selenium.WebHelper.java
License:Open Source License
/** * This method reads and returns data from each cell of a provided worksheet * //from w w w . j a va2 s . com * @param reqValue * @param reqSheet * @param rowIndex * @param inputHashTable * @return * @throws IOException */ @SuppressWarnings("null") public static String getCellData(String reqValue, HSSFSheet reqSheet, int rowIndex, HashMap<String, Object> inputHashTable) throws IOException { HSSFCell reqCell = null; 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) { TransactionMapping.report.setStrMessage("Column " + reqValue + " not Found. Please Check input Sheet"); TransactionMapping.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); //TM 27-04-2015: Updated the code for formula in cells if (reqCell == null) { System.out.println(reqValue + " is Null"); } else { HSSFWorkbook wb = reqCell.getSheet().getWorkbook(); //TM-05/05/2015: Get workbook instance from the worksheet HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); //TM-05/05/2015: To refresh all the formulas in the worksheet FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); CellValue cellValue = evaluator.evaluate(reqCell); int type = 0; if (cellValue != null) { type = cellValue.getCellType(); } else { type = reqCell.getCellType(); } switch (type) { case HSSFCell.CELL_TYPE_BLANK: req = ""; break; case HSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(reqCell)) { SimpleDateFormat form = new SimpleDateFormat( Automation.configHashMap.get("DATEFORMAT").toString()); req = form.format(reqCell.getDateCellValue()); } else req = fmt.formatCellValue(reqCell, evaluator); 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; } } } 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 . jav a2s .com*/ 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"); } }