CODEWITHSUNDEEP
javaexcel
user3872094
user3872094

Reputation: 3351

Unable to insert data in number format in Excel

I've the below Java (Selenium) method that will insert data into Excel sheet:

private static void getPaceNumber(WebDriver chromeDriver, String dBName, XSSFSheet paceSheet, String pubName, int i,
        XSSFCell cell, XSSFWorkbook workbook) throws Exception {
    CellStyle style = workbook.createCellStyle();
    cell = paceSheet.getRow(i).createCell(1);
    if (dBName == "" || dBName.equals("null")) {
        cell.setCellValue("N/A");
    } else {
        chromeDriver.findElement(By.xpath("html/body/form[2]/b/b/table/tbody/tr[2]/td[2]/textarea"))
                .sendKeys("\"" + dBName + "\"");
        chromeDriver.findElement(By.xpath("html/body/form[2]/b/b/table/tbody/tr[4]/td[2]/input[1]")).click();
        List<WebElement> pace = chromeDriver
                .findElements(By.xpath("html/body/form[2]/table[1]/tbody/tr[2]/td[2]/input[1]"));
        int paceSize = pace.size();
        if (paceSize >= 1) {
            int dbPaceNumber = Integer.parseInt(
                    chromeDriver.findElement(By.xpath("html/body/form[2]/table[1]/tbody/tr[2]/td[2]/input[1]"))
                            .getAttribute("value"));
            chromeDriver.findElement(By.xpath(".//*[@id='searchPublication']")).click();
            chromeDriver.findElement(By.xpath("html/body/form[2]/b/b/table/tbody/tr[2]/td[2]/textarea"))
                    .sendKeys("\"" + pubName + "\"");
            chromeDriver.findElement(By.xpath("html/body/form[2]/b/b/table/tbody/tr[4]/td[2]/input[1]")).click();
            int pubPaceNumber = Integer.parseInt(
                    chromeDriver.findElement(By.xpath("html/body/form[2]/table[1]/tbody/tr[2]/td[2]/input[1]"))
                            .getAttribute("value"));
            if (dbPaceNumber == pubPaceNumber) {
                cell.setCellValue(dbPaceNumber);
            } else {

                cell.setCellValue(dbPaceNumber + "\n" + pubPaceNumber);
                style.setWrapText(true);
                cell.setCellStyle(style);
            }

        } else {
            List<WebElement> table = chromeDriver
                    .findElements(By.xpath("html/body/form[2]/table[1]/tbody/tr[4]/td/b"));
            int tabSize = table.size();
            if (tabSize == 1) {
                chromeDriver.findElement(By.xpath(".//*[@id='searchPublication']")).click();
                chromeDriver.findElement(By.xpath("html/body/form[2]/b/b/table/tbody/tr[2]/td[2]/textarea"))
                        .sendKeys("\"" + pubName + "\"");
                chromeDriver.findElement(By.xpath("html/body/form[2]/b/b/table/tbody/tr[4]/td[2]/input[1]"))
                        .click();
                List<WebElement> paceWithFPN = chromeDriver
                        .findElements(By.xpath("html/body/form[2]/table[1]/tbody/tr[2]/td[2]/input[1]"));
                int paceWithFPNSize = paceWithFPN.size();
                if (paceWithFPNSize >= 1) {
                    int paceSubNumber = Integer.parseInt(chromeDriver
                            .findElement(By.xpath("html/body/form[2]/table[1]/tbody/tr[2]/td[2]/input[1]"))
                            .getAttribute("value"));

                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(paceSubNumber);
                } else {
                    cell.setCellValue("N/A");
                }

            } else {
                cell.setCellValue("N/A");
            }
        }
    }
}

I want to check the value with two different criteria, if both of them are same, insert the value in Excel cell, else insert both the values in a single cell. Basically the values retrieved are of integer type. I'm able to insert the values correctly, but if there are two values, they are getting inserted as a single line (one continuation of the other). The single value is automatically aligned right in Excel cell (in general number format). Where there are two values, I need to double click on the cell and then they are shown in two line format, and also they are displayed as strings (left aligned).

I'm aware that when I use a +"XXX"+ the resultant is a string, but how can I make this into an integer?

Like single value, this has to be right aligned and also is there a way I can get a line break automatically inside the cell?

**Current output:**                      **Expected Output:**

enter image description here enter image description here

Upvotes: 2

Views: 77

Answers (1)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

I think you have to increase the rowheight of the row that contains the cell.

In the beginning:

int defaultHeight = paceSheet.getRow(0).getHeight();

and later, when you create such a cell:

paceSheet.getRow(i).setHeight(defaultHeight*2);

Those lines: 

style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_RIGHT);

are something you just have to do once (e.g.right after creation) since style is the same for all affected cells.

Upvotes: 1

Related Questions