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?

Answer 1

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

Answer 2

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 .

Answer 3

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