Apache POI / 一键在Excel中插入新行

huangapple go评论59阅读模式
英文:

Apache POI / new row in Excel with one button press

问题

以下是你的代码的中文翻译部分:

我目前正在尝试编写一个可以更好地组织你的工作时间的程序因此我使用Apache POI来创建一个Excel文件我已经相对进展较多但在某一点上无法继续前进我的问题是你每天输入工时程序将其写入相应的Excel文件但是如果我想输入下一天程序会覆盖Excel文件并删除前一天的内容
所以我的问题是我不知道如何在下面的行中写入数据
我已经尝试过FileInputStream但因为我每个月都创建一个新文件所以它并没有真正起作用

这是我用于创建和写入Excel文件的代码

public void speichern(ActionEvent event) throws Exception {

    LocalDate localDate = date.getValue();
    datum = String.valueOf(localDate);

    do {
        if(btnNextMon.isArmed()){
            monClick++;
            System.out.println(monClick);
        }
        if (btnSave.isArmed()){
            saveClick++;
        }

        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("Stundenabrechnung");
            sheet.setDefaultColumnWidth(18);

            Map<String, Object[]> data = new TreeMap<>();
            data.put("1", new Object[]{"DATUM:", " INS. ABG. STUNDEN:", " ABGR. STUNDEN:", " BESCHREIBUNG:"});
            data.put("2", new Object[]{datum, ergbnis + "0", LHabg.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" + monClick + ".xlsx");
            workbook.write(outputStream);
            workbook.close();

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

希望这对你有所帮助!

英文:

I am currently trying to write a program that can better organize your working hours. So I use Apache POI to create an Excel file. I'm already relatively far, but I can't get any further at one point. My problem is that you enter your hours per day and the program writes them in the corresponding Excel file. However, if I want to enter the next day, the program overwrites the Excel file and the previous day is deleted.
So my problem is that I don't know how to write in the rows underneath.
I've already tried FileInputStream, but it didn't really work because I create a new file every month.

This is my code for creating and writing the Excel file.

public void speichern(ActionEvent event) throws Exception {
LocalDate localDate = date.getValue();
datum = String.valueOf(localDate);
do {
if(btnNextMon.isArmed()){
monClick++;
System.out.println(monClick);
}
if (btnSave.isArmed()){
saveClick++;
}
try {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(&quot;Stundenabrechnung&quot;);
sheet.setDefaultColumnWidth(18);
Map&lt;String, Object[]&gt; data = new TreeMap&lt;&gt;();
data.put(&quot;1&quot;, new Object[]{&quot;DATUM:&quot;, &quot; INS. ABG. STUNDEN:&quot;, &quot; ABGR. STUNDEN:&quot;, &quot; BESCHREIBUNG:&quot;});
data.put(&quot;2&quot;, new Object[]{datum, ergbnis + &quot;0&quot;, LHabg.getText(), taBes.getText()});
Set&lt;String&gt; 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(&quot;Stundenabrechnung&quot; + monClick +&quot;.xlsx&quot;);
workbook.write(outputStream);
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(&quot;ExcelFile is created succsessfully&quot;);
}while (btnNextMon.isPressed());
}

thanks for the help Apache POI / 一键在Excel中插入新行

答案1

得分: 1

我认为在创建新的书和电子表格之前,你应该检查文件是否已经存在。

你可以使用类似以下的代码:

编辑: 我已经更改了引发问题的那一行

try {
    boolean fileExists = new File("Stundenabrechnung" + monClick + ".xlsx").exists();
    XSSFWorkbook workbook;
    XSSFSheet sheet;
    if (fileExists) {
        workbook = new XSSFWorkbook(new FileInputStream(new File("Stundenabrechnung" + monClick + ".xlsx")));
        sheet = workbook.getSheetAt(0);

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

        Set<String> keyset = data.keySet();
        int rownum = sheet.getLastRowNum() + 1;
        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:", " BESCHREIBUNG:"});
        data.put("2", new Object[]{datum, ergbnis + "0", LHabg.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" + monClick + ".xlsx");
    workbook.write(outputStream);
    workbook.close();

} catch (Exception e) {
    e.printStackTrace();
}
英文:

I think you should check if the file already exists before creating a new book and spreadsheet

You could use something like:

EDIT: I changed the line that was causing the problem

 try {
boolean fileExists = new File(&quot;Stundenabrechnung&quot; + monClick +&quot;.xlsx&quot;).exists();
XSSFWorkbook workbook;
XSSFSheet sheet;
if (fileExists) {
workbook = new XSSFWorkbook(new FileInputStream(new File(&quot;Stundenabrechnung&quot; + monClick +&quot;.xlsx&quot;)));
sheet = workbook.getSheetAt(0);
Map&lt;String, Object[]&gt; data = new TreeMap&lt;&gt;();
data.put(&quot;3&quot;, new Object[]{datum, ergbnis + &quot;0&quot;, LHabg.getText(), taBes.getText()});
Set&lt;String&gt; keyset = data.keySet();
int rownum = sheet.getLastRowNum() + 1;             
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(&quot;Stundenabrechnung&quot;);
sheet.setDefaultColumnWidth(18);
Map&lt;String, Object[]&gt; data = new TreeMap&lt;&gt;();
data.put(&quot;1&quot;, new Object[]{&quot;DATUM:&quot;, &quot; INS. ABG. STUNDEN:&quot;, &quot; ABGR. STUNDEN:&quot;, &quot; BESCHREIBUNG:&quot;});
data.put(&quot;2&quot;, new Object[]{datum, ergbnis + &quot;0&quot;, LHabg.getText(), taBes.getText()});
Set&lt;String&gt; 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(&quot;Stundenabrechnung&quot; + monClick +&quot;.xlsx&quot;);
workbook.write(outputStream);
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}

huangapple
  • 本文由 发表于 2020年8月7日 07:20:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/63293052.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定