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
– Gagravarr

2018-07-05 14:14:50 +00:00
Commented Jul 5, 2018 at 14:14 — Gagravarr
– Gagravarr, Commented Jul 5, 2018 at 14:14
i am Using latest apache poi 3.17.

Raj Perumalsamy
– Raj Perumalsamy

2018-07-05 14:47:45 +00:00
Commented Jul 5, 2018 at 14:47 — Raj Perumalsamy
– Raj Perumalsamy, Commented Jul 5, 2018 at 14:47

Axel Richter · Accepted Answer · 2018-11-06 17:22:52Z

11

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

edited Nov 6, 2018 at 17:22

answered Jul 5, 2018 at 16:42

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MattWeiler Over a year ago

Can this be done in legacy HSSF workbooks? I haven't been able to find a way :(

Axel Richter Over a year ago

@MattWeiler: Not possible using apache poi until now. There is only XSSFTable which implements Table. There is no HSSFTable until now.

MattWeiler Over a year ago

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?

Axel Richter Over a year ago

@MattWeiler: The binary *.xls BIFF format supports table aka ListObject too. But apache poi does not provide any classes to handle them until now.

MattWeiler Over a year ago

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.

volt · Accepted Answer · 2022-03-22 14:30:00Z

3

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

answered Mar 22, 2022 at 14:30

volt

1851 gold badge2 silver badges8 bronze badges

1 Comment

Nick Over a year ago

can you write full example, because I tried what I have written, but it doens't help

Toni · Accepted Answer · 2020-10-21 05:19:53Z

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 .

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related