Java Apache POI - Adding table in all sheets of a workbook throwing error in MS Excel

Question

I am using Apache POI 3.17 and XSSFWorkbook to create an excel workbook. The workbook contains multiple sheets and I am trying to add data in a table in every sheet.

Java Main Method

    XSSFWorkbook workbook = new XSSFWorkbook();
    for (String sheet : sheets)
    {
        XSSFSheet sheet = workbook.createSheet(sheet);
        List<String> colNames = Arrays.asList("Column1","Column2","Column3");

        createTable(sheet,colNames);

        for (int r = 0; r <= 1; r++)
        {
            XSSFRow row = sheet.createRow(r);
            for (int c = 0; c < colNames.size(); c++)
            {
                XSSFCell cell = row.createCell(c);
                cell.setCellValue("some value");
            }

        }
        for (int i = 0; i < colNames.size(); i++)
        {
            sheet.autoSizeColumn(i);
        }
    }

Method for adding a table in a sheet

private void createTable(XSSFSheet sheet, List<String> colNames)
{
    XSSFTable table = sheet.createTable();
    CTTable cttable = table.getCTTable();

    CellReference startCellReference = new CellReference(0, 0);

    CellReference endCellReference = new CellReference(2,colNames.size());
    AreaReference areaReference = new AreaReference(startCellReference, endCellReference, SpreadsheetVersion.EXCEL2007);

    cttable.setDisplayName("SummaryData_" + sheet.getSheetName());
    cttable.setId(1);
    cttable.setName("SummaryData_" + sheet.getSheetName());
    cttable.setRef(areaReference.formatAsString());
    cttable.setTotalsRowShown(false);

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

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(colNames.size());
    for (int i = 1; i <= colNames.size(); i++)
    {
        CTTableColumn column = columns.addNewTableColumn();
        column.setId(i);
        column.setName(colNames.get(i - 1));
    }
}

If there is only one sheet then excel opens up properly in MS-Excel, but if there are more than 1 sheet then excel gives an error on opening the workbook.

If I click on yes then I get the repair summary for the table

Although data is present in the workbook.

If I remove the table and add the data directly into the sheet then it works properly without any error.

Can someone please help Thanks in advance

Answer 1

Multiple problems here:

1. The variable sheet cannot be a String and a XSSFSheet the same time.

2. The colNames.size() gets 3 in your case. So your endCellReference is D3 and your areaReference is A1:D3, thus one column too wide. It would must be A1:C3. So: CellReference endCellReference = new CellReference(2, colNames.size()-1);.

3. Don't set all table's Ids to 1 manually using cttable.setId(1). Each table needs its own Id. The sheet.createTable() had done that properly already.

4. Sheet's cell values must match the table's column names. So you cannot set "some value" in all cells. The values of the cells which display the table's column names must match the table's column names.

Complete example corrected:

import java.io.FileOutputStream;

import org.apache.poi.ss.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

import java.util.*;

public class CreateTablePOI3 {

 private static void createTable(XSSFSheet sheet, List<String> colNames) {

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

  CellReference startCellReference = new CellReference(0, 0);
  //CellReference endCellReference = new CellReference(2, colNames.size()); //one column too wide
  CellReference endCellReference = new CellReference(2, colNames.size()-1);
  AreaReference areaReference = new AreaReference(startCellReference, endCellReference, SpreadsheetVersion.EXCEL2007);

  cttable.setDisplayName("SummaryData_" + sheet.getSheetName());
  //cttable.setId(1); // Don't set table's Id manually. The sheet.createTable() is doing that properly.
  cttable.setName("SummaryData_" + sheet.getSheetName());
  cttable.setRef(areaReference.formatAsString());
  cttable.setTotalsRowShown(false);

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

  CTTableColumns columns = cttable.addNewTableColumns();
  columns.setCount(colNames.size());
  for (int i = 1; i <= colNames.size(); i++) {
   CTTableColumn column = columns.addNewTableColumn();
   column.setId(i);
   column.setName(colNames.get(i - 1));
  }
 }

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

  List<String> sheetNames = Arrays.asList("Sheet1","Sheet2","Sheet3");

  XSSFWorkbook workbook = new XSSFWorkbook();
  for (String sheetName : sheetNames) {
   XSSFSheet sheet = workbook.createSheet(sheetName);
   List<String> colNames = Arrays.asList("Column1","Column2","Column3");

   createTable(sheet, colNames);

   for (int r = 0; r <= 2; r++) {
    XSSFRow row = sheet.createRow(r);
    for (int c = 0; c < colNames.size(); c++) {
     XSSFCell cell = row.createCell(c);
     //cell.setCellValue("some value"); //sheet's cell values must match the table's column names  
     if (r == 0) {
      cell.setCellValue(colNames.get(c));
     } else {
      cell.setCellValue("some value");
     }
    }
   }
   for (int i = 0; i < colNames.size(); i++) {
    sheet.autoSizeColumn(i);
   }
  }

  FileOutputStream out = new FileOutputStream("./Excel.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

---

The above had worked using Apache POI 3.17 version as mentioned in Question.

Current Apache POI versions of 2023 do not have XSSFSheet.createTable() without parameters anymore. To make this code work using current Apache POI versions of 2023, one could change:

...
  //XSSFTable table = sheet.createTable();
  XSSFTable table = sheet.createTable(null);
...

But the whole usage of the underlying CT* classes is not more necessary.

The current code should look like so:

import java.io.FileOutputStream;

import org.apache.poi.ss.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.*;

import java.util.*;

public class CreateTablePOI4 {

 private static void createTable(XSSFSheet sheet, List<String> colNames) { 
  CellReference startCellReference = new CellReference(0, 0);
  CellReference endCellReference = new CellReference(2, colNames.size()-1);
  AreaReference areaReference = new AreaReference(startCellReference, endCellReference, SpreadsheetVersion.EXCEL2007);
  XSSFTable dataTable = sheet.createTable(areaReference);
  //dataTable.setName("SummaryData_" + sheet.getSheetName());
  dataTable.setDisplayName("SummaryData_" + sheet.getSheetName());
  //this styles the table as Excel would do per default
  dataTable.getCTTable().addNewTableStyleInfo();
  XSSFTableStyleInfo style = (XSSFTableStyleInfo)dataTable.getStyle();
  style.setName("TableStyleMedium13");
  style.setShowColumnStripes(false);
  style.setShowRowStripes(true);
  dataTable.getCTTable().addNewAutoFilter().setRef(areaReference.formatAsString());
 }

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

  List<String> sheetNames = Arrays.asList("Sheet1","Sheet2","Sheet3");

  XSSFWorkbook workbook = new XSSFWorkbook();
  for (String sheetName : sheetNames) {
   XSSFSheet sheet = workbook.createSheet(sheetName);
   List<String> colNames = Arrays.asList("Column1","Column2","Column3");

   for (int r = 0; r <= 2; r++) {
    XSSFRow row = sheet.createRow(r);
    for (int c = 0; c < colNames.size(); c++) {
     XSSFCell cell = row.createCell(c);
     //cell.setCellValue("some value"); //sheet's cell values must match the table's column names  
     if (r == 0) {
      cell.setCellValue(colNames.get(c));
     } else {
      cell.setCellValue("some value");
     }
    }
   }
   
   createTable(sheet, colNames);

   for (int i = 0; i < colNames.size(); i++) {
    sheet.autoSizeColumn(i);
   }
  }

  FileOutputStream out = new FileOutputStream("./Excel.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

Note, that createTable(sheet, colNames) gets called after the sheet data is filled. The new version of XSSFSheet.createTable(AreaReference) gets all the information for table columns out of the filled AreaReference.

Answer 2

The previous answer is not working with modern version of POI.

Sheet.createReference() now needs an AreaReference as parameter.

So I switched some lines to have it :

CellReference startCellReference = new CellReference(0, 0);
    //CellReference endCellReference = new CellReference(2, colNames.size()); //one column too wide
    CellReference endCellReference = new CellReference(2, colNames.size() - 1);
    AreaReference areaReference = new AreaReference(startCellReference, endCellReference, SpreadsheetVersion.EXCEL2007);

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

    cttable.setDisplayName("SummaryData_" + sheet.getSheetName());
    //cttable.setId(1); // Don't set table's Id manually. The sheet.createTable() is doing that properly.
    cttable.setName("SummaryData_" + sheet.getSheetName());
    cttable.setRef(areaReference.formatAsString());
    cttable.setTotalsRowShown(false);

It was still not working. BUT, if you pass null as parameter instead of a valid AreaReference, then it works :

 CellReference startCellReference = new CellReference(0, 0);
    //CellReference endCellReference = new CellReference(2, colNames.size()); //one column too wide
    CellReference endCellReference = new CellReference(2, colNames.size() - 1);
    AreaReference areaReference = new AreaReference(startCellReference, endCellReference, SpreadsheetVersion.EXCEL2007);

    XSSFTable table = sheet.createTable(null); // Look HERE
    CTTable cttable = table.getCTTable();

    cttable.setDisplayName("SummaryData_" + sheet.getSheetName());
    //cttable.setId(1); // Don't set table's Id manually. The sheet.createTable() is doing that properly.
    cttable.setName("SummaryData_" + sheet.getSheetName());
    cttable.setRef(areaReference.formatAsString());
    cttable.setTotalsRowShown(false);

I don't get why POI is not working when passing a valid AreaReference but now it works, and that's what is important.