Tuesday, March 30, 2010

From database table to Excel with Groovy

I have a MySQL table with customers data and I want to select records and create an Excel CSV file from them. This can be easily done in Groovy with few lines of code:

package it.alfresco.groovy

import groovy.sql.Sql

def sql = Sql.newInstance("jdbc:mysql://localhost:3306/test",
"root", "******", "com.mysql.jdbc.Driver")

def query = "select * from webinar order by company"

outFile = new File("/Users/mturatti/Desktop/Roadshow.csv")
appendFlag = false
outStream = new FileOutputStream(outFile, appendFlag)
writer = new FileWriter(outFile, appendFlag)
outChannel = outStream.channel

outFile << "Firstname; Lastname; Email; Company; Job Title; Process\r\n"
sql.eachRow(query, { outFile << "${it.firstname}; ${it.lastname}; ${it.email}; ${it.company}; ${it.title}; 1\r" });

println outFile.text.size()
The table contains the following columns: firstname, lastname, email, company, title.
The resulting CSV file can be opened by Excel and Openoffice.