Apache-POI/ Java/ leaving out rows when writing to an Excel file

Question

so I am trying to add some numbers together I wrote into an Excel file. At the end of the File I want to write the result, but my problem is, everytime I create the Excel file I can write two rows but the second one is getting skiped, so nothing is written into it. after the third row everything continues running perfectly fine. I tried to solve it myself but unfortunately I can‘t get any further.

This is my code for writing to the ExelFile:

try {
        boolean fileExists = new File("Stundenabrechnung" + test + ".xlsx").exists();
        XSSFWorkbook workbook;
        XSSFSheet sheet;

        if (fileExists) {

            workbook = new XSSFWorkbook(new FileInputStream(new File("Stundenabrechnung" + test + ".xlsx")));
            sheet = workbook.getSheetAt(0);


            Map<String, Object[]> data = new TreeMap<>();
            data.put("3", new Object[]{datum, ergebnis + "0", LHabg.getText(), über, taBes.getText()});

            Set<String> keyset = data.keySet();
            int rownum = sheet.getPhysicalNumberOfRows();
            for (String key : keyset) {
                XSSFRow row = sheet.createRow(rownum++);
                Object[] objArr = data.get(key);
                int cellnum = 0;
                for (Object obj : objArr) {

                    XSSFCell cell = row.createCell(cellnum++);
                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
                    cell.setCellStyle(cellStyle);

                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Integer) {
                        cell.setCellValue((Integer) obj);
                    }
                }
            }

        } else {
            workbook = new XSSFWorkbook();
            sheet = workbook.createSheet("Stundenabrechnung");
            sheet.setDefaultColumnWidth(18);

            Map<String, Object[]> data = new TreeMap<>();
            data.put("1", new Object[]{"DATUM:", " INS. ABG. STUNDEN:", " ABGR. STUNDEN:", "ÜBERSTUNDEN:", " BESCHREIBUNG:"});
            data.put("2", new Object[]{datum, ergebnis + "0", LHabg.getText(), Lueber.getText(), taBes.getText()});


            Set<String> keyset = data.keySet();
            int rownum = 0;
            for (String key : keyset) {
                XSSFRow row = sheet.createRow(rownum++);
                Object[] objArr = data.get(key);
                int cellnum = 0;
                for (Object obj : objArr) {

                    XSSFCell cell = row.createCell(cellnum++);
                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
                    cell.setCellStyle(cellStyle);
                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Integer) {
                        cell.setCellValue((Integer) obj);
                    }
                }
            }
FileOutputStream outputStream = new FileOutputStream("Stundenabrechnung" + test + ".xlsx");
                workbook.write(outputStream);
                workbook.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
            System.out.println("ExcelFile is created succsessfully");

And this is the code for the result:

XSSFRow rowTotal = sheet.createRow(24);
        XSSFCell totalText = rowTotal.createCell(2);
        totalText.setCellValue("Überstunden:");
        XSSFCell totalValue = rowTotal.createCell(3);
        totalValue.setCellFormula("SUM(D2:D20)");

I tried putting the ResultCode into the if-else statment but it didn't work.

This is what the ExcelFile looks like:

I hope someone can help me because this is the last problem I have to solve. After this my program is finished.

thanks!!!

Answer 1

i think you are starting with row number 3 try changing getLastRowNum() from getPhysicalNumberOfRows().