How to insert a table in ms excel using apache java poi

Question

I am trying to insert a table in Excel using Java Apache Poi. But when I am opening the xlsx file it is throwing the following error and I could not solve it:

Removed Part: /xl/tables/table1.xml part with XML error. (Table) Load error. Line 2
repaired records: table from /xl/tables/table1.xml part (table)

My code is the following:

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;

public class Test {

  public static void main(String[] args) throws FileNotFoundException, IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Architecture");

    XSSFTable table = sheet.createTable();
    CTTable cttable = table.getCTTable();


    cttable.setDisplayName("Table1");
    cttable.setId(1);
    cttable.setName("Test");
    cttable.setRef("A1:C11");
    cttable.setTotalsRowShown(false);

    CTTableStyleInfo styleInfo = cttable.addNewTableStyleInfo();
    styleInfo.setShowColumnStripes(false);
    styleInfo.setShowRowStripes(true);


    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);
    for (int i = 1; i <= 3; i++) {
      CTTableColumn column = columns.addNewTableColumn();
      column.setId(i);
      column.setName("Column" + i);
    }

    try (FileOutputStream outputStream = new FileOutputStream("C:\\Office\\TimeSheet\\JavaBooks.xlsx")) {
      workbook.write(outputStream);
    }
  }
}

How can I insert a table in Microsft Excel using Apache Java Poi?

Try upgrading to the latest version of Apache POI? – Gagravarr Jul 05 '18 at 14:14 — Gagravarr, Jul 05 '18 at 14:14
i am Using latest apache poi 3.17. – Raj Perumalsamy Jul 05 '18 at 14:47 — Raj Perumalsamy, Jul 05 '18 at 14:47

Axel Richter · Accepted Answer · 2018-11-06T17:22:52.367

There must be at least content in sheet cells for table column names. In your case cells A1:C1 in sheet Architecture must have content. In former versions of apache poi this content had must match the table column names. In current version now the setting the cell content updates the table column names.

Your code extended to work:

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;

public class ExcelTableTest {

  public static void main(String[] args) throws FileNotFoundException, IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Architecture");

    XSSFTable table = sheet.createTable();
    //XSSFTable table = sheet.createTable(null); //since apache poi 4.0.0
    CTTable cttable = table.getCTTable();

    cttable.setDisplayName("Table1");
    cttable.setId(1);
    cttable.setName("Test");
    cttable.setRef("A1:C11");
    cttable.setTotalsRowShown(false);

    CTTableStyleInfo styleInfo = cttable.addNewTableStyleInfo();
    styleInfo.setName("TableStyleMedium2");
    styleInfo.setShowColumnStripes(false);
    styleInfo.setShowRowStripes(true);

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);
    for (int i = 1; i <= 3; i++) {
      CTTableColumn column = columns.addNewTableColumn();
      column.setId(i);
      column.setName("Column" + i);
    }

    for (int r = 0; r < 2; r++) {
      XSSFRow row = sheet.createRow(r);
      for(int c = 0; c < 3; c++) {
        XSSFCell cell = row.createCell(c);
        if(r == 0) { //first row is for column headers
          cell.setCellValue("Column"+ (c+1)); //content **must** be here for table column names
        } else {
          //cell.setCellValue("Data R"+ (r+1) + "C" + (c+1));
        }
      }
    }    

    try (FileOutputStream outputStream = new FileOutputStream("ExcelTableTest.xlsx")) {
      workbook.write(outputStream);
    }
  }
}

Can this be done in legacy HSSF workbooks? I haven't been able to find a way :( — MattWeiler, Feb 12 '21 at 20:52
@MattWeiler: Not possible using `apache poi` until now. There is only `XSSFTable` which implements [Table](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Table.html). There is no `HSSFTable` until now. — Axel Richter, Feb 13 '21 at 05:12
Thanks for the info. Is that to say that **apache poi** doesn't support it or the `.xls` format doesn't support it? When I use **Excel** and insert a table to wrap data, and save it as `.xls` format, it looks the same as if I use the `.xlsx` format. Does this mean that the `.xls` format supports tables or does **Excel** just apply styles to the cells/rows to make them look like tables in the `.xlsx` format? — MattWeiler, Feb 17 '21 at 14:21
@MattWeiler: The binary `*.xls` `BIFF` format supports table aka ListObject too. But `apache poi` does not provide any classes to handle them until now. — Axel Richter, Feb 17 '21 at 14:41
Thank you, that makes sense. So I guess I'll just have to apply cell/row styles if the user opts to have `.xls` files generated and apply a proper `table` if the user opts to have `.xlsx` files generated. — MattWeiler, Feb 17 '21 at 20:33

score 3 · Answer 2 · answered Mar 22 '22 at 14:30

in case of excel errors appears with file opening.... Remove part:t /xl/tables/table1.xml with XML error.

seemed to be caused by duplicite table/tableColumn section in table1.xml

can be fixed with slightly different column name creations/setting

       for (int i = 0; i <= REPORT_COL_COUNT; i++) {
           String colName = sh.getRow(FIRST_DATA_ROW-1).getCell(i).getStringCellValue();
           CTTableColumn column = columns.getTableColumnArray(i);
           column.setName(colName);
       }

tested with poi 5.2.2

score 0 · Answer 3 · answered Oct 21 '20 at 05:19

Additional to 'Axel Richter' I had to add the Autofilter. I realized when I opened the Excel with an ZipArchiv-software.

Here is the code I use:

private void createCTTable(XSSFTable table)
{
   cttable = table.getCTTable();
   cttable.setDisplayName("Tabelle1");
   cttable.setId(1);
   cttable.setName("Test");
   cttable.setTotalsRowShown(false);
   AreaReference my_data_range = new AreaReference(new CellReference(0, 0),
         new CellReference(rowVectors.size(), header.length - 1), SpreadsheetVersion.EXCEL2007);
   cttable.setRef(my_data_range.formatAsString());
   CTAutoFilter autofilter = cttable.addNewAutoFilter();
   autofilter.setRef(my_data_range.formatAsString());
}

'header' is an Array with Strings and rowVectors is an Collection of Objects with the values of the DataBody of the table.

I use Apache Poi 4.1.1 .

How to insert a table in ms excel using apache java poi

3 Answers3

Linked