Dynamically creating rows in Excel sheet with Apache POI

Question

I'm writing a program to read a large xml file and create an excel file from it. The attributes of each node will be the column headers in excel file. I created a Dom object and got the nodelist. I need to iterate through it and for each node, i need to add a row in excel sheet with the node's attributes values as column values. So, when iterating, i need to create rows dynamically. How can i do it? I dont see a functionality to add created rows in apache POI, so far what i have seen is to define new rows everytime. I'm unable to do it since it has more than 5000 entries. Basically what i want to do is:

    Node node = null;
    HSSFRow datarow = null;
    for (int i = 0; i < nodeList.getLength(); i++) {
        node = nodeList.item(i);
        datarow = spreadSheet.createRow(i);
        //set values for data row here, and add it.
        //so in the loop, next time the same variable will be assigned to spreadSheet.createRow(1) etc.
    }

I understand that the createRow is invoked from spreadSheet, which will add the row to it. But in the loop, the same variable will be assigned to other rows too, so i think finally i will get only 1 row. Please advice me on this.

Can you just do this: spreadSheet.createRow(i); and remove the index 0. — tjg184
I'm already doing this, i edited my post too. When i create the row and set the column values, in the next iteration, it is overridden right? thats what i asked.... thanks. — popcoder

Srinivas M.V. Srinivas M.V. · Accepted Answer · 2012-06-28T04:25:24

Try the following

   Node node = null;
    HSSFRow datarow = null;
    for (int i = 0; i < nodeList.getLength(); i++) {
        // On each loop you get the value of node item
        node = nodeList.item(i);
        //For every new node list you will create a row 
        datarow = spreadSheet.createRow(i);
        //Finally set the node value to the columns of the newly created Row
    }

Hope this helps !!

Dynamically creating rows in Excel sheet with Apache POI

1 Answers