Java - Apache POI - Trouble filling rows and cells

问题:

There's a HashMap:

HashMap<String, ArrayList<String>> matrix = new HashMap<String, ArrayList<String>>();

I want to fill an excel sheet in this pattern:

hashkey1 | hashkey2 | hashkey3 | hashkey4
value1-1 | value2-1 | value3-1 | value4-1  
value1-2 | value2-2 | value3-2 | value4-2  
value1-3 | value2-3 | value3-3 | value4-3  
value1-4 | value2-4 | value3-4 | value4-4  
value1-5 | value2-5 | value3-5 | value4-5  

The "hashKeys" are "Categories", and every key has its own ArrayList. Every string-element of the ArrayLists shall be drawn under its correspondent key.

Here's the actual code:

int keyCell = -2;
int row = 5;
Row keyRow = worksheet.createRow(4);
Row valueRow = null;
for (Map.Entry<String, ArrayList<String>> e : matrix.entrySet()) {           
    keyRow.createCell(keyCell += 2).setCellValue(e.getKey());
    for (String s : e.getValue()) { 
        if ((row - 5) < (e.getValue().size())) {
            valueRow = worksheet.createRow(row += 1);
            valueRow.createCell(keyCell).setCellValue(s);
            } else {
                valueRow.createCell(keyCell).setCellValue(s);
            }
        }
    }

It works beautifully, except for the fact that the result goes in this pattern:

hashkey1 | hashkey2 | hashkey3 | hashkey4
value1-1 |          |          |            
value1-2 |          |          |            
value1-3 |          |          |           
value1-4 |          |          |           
value1-5 | value2-5 | value3-5 | value4-5  

I think it is working exactly how I want, but the cells get erased at each loop because of the new row that is created at each loop. This is a very challenging problem. I had huge difficulty to come to this, and now I'm absolutely stuck. Nothing works. The cells ALWAYS get erased.

Well, I hope this is a not too prolix topic. I really thank you all for any help.

回答1:

In the inner for loop, keyCell never changes value. This means you are writing to the same cell value each time. (I'm surprised the code works at all due to this problem.)

 for (String s : e.getValue()) { 
        if ((row - 5) < (e.getValue().size())) {
            valueRow = worksheet.createRow(row += 1);
            valueRow.createCell(keyCell).setCellValue(s);
            } else {
                valueRow.createCell(keyCell).setCellValue(s);
            }
        }

So first, I'd fix this problems and use keyCell++. If that doesn't work, add debugging showing the row and keyCell number each time you call createCell(). This will show if the indexes are following the correct pattern or not. You want the pattern:

• 0, 0
• 0, 1
• ....
• 0, 4
• 1, 0
• etc