Dùng Groovy để đọc excel file

Đây là bài gốc từ mailing list của org.codehaus.groovy.userI got introduced to JExcelApi on the Groovy IRC channel this morning (http://www.andykhan.com/jexcelapi/)This is a Java package which lets you read an write Excel spreadsheets. It look pretty slick.

I have just hacked a Groovy script which reads an Excel spreadsheet and writes out the cell contents as a CSV

import java.io.File import jxl.Workbook

writeCsv = { | out, workBook, hide | 				for (sheetNumber in 0...workBook.numberOfSheets) { 					sheet = workBook.getSheet(sheetNumber)

out.println sheet.name

if (!(hide && sheet.settings.hidden)) { 						for (rowNumber in 0...sheet.rows) { 							row = sheet.getRow(rowNumber)

println row.findAll({!(hide && it.hidden)}).contents.join(', ') 						} 					} 				} 			}

writeCsv(System.out, Workbook.getWorkbook(new File("jxlrwtest.xls")),  false)

Some things to note:

sheet.settings.hidden

in Java this would have to be

sheet.getSettings().isHidden()

– Groovy’s bean support reduced the noise for

(sheetNumber in 0...workBook.numberOfSheets)

we use the ‘…’ form of the range to go to workBook.getNumberOfSheets() – 1 – again lots less noise

row.findAll({!(hide && it.hidden)}).contents

the findAll call returns a list of cell objects. We want to call getContents() on each cell object. We could use collect({ it.contents}) but someList.someProperty is equivalent to someList.collect({it.someProperty}) and a lot less noisy

If you download the package from the link above you will see a Java version of this in the demo package.

John Wilson The Wilson Partnership http://www.wilson.co.uk