Java POI Excel creating new column and new rows

Question

Ok so Im iterating over a list and instead of inserting values into cells horizontally, im putting the values in the cells vertically.

It works fine for the first time through the list but when I go in the list the 2nd time it blows away the first list and replaces it in the 2nd column.

if i remove the row= 0 at the end of the loop, it look like:

val 1
val 2
      val 1
      val 2

=========

int row = 0;
int k = 1; 
for (List dataList: someList) {
  Row myRow = sheet.createRow ((short)row);

  myRow.createCell(k).setCellValue (dataList.getVal())); 
  myRow = sheet.createRow ((short)row++);

  myRow.createCell(k).setCellValue (dataList.getSecVal())); 
  myRow = sheet.createRow ((short)row++);
  k++;
  row = 0;
}

Answer can be found here also - stackoverflow.com/a/28405752/3409405 — Tejus Prasad
– Tejus Prasad, Commented Apr 20, 2016 at 18:12

Joseph Helfert · Accepted Answer · 2013-08-13 15:54:49Z

2

You are incrementing the row index wrong. row++ will increment after you create the second row. So you are creating two rows at index 0. If you change all your row++ to ++row it should work.

answered Aug 13, 2013 at 15:54

Joseph Helfert

4213 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

akokskis Over a year ago

This has nothing to do with how the row index is being incremented - rather, it's all about the fact that he's re-creating the rows at index 0 & 1. See my answer for more details.

Doc Holiday Over a year ago

@JosephHelfert I am not doing anything wrong. You interpreted the question wrong. My requirement is to have it column based not row based which there is no api for....the answer is utilizing sheet.getRow after you created a row in the first loop

Doc Holiday Over a year ago

by doing that...you can get the index of that row and create another cell right next to it....

Guillaume Taillez · Accepted Answer · 2016-03-07 10:54:57Z

2

On each iteration of your loop, you're recreating the rows at index 0 & 1. When you re-create these rows, you're going to blow away all of your already existing data.

Try something like this:

int k = 1;
Row myRow1 = sheet.createRow(0); //first row of the document
Row myRow2 = sheet.createRow(1);
for (List dataList: someList) {
  myRow1.createCell(k).setCellValue (dataList.getVal())); 
  myRow2.createCell(k).setCellValue (dataList.getSecVal())); 
  k++;
}

edited Mar 7, 2016 at 10:54

Guillaume Taillez

191 silver badge7 bronze badges

answered Aug 13, 2013 at 15:58

akokskis

1,54815 silver badges32 bronze badges

8 Comments

Doc Holiday Over a year ago

Thats not very dynamic. You are declaring 2 rows before you get in the list. Each iteration of the list is a column worth of data. I have to increment the row index to get the data to show in the row beneath it, hence row++

rozar Over a year ago

Can you create an array of rows of size someList.size() and use it to create rows dynamically.

rozar Over a year ago

@Doc Holiday. Check answer

Doc Holiday Over a year ago

@rozar I think I know the problem....its the actual instance of the row. i created a dummy row Row row2 = sheet.createRow at the end of the loop and it didnt blow away the first loop

akokskis Over a year ago

@DocHoliday - it may not be dynamic, but if you're looking to go about inserting data by the column instead of by the row (which, I may add, seems a bit backwards to how POI operates), this will do the trick. If you really want it to be dynamic, then do as rozar suggested and create a list of the rows. But whatever you do, do NOT re-create rows on every iteration. You should only be creating the rows once.

|

Nate · Accepted Answer · 2013-08-13 23:50:05Z

1

this is the solution that worked:

myRow = sheet.getRow(row);

if(null == myRow)
{

    myRow=sheet.createRow(row);
}

edited Aug 13, 2013 at 23:50

Nate

31.1k13 gold badges87 silver badges217 bronze badges

answered Aug 13, 2013 at 22:47

Doc Holiday

10.3k32 gold badges103 silver badges153 bronze badges

1 Comment

Nate Over a year ago

You can answer your own question. But, to do so, put the answer here, not up in the question. You can then accept your own answer.

Jonathan Drapeau · Accepted Answer · 2013-08-13 16:09:53Z

0

You're creating a row each time, you want to check if the row exists first. Something like this will work.

      myRow = sheet.getRow((short)row);
      if (myRow == null) {
        myRow = sheet.createRow((short)row);
      }

Also, in your current code, each time you're recreating the row at 0 twice.

  Row myRow = sheet.createRow ((short)row);

  myRow.createCell(k).setCellValue (dataList.getVal())); 
  myRow = sheet.createRow ((short)row++); // Here, you're creating a new row at index 0

akokskis answer is good, actually better, either create them as he did before iterating or check if it exists first.

edited Aug 13, 2013 at 16:09

answered Aug 13, 2013 at 15:58

Jonathan Drapeau

2,6202 gold badges27 silver badges32 bronze badges

Comments

rozar · Accepted Answer · 2013-08-13 17:02:45Z

0

When you iterate through the first list. You have created the rows. The next time when you iterate you have to update the rows instead of creating it again. When you create new row, its going to add at the end.

When iterating through List1, when you reached at the end you have already created three new rows. If you now make row = 0 and iterate through the second list List2. It is going to add a new row at 0 and so on till it reaches the end of List2.

If you have not made rows = 0 at the end of the iteration. New rows will be added at the end.

int row = 0;
int k = 1; 

ArrayList<Row> rows = new ArrayList<Row>();
for(int i = 0; i<someList.size();i++){
    Row myRow = sheet.createRow(i);
    rows.add(myRow);
    myRow = null;
}

for (List dataList: someList) {
    for(Row row : rows){
        row.createCell(k).setCellValue(dataList.getVal()));         
    }

    k++;
}


     Col1    Col2    Col3    Col4
Row1   1
Row2   2
Row3          3 
Row4          4

or 

     Col1    Col2    Col3    Col4
Row1   1       3
Row2   2       4
Row3           
Row4

edited Aug 13, 2013 at 17:02

answered Aug 13, 2013 at 16:03

rozar

1,0783 gold badges15 silver badges30 bronze badges

2 Comments

Doc Holiday Over a year ago

if i add row = 0, then the first list will disappear and the second list will start in the 2nd column

Doc Holiday Over a year ago

each iteration of the loop is a new column...and the values in each iteration are the rows in that column

Collectives™ on Stack Overflow

Java POI Excel creating new column and new rows

5 Answers 5

3 Comments

8 Comments

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

8 Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related