Java POI Excel creating new column and new rows

问题:

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;
}

回答1:

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.

回答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++;
}

回答3:

this is the solution that worked:

myRow = sheet.getRow(row);

if(null == myRow)
{

    myRow=sheet.createRow(row);
}

回答4:

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.

回答5:

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