[Solved] Insert a row at the end of an Excel file without it’s old data

by


In general you can’t update a spreadsheet inline, or the API doesn’t work nicely anyway. But, this is how you can modify an existing spreadsheet and overwrite the old workbook with the changed workbook.

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;

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.ss.usermodel.WorkbookFactory;


public class ModifySheet {

    public static void main(String[] args) throws Exception {
        String fileName = "MyExcel.xlsx";

        // load the workbook
        InputStream inp = new FileInputStream(fileName);
        Workbook wb = WorkbookFactory.create(inp);
        inp.close();

        // make some changes
        Sheet sh = wb.getSheetAt(0);
        Row r = sh.createRow(sh.getPhysicalNumberOfRows());
        Cell c = r.createCell(0);
        c.setCellValue(555);
        c = r.createCell(1);
        c.setCellValue(777);

        // overwrite the workbook with changed workbook
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();
        wb.close();
    }
}

Here is a reference for you so you don’t have to do a lot of searching: https://poi.apache.org/spreadsheet/quick-guide.html

There is also a lot of other useful stuff on that site, including javadocs and examples.

2

solved Insert a row at the end of an Excel file without it’s old data