how to read from Excel with Groovy
Hi,
I need to set many properties whose values come from an Excel file. I don't want to use a DataSource, because there are hundreds of these values/properties and I think it is easier to script them. What is the best way of reading from an Excel file in Groovy? Can I make use of any SoapUI classes, or do I need to use Scriptom or something like that?
I started using Scriptom, and after nailing the correct release (1.5.4), I finally have the following code:
import org.codehaus.groovy.scriptom.*;
import org.codehaus.groovy.scriptom.tlb.office.excel.XlChartType
import org.codehaus.groovy.scriptom.tlb.office.excel.XlSheetType
import org.codehaus.groovy.scriptom.tlb.office.excel.XlRowCol
import org.codehaus.groovy.scriptom.tlb.office.excel.XlChartLocation
import org.codehaus.groovy.scriptom.tlb.office.excel.Excel;
import org.codehaus.groovy.scriptom.util.office.ExcelHelper;
def addresses = new File('Prices.xls').canonicalPath
def xls = new ActiveXObject('Excel.Application')
def workbooks = xls.Workbooks
def workbook = workbooks.Open(addresses)
def sheet = workbook.ActiveSheet
def rate1 = sheet.Range("B17").Value
workbook.Close(false, null, false)
xls.Quit()
xls.release()
testRunner.testCase.setPropertyValue( "rate1", rate1 )
The problem with this is that xls.Release() throws "com.jacob.com.ComFailException: A COM exception has been encountered: At Invoke of: 1610612738 Description: Member not found.". If I take out xls.Release() then the Excel process stays open after close. Someone else had a similar issue: http://groovy.329449.n5.nabble.com/FW-S ... 71390.html. I tried to follow the thread, but couldn't make it work.
Dos anyone have any ideas how to make this work? Am I going in a wrong direction with Scriptom? How active is the Scriptom development? It looks like a dieing project...
Thanks,
Tiberiu
I need to set many properties whose values come from an Excel file. I don't want to use a DataSource, because there are hundreds of these values/properties and I think it is easier to script them. What is the best way of reading from an Excel file in Groovy? Can I make use of any SoapUI classes, or do I need to use Scriptom or something like that?
I started using Scriptom, and after nailing the correct release (1.5.4), I finally have the following code:
import org.codehaus.groovy.scriptom.*;
import org.codehaus.groovy.scriptom.tlb.office.excel.XlChartType
import org.codehaus.groovy.scriptom.tlb.office.excel.XlSheetType
import org.codehaus.groovy.scriptom.tlb.office.excel.XlRowCol
import org.codehaus.groovy.scriptom.tlb.office.excel.XlChartLocation
import org.codehaus.groovy.scriptom.tlb.office.excel.Excel;
import org.codehaus.groovy.scriptom.util.office.ExcelHelper;
def addresses = new File('Prices.xls').canonicalPath
def xls = new ActiveXObject('Excel.Application')
def workbooks = xls.Workbooks
def workbook = workbooks.Open(addresses)
def sheet = workbook.ActiveSheet
def rate1 = sheet.Range("B17").Value
workbook.Close(false, null, false)
xls.Quit()
xls.release()
testRunner.testCase.setPropertyValue( "rate1", rate1 )
The problem with this is that xls.Release() throws "com.jacob.com.ComFailException: A COM exception has been encountered: At Invoke of: 1610612738 Description: Member not found.". If I take out xls.Release() then the Excel process stays open after close. Someone else had a similar issue: http://groovy.329449.n5.nabble.com/FW-S ... 71390.html. I tried to follow the thread, but couldn't make it work.
Dos anyone have any ideas how to make this work? Am I going in a wrong direction with Scriptom? How active is the Scriptom development? It looks like a dieing project...
Thanks,
Tiberiu
