writing Map<String, List<Map<String,Object>>> data into excel using apache poi

Question

I am working on creating excel file using Map<String, List<Map<String,Object>>> data in springboot application using apache poi library.

I have data: {Sheet1=[{header1=abc, header2=def, header3=ghi}], Sheet2=[{header1=ab, header2=de, header3=gh}], Sheet3=[{header1=bc, header2=ef, header3=hi}]}

I need to create excel as below.

  **data.xlsx**

 | header1 header2 header3 |   |header1 header2 header3 |  |header1 header2 header3 |
    abc     def     ghi          ab      de      gh          bc      ef      hi
  <Sheet1>                      <Sheet2>                    <Sheet3>

I am able to create different sheets with name of key from Map<String, List<Map<String,Object>>> data.I am trying to create excel sheets from value i.e List<Map<String,Object>, where the String should be used as header and Object data as respective rows of header. I am trying to populate the row of excel sheet with header names. the for loop runs and populates only last cell value.

I wrote the below code to populate header names as below.

//code to create excel sheet

Map<String, List<Map<String, Object>>> data = info.getData(); //getting data from database

 XSSFWorkbook wb = new XSSFWorkbook();

  FileOutputStream out = new FileOutputStream(new File("C:...path"+"data.xlsx"));

  Set<String> keyset = data.keySet();
  for(String key: keyset){

    XSSFSheet sheet = wb.crateSheet(key);
    int rowNo = 0;
    List<Map<String, Object>> list = data.get(key);

    for(Map<String, Object> value : list){

      XSSFRow row = sheet.createRow(rowNo++);

      Set<String> headerKeyset = value.keySet();

      for(String keyHeader : headerKeyset){

       int cellnum= 0;
       XSSFCell cell = row.createCell(cellnum++);
       cell.setCellValue(keyHeader);
}}}
 wb.write(out);
 out.close();

.
.
.

I'm able to create different Sheets/tab in Excel file with names Sheet1,Sheet2,Sheet3. But unable to fill in proper header and data, only last cell value is being populated as below when I run above code.

  **data.xlsx**

 | header3                  |   |header3                 |  |header3                |
    
  <Sheet1>                      <Sheet2>                    <Sheet3>

Can someone please let me know what I have to change to make it working?

can try moving wb.write(out) inside the loop, the reason is as you are out of loop only the last iterated value is written to spread sheet.. can try making the change? — Zakir Hussain
– Zakir Hussain, Commented Sep 14, 2021 at 14:34
@zakir Hussain I tried moving WB.write(out) inside loop. But now no additional sheets are created in work book and only first value is displayed as header. — Nithya shree
– Nithya shree, Commented Sep 14, 2021 at 15:51

anteastra · Accepted Answer · 2021-09-14 16:51:12Z

2

Your code snippet is not compilable. So I expect you modified it before post. This line -> for(Map<String, Object> value : list({

In regards of code probably you missed or missplaced curly braces. In this particular example your line wb.write(out); is called 3 times

Update: after your changes in code I see that you do not even use object value to fill data, only header. Use something like this:

XSSFRow hrow = sheet.createRow(0);
XSSFRow vrow = sheet.createRow(1);
for(Map<String, Object> value : list) {
    
    Set<String> headerKeyset = value.keySet();
    for(String keyHeader : headerKeyset) {
       int cellnum= 0;
       XSSFCell hcell = hrow.createCell(cellnum++);
       hcell.setCellValue(keyHeader);
       XSSFCell vcell = vrow.createCell(cellnum++);
       vcell.setCellValue(value.getValue(keyHeader));
    }
}

edited Sep 14, 2021 at 16:51

answered Sep 14, 2021 at 14:26

anteastra

1257 bronze badges

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

Collectives™ on Stack Overflow

writing Map<String, List<Map<String,Object>>> data into excel using apache poi

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related