Getting Started in Automation – Making Excel Tables

Previously I took advantage of the ‘autosize’ function in Excel, to make the output data slightly more presentable, but I can take this further, and apply a Table style to the data before I output it. This turns this


Into this


To get to this point, I need to extend my called function, to call another function in a sort of Russian Doll metaphor.


Main function generates data

-> Passes data into Excel function, which creates excel workbook and sheet

-> Passes workbook, worksheet, and data dimensions to table creator function, which creates the table

-> Passes the formatted Excel sheet back to the Excel function, which then adds the data to the table, and writes it out to the file

-> Returns to the main function

So on to the code. My first Russian Doll, the smallest one, applies the formatting to the table. I’ve already defined the rows and columns, so can pass them in easily enough, and the Excel sheet and Workbook are created in the previous script.


import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
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 cFormatAsTable {
     public void mFormatExcelAsTable(Workbook wb, XSSFSheet sheet, int rows, int cols) {

//Create an object of type XSSFTable
XSSFTable my_table = sheet.createTable();
CTTable cttable = my_table.getCTTable();
//Define the required Style for the table   
CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();

//Set Table Style Options
table_style.setShowColumnStripes(false); //showColumnStripes=0
table_style.setShowRowStripes(true); //showRowStripes=1

//Define the data range
AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(rows-1, cols-1), SpreadsheetVersion.EXCEL2007);
//Set Range to the Table
cttable.setId(1); //id attribute of table
CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(cols); //define number of columns
//Define Header Information for the Table
for (int i = 0; i < cols; i++)
CTTableColumn column = columns.addNewTableColumn();  
column.setName("Column" + i);     

So now the Excel Writing script becomes:


import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class cWriteExcel {
public String mWriteExcel(String xls, String worksheetName, String[][] excelData) throws InterruptedException, IOException {

int cols = excelData[0].length;   
int rows = excelData.length;
System.out.println("Writing: Rows = "+rows+" : Columns = "+cols);

//Start with creating a workbook and worksheet object
Workbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.createSheet(worksheetName);

          //Call the Format Table Function
          cFormatAsTable format = new cFormatAsTable();
          format.mFormatExcelAsTable(wb, sheet, rows, cols);

//Populate Table                                                                                                             
int rowNum = 0;
for (String[] datatype : excelData)
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (String field : datatype)
Cell cell = row.createCell(colNum++);
if (field instanceof String)
cell.setCellValue((String) field);

for (int autosize=0;autosize<cols;autosize++)

//Write output File
FileOutputStream fileOut = new FileOutputStream("C:\\SeleniumData\\" +xls+ "DataOut.xlsx");
String filename = xls+ "DataOut.xlsx";
return filename;

The main function hasn’t changed from the previous article, but now my outputted Excel is both autosized and formatted as a table.

One amendment that could be made, is to optionally pass in a Boolean value formatAsTable=true, or false. Then within the Excel Writer method, I check in an if statement what that value is, and only if Boolean formatAsTable == true, do we call the table formatting function.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s