CODEWITHSUNDEEP
excelgroovyplot
blaughli
blaughli

Reputation: 527

How to script Excel files with Groovy

I want to create excel files in Groovy, then have them plotted. This code was taken from an example using Microsoft's Shell Scripting language:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1,1) = "Operating System"
objWorksheet.Cells(2,1) = "Windows Server 2003"
objWorksheet.Cells(3,1) = "Windows XP"
objWorksheet.Cells(5,1) = "Windows NT 4.0"
objWorksheet.Cells(6,1) = "Other"

objWorksheet.Cells(1,2) = "Number of Computers"
objWorksheet.Cells(2,2) = 145
objWorksheet.Cells(3,2) = 987
objWorksheet.Cells(4,2) = 611
objWorksheet.Cells(5,2) = 41
objWorksheet.Cells(6,2) = 56

Set objRange = objWorksheet.UsedRange
objRange.Select

Set colCharts = objExcel.Charts
colCharts.Add()

Set objChart = colCharts(1)
objChart.Activate

objChart.HasLegend = FALSE
objChart.ChartTitle.Text = "Operating System Use"

How would I modify this to work in Groovy?

Upvotes: 4

Views: 20294

Answers (5)

Jacek Cz
Jacek Cz

Reputation: 1906

Probably simplest (in terms of external code) is text format, cells are delimited by '\t' and lines "\r\n". Be careful on cell having sense String but with num characters (or Date-alike), best to prepend with single apostrophe

Such format can be pasted by clipboard or open from file menu.

Upvotes: 0

ChrLipp
ChrLipp

Reputation: 15668

I would also go for Apache POI. A working example can be found at the busy developer guide. There is also GSheets, a thin groovy wrapper for Apache POI, see the blog post and the unit test for an example usage.

Upvotes: 0

Robert Patterson
Robert Patterson

Reputation: 520

Mark's answer above was a great example. It's event easier to follow if you make a couple simple changes:

import org.codehaus.groovy.scriptom.*
import org.codehaus.groovy.scriptom.tlb.office.excel.*

def xls = new ActiveXObject("Excel.Application")

xls.Visible = true

Thread.sleep(1000)

// add a new workbook
def workbook  = xls.Workbooks.Add()

// select the active sheet
def sheet = workbook.ActiveSheet    

sheet.Range("A1").Value = "Operating System"
sheet.Range("A2").Value = "Windows Server 2003"
sheet.Range("A3").Value = "Windows XP"
sheet.Range("A4").Value = "Windows NT 4.0"
sheet.Range("A5").Value = "Other"

sheet.Range("B1").Value = "Number of Computers"
sheet.Range("B2").Value = 145
sheet.Range("B3").Value = 987
sheet.Range("B4").Value = 611
sheet.Range("B5").Value = 41

def chart = workbook.Charts.Add(Scriptom.MISSING, sheet)  // create chart object
chart.ChartType = XlChartType.xl3DArea // set type to pie
chart.SetSourceData(sheet.Range("A1:B5"), XlRowCol.xlColumns) // set source data
chart.Location(XlChartLocation.xlLocationAsNewSheet) // add chart as new sheet

Upvotes: 2

Mark
Mark

Reputation: 108537

You need groovy to work with COM. Towards the bottom of this page is an example of automating Excel.

EDITS

Here's your example translated into Groovy (I ran this under Groovy 1.8.2):

import org.codehaus.groovy.scriptom.*
import org.codehaus.groovy.scriptom.tlb.office.excel.XlChartType
import org.codehaus.groovy.scriptom.tlb.office.excel.XlRowCol
import org.codehaus.groovy.scriptom.tlb.office.excel.XlChartLocation

// create a xls instance
def xls = new ActiveXObject("Excel.Application")

xls.Visible = true

Thread.sleep(1000)

// get the workbooks object
def workbooks = xls.Workbooks
// add a new workbook
def workbook  = workbooks.Add()

// select the active sheet
def sheet = workbook.ActiveSheet

cell = sheet.Range("A1")
cell.Value = "Operating System"
cell = sheet.Range("A2")
cell.Value = "Windows Server 2003"
cell = sheet.Range("A3")
cell.Value = "Windows XP"
cell = sheet.Range("A4")
cell.Value = "Windows NT 4.0"
cell = sheet.Range("A5")
cell.Value = "Other"

cell = sheet.Range("B1")
cell.Value = "Number of Computers"
cell = sheet.Range("B2")
cell.Value = 145
cell = sheet.Range("B3")
cell.Value = 987
cell = sheet.Range("B4")
cell.Value = 611
cell = sheet.Range("B5")
cell.Value = 41

def chart = workbook.Charts.Add(Scriptom.MISSING, sheet)  // create chart object
chart.ChartType = XlChartType.xl3DArea // set type to pie
chart.SetSourceData(sheet.Range("A1:B5"), XlRowCol.xlColumns) // set source data
chart.Location(XlChartLocation.xlLocationAsNewSheet) // add chart as new sheet

Upvotes: 14

Dave Newton
Dave Newton

Reputation: 160191

Another option may be Apache POI, depending on what all you need to actually implement.

Either option will be complicated for a beginner; the easy path would be to use the shell.

Upvotes: 0

Related Questions