Selenium Compare Web table with Excel data

Question

I need to compare Web table with downloaded excel file. Excel data have numeric value which is formatted in excel (example 523010735055.256 formatted to in excel $ 523,011. I need to validate $ 523,011 with UI value. How can I do equal both the table. Image for reference [1]: https://i.imgur.com/a/OWXS7pJ "image"

I have used DataFormatter as most of the other post suggested for similar question. yet its not working.

I have found other way to solve this problem without

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cell.getNumericCellValue();
                    String value = formatter.formatCellValue(cell);
                    System.out.print(value + " ");

                    break;

How can I change above code in this below manner

                    String val1 = null;
                    Double numvalue1 = cell.getNumericCellValue();
                    Long longValue1 = numvalue1.longValue();
                    val1 = new String(longValue1.toString());
                    BigDecimal convertres1 = new BigDecimal(val1);
                    String aarwavalue1 = (convertres1.divide(new BigDecimal("1000000")).setScale(0, RoundingMode.FLOOR) + "");

Code
waitForElementPresent(driver, 30, mstrframe);

        // To locate table.
        WebElement mytable = driver.findElement(By.xpath("(//div[contains(@id,'ReportGridStyle_forRW')])[2]//tbody"));
        // To locate rows of table.
        List<WebElement> rows_table = mytable.findElements(By.tagName("tr"));
        // To calculate no of rows In table.
        int rows_count = rows_table.size();
        // Loop will execute till the last row of table.
        for (int row = 0; row < rows_count; row++) {
            // To locate columns(cells) of that specific row.
            List<WebElement> Columns_row = rows_table.get(row).findElements(By.tagName("td"));
            // To calculate no of columns (cells). In that specific row.
            int columns_count = Columns_row.size();
            System.out.println("Number of cells In Row " + row + " are " + columns_count);
            // Loop will execute till the last cell of that specific row.
            for (int column = 0; column < columns_count; column++) {
                // To retrieve text from that specific cell.
                String celtext = Columns_row.get(column).getText();
                System.out
                        .println("Cell Value of row number " + row + " and column number " + column + " Is " + celtext);
            }

        }
        FileInputStream fis = new FileInputStream(System.getProperty("user.dir") + "\\src\\main\\resources\\excelfiles\\Bas Outline Mode Report.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(fis);

        // Read sheet inside the workbook by its name
        XSSFSheet sh1 = wb.getSheetAt(0);
        // Data formatter
        DataFormatter formatter = new DataFormatter();
        // Find number of rows in excel file
        // Iterate through each rows one by one
        Iterator<Row> rowIterator = sh1.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            // For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                // Check the cell type and format accordingly
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    cell.getNumericCellValue();
                    String value = formatter.formatCellValue(cell);
                    System.out.print(value + " ");

                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + " ");
                    break;
                }
            }
            System.out.println("");
        }

Output

    Number of cells In Row 0 are 5
    Cell Value of row number 0 and column number 0 Is DataGrouping
    Cell Value of row number 0 and column number 1 Is RWAExposureType
    Cell Value of row number 0 and column number 2 Is AA RWA ex 1.06x
    Cell Value of row number 0 and column number 3 Is AA RWA
    Cell Value of row number 0 and column number 4 Is SA RWA
    Number of cells In Row 1 are 5
    Cell Value of row number 1 and column number 0 Is Credit Risk
    Cell Value of row number 1 and column number 1 Is Available For Sale
    Cell Value of row number 1 and column number 2 Is $ 449,454
    Cell Value of row number 1 and column number 3 Is $ 476,421
    Cell Value of row number 1 and column number 4 Is $ 264,503
    Number of cells In Row 2 are 4
    Cell Value of row number 2 and column number 0 Is Contingent
    Cell Value of row number 2 and column number 1 Is $ 113,262
    Cell Value of row number 2 and column number 2 Is $ 120,057
    Cell Value of row number 2 and column number 3 Is $ 258,508
    Number of cells In Row 3 are 4
    Cell Value of row number 3 and column number 0 Is Total
    Cell Value of row number 3 and column number 1 Is $ 562,715
    Cell Value of row number 3 and column number 2 Is $ 596,478
    Cell Value of row number 3 and column number 3 Is $ 523,011
    Number of cells In Row 4 are 5
    Cell Value of row number 4 and column number 0 Is Total
    Cell Value of row number 4 and column number 1 Is  
    Cell Value of row number 4 and column number 2 Is $ 562,715
    Cell Value of row number 4 and column number 3 Is $ 596,478
    Cell Value of row number 4 and column number 4 Is $ 523,011




    Data Grouping RWA Exposure Type ( $ M ) AA RWA ex 1.06x ( $ M ) AA RWA ( $ M ) SA RWA 
    Credit Risk 5.62715E+11 5.96478E+11 5.23011E+11 
    Available For Sale 4.49454E+11 4.76421E+11 2.64503E+11 
    Contingent 1.13262E+11 1.20057E+11 2.58508E+11 
    Total 5.62715E+11 5.96478E+11 5.23011E+11 
    5/2/2019 1:16:59 PM

POI change to 4.1.0

                while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                // Check the cell type and format accordingly
                switch (cell.getCellType()) {   
                case NUMERIC:
                    cell.getNumericCellValue();                 
                    String value =formatter.formatCellValue(cell);
                    System.out.print(value + " ");
                    break;                      
                case STRING:
                    System.out.print(cell.getStringCellValue() + " ");
                    break;
                default:
                    break;
                }
            }
            System.out.println("");
        }

Answer 1

It seems that DataFormatter needs a semicolon after the last sub pattern if multiple patterns for positive numbers, for negative numbers, for zeros and/or for text is used and not all four sub patterns are given.

Example: Format for positive numbers;Format for negative numbers;Format for zeros;Format for text works but Format for positive numbers;Format for negative numbers does not work and needs to be Format for positive numbers;Format for negative numbers;. Note the semicolon after the pattern for negative numbers.

This is different to Excel since there the last semicolon is not needed.

If we know this we can do the following:

Sheet example:

Number format pattern in Excel is "$ "#,##0,,;"($ "#,##0,,\).

Code:

import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.*;

class ReadExcelSpecialNumberFormat {

 public static void main(String[] args) throws Exception {

  Workbook workbook  = WorkbookFactory.create(new FileInputStream("ExcelSpecialNumberFormat.xlsx"));

  DataFormatter dataFormatter = new DataFormatter();

  CreationHelper creationHelper = workbook.getCreationHelper();

  FormulaEvaluator formulaEvaluator = creationHelper.createFormulaEvaluator();

  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) {
   for (Cell cell : row) {
    CellStyle cellStyle = cell.getCellStyle();
    String dataFormatString = cellStyle.getDataFormatString();
    if (dataFormatString != null && dataFormatString.contains(";"))
     cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(dataFormatString+";"));
    String cellContent = dataFormatter.formatCellValue(cell, formulaEvaluator);
    System.out.print(cellContent + " ");
   }
   System.out.println();
  }

  workbook.close();

 }
}

Result:

axel@arichter:~/Dokumente/JAVA/poi/poi-4.1.0$ javac -Xlint:deprecation -Xlint:unchecked -cp .:./*:./lib/*:./ooxml-lib/* ReadExcelSpecialNumberFormat.java 
axel@arichter:~/Dokumente/JAVA/poi/poi-4.1.0$ java -cp .:./*:./lib/*:./ooxml-lib/* ReadExcelSpecialNumberFormat 
Data Grouping  Amount 1 ($ M) Amount 2 ($ M) Amount 3 ($ M) 
Credit Risk $ 772.314 $ 913.546 $ 812.453 
Available For Sale ($ 685.104) $ 800.603 $ 830.131 
Contingent $ 509.480 $ 524.524 ($ 629.797) 
Total $ 596.689 $ 2.238.674 $ 1.012.786 

Answer 2

Thanks for all the feedback @AxcelRicher. Dataformatter is not working in this case.

I will used below code for resolve to problem now

                switch (cell.getCellType()) {   
                case NUMERIC:
                    //cell.getNumericCellValue();
                    Double value= cell.getNumericCellValue();
                    Long longValue1 = value.longValue();
                    String val1 = new String(longValue1.toString());
                    BigDecimal convertres1 = new BigDecimal(val1);
                    String aarwavalue1 = (convertres1.divide(new BigDecimal("1000000")).setScale(0, RoundingMode.FLOOR) + "");
                    System.out.print(aarwavalue1 + "\t ");
                    break;                      
                case STRING:
                    System.out.print(cell.getStringCellValue() + " ");
                    break;
                default:
                    break;