Getting Started in Automation – Writing to Excel

I’ve now gone through getting data both in and out of an application via CSV, and then looked at getting it in via Excel. The obvious next question is how to report data out using Excel.

The starting point of this process is obviously knowing what the data is, and being able to send that data in some container. The script I’ve put together accepts a two dimensional string array.

For the string array string[x][y], y is the number of columns, and x is the number of rows, and in fact that’s the first thing that the script establishes:

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

Note that the integer ‘cols’ (i.e. the number of columns) is established, based on the length of the [0] value of the array, so as with the script to read data this is best used with rectangular datasets.

The script goes on to create a workbook, then a worksheet.

Once these parameters are established a simple loop within a loop, creates a row and then populates each cell within that row.

Since we’re dealing with Excel rather than a csv, we can then take advantage of the autosize function to make the data a little more presentable:

             sheet.autoSizeColumn(autosize);

The script then actually writes this out to a file using the FileOutputStream function, and finally closes the workbook it has created.

What does this look like in its entirety?

package com.my.sharedClasses;

import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
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;

public class cWriteExcel {
public void 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);

           //Create a workbook and worksheet
           Workbook wb = new XSSFWorkbook();
           XSSFSheet sheet = (XSSFSheet)wb.createSheet(worksheetName);

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

           //Autosize the columns
           for (int autosize=0;autosize<cols;autosize++)
           {
              sheet.autoSizeColumn(autosize);
           }

          // Write output as File
           FileOutputStream fileOut = new FileOutputStream("C:\\SeleniumData\\" +xls+ "DataOut.xlsx");
           wb.write(fileOut);
           fileOut.close();
           wb.close();
           System.out.println("Done");
           return;
         }
}

So in terms of calling this script, we would have our main script in which we create a two dimensional array, populate it, and then call this script, passing this in as a parameter.

package com.my.Processes;

import java.io.IOException;
import com.my.sharedClasses.cWriteExcel;

public class blogExcelWrite {
       public static void main(String[] args) throws InterruptedException, IOException {

//Initialise the Excel Writer class
cWriteExcel writer = new cWriteExcel();

//Specify number of rows and columns
       int rows = 4;
       int cols = 5;
       String[][] excelOut = new String[rows][cols];

//A simple double loop that fills each column in row 1, then moves on to row 2 etc.   
       for (int r=0;r<rows;r++)
       {
              for (int c=0;c<cols;c++)
              {
              excelOut[r][c] = "Row " + (r+1) + " - Column " + (c+1);
              }
       }

//Call the Excel Writer, and pass in the 2 dimensional array
writer.mWriteExcel("blog", "blog", excelOut);

}
}

This will then create a file in C:\SeleniumData, called blogDataOut.xlsx – the result should look like this:

BlogDataOut

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