java - 在 java 中使用 apache poi 在现有 Excel 文件中追加数据

Question

我正在尝试将数据附加到现有的 excel 文件中。但是当我在上面写入时，它会删除我以前的数据

文件excelRead

package Excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelRead {
    static int passRowCount;
    int rowCount;

    Sheet guru99Sheet;
    Workbook guru99Workbook = null;

    public void readExcel(String filePath, String fileName, String sheetName) throws IOException, InterruptedException {
        // Create a object of File class to open xlsx file
        System.out.println(filePath + "\\" + fileName);

        File file = new File(filePath + "\\" + fileName);

        // Create an object of FileInputStream class to read excel file
        FileInputStream inputStream = new FileInputStream(file);

        // Find the file extension by spliting file name in substring and
        // getting only extension name
        String fileExtensionName = fileName.substring(fileName.indexOf("."));

        // Check condition if the file is xlsx file
        if (fileExtensionName.equals(".xlsx")) {
            System.out.println("in xlsx");
            // If it is xlsx file then create object of XSSFWorkbook class
            guru99Workbook = new XSSFWorkbook(inputStream);

        }
        // Check condition if the file is xls file
        else if (fileExtensionName.equals(".xls")) {
            // If it is xls file then create object of XSSFWorkbook class
            guru99Workbook = new HSSFWorkbook(inputStream);

        }

        // Read sheet inside the workbook by its name
        guru99Sheet = guru99Workbook.getSheet(sheetName);
        System.out.println("getFirstRowNum: " + guru99Sheet.getFirstRowNum());

        Thread.sleep(1000);

        // Find number of rows in excel file
        rowCount = (guru99Sheet.getLastRowNum()) - (guru99Sheet.getFirstRowNum());
        System.out.println("rowcount: " + rowCount);

        setRowCount(rowCount);

        // Create a loop over all the rows of excel file to read it
        for (int i = 1; i < rowCount; i++) {
            Thread.sleep(1000);
            // System.out.println("i: " + i);
            Row row = guru99Sheet.getRow(i);

            // System.out.println("getLastCellNum : " + row.getLastCellNum());
            // Create a loop to print cell values in a row
            for (int j = 1; j < row.getLastCellNum(); j++) {
                Thread.sleep(1000);
                // System.out.println("j: " + j);
                // Print excel data in console

                System.out.print(row.getCell(j).getStringCellValue() + " ");
                // System.out.println("\n");
            }
            System.out.println();
        }
    }

    public void setRowCount(int rc) {
        passRowCount = rc;
    }

    public int getRowCount() {
        return passRowCount;
    }
}

文件主文件

package Excel;

import java.io.IOException;

public class MainFile {
    public static void main(String[] args) throws IOException, InterruptedException {
        ExcelRead objExcelFile = new ExcelRead();

        // Prepare the path of excel file
        String filePath = System.getProperty("user.dir") + "\\src\\Excel\\";

        // Call read file method of the class to read data
        objExcelFile.readExcel(filePath, "TestCase2.xlsx", "Java Books");
        AppendDataInExcel appendData = new AppendDataInExcel();
        appendData.append();
    }
}

在 Excel 中追加数据

package Excel;

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class AppendDataInExcel {
     ExcelRead excelRead = new ExcelRead();
    public  void append() {
        int rowc = excelRead.getRowCount();
        System.out.println("rowCountIn Append: " + rowc);
        appendWrite(rowc);
    }

    public  void appendWrite(int rowc) {
        Object[][] bookData = { { "geography", "ali", "e" }, { "chemistry", "Joeloch", "f" }, { "Code", "rahul", "g" },
                { "phyysics", "banl", "h" }, };

        for (Object[] aBook : bookData) {
            Row row = s.createRow(++rowc);
            System.out.println("Row: " + row.getRowNum());
            int columnCount = 0;

            for (Object field : aBook) {
                Cell cell = row.createCell(++columnCount);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }

        }

        try {
            FileOutputStream outputStream = new FileOutputStream(
                    new File(System.getProperty("user.dir") + "\\src\\Excel\\TestCase2.xlsx"));
            workbook.write(outputStream);
            System.out.println("Wrote in Excel");
        } catch (Exception e) {
            System.out.println("Exception: " + e);
        }
    }
}

有时整个以前的 excel 数据都被删除了。只是想要在现有行的末尾附加数据的解决方案。这个概念用于记录我的带有时间戳的测试脚本的执行。每当我运行脚本时，它都会写入现有的 excel 所以我可以看到我执行死刑的所有历史记录。

Answer 1

在 Existing .xlsx 中追加数据的代码非常简单:

public static void write(){
   try
   {
       FileInputStream myxls = new FileInputStream("poi-testt.xls");
       HSSFWorkbook studentsSheet = new HSSFWorkbook(myxls);
       HSSFSheet worksheet = studentsSheet.getSheetAt(0);
       int lastRow=worksheet.getLastRowNum();
       System.out.println(lastRow);
       Row row = worksheet.createRow(++lastRow);
       row.createCell(1).setCellValue("Dr.Hola");
       myxls.close();
       FileOutputStream output_file =new FileOutputStream(new File("poi-
                                                              testt.xls"));  
       //write changes
       studentsSheet.write(output_file);
       output_file.close();
       System.out.println(" is successfully written");
    }
    catch(Exception e)
    {
    }
}