POI: Append rows to existing workbook

问题:

Using XSSFWorkbook, is it possible to append rows to an existing sheet? I am doing multiple writes (which was a PITA to solve due to a bug) and while I can write out new sheets multiple times, it does not appear that I can append.

What I am currently doing is the following:

1. Read sheet to my workbook.
2. Load workbook.
3. Append rows to workbook in memory
4. Write out again.

4 Does not appear to work, just ignores it completely!

I am aware that SXSSFWorkbook exists, but attempting to convert my existing XSSFWorkbook into a streaming workbook creates corruption upon write.

Is it possible to solve this concundrum?

Update: Changed code based on suggestion, but getting stream closed error.

Code: (The physical rows returns correctly, but nothing gets written out)

private void writeToSheetMultipleTimes(SXSSFWorkbook wb,
            ReportTemplateStructure appA, File wbFile)
    {

        Sheet sheet = wb.getSheetAt(0);

        log.info("Attempting multi-write to sheet: " + sheet.getSheetName());
        for(int i = 0; i < 10; i++)
        {

            Row row = sheet.getRow(i);

            if (row == null) {
               row = sheet.createRow(i);
            }
            Cell cell = row.getCell(0, Row.CREATE_NULL_AS_BLANK);



            cell.setCellValue("Written value:" + i);

            int numRows = sheet.getPhysicalNumberOfRows();

            log.info("Current row count: " + numRows);

            try{
                XSSFWorkbook xssfBook = (XSSFWorkbook)writeOutAndReadBack(wb);
                wb.dispose();

                wb = new SXSSFWorkbook(xssfBook);
            } catch (Exception e)
            {
                log.error("Unable to perform multiple write to same sheet", e);
            }
        }


    }

   public Workbook writeOutAndReadBack(Workbook wb) {
        if(!(wb instanceof SXSSFWorkbook)) {
            throw new IllegalArgumentException("Expected an instance of SXSSFWorkbook");
        }

        Workbook result;
        try {
            FileOutputStream baos = new FileOutputStream(streamingWorkBookFile);
            wb.write(baos);
            InputStream is = new FileInputStream(streamingWorkBookFile);
            result = new XSSFWorkbook(is);

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return result;
    }  

回答1:

You appear to be always making changes to Sheet 0, but you're calling createRow every time. This won't go well if there's already something there, eg on your second pass! You either need to add a new Sheet every time, or check if the Row is there with a call to getRow(int) first and only create if it is null.

If we look at your code snippet:

Sheet sheet = wb.getSheetAt(0);
for(int i = 0; i < 10; i++)
{
    Row row = sheet.createRow(i);
    Cell cell = row.createCell(0);

That should either be something like:

Sheet sheet = wb.createSheet();
for(int i = 0; i < 10; i++)
{
    Row row = sheet.createRow(i);
    Cell cell = row.createCell(0);

Or you should check first and only create missing rows/cells, eg

Sheet sheet = wb.getSheetAt(0);
for(int i = 0; i < 10; i++)
{
    Row row = sheet.getRow(i);
    if (row == null) {
       row = sheet.createRow(i);
    }
    Cell cell = row.getCell(0, Row.CREATE_NULL_AS_BLANK);

回答2:

You can use getPhysicalNumberOfRows() Method in XSSFSheet class to get last updated row number. Now create a new row by incrementing this value to append new data.