Writing excel sheet with LinkedHashmap using Java POI

Question

I am almost there to solve my problem, but just got stuck in small issue. I am trying to print a excel file from LinkedHashmap. I use Java and POI library for my purpose.

I am extracting some data from another excel file in LinkedHashmap and printing this data to new excel file.

Data stored in LinkedHashmap is large and listed in following manner:

    Keys | Values
-----------------------------------
    Key1 : value1, value1, value1
    Key2 : value2, value2, value2
    key3 : value3, value3, value3

What I want:

Output in excel file should be displayed in following format:

Excel_file_out_deserved.xslx:

     + 
key1 | value1
key1 | value1
key1 | value1
     |        // (if possible, empty cell space here will look good)
key2 | value2
key2 | value2
key2 | value2
     |        // (if possible, empty cell space here will look good)
key3 | value3
key3 | value3
key3 | value3

What I am getting:

My_excel_file_out.xslx:

     | value1  // (why my first column is empty?!, it should print keys there)
     | value1
     | value1
     | value2
     | value2
     | value2
     | value3
     | value3
     | value3

What I tried:

FileOutputStream out = new FileOutputStream(new File("my_excel.xlsx"));

XSSFWorkbook newWorkbook = new XSSFWorkbook();
XSSFSheet sheet = newWorkbook.createSheet("print_vertical");
int row = 0;

// loop through all the keys
for(String key : linkMap.keySet()){

    List<String> values = linkMap.get(key);

    for (String value:values){

         // print keys in 1st column
         sheet.createRow(row).createCell(0).setCellValue(key); // why this doesn't work? :-/
         // print values in 3rd column
         sheet.createRow(row).createCell(2).setCellValue(value); // this works fine
         row++;
        }

    }

    newWorkbook.write(out);
    out.close();

Mysteriously, when I remove inner for-loop of values without omitting sheet.createRow(row).createCell(0).setCellValue(key); line, the outer loop will correctly print key values in first column row wise. I cannot figure out where I am making mistake. Thanks.

Nikolas Nikolas · Accepted Answer · 2017-01-26T15:36:01

you create your row twice by calling

sheet.createRow(row)

You will want to store the row in a variable and calling the createCell(x)there

for (String value:values){
    Row newRow = sheet.creatRow(row);
    newRow.createCell(0).setCellValue(key);
    newRow.createCell(2).setCellValue(value);
    row++;
}

Problem was:

sheet.createRow(row).createCell(0).setCellValue(key); // create new row at index 'row' sheet.createRow(row).createCell(2).setCellValue(value); // create new row at same row index

so your first row will be overridden.

Writing excel sheet with LinkedHashmap using Java POI

1 Answers