append data into xlsx file through java

Question

I am using Apache POI for writing into .xlsx file. I can write into .xlsx file but I am unable to append new content. How can I append new content in the .xlsx file?

My Code is:

public static void write(){
    try {           
        Workbook[] wbs = new Workbook[]{new XSSFWorkbook()};
        Workbook workbook=wbs[0];
        org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet();
        System.out.println(sheet.getSheetName());
        Row row = sheet.createRow(2);
        for(int i=0;i<10;i++){
               Cell cell=row.createCell(i);
               cell.setCellValue("Sun System");
        }
        FileOutputStream fout=new FileOutputStream("D:/Test.Xlsx");
        workbook.write(fout);
        fout.close();
    } catch (Exception e) {
    }
}

What kind of content? Cells? Columns? Worksheets? And about *unable* - because of an error/exception or because you need additional knowledge? — Andreas Dolk, Jul 05 '10 at 08:26

score 7 · Answer 1 · edited Dec 02 '15 at 15:42

The first thing U've to do :

When you're working with Excel 2007 format, its more wise to use XSSF-Implementations, because you've used abstract implementations. Always remember this when using any implementation.

To append to an existing file you need to reach the end of the rows in that particular workbook sheet. This can be achieved by:

int rows = sheet.getPhysicalNumberOfRows(); // or sheet.getLastRowNum();

After that you can create new cells with the XSSF- Implementation classes. For more information refer to this page

score 3 · Answer 2 · edited May 23 '17 at 12:17

3

You should open the existing file instead of creating a new one if you want to append, see also this stackoverflow question:

Edit existing excel files using jxl api / Apache POI

edited May 23 '17 at 12:17

Community

1
1

answered Jul 05 '10 at 09:22

Marc van Kempen

546
2
7

score 0 · Answer 3 · answered Jun 20 '22 at 17:47

You are creating a new workbook each time this is run youd want to create a FileInputStream with a file path to the excel file and then use that input stream to get the XSSF workbook you want to edit

FileInputStream fis = new FileInputStream(filePath);
XSSFWorkbook workBook = new XSSFWorkbook(fis);

then in order to get a specific sheet you simply just use the .getSheet or getSheetAt methods that are apart of workBook. then create and set cells

append data into xlsx file through java

3 Answers3