使用Apache POI在Java中向现有的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;
    }
}

File MainFile

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文件中，这样我就可以看到所有执行历史记录。

- DoubtClear

Answer 1

现有的 .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)
    {
    }
}

- Ashwani

Answer 2

您可以使用以下Java代码将数据添加到现有Excel表格的新行中，而不是覆盖先前的数据。

File myFile_ED = new File("your_file_path");
                        FileInputStream inputStream_ED = new FileInputStream(myFile_ED);

XSSFWorkbook workbook_ED = new XSSFWorkbook(inputStream_ED);
                        XSSFSheet sheet_ED = workbook_ED.getSheetAt(0);
                        Iterator<Row> riterator_ED = sheet_ED.iterator();
                        Row row_ED = sheet_ED.createRow(sheet_ED.getLastRowNum()+1);
                        if(sheet_ED.getLastRowNum()==0){

                        }

                        Cell StartTime_ED = row_ED.createCell(0);
                       StartTime_ED.setCellValue("your data");

                        Cell NoofDevice_ED = row_ED.createCell(1);
                        NoofDevice_ED.setCellValue("your data");

                        Cell Device_ID_ED = row_ED.createCell(2);
                        Device_ID_ED.setCellValue("your data");


                        FileOutputStream os_ED = new FileOutputStream(myFile_ED);
                            workbook_ED.write(os_ED);

                            os_ED.close();
                            workbook_ED.close();
                            inputStream_ED.close();

- Sumeet Gawas

Answer 3

你可以在新行添加数据，只要根据你的逻辑将这段代码放入某个循环中，以便重复执行该过程并将数据添加到新行。

File myFile = new File("your_file_path");
                    FileInputStream inputStream = new FileInputStream(myFile);
                    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
                    System.out.println(" wb: " + workbook);
                    XSSFSheet sheet = workbook.getSheet("your_sheet");
                    System.out.println("sheet: " + sheet + " wb: " + workbook);
                    Object[][] bookData = {

                                    {"data","data", "data","data", "data", "data"},    
                                };

                                int rowCount = 0;

                                Iterator<Row> riterator = sheet.iterator();

                                while (riterator.hasNext()) {
                                    Row nextRow = riterator.next();

                                    if (isColumnEmpty(sheet, 0, 0)) 
                                    break;
                                               rowCount++;

                                }

                                for (Object[] aBook : bookData) {
                                    Row row = sheet.createRow(rowCount++);

                                    int columnCount = 0;

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

                                }
//                              
//                              
                                FileOutputStream os = new FileOutputStream(myFile);
                                workbook.write(os);

                                os.close();
                                workbook.close();
                                inputStream.close();

- Sumeet Gawas

Answer 4

如果有帮助的话，Ashwani回答中的顺序很重要，即：

以下代码有效：

   try
   {
       FileInputStream input = new FileInputStream("testfile.xlsx");
       Workbook workbook = new XSSFWorkbook(input);
       FileOutputStream output = new FileOutputStream("testfile.xlsx");

       ...

       workbook.write(output);

       input.close();
       workbook.close();
       output.close();
    }
    catch(Exception e)
    {
    }

以下代码无法正常工作：

   try
   {
       FileInputStream input = new FileInputStream("testfile.xlsx");
       FileOutputStream output = new FileOutputStream("testfile.xlsx");
       Workbook workbook = new XSSFWorkbook(input);

       ...

       workbook.write(output);

       input.close();
       workbook.close();
       output.close();
    }
    catch(Exception e)
    {
    }

- Yas

Answer 5

不要以这种方式使用静态字段passRowCount，因为该值与类而不是对象相关联。您可以将其更改为以下循环：

for (Object[] aBook : bookData) {
    Row row = s.createRow(s.getLastRowNum()+1);
    System.out.println("Row: " + row.getRowNum());

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

}

我相信你只提供了代码片段，因为AppendDataInExcel使用了之前从未创建过的工作簿。

你应该从readExcel方法返回Workbook接口，并通过构造函数或方法将其传递给AppendDataInExcel。

你还可以向FileOutputStream添加追加标志：

FileOutputStream outputStream = new FileOutputStream(
                    new File(System.getProperty("user.dir") + "\\src\\Excel\\TestCase2.xlsx"), true);

- itpr

---

其实发生的情况是，如果我在appendWrite()方法中创建String sheetName="Java Books"和XSSFWorkbook workbook = new XSSFWorkbook()的实例，就可以向Excel中写入数据，但它会打开一个新的Excel文件。但是，当我试图从ExcelRead类中传递XSSFSheet和XSSFWorkbook的实例时，Excel就变成了空白。那么我应该在代码中做出什么修改呢？ - DoubtClear