not able to update the excel data - it overwrites the actual data

Question

I have a code which writes to the excel file using apache poi api. The problem is it everytime writes the new data to the file and not append the data. can you please help me here. Here is my code.

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

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

public class ExcelWrite {
    public static void write(AddExcel addExcel){
       try {
           XSSFWorkbook workbook = new XSSFWorkbook("NG.xlsx");
           XSSFSheet worksheet = workbook.createSheet("Scrap Data");



           int lastRow = worksheet.getLastRowNum();
           System.out.println(lastRow);
           Row row = worksheet.createRow(++lastRow);
           row.createCell(2).setCellValue(addExcel.getArtistName());
           row.createCell(3).setCellValue(addExcel.getItemName());
           row.createCell(6).setCellValue(addExcel.getOriginalPrimaryMarket());
           row.createCell(7).setCellValue(addExcel.getAvgResalePrice());
           row.createCell(8).setCellValue(addExcel.getPriceChangedFromPrimaryMarket());
           row.createCell(9).setCellValue(addExcel.getHighestAvgBid());
           row.createCell(10).setCellValue(addExcel.getLastSoldPrice());
           row.createCell(11).setCellValue(addExcel.getSecondayMarketVolume());
           row.createCell(12).setCellValue(addExcel.getSecondarySales());
           row.createCell(13).setCellValue(addExcel.getPrimarySales());
           row.createCell(14).setCellValue(addExcel.getDateCreated());
           row.createCell(16).setCellValue(addExcel.getInstagramURl());
           row.createCell(17).setCellValue(addExcel.getTwitterURL());


           FileOutputStream out = new FileOutputStream(new File("NG.xlsx"));
           workbook.write(out);
           out.close();
           System.out.println("Write Successfully.");

       }
       catch(IOException io){
           System.out.println(io.getMessage());
           System.out.println(io.getStackTrace());
       }

    }
}

your file output stream should be in [append](https://docs.oracle.com/javase/7/docs/api/java/io/FileOutputStream.html#FileOutputStream(java.io.File,%20boolean)) mode and even then I don't think the file will be readable if you write it that way. Your best bet is to again read the file using the api and append rows/columns to it rather than appending raw bytes to the file — Sync it, Jan 17 '21 at 05:14
Can you please give me the correction in the code, I changed this code to this FileOutputStream out = new FileOutputStream(new File("NG.xlsx"),true);, is there still anything missing? — thisisdude, Jan 17 '21 at 07:01
NEVER user "new FileOutputStream(new File("NG.xlsx"), true)" beacuse it will ALWAYS brake your code. May be duplicate of https://stackoverflow.com/questions/65967508/unable-to-open-excel-file-after-excecuting-my-java-apache-poi-program-and-i-am-u/65972752#65972752 read all the answer for hints about how to write new data without overriding existing one. Each time you execute your code, you're RECREATING a new excel DELETING the old one. Another useful link to learn how to update excels: https://stackoverflow.com/questions/65940340/how-to-update-an-excel-file-in-java/65945983#65945983 — VinceLomba, Feb 01 '21 at 10:57

score 1 · Answer 1 · answered Feb 02 '21 at 16:46

The problem is that each time you run your code, you always create a brand new XSSFWorkbook and so a brand new excel file, deleting and overriding the existing one.

It doesn't matters you're calling worksheet.getLastRowNum(); it will always return -1 because your XSSFWorkbook will always be empty.

If you want to update an existing excel file (appending new rows to it), you MUST create your XSSFWorkbook by loading that existing excel file. You're code is broken because with the line

  workbook = new XSSFWorkbook();

you're creating a brand new XSSFWorkbook which is totally unrelated to the excel file you want to update. You HAVE TO use instead:

  Workbook workbook = WorkbookFactory.create(new FileInputStream(toUpdateExcelFilePath));

You might want to have a look at this post for more details: how to update an existing excel file in Java.

score 0 · Accepted Answer · answered Jan 17 '21 at 07:22

According to my test (I used 4.1.2 version from maven repository) when you use createSheet it produce an exception:

java.lang.IllegalArgumentException: The workbook already contains a sheet named 'Scrap Data'

With the getSheet() method you can grab the existing sheet. If it does not exist you will be get a null then you can create a new sheet with the required name.

XSSFWorkbook workbook;
try {
    File file = new File("NG.xlsx");
    workbook = new XSSFWorkbook();
    if(file.exists()) {
       FileInputStream fs = new FileInputStream(file);
       workbook = new XSSFWorkbook(fs);                
    }
    String sheetName = "Scrap Data";
    XSSFSheet worksheet = workbook.getSheet(sheetName);
    if(worksheet == null) {
       worksheet = workbook.createSheet(sheetName);
    }

... //other code unchanged

} catch(Exception io){
  io.printStackTrace();
}

NOTE: I changed the file reading code a little bit because I got errors and the documentation says the following:

* Note - if the Document was opened from a {@link File} rather
*  than an {@link InputStream}, you <b>must</b> write out to
*  a different file, overwriting via an OutputStream isn't possible.

Getting this error while using your code - org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException: No valid entries or contents found, this is not a valid OOXML (Office Open XML) file — thisisdude, Jan 17 '21 at 08:22
sorry to say but if i downgrage to 3.15 the same code working for me. Possible file corruption? I found a similar problem here: [NotOfficeXmlFileException](https://stackoverflow.com/questions/43641081/how-to-fix-notofficexmlfileexception-in-java-apache-poi) may you can check that. I am tested on windows only. — wia, Jan 17 '21 at 10:33

not able to update the excel data - it overwrites the actual data

2 Answers2