Spread Sheet Converter

Hello to all of my zero readers, recently I've put together a quick tool to convert rows in a spreadsheet to text in a document. The main principal is that the spreadsheet forms a table which has named columns, and is broken down by row. The innovative portion of this is that each row is then inserted into a document template. A document translator matches "hash tags" in the template document and replaces each tag with the cell from the column which matches it.

Problem: Ugly Spreadsheet Data

This started, like most things, when I needed to convert rows in a spreadsheet to more presentable documents. The FRC Team that I mentor collects officer candidates using a google forms, which comes in as a google spreadsheet - not super easy to read. To be honest though, I had been thinking about this for a long while. Not necessarily as specific as spreadsheet to document, but I had been thinking of this as a collection of tools for making templates which I can embed into applications. I could have just as easily made a template tool for slideshows as documents, or changed the data source from a spreadsheet to some other form of tabular or even object data. Spreadsheet to document just happened to suit the need I had.

Apache POI

Apache POI is the real worker of this tool. It is an open source library for reading and modifying Office Open XML files. This is the docx and xlsx family of formats which are used by Microsoft Office products. It parses the zipped XML files into a document structure which is very easily accessed by a user. This entry is not specifically about POI, so I will not go into much detail about it.

Converting Spreadsheets to Documents

So, I've put together an example usage of my spreadsheet converter tool. It is based on my original problem of reading student's officer applications, but I've made a fake data set to protect my student's privacy. On the right is a spreadsheet with long text answers. On the left is the template which they will go into.

The spreadsheet has column names across the first row, and the following rows are data rows.

The template document has "hash tags" in it. Each hash tag has the same name as one of the column names. When a row from the spreadsheet is inserted into the template, the name of each hash tag is matched to the cell in the column with the same name. Each hash is then replaced with its corresponding data.

To perform the conversion of the example use the following example. The --data argument is the file which has the rows of data. The --template argument is the document template file. The --outputDirectory argument is the directory which the generated documents will be placed. The --header argument specifies which column is used as the generated files' names, using the column name. There is alternatively the --column argument which speficies a column using its column number, starting at zero.

> java -jar target/spreadsheetConverter.jar --data examples/exampleData.xlsx --template examples/exampleTemplate.docx --outputDirectory target/examples --header Name

And you can see that it generated a file for each row in the spreadsheet.

Thanks for reading, and I hope that this tool can be helpful. The code and download for my spreadsheet converter tool is located in my gitbucket here.