How do I convert an area in an Excel doc to a table using Apache POI?

Question

I've written an application that fetches data from a database and creates an Excel doc from said data using the XSSF classes in the Apache POI library. I've imported poi, poi-ooxml, and poi-ooxml-schemas, all version 4.1.0.

The file gets written fine and there are no errors when opening the file until I uncomment the table creation code, which I'll paste below:

CellReference topLeft = new CellReference(sheet.getRow(3).getCell(0));
CellReference bottomRight = new CellReference(sheet.getRow(nextRow-1).getCell(3));
AreaReference tableArea = workbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
XSSFTable dataTable = sheet.createTable(tableArea);

I added a few additional fields just to rule out some potential issues:

int test = dataTable.getEndRowIndex(); //Returns 968, as expected
tableArea = dataTable.getArea(); //The area remains A4 to D968, as expected
int testColumns = dataTable.getColumnCount(); //Returns 4, as expected
int testRows = dataTable.getRowCount(); //Returns 968, as expected

When I attempt to open the workbook after uncommenting the above code, I get the following error:

"We found a problem with some content in 'filename.xlsx'. Do you want us to try and recover as much as we can? If you trust the source of this workbook, click yes."

After clicking yes, the data appears without the table present and the following error is displayed: "Removed Part: /xl/tables/table1.xml part with XML error. (Table) Load error. Line 2, column 94."

This is confusing, as all indications show that there should only be 4 columns in the table... Anyone have a clue what might be going on and how to fix it?

Axel Richter · Accepted Answer · 2020-04-20T09:07:18.720

Your XSSFTable lacks names. At least the display name must be set using XSSFTable.setDisplayName.

So dataTable.setDisplayName("Table1"); should solve your issue.

How to detect?

First create a simple complete example. Then, after opening the result using Excel, note what row/column of the /xl/tables/table1.xml the error is about. Then do opening the /xl/tables/table1.xml after unzipping the *.xlsx. Now you should find, the erroneous XML is:

<table id="1" ref="A4:D..." xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><tableColumns count="4">

And the error is immediately after <table id="1" ref="A4:D..." xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">. So the first tag seems to be incomplete.

Now compare that with XML Excel itself creates. You will find that Excel table elements always will have name="TableN" displayName="TableN" set.

Complete example:

import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;

import java.util.GregorianCalendar;

class CreateExcelTable {

 public static void main(String[] args) throws Exception {

  Object[][] data = new Object[][] {
   new Object[] {"Text", "Date", "Number", "Boolean"},
   new Object[] {"Text 1", new GregorianCalendar(2020, 0, 1), 1234d, true},
   new Object[] {"Text 2", new GregorianCalendar(2020, 1, 15), 5678d, true},
   new Object[] {"Text 3", new GregorianCalendar(2020, 2, 1), 90.1234, false},
   new Object[] {"Text 4", new GregorianCalendar(2020, 3, 15), 567.89, false}
  };

  try (XSSFWorkbook workbook = new XSSFWorkbook();
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   XSSFCellStyle dateCellStyle = workbook.createCellStyle();
   dateCellStyle.setDataFormat(14);

   XSSFSheet sheet = workbook.createSheet();
   XSSFRow row = sheet.createRow(0);
   XSSFCell cell = row.createCell(0);
   cell.setCellValue("Lorem ipsum");
   row = sheet.createRow(1);
   cell = row.createCell(0);
   cell.setCellValue("semit dolor");

   int nextRow = 3;
   int nextCol = 0;
   for (Object[] dataRow : data) {
    row = sheet.createRow(nextRow++);
    nextCol = 0;
    for (Object value : dataRow) {
     cell = row.createCell(nextCol++);
     if (value instanceof String) cell.setCellValue((String)value);
     else if (value instanceof GregorianCalendar) {
      cell.setCellValue((GregorianCalendar)value);
      cell.setCellStyle(dateCellStyle);
     }
     else if (value instanceof Double) cell.setCellValue((Double)value);
     else if (value instanceof Boolean) cell.setCellValue((Boolean)value);
    }
   }

   CellReference topLeft = new CellReference(sheet.getRow(3).getCell(0));
   CellReference bottomRight = new CellReference(sheet.getRow(nextRow-1).getCell(3));
   AreaReference tableArea = workbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
   XSSFTable dataTable = sheet.createTable(tableArea);
   //dataTable.setName("Table1");
   dataTable.setDisplayName("Table1");

/* 
   //this styles the table as Excel would do per default
   dataTable.getCTTable().addNewTableStyleInfo();
   XSSFTableStyleInfo style = (XSSFTableStyleInfo)dataTable.getStyle();
   style.setName("TableStyleMedium2");
   style.setShowColumnStripes(false);
   style.setShowRowStripes(true);

   dataTable.getCTTable().addNewAutoFilter().setRef(tableArea.formatAsString());
*/

   workbook.write(fileout);
  }

 }
}

How do I convert an area in an Excel doc to a table using Apache POI?

1 Answers1

Linked