HTTPS SSH

README

Frosted Sheets is a Groovy library which provides decorators for Apache POI spreadsheets, making it MUCH easier to work with spreadsheets. Frosted Sheets makes Apache POI Groovy-er, and provides some facades for additional features, such as XLSX encryption and decryption.

Who is this repository for?

Frosted Sheets is for people who need to read and/or write Microsoft Excel spreadsheets using the Groovy programming language.

How do I get set up?

The easiest way to get set up is to use Maven to resolve the dependency. First, add the following Maven repository: https://bitbucket.org/erosa/erosamaven/raw/default/

Then, use the following artifact:

  • group: com.emmanuelrosa
  • module: frostedsheets
  • version: 0.4.0

Here's an example using Groovy Grape:

@GrabResolver(name='erosa', root='https://bitbucket.org/erosa/erosamaven/raw/default/')
@Grab('com.emmanuelrosa:frostedsheets:0.4.0')

How do I use it?

Frosted Sheets was designed to make it easy to work with Microsoft Excel spreadsheets. So naturally, using it is easy. Let's create a new XLSX workbook and add some data to it:

Creating a workbook

import com.emmanuelrosa.frostedsheets.* 

def workbook = FrostedWorkbook.createXLSX()
def sheet = workbook['my sheet']

sheet << ['First name', 'Last name', 'Subscription date']

Those few lines of code actually accomplished a lot! But don't take my word for it, here's how the equivalent would be done in plain Apache POI:

import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.apache.poi.ss.usermodel.Cell

def workbook = new XSSFWorkbook()
def sheet = workbook.createSheet('my sheet')
def row = sheet.createRow(0)

['First name', 'Last name', 'Subscription date'].eachWithIndex { name, index ->
    row.createCell(index, Cell.CELL_TYPE_STRING).cellValue = name
}

Phew! And that's not accounting for the edge cases, such as handling when a sheet or row already exists. Things Frosted Sheets takes care of. So, let's talk about what's going on behind the scenes...

  1. def workbook = FrostedWorkbook.createXLSX() - Creates an XSSFWorkbook, wrapped in a FrostedWorkbook.
  2. def sheet = workbook['my sheet'] - Gets or creates a sheet with the specified name, using Groovy's Map access.
  3. sheet << ['First name', 'Last name', 'Subscription date'] - Appends a row of cells to the sheet.

A FrostedWorkbook implements the Java Map interface. This is quite neat because you automatically get all functionality Groovy adds to Map. For example, you can iterate through the workbook sheets. However, you need to take precaution when accessing properties, just like you would with a Map in Groovy. For example, to access the underlying XSSFWorkbook...

// You can do this...
def w = workbook.getWorkbook()

// But you do this...
def w = workbook.workbook

// You'll get a sheet named 'workbook' instead.

Let's take advantage of a unique feature of Frosted Sheets to add more rows of data.

sheet << [
        ['John', 'Galt', new Date()],
        ['Dagny', 'Taggart', new Date() - 1]
    ]

I think that just by looking at the code you can figure out what is going on. When you append a List of Lists, the FrostedSheet iterates through the outer List and creates cells for each row. Notice something interesting. Unlike with plain Apache POI, you don't have to mess around with row and cell indices. If you want, or need to, you can however. Because Frosted Sheets delegates to Apache POI.

So far, you have a sheet named my sheet containing three rows of data. Let's save the workbook to a file so you can see I'm making this up.

new FileOutputStream('frostedsheets.xlsx').withStream {
    workbook.write(it)
}

Go ahead, open the file. Once you've been impressed thoroughly, come back here and we'll comtinue.

Reading a workbook

Let's go ahead and load the workbook you've already created and play with it.

import com.emmanuelrosa.frostedsheets.*

def workbook = FrostedWorkbook.readXLSX(new FileInputStream('frostedsheets.xlsx'))
def sheet = workbook['my sheet']

sheet.tail().each { row ->
    (firstName, lastName, subscriptionDate) = row 
    println "$firstName $lastName subscribed on $subscriptionDate"
}

The example above demonstrates that with Frosted Sheets, sheets and rows are iterable! Yeah, let that sink in. When you're done, let's move on to encryption.

Encrypting and decrypting an XLSX workbook

Frosted Sheets provides a facade to greatly simplify encryption and decryption. Don't believe me? Check this out, and then look at the following code:

import com.emmanuelrosa.frostedsheets.* 

def workbook = FrostedWorkbook.createXLSX()
def sheet = workbook['my sheet']

sheet << [
    ['First name', 'Last name', 'Subscription date'],
    ['John', 'Galt', new Date()],
    ['Dagny', 'Taggart', new Date() - 1]
]

new FileOutputStream('frostedsheets.xlsx').withStream {
    workbook.writeEncrypted(outputStream: it, password: 'frostedsheets')
}

Now, the file frostedsheets.xlsx is encrypted with the password frostedsheets. I should mention that the encryption process needs temporary storage. By default, writeEncrypted() uses a byte array, but if you provide the useTempFile option and set it to true, a temp file will be used instead. Keep in mind that this temporary storage will contain the unencrypted workbook.

Now, let's try decryption:

import com.emmanuelrosa.frostedsheets.* 

def workbook = FrostedWorkbook.readXLSX(new FileInputStream('frostedsheets.xlsx'), 'frostedsheets')

That's all there is to encryption :)

Cell traversion

A FrostedSheet contains a special iterator which makes traversing through the cells on a sheet real simple. This special iterator, FrostedSheet.cellIterator() traverses by row, then column; top-down, left-right. An example should do the trick...

Take a sheet containing the following:

workbook['cell traverse test'] << [
    [1, 2, 3, 4],
    [5, 6, 7, 8],
    [9, 10],
]

This...

workbook['cell traverse test'].cellIterator().each { cell -> println cell.cellValue }

... is the same as this...

workbook['cell traverse test'].each { row ->
    row.each { cell ->
        println cell.cellValue
    }
}

And if you need to know the row and/or column of the cell, you can use cell.rowIndex and cell.columnIndex, respectively.