CODEWITHSUNDEEP
javaexcelapache-poixlsx
luthier
luthier

Reputation: 2871

Adding a column to an existing table in an XSLX file using Apache POI

I'm trying to use Apache POI (version 3.15-beta2, but I have also tried 3.14) to add columns to an existing table in an XLSX spreadsheet. Using the code below, I manage to add a column, but it doesn't fully work.

Excel 2013 gives an error when trying to open the file (We found a problem with some content in '....xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.). If I click "yes", the file is opened and the table has been successfully resized and the new content added, but the Column does not have the name I set for it (it's set to "Columna1") and it has a filter that I don't want, plus of course I'd like to get rid of the warning when opening the file.

Does anyone know where the problem might lie? Thanks in advance!

Here's the (edited) code. My original table has three columns already (the first one is a header column), and its data is on A18:C24).

XSSFTable selectedTable = findMyTable();

CTTable ctTable = selectedTable.getCTTable();

CTTableColumns tableColumns = ctTable.getTableColumns();
tableColumns.setCount(4);

CTTableColumn newCol = tableColumns.addNewTableColumn();

newCol.setId(4);
newCol.setName("NEWCOLUMN");

ctTable.setRef("A18:D24");
CTAutoFilter autoFilter = ctTable.getAutoFilter();
autoFilter.setRef("A18:D24");

For reference, this is what I get: Excel table with new column

Upvotes: 0

Views: 3894

Answers (1)

luthier
luthier

Reputation: 2871

Ok, the problem was apparently that you have to manually set the cell value of the column header to the name that you have set for the CTTableColumn object.

So, adding

sheet.getRow(17).createCell(3);
sheet.getRow(17).getCell(3).setCellValue("NEWCOLUMN");

did the trick.

And to disable filtering in all columns, just add

ctTable.unsetAutoFilter();

Upvotes: 0

Related Questions