使用java中的apache poi在现有Excel文件中附加数据

I am trying to append data in existing excel file .But when i write on it it delete my previous data

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

File excelRead

文件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

文件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();
        }
    }

AppendDataInExcel

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);
        }
    }
}

Sometime whole previous excel data deleted.Just wanted solution to append data at the end of the existing row.This concept is used for to log the execution of my testscript with timestamp.whenever I run script it will write in existing excel so that i can see all history of my execution

有时整个以前的excel数据被删除。只需要在现有行的末尾附加数据的解决方案。这个概念用于记录我的testscript的执行时间戳。每当我运行脚本它将写入现有的excel，这样我就可以查看我执行的所有历史记录

4 个解决方案

#1

The code is :

代码是：

package poiwriteexcelfile;
import java.io.File;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class PoiWriteExcelFile {
   private static final PoiWriteExcelFile INSTANCE = new  
      PoiWriteExcelFile();
   private static int a;
   public static PoiWriteExcelFile getInstance() 
   {
        return INSTANCE;
   }

   private PoiWriteExcelFile() {}

   public static void main(String args[])
   {
       write();
   }

   public static void write(){
       try
       {
           FileInputStream myxls = new FileInputStream("poi-testt.xls");
           HSSFWorkbook studentsSheet = new HSSFWorkbook(myxls);
           HSSFSheet worksheet = studentsSheet.getSheetAt(0);
           a=worksheet.getLastRowNum();
           System.out.println(a);
           Row row = worksheet.createRow(++a);
           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){
       }
   }

}

#2

Don't use static field passRowCount in this way, because value is associated with class instead of an object. You can change it to this loops:

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

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);
        }
    }

}

I believe you provided only the code snippet because AppendDataInExcel use workbook never created before.

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

You should return Workbook interface from readExcel method and pass it to AppendDataInExcel, through constructor or method.

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

You can also add append flag to FileOutputStream:

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

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

#3

You can add data in new row in your existing excel sheet rather then overwritting the previous data by using following java code

您可以在现有Excel工作表的新行中添加数据，而不是使用以下java代码覆盖以前的数据

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();

#4

You can add data on new row provided you have to put this code in some loop according to your logic so that it will repeat the process and data is getting added on new row

您可以在新行上添加数据，前提是您必须根据逻辑将此代码放入某个循环中，以便重复该过程并在新行上添加数据

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();

#1