POI:将行附加到现有工作簿 [英] POI: Append rows to existing workbook

问题描述

使用 XSSFWorkbook，是否可以将行附加到现有工作表?我正在执行多次写入(由于错误，这是一个需要解决的 PITA)，虽然我可以多次写出新工作表，但似乎无法追加.

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. 将工作表读到我的工作簿中.
2. 加载工作簿.
3. 将行追加到内存中的工作簿
4. 再写一遍.

4 似乎不起作用，只是完全忽略它！

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

我知道 SXSSFWorkbook 存在，但尝试将我现有的 XSSFWorkbook 转换为流式工作簿会在写入时造成损坏.

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?

更新:根据建议更改了代码，但出现流关闭错误.

代码:(物理行正确返回，但没有写出)

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;
    }  

推荐答案

您似乎总是对 Sheet 0 进行更改，但您每次都在调用 createRow.如果那里已经有东西，这将不会顺利，例如在您的第二次通过时！您要么每次都需要添加一个新工作表，或者首先调用 getRow(int) 来检查该行是否存在，并且仅在它为空时才创建.

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

应该是这样的:

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

这篇关于POI:将行附加到现有工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！