0
votes

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?