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

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

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 <table>, 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.