Write data in excel(xlsx) sheet having a table

Question

I am trying to write dynamic data in an excel sheet (xlsx) having a table already created, based on which we plot a chart in excel itself, using a macro.

I am using POI to write the data.

The table in the sheet has been set to 10 rows. When I write data for more than 10 rows, the table does not get expanded.So, the chart plotted contains data corresponding to 10 rows only.

How can I write data, so that the data always expands the table to number of rows in the data?

Answer 1

You should create an XSSFTable object from sheet.createTable();.

Here's an example I found at http://thinktibits.blogspot.co.il/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html - just make sure the table created covers all the rows/columns you dynamically place in the file.

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

Answer 2

While this question is a bit dated, it cost me more than 4 hours to figure this out, so I thought posting it might help. This alters an existing table instead of creating a new one.

// tell your xssfsheet where its content begins and where it ends
((XSSFWorksheet) sheet).getCTWorksheet().getDimension()
    .setRef("A1:H" + (sheet.getLastRowNum() + 1));

CTTable ctTable = sheet.getTables().get(0).getCTTable();

ctTable.setRef("A1:H" + (sheet.getLastRowNum() + 1)); // adjust reference as needed

ctTable.unsetSortState(); // if you had sorted the data in Excel before reading the file,
                          // you may want an unsorted table in your output file

CTTableColumns ctColumns = ctTable.getTableColumns(); // setting new table columns will
                                                      // muck everything up, 
                                                      // so adjust the existing ones

// remove the old columns first if you plan on expanding your table in the column direction
for (int i = 0; i < ctColumns.getCount(); i++) {
    ctColumns.removeTableColumn(0);
}

// throw in your new columns
for (int i = 0; i < tableHeaders.size(); i++) {
    CTTableColumn column = ctColumns.addNewTableColumn();
    column.setName(tableHeaders.get(i));
    column.setId(i + 1);
}

// for some reason this isn't being take care of when columns are modified,
// so fix the column count manually
ctColumns.setCount(tableHeaders.size());

A word of warning: all the unsetX methods in CTTable will throw IndexOutOfBoundsExceptions if the respective property doesn't exist in the table, so they need to be try-catch-ed. An alternative mentioned by @Gagravarr in the comments is to use isSetX first, then there's no need for trying.

If this doesn't work, then you probably have a bunch of other stuff set in your table. Unfortunately, the low-level XML stuff (encapsulated in all those CT objects) is very poorly documented since it's apparently auto-generated from the spec, so the API is pretty opaque. The teeth-grinding process I go through to figure out what to use is to unpack the .xlsx, look at the XML I need to adjust, figure out where it lives in my XSSFSheet and how I need to adjust it, then go hunting for the suitable CT-methods, since the XSSF-API does not really let you do all that much. This way, you can create pretty much anything you would be able to create in Excel, and even alter some stuff you can't there - if you have the time and nerves.