Groovy - How to read and write to excel sheets with Groovy?


Scriptom provides a simple COM library for Groovy. It is a Groovy wrapper around JACOB (Java COM Bridge) which provides access to COM Automation components. Scriptom can be used to work with MS Office tools, Internet Explorer or any other Microsoft Windows COM based components. In this article, you will use Scriptom to read and write to excel files.

Dependencies


You need to download and add the scriptom jar to the classpath. It is available from the maven repo here.

In your groovy script import the ActiveXObject class.
import org.codehaus.groovy.scriptom.ActiveXObject

Usage


Next, create an instance of the ActiveXObject for Excel and use it to open the excel file.
    def excelObj = new ActiveXObject('Excel.Application')

    def workBook = excelObj.Workbooks.Open(1c:/codelooru.xlsx1)
You can get a reference to the Sheet in the following ways.
    //To get a sheet by index
    def sheetByIndex = workBook.Sheets.Item[1]
    
    //To get the active sheet in the workbook
    def sheetActive = workBook.ActiveSheet

    //To get the sheet by name
    def sheetByName = workBook.Sheets('Sheet1')


Now you can access the cell data in the following manner. The first parameter to Cells is the row number and the second parameter is column number.
    def cellValue = sheet.Cells(2,1).Value

To update a cell value :
    sheet.Cells(1,1).Value = 100

If you do not have the file after changes to the cells, you will be prompted with a dialog to save. So, to save the changes in the workbook :
    workBook.save


If you do not wish to save the changes, then set the Saved property to true, like so
    workBook.Saved = true

You can get the row and column count of the sheet, like so
    def rows = sheet.UsedRange.Rows.Count
    def cols = sheet.UsedRange.Columns.Count


Remember to close the workbook after the use. Otherwise, you will notice an excel instance in the processes.
    
    workBook.close()

Working Example


Here is a sample code that uses all of the above snippets to read the data in an active sheet, increment the value by 1 and save it, assuming that the sheet has integer data.

import org.codehaus.groovy.scriptom.ActiveXObject

def excelObj = new ActiveXObject('Excel.Application')

def workBook = excelObj.Workbooks.Open('c:/codelooru.xlsx')
try {
    def sheet = workBook.ActiveSheet

    def rows = sheet.UsedRange.Rows.Count
    def cols = sheet.UsedRange.Columns.Count

    //Update the values
    1.upto(rows, {row ->

        1.upto(cols, {col ->
            sheet.Cells(row,col).Value = sheet.Cells(row,col).Value + 1
        })

    })

    //Print the data
    1.upto(rows, {row ->

        1.upto(cols, {col ->
            print sheet.Cells(row,col).Value + '\t'
        })

        println ''

    })
    
    workBook.save
}
finally {
    workBook.close()
}

Conclusion


In this post, you saw how you can work with excel sheets in groovy. The post covered very few capabilities of the Excel COM Object. To explore it further, refer the Excel COM Object model here.



1 comment:

  1. I want to try this logic on a specific column in excel sheet.Say on coloumn c.Can you help where should I change it? Also should I use SOAPUI to test this?

    ReplyDelete