Unable to set value for HSSFCell object dynamically

Question

I am trying to export data from my jsp to excel using Apache POI. The Number of columns that I need will depend on two parameters and hence, I fetch it through a query.

For EX : If No. of dynamic columns required = 3, then output should be something like this.

 A | B | DH1 | DH2 | DH3 | C

Here A, B, C = Static column Header.

DH = Dynamic column Header ( fetched from the query.)

To do this , I initially set the A, B,C , then find the No. of DH columns required. then I generate the DH columns. After that, I find out the value needed in each DH column , store that value in an Arraylist, and then traverse Arraylist and set the value.

Here the code

            HSSFCell c1= row.createCell(0);
            HSSFCell c2= row.createCell(1);

           HSSFCell cellObject = null;

          oRecordMetaInfo = getNoofDynamicColumns();
          NoofColumns = oRecordMetaInfo.getRecordCount();

                    for(int i = 2; i < NoofColumns + 2; i++)
        {
            cellObject =     row.createCell((int) i);
        }

           HSSFCell c3 = row.createCell((int) (NoofColumns + 2));


          c1.setCellValue(" A ");
          c2.setCellValue(" B ");

         Array1 = new ArrayList();

        Array1 = dao.getDynamicColumnValues();


     If(Array1.size != 0)
    {  
      QueryRow oRow = null;
        Iterator oIt = null;
       oIt = Array1.iterator();

       while (oIt.hasNext()) {
            oRow = (QueryRow) oIt.next();
            oRow.get("DYNAMIC_VALUE");

           cellObject.setCellValue(oRow.get("DYNAMIC_VALUE").getString());
          }
        }

          c3.setCellValue(" C ");

But , the outtpu that I get is

 A | B |  |  | DH3 | C

Hence only the last value gets set in the cell, the previous value are not. Can anyone suggest what's wrong ?

score 2 · Answer 1 · answered Nov 12 '13 at 11:17

Your problem is you're creating 3 DH cells, but only keeping a reference to the last one, and hence overwriting it 3 times

This bit creates the 3 columns (or however many for the setup):

    for(int i = 2; i < NoofColumns + 2; i++)
    {
        cellObject =     row.createCell((int) i);
    }

But as you see, only the last one is kept. Then when you do:

  while (oIt.hasNext()) {
        oRow = (QueryRow) oIt.next();
        oRow.get("DYNAMIC_VALUE");

       cellObject.setCellValue(oRow.get("DYNAMIC_VALUE").getString());
  }

You keep overwriting the same last cell

You'll need to change your logic to fetch the right cell every time, eg

  int cellNumber = 2;
  while (oIt.hasNext()) {
        oRow = (QueryRow) oIt.next();
        oRow.get("DYNAMIC_VALUE");

       cellObject = row.getCell(cellNumber);
       cellObject.setCellValue(oRow.get("DYNAMIC_VALUE").getString());
       cellNumber++;
  }

Alternately, create the cells and store them in an array that you iterate in parallel, or something like that. Also, consider putting that 2 into a constant, as you use it everywhere and it'll be a faff to update when you need to add another column at the start one later...

Unable to set value for HSSFCell object dynamically

1 Answers1