URGENT !! Extracting Web Table into Excel sheet with POI API, problem

[srinivas nomu]

My code is as follows:

I am trying to extract web tbale elements into excel sheet. I am able to extract only the last column from the web table. I am just trying with a simple table from "http://www.w3schools.com/html/html_tables.asp", before implementing on my project. In this table you can see "Age" column is getting extracted but other fields are getting printed but they are not writing into Excel sheet. I am  not sure where I am making a mistake. Can somebody help me.

Here is my code:

import java.io.File;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.Iterator;

import java.util.List;

import org.openqa.selenium.By;

import org.openqa.selenium.WebDriver;

import org.openqa.selenium.WebElement;

import org.openqa.selenium.firefox.FirefoxDriver;

import org.testng.annotations.Test;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExtractWebTablesToExcel {

    @Test

public void extractWebTables() throws IOException{

     HSSFWorkbook workbook = new HSSFWorkbook();

       HSSFSheet sheet = workbook.createSheet("output");

      FileOutputStream f = new FileOutputStream("C:\\Temp\\output.xls",true);

    

   

WebDriver driver=new FirefoxDriver();

driver.get("http://www.w3schools.com/html/html_tables.asp");

WebElement Table= driver.findElement(By.xpath(".//*[@id='main']/table[1]"));

List<WebElement> rows=Table.findElements(By.tagName("tr"));

Iterator<WebElement> i=rows.iterator();

int x=0;

while(i.hasNext())

{   

WebElement row=i.next();

List<WebElement> columns=row.findElements(By.tagName("td"));

Iterator<WebElement> j=columns.iterator();

int y=1;

while(j.hasNext())

{

   WebElement column=j.next();

System.out.println(column.getText());

  

   HSSFRow dataRow = sheet.createRow((short)x);

   System.out.println("x=" +"" +x);

   System.out.println("y=" +""+y);

   HSSFCell cell=dataRow.createCell(y);

   cell.setCellType(HSSFCell.CELL_TYPE_STRING);

   cell.setCellValue(column.getText());

   

   

y=y+1;

 

}

x=x+1;

}

try {

f.flush();

workbook.write(f);

f.close();

       System.out.println("Excel written successfully..");

        

   } catch (FileNotFoundException e) {

       e.printStackTrace();

   } catch (IOException e) {

       e.printStackTrace();

   }

}

    

    

}

[Neeraj S]

Hello Srinivas,

I tried your code and with your url, I am not able to see Age table. I am trying to solve same issue. Could you please redo your table link that shows Age column or if you choose another table from different web, page, please send new url and code based on that code, which column your code is trying to print the data in excel sheet. Please redo it again.

[Oscar Rieken]

you should probably ask the poi api user group 

On Mon, Jan 26, 2015 at 3:23 AM, Neeraj S <s.neer...@gmail.com> wrote:

Hello Srinivas,

I tried your code and with your url, I am not able to see Age table. I am trying to solve same issue. Could you please redo your table link that shows Age column or if you choose another table from different web, page, please send new url and code based on that code, which column your code is trying to print the data in excel sheet. Please redo it again.

--
You received this message because you are subscribed to the Google Groups "Selenium Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to selenium-user...@googlegroups.com.
To post to this group, send email to seleniu...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/selenium-users/efbd4e33-f715-47ac-8373-03d6d914d891%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Manish Bansal]

Check this, it might help 

http://softtechlabs.com/2015/10/selentable-selenium-webtables-the-best-practice/

Thanks,

Manish Bansal

[Pavani Samineni]

Hi Srinivas,

   In the same way am trying to write web table data to excel, am able to do that, Refer to my code below

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import java.util.List;
import java.util.concurrent.TimeUnit;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.firefox.FirefoxDriver;

import org.testng.annotations.AfterTest;
import org.testng.annotations.BeforeTest;


public class testpins1 {

    public static void main(String[] args) throws Exception {
        testpins1 tp =new testpins1();
        tp.setup();
        tp.Handle_Dynamic_Webtable();
        tp.tearDown();

    }
   
    WebDriver driver = new FirefoxDriver();

    @BeforeTest
    public void setup() throws Exception {
        driver.manage().window().maximize();
        driver.manage().timeouts().implicitlyWait(5, TimeUnit.SECONDS);
        driver.get("http://www.moneycontrol.com/");
    }

    @AfterTest
    public void tearDown() throws Exception {
        driver.quit();
    }

   
    @Test
    public void Handle_Dynamic_Webtable() throws FileNotFoundException {
       
        FileOutputStream fos = new FileOutputStream("C://WebTableTOSpreedsheet.xls");                                
       
        XSSFWorkbook wkb = new XSSFWorkbook();      
        XSSFSheet sheet1 = wkb.createSheet("DataStorage");

        WebElement mytable = driver.findElement(By.xpath(".//*[@id='mc_mainWrapper']/section/div/div[2]/aside/div[3]/div[2]/div[1]/table"));
       
        List<WebElement> rows_table = mytable.findElements(By.tagName("tr"));
        int rows_count = rows_table.size();
        System.out.println("Number of Rows " + rows_count);

        for (int row = 0; row < rows_count; row++) {
           
            XSSFRow excelRow = sheet1.createRow(row);
            if(row==0){
                List<WebElement> head_row = rows_table.get(row).findElements(By.tagName("th"));
                int Head_count = head_row .size();
                System.out.println("Number of Header cells In Row 0 are "+ Head_count);
               
                    for(int i=0;i<Head_count;i++) {
                        XSSFCell excelCell = excelRow.createCell(i);
                        excelCell.setCellType(XSSFCell.CELL_TYPE_STRING);
                        String celtext = head_row.get(i).getText();
                        excelCell.setCellValue(celtext);
                        System.out.println("Header in valuein column number " + i + " Is " + celtext);
                }
               
            }else{
                List<WebElement> Columns_row = rows_table.get(row).findElements(By.tagName("td"));
                int columns_count = Columns_row.size();
                System.out.println("Number of cells In Row " + row + " are "+ columns_count);
               
                for (int column = 0; column < columns_count; column++) {
                    XSSFCell excelCell = excelRow.createCell(column);
                    excelCell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    String celtext = Columns_row.get(column).getText();
                    excelCell.setCellValue(celtext);
                    System.out.println("Cell Value Of row number " + row+ " and column number " + column + " Is " + celtext);
                }
   
            }
            System.out.println("--------------------------------------------------");
        }
        try {
            fos.flush();
            wkb.write(fos);
            fos.close();

[janardhan...@gmail.com]

Hi Pavani,

I am getting Null pointer exception using this code.

Exception in thread "main" java.lang.NullPointerException

at bmc.Project1.webtable.testpins1.Handle_Dynamic_Webtable(testpins1.java:71)

at bmc.Project1.webtable.testpins1.main(testpins1.java:36)

Could you please help me on this?

[Herok Rout]

Hi  janardan,

        if u do this code plz send me.i am unable to do this.

        rout...@gmail.com