Excel Insert Format Table - Apache POI Example

This tutorial explains how to enable "Format as Table" option in Apache POI on Excel Workbooks with a Java Example. In Excel 2007 and above, there is an option to format your data by inserting a table.This option, "Format as Table" converts your data into a neat looking table, with the ability to perform additional operations on them. A screenshot is shown below:

Excel Format As Table - Apache POI Example
Excel Format As Table - Apache POI Example
We will discuss the following in this tutorial;

a) How to add new data and Format As Table using Apache POI.
b) How to convert an existing Workbook data and Format As Table in Apache POI

Check all our Apache POI Tutorials
.

Insert Table - Format As Table - Excel Apache POI Example


The steps involved in "Format As Table" Java Program are captured below: (for new workbooks)
  • Create a workbook object, Add a worksheet.
  • Create an XSSFTable object in the worksheet.
  • Get CTTable object from XSSFTable.
  • Define Style and Style options for your Excel Table - CTTableStyleInfo
  • Define Data Range for your Excel Table - AreaReference
  • Set Table Name, Display Name, ID
  • Add Required Columns, Set Column Name, ID
  • Add your Table Data, with table headings
  • Write the workbook object to a file
  • You are done!
And the Java program that inserts a table into Excel workbook is provided below:

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
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 formatAsTable
{
  public static void main(String[] args)
    throws FileNotFoundException, IOException
  {
    /* Start with Creating a workbook and worksheet object */
        Workbook wb = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet)wb.createSheet();
        
    /* Create an object of type XSSFTable */
    XSSFTable my_table = sheet.createTable();
                
        /* get CTTable object*/
    CTTable cttable = my_table.getCTTable();
    
    /* Let us define the required Style for the table */    
    CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();
    table_style.setName("TableStyleMedium9");   
        
        /* Set Table Style Options */
    table_style.setShowColumnStripes(false); //showColumnStripes=0
    table_style.setShowRowStripes(true); //showRowStripes=1
    
    /* Define the data range including headers */
    AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(4, 2));
    
    /* Set Range to the Table */
        cttable.setRef(my_data_range.formatAsString());
        cttable.setDisplayName("MYTABLE");      /* this is the display name of the table */
    cttable.setName("Test");    /* This maps to "displayName" attribute in <table>, OOXML */            
    cttable.setId(1L); //id attribute against table as long value
             
    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3L); //define number of columns

        /* Define Header Information for the Table */
    for (int i = 0; i < 3; i++)
    {
    CTTableColumn column = columns.addNewTableColumn();   
    column.setName("Column" + i);      
        column.setId(i+1);
    }
          
         /* Add remaining Table Data */
         for (int i=0;i<=4;i++) //we have to populate 4 rows
         {
         /* Create a Row */
     XSSFRow row = sheet.createRow(i);
     for (int j = 0; j < 3; j++) //Three columns in each row
     {
          XSSFCell localXSSFCell = row.createCell(j);
          if (i == 0) {
         localXSSFCell.setCellValue("Heading" + j);
       } else {
         localXSSFCell.setCellValue(i + j);
       }   
     }
         } 
    
   /* Write output as File */
    FileOutputStream fileOut = new FileOutputStream("Excel_Format_As_Table.xlsx");
    wb.write(fileOut);
    fileOut.close();
  }
}

Format Existing Excel Workbook Data as Table in POI


In the example above, we created new workbook with table and data in it. You may have scenarios where you need to read an existing workbook and modify it to have a table inserted. Let us see how to do that in this section.

The input file and the expected output is shown below:

Insert Table Existing Excel Workbook - POI Java Example
Insert Table Existing Excel Workbook - POI Java Example
You have to make simple modifications to the listing above. You do all the steps, except adding data at the end. And on top, you read an existing workbook instead of creating a new one. The full Java program is provided below:

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
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 insertTable
{
  public static void main(String[] args)
    throws FileNotFoundException, IOException
  {
   /* Read the input file that contains the data to be converted to table */
   FileInputStream input_document = new FileInputStream(new File("FormatAsTable.xlsx"));    
   /* Create Workbook */
   XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
   /* Read worksheet */
   XSSFSheet sheet = my_xlsx_workbook.getSheetAt(0); 
   /* Create Table into Existing Worksheet */
   XSSFTable my_table = sheet.createTable();    
   /* get CTTable object*/
   CTTable cttable = my_table.getCTTable();
   /* Define Styles */    
   CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();
   table_style.setName("TableStyleMedium9");           
   /* Define Style Options */
   table_style.setShowColumnStripes(false); //showColumnStripes=0
   table_style.setShowRowStripes(true); //showRowStripes=1    
   /* Define the data range including headers */
   AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(5, 2));    
   /* Set Range to the Table */
   cttable.setRef(my_data_range.formatAsString());
   cttable.setDisplayName("MYTABLE");      /* this is the display name of the table */
   cttable.setName("Test");    /* This maps to "displayName" attribute in &lt;table&gt;, OOXML */            
   cttable.setId(1L); //id attribute against table as long value
   /* Add header columns */               
   CTTableColumns columns = cttable.addNewTableColumns();
   columns.setCount(3L); //define number of columns
   /* Define Header Information for the Table */
    for (int i = 0; i < 3; i++)
    {
    CTTableColumn column = columns.addNewTableColumn();   
    column.setName("Column" + i);      
        column.setId(i+1);
    }   
    /* Write output as File */
    FileOutputStream fileOut = new FileOutputStream("Excel_Format_As_Table.xlsx");
    my_xlsx_workbook.write(fileOut);
    fileOut.close();
  }
}

Format Table - Style Options in POI


A quick note on CTTableStyleInfo. The mapping between this object and the options you see in Excel is captured below:

  • setShowColumnStripes - Banded Columns
  • setShowRowStripes - Banded Rows
  • setShowFirstColumn - First Column
  • setShowLastColumn - Last Column
You can also change the Style Name to match to a different style of your choice. And as a final note, all the style and table specific metadata gets written into an XML file. A sample content is shown below - if you have any issues you have to inspect this file.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="Table1" displayName="Table1" ref="A1:C3" totalsRowShown="0">
        <autoFilter ref="A1:C3"/>
        <tableColumns count="3">
                <tableColumn id="1" name="A "/>
                <tableColumn id="2" name="B"/>
                <tableColumn id="3" name="C"/>
        </tableColumns>
        <tableStyleInfo name="TableStyleMedium9" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/>
</table>

That completes our tutorial to insert a table in Excel with Apache POI, with "Format As Table" option. You can post a question to us if you are stuck.

7 comments:

  1. I tried out the sample code (even the sample from POI example) and I always got the following issue at Office 2010 when I want to open the created file:

    Removed Feature: Table from /xl/tables/table1.xml part (Table)


    Any idea, what can be wrong?

    ReplyDelete
    Replies
    1. I was getting a similar error as you described but mine was Repaired Records: Table from /xl/tables/table1.xml part (Table)

      I resolved my error by ensuring that the ID of the table was correctly set and that it was in the right "long" type.

      Delete
    2. I am getting same error , related to Repaired records. Id column is properly set still seeing this issue when i open the xml file. Can you please tell me how to fix it

      Delete
  2. where can I find Point of Interest with table. If anyone know link, please help me.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. How to make it work with .xls format file?

    ReplyDelete
  5. Nice Tutorial, include or mention the right jar files next time please.

    ReplyDelete