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.

    //codelooru.com
    
    //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.

    //codelooru.com
    def cellValue = sheet.Cells(2,1).Value

To update a cell value :

    //codelooru.com
    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 :

    //codelooru.com
    workBook.save

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

    //codelooru.com
    workBook.Saved = true

You can get the row and column count of the sheet, like so

    //codelooru.com
    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.

    //codelooru.com
    
    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.

//codelooru.com
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.

More Articles on Groovy

• How to Read Files in Groovy?
• How to Execute Groovy Scripts in Sublime Text?