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

excelTable1

Into this

excelTable2

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

rdoll

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.

package com.my.sharedClasses;

import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Workbook;
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();
table_style.setName("TableStyleMedium9");  

//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.setRef(reference.formatAsString());
cttable.setDisplayName("Blog");
cttable.setName("Blog");         
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);     
column.setId(i+1);
}
}
}

So now the Excel Writing script becomes:

package com.my.sharedClasses;

import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

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++)
{
sheet.autoSizeColumn(autosize);
}

//Write output File
FileOutputStream fileOut = new FileOutputStream("C:\\SeleniumData\\" +xls+ "DataOut.xlsx");
String filename = xls+ "DataOut.xlsx";
wb.write(fileOut);
fileOut.close();
wb.close();
System.out.println("Done");
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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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