Reputation: 149

Append Data in existing Excel file using apache poi in java

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

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

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.

Upvotes: 9

Answers (5)

Yas

Reputation: 33

In case this helps, the order in Ashwani's answer is important, i.e. :

The following code works :

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

The following code doesn't work :

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

Upvotes: 0

Ashwani

Reputation: 127

The code to append data in Existing .xlsx is as simple:

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

Upvotes: 9

Sumeet Gawas

Reputation: 21

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

Upvotes: 0

Sumeet Gawas

Reputation: 21

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

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

Upvotes: 0

itpr

Reputation: 404

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:

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.

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

You can also add append flag to FileOutputStream:

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

Upvotes: 0

Append Data in existing Excel file using apache poi in java

Answers (5)

Related Questions