Easy Excel reports using Groovy and POI
import groovy.sql.Sql
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.hssf.util.*
import java.io.File
if (args.size() != 1)
{
println(args.join(", "));
println("Error: expecting output filename as a commandline parameter");
System.exit(1);
}
Sql.loadDriver("oracle.jdbc.driver.OracleDriver")
sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1521:sid", "user", "password")
workbook = new HSSFWorkbook();
sheet = workbook.createSheet("Games");
sheet.setColumnWidth(1.shortValue(), 5600.shortValue())
sheet.setColumnWidth(2.shortValue(), 8000.shortValue())
sheet.setColumnWidth(3.shortValue(), 8000.shortValue())
rowNum = 0
sql.eachRow("select * from tb_game order by name", { game |
skuCount = 0
sql.eachRow("select count(*) from tb_sku where product_id = ${game.id}", { skuCount = it[0] })
row = sheet.createRow(rowNum++)
row.createCell(0.shortValue()).setCellValue(game.id.doubleValue())
row.createCell(1.shortValue()).setCellValue(game.name)
row.createCell(2.shortValue()).setCellValue(game.developer)
row.createCell(3.shortValue()).setCellValue(game.vendor)
row.createCell(4.shortValue()).setCellValue(skuCount.doubleValue())
})
new File(args[0]).withOutputStream({ os | workbook.write(os) })
