Issue in writing data to excel in java

Question

I am trying to pull data from SQL Table and write it to Excel sheet.

I am currently creating a HashMap - HashMap<Integer, ArrayList<String>> tblDataMap = new HashMap<Integer, ArrayList<String>>() and writing all the data from table to hashmap.

When I try to write the same thing to excel, only the last column gets written, rest of the columns are blank.

Below is my code to iterate.

public String writeDataToExcel(String fileLoc, String tblName)
    {
        try{
            LisaDBUtil lisaDb = new LisaDBUtil();
            HashMap<Integer, ArrayList<String>> tblDataMap = lisaDb.getTableDetails(tblName);
            System.out.println("Map : " +tblDataMap.toString());
            XSSFWorkbook workbook = new XSSFWorkbook(); 
            XSSFSheet sheet = workbook.createSheet(tblName);

            Integer rowCount = 0;
            Integer key = 1;

            short col = 0;
            ArrayList<String> tmpArr = null;

            while(tblDataMap.containsKey(key))
            {

                tmpArr = tblDataMap.get(key);
                System.out.println("tmpArr : " +tmpArr.toString());
                for (String val : tmpArr)
                {
                    XSSFRow row = sheet.createRow(rowCount);
                    System.out.println("Cell value : " +val +"Col : " +col);
                    XSSFCell cell = row.createCell(col);
                    cell.setCellValue(val);
                    System.out.println("Cel Value set : " +cell.getColumnIndex() +cell.getRowIndex());
                    col++;
                    sheet.autoSizeColumn(col);
                }

                col = 0;
                rowCount++;
                key++;

                System.out.println("row value : " +rowCount);
            }
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            //get current date time with Date()
            Date date = new Date();
            System.out.println(dateFormat.format(date));
            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File(fileLoc+"/"+tblName+"_"+dateFormat.format(date)+".xlsx"));
            workbook.write(out);
            out.close();
            FILE_CREATION_STATUS = "success";
            System.out.println("Excel written successfully on disk.");
        } 
        catch (Exception e) 
        {
            e.printStackTrace();
        }
        return FILE_CREATION_STATUS;
    }

I am able to see the cell values are set properly.. Please find below the console log.

Map : {1=[tdm_id, Account_No, Customer_Name, IACode, Supp_Account], 2=[1001, 9384938493, Sindhu, YW, 34545655667], 3=[1002, 9486958958, Ussanar, LLB, 656565576], 4=[1003, 8568632432, XYZ, YB, 45654654654]}
tmpArr : [tdm_id, Account_No, Customer_Name, IACode, Supp_Account]
Cell value : tdm_idCol : 0
Cel Value set : 00
Cell value : Account_NoCol : 1
Cel Value set : 10
Cell value : Customer_NameCol : 2
Cel Value set : 20
Cell value : IACodeCol : 3
Cel Value set : 30
Cell value : Supp_AccountCol : 4
Cel Value set : 40
row value : 1
tmpArr : [1001, 9384938493, Sindhu, YW, 34545655667]
Cell value : 1001Col : 0
Cel Value set : 01
Cell value : 9384938493Col : 1
Cel Value set : 11
Cell value : SindhuCol : 2
Cel Value set : 21
Cell value : YWCol : 3
Cel Value set : 31
Cell value : 34545655667Col : 4
Cel Value set : 41
row value : 2
tmpArr : [1002, 9486958958, Ussanar, LLB, 656565576]
Cell value : 1002Col : 0
Cel Value set : 02
Cell value : 9486958958Col : 1
Cel Value set : 12
Cell value : UssanarCol : 2
Cel Value set : 22
Cell value : LLBCol : 3
Cel Value set : 32
Cell value : 656565576Col : 4
Cel Value set : 42
row value : 3
tmpArr : [1003, 8568632432, XYZ, YB, 45654654654]
Cell value : 1003Col : 0
Cel Value set : 03
Cell value : 8568632432Col : 1
Cel Value set : 13
Cell value : XYZCol : 2
Cel Value set : 23
Cell value : YBCol : 3
Cel Value set : 33
Cell value : 45654654654Col : 4
Cel Value set : 43
row value : 4
2013-11-28
Excel written successfully on disk.
Status : success

The cells are iterated properly and values are set properly. But once excel is generated, there is values only in one column. Someone please help.

Answer 1

Seems just a silly mistake. XSSFRow row = sheet.createRow(rowCount); should be just before the for loop. Once the row has been created, you need not to create again and again for the cells of the same row. Each time you are creating the cells, you just replacing the previous data of the same row with newly created blank row. And that is why only last column is appearing.

    while(tblDataMap.containsKey(key))
        {

            tmpArr = tblDataMap.get(key);
            System.out.println("tmpArr : " +tmpArr.toString());
            // for all the column of same row, you just need to create row only once
            XSSFRow row = sheet.createRow(rowCount);
            for (String val : tmpArr)
            {
                System.out.println("Cell value : " +val +"Col : " +col);
                XSSFCell cell = row.createCell(col);
                cell.setCellValue(val);
                System.out.println("Cel Value set : " +cell.getColumnIndex() +cell.getRowIndex());
                col++;
                sheet.autoSizeColumn(col);
            }

            col = 0;
            rowCount++;
            key++;

            System.out.println("row value : " +rowCount);
        }