javaexport-to-excelpoi-hssf

not able to export data from ArrayList To Excel cells correctly using Java


I want to export data from my 2d ArrayList as in the code:

ArrayList<ArrayList<Double>> bpDataAllLists = messtisch.getMeasDataBrushP();
    for (ArrayList<Double> bpDataList : bpDataAllLists) {
        int bp = ((bpDataAllLists.indexOf(bpDataList)) + 1);
        int measPerP = messtisch.getBpMeasPerPoint();

        HSSFWorkbook bprofileWb = new HSSFWorkbook();
        HSSFSheet mainsheet = bprofileWb.createSheet("Meting");

        HSSFRow bprofileRow = mainsheet.createRow(5);
        HSSFCell bprofilecell = bprofileRow.createCell(3);
        time = messtisch.getMeastime();
        formater = new SimpleDateFormat("yyyy-MM-dd");
        String date = formater.format(time.getTime());
        bprofilecell.setCellValue(date);

        int sCol = 7;
        for (int j = 1; j < measPerP + 1; j++) {
            // TODO create measperpoint colmn
            int sRow = 15;
            int bpPointsSize = bpDataList.size();
            for (int k = j - 1; k < bpPointsSize; k += measPerP) {
                bprofileRow = mainsheet.createRow(sRow);
                bprofilecell = bprofileRow.createCell(sCol);
                bprofilecell.setCellValue(bpDataList.get(k));
                System.out.printf("Row: %d, Column: %d, Value: %f\n",sRow,sCol, bpDataList.get(k));
                sRow+=1;
            }

            sCol+=1;
        }
    }
}

By print function, data is correct as follow:

Row: 15, Column: 7, Value: 12,000000

Row: 16, Column: 7, Value: 12,000000

Row: 17, Column: 7, Value: 12,000000

Row: 18, Column: 7, Value: 12,000000

Row: 19, Column: 7, Value: 12,000000

Row: 20, Column: 7, Value: 12,000000

Row: 21, Column: 7, Value: 12,000000

Row: 22, Column: 7, Value: 12,000000

Row: 23, Column: 7, Value: 12,000000

Row: 24, Column: 7, Value: 12,000000

Row: 25, Column: 7, Value: 12,000000

Row: 26, Column: 7, Value: 12,000000

Row: 27, Column: 7, Value: 12,000000

Row: 28, Column: 7, Value: 12,000000

Row: 29, Column: 7, Value: 12,000000

Row: 30, Column: 7, Value: 12,000000

Row: 31, Column: 7, Value: 12,000000

Row: 32, Column: 7, Value: 12,000000

Row: 33, Column: 7, Value: 12,000000

Row: 34, Column: 7, Value: 12,000000

Row: 35, Column: 7, Value: 12,000000

Row: 36, Column: 7, Value: 12,000000

Row: 37, Column: 7, Value: 12,000000

Row: 38, Column: 7, Value: 12,000000

Row: 15, Column: 8, Value: 14,000000

Row: 16, Column: 8, Value: 14,000000

Row: 17, Column: 8, Value: 14,000000

Row: 18, Column: 8, Value: 14,000000

Row: 19, Column: 8, Value: 14,000000

Row: 20, Column: 8, Value: 14,000000

Row: 21, Column: 8, Value: 14,000000

Row: 22, Column: 8, Value: 14,000000

Row: 23, Column: 8, Value: 14,000000

Row: 24, Column: 8, Value: 14,000000

Row: 25, Column: 8, Value: 14,000000

Row: 26, Column: 8, Value: 14,000000

Row: 27, Column: 8, Value: 14,000000

Row: 28, Column: 8, Value: 14,000000

Row: 29, Column: 8, Value: 14,000000

Row: 30, Column: 8, Value: 14,000000

Row: 31, Column: 8, Value: 14,000000

Row: 32, Column: 8, Value: 14,000000

Row: 33, Column: 8, Value: 14,000000

Row: 34, Column: 8, Value: 14,000000

Row: 35, Column: 8, Value: 14,000000

Row: 36, Column: 8, Value: 14,000000

Row: 37, Column: 8, Value: 14,000000

Row: 38, Column: 8, Value: 14,000000

Row: 15, Column: 9, Value: 16,000000

Row: 16, Column: 9, Value: 16,000000

Row: 17, Column: 9, Value: 16,000000

Row: 18, Column: 9, Value: 16,000000

Row: 19, Column: 9, Value: 16,000000

Row: 20, Column: 9, Value: 16,000000

Row: 21, Column: 9, Value: 16,000000

Row: 22, Column: 9, Value: 16,000000

Row: 23, Column: 9, Value: 16,000000

Row: 24, Column: 9, Value: 16,000000

Row: 25, Column: 9, Value: 16,000000

Row: 26, Column: 9, Value: 16,000000

Row: 27, Column: 9, Value: 16,000000

Row: 28, Column: 9, Value: 16,000000

Row: 29, Column: 9, Value: 16,000000

Row: 30, Column: 9, Value: 16,000000

Row: 31, Column: 9, Value: 16,000000

Row: 32, Column: 9, Value: 16,000000

Row: 33, Column: 9, Value: 16,000000

Row: 34, Column: 9, Value: 16,000000

Row: 35, Column: 9, Value: 16,000000

Row: 36, Column: 9, Value: 16,000000

Row: 37, Column: 9, Value: 16,000000

Row: 38, Column: 9, Value: 16,000000

But in Excel export, I get this as shown in the picture excel screenshot. I am not able to get, what I did wrong. Please help regarding.


Solution

  • Each time I was creating a row, a new row created and that overwritten the previous data. So I needed to create row only once. If the row exists then write in the same row.

    By using this method:

     private HSSFRow getRow(HSSFSheet sheet, int rowNum) {
     HSSFRow sheetRow = null;
     sheetRow = sheet.getRow(rowNum);
      if (null == sheetRow) {
        sheetRow = sheet.createRow(rowNum);
     }  
     return sheetRow;
     }
    

    So now, I changed mainsheet.createRow(sRow); with getRow(mainsheet, sRow); It worked.