Export Java HashMap to xlsx

Question

I need convert HashMaps to xlsx using poi. For sheet data2 i need something like that:

table1:

But i have table2:

Here's my list of HashMaps:

rows=[{kol2=s, kol1=s}, {kol2=bbbb, kol3=bbbb, kol1=aaaa}, {kol2=bbbb, kol3=bbbb, kol1=aaaa}, {kol2=bbbb, kol3=bbbb, kol1=aaaa}, {kol2=s, kol1=s}]}

Here's my code:

            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet("data");
            XSSFSheet sheet2 = workBook.createSheet("data2");
            int rowCount = 0;
            int help = 1;


                List<HashMap<String, Object>> rows = ((List<HashMap<String, Object>>) x);
                int rowCount2 = 0;
                int header = 1;
                Row header2 = sheet2.createRow(0);
                for (int i = 0; i < rows.size(); i++) {
                    int li = 0;
                    Row row2 = sheet2.createRow(++rowCount2);
                    HashMap<String, Object> row = rows.get(i);
                    int columnCount2 = 0;

                    for (HashMap.Entry<String, Object> subElement : row.entrySet()) {

                        if (subElement.getValue() != null) {

                            if (i == li) {
                                Cell cell = header2.createCell(header);
                                cell.setCellValue(subElement.getKey().toString());
                                header++;
                            }
                            li++;
                            Cell cell2 = row2.createCell(++columnCount2);
                            cell2.setCellValue(subElement.getValue().toString());
                        }
                    }
                }

Someone can help?

Answer 1

Iterating over a HashMap's EntrySet

The first problem is that you are iterating over the entrySet of your HashMap

for (HashMap.Entry<String, Object> subElement : row.entrySet()) {
    // no guaranteed order
}

Looking at the JavaDoc of the Set#iterator() method you will see this:

Returns an iterator over the elements in this set. The elements are returned in no particular order (unless this set is an instance of some class that provides a guarantee).

There are Sets which are ordered (such as the TreeSet), but since you are using a HashMap, your EntrySet won't be ordered too.

Notice the column order in your sheet is kol2-kol3-kol1. Don't you want it to be kol1-kol2-kol3?

Not creating empty columns

You are forgetting to create empty cells for columns you don't have in your Map.

if (subElement.getValue() != null) {
    // there won't be an empty cell if you e.g. don't have kol2 in your rows Map, 
    // since this just skips your current value
}

This is why you end up with something like:

kol2   kol3   kol1
s      s      
bbbb   bbbb   aaaa
...

instead of:

kol2   kol3   kol1
s             s      
bbbb   bbbb   aaaa
...

Creating the header row inside the loop

By creating the header row inside your loop, you are making your solution more complicated than necessary. It would be much easier just to create the header row and then loop over your entries in the List.

if (i == li) {
    Cell cell = header2.createCell(header);
    cell.setCellValue(subElement.getKey().toString());
    header++;
}

If you are doing this outside the loop, there is no need for the li and the header variable

Suggested solution

I would (for a start) come up with something like this (I added some extra comments I normally wouldn't put there to make more clear what the intentions are and what aspects of the solution you need to understand):

    XSSFSheet sheet2 = workBook.createSheet("data2");
    List<HashMap<String, Object>> rows = ((List<HashMap<String, Object>>) x);

    List<String> headers = Arrays.asList("kol1", "kol2", "kol3");
    int currentRowNumber = 0;

    // create header row
    Row header = sheet2.createRow(currentRowNumber);
    for (int i = 0; i < headers.size(); i++) {
        Cell headerCell = header.createCell(i);
        headerCell.setCellValue(headers.get(i));
    }

    // create data rows (we loop over the rows List)
    for (int i = 0; i < rows.size(); i++) {
        HashMap<String, Object> row = rows.get(i);

        // we neet to increment the rowNumber for the row in the sheet at the beginning of 
        // each row. entry 0 in the rows List is in sheetRow 1, entry 1 in sheetRow 2, etc.
        currentRowNumber++;
        Row sheetRow = sheet2.createRow(currentRowNumber);

        // we can now loop over the columns inside the row loop (using the headers List)
        // we create a Cell for each column, but only fill it if there is
        for (int j = 0; j < headers.size(); j++) {
            Cell cell = sheetRow.createCell(j);

            // only fill the cell if we are having data in the row map for the current column
            String currentColumnName = headers.get(j);
            if (row.containsKey(currentColumnName)) {
                cell.setCellValue(row.get(currentColumnName).toString());
            }
        }
    }

If you want a different column order, just change the header List and you are done (e.g. Arrays.asList("kol2", "kol3", "kol1")).