0

Working with Apache POI to write some data from DB to Excel file template. The template contains the header and no rows. After adding all the rows properly when I try to update the table area as below

AreaReference newArea = new AreaReference("A1:Bxxx", SpreadsheetVersion.EXCEL2007);//Update xxx right bottom based on rows added.
        XSSFTable table = sheet.getTables().get(0);
        log.error(table.getArea().formatAsString());//Existing area is going to  print(A1:BJ1) // No rows only table header in file
        table.setArea(newArea);//Set new area
        log.error(table.getArea().formatAsString());//Will print new area (A1:Bxxx)

The file can not open in Excel any more. Error as below:

</summary><removedParts><removedPart>Removed Part: /xl/queryTables/queryTable1.xml part.  (External data range)</removedPart>

File can be recovered but it's not anymore a table just rows and headers.

If I remove the lines of code the file is open properly but the rows added are not recognize as table rows.

What I have tried so far it's not fixing this issue.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>

If I try to apply the same area in Excel it works properly.

A dirty solution would be to resize the table in the template file and make the table enough area to refer to a large number of rows so the number of rows that we are going to add will never exceed that. But that will add unnecessary rows to the table. Example A1:B500.

java programming
  • 125
  • 2
  • 11
  • That's correct from Excel the user should be able to update directly the DB. But the issue is that this happened only if I attempted to specify the new table AreaReference. – java programming Aug 31 '23 at 09:54
  • 1
    The error message tells: `Removed Part: /xl/queryTables/queryTable1.xml`. That is a query table probably assigned to your table. That is something special what you must have done while creating the table template. Is this really necessary? A query table gets used to fetch data to the table directly from an external source, from SQL-database for ex. But you seems want to fetch data to the table via Apache POI, no? But then the query table is not necessary. If it is needed, the query table also needs to be updated to be synchron with the assigned table. But that is not provided by Apache POI. – Axel Richter Aug 31 '23 at 09:54

0 Answers0