Creating Excel Files - tsgrp/HPI GitHub Wiki

Creating Excel Files

There are several cases in HPI where we need to generate an Excel Spreadsheet for one reason or another. Since the Java code to actually create an Excel Document is pretty wordy/messy, we've made a util class in OC for dealing Excel files. Ideally all you'll need to do is build up a POJO (Plain-Old-Java-Object), called a Workbook Section and let ExcelUtil do all the other messy work for you! Here's how its works:

ExcelUtil.java

ExcelUtil will create a new Excel document, consisting of a sheet with the passed in title (and optional subheader), followed by the data for each WorkbookSection. A WorkbookSection defines an section of the Excel document where all the columns will correspond to the same title/property. A WorkbookSection can have a title row, and each row after will represent a different object, which may or may not have a value for each column in the section that will be filled in. WorkbookSections also have several other useful properties which can be set.

WorkbookSection's Attributes:

• headerTitle: The title that will be displayed at the top of the section.
• columnTitles: The titles at the top of each column, usually the name of the property that will be displayed in that column.
• objects: A list of objects to be displayed for each row. Each object should be a map with property-names as keys and property-values as values. Each column will populate itself by adding a cell for each object, which contains the value of whichever property corresponds with the column.
• titleToPropertyNameMap: In the case that the columnTitles are not a key in the objects property map, a titleToPropertyMap must be specified.

Example: If the objects have a property named "Last Modified", but in the spreadsheet, the "Last Modified" field should appear under a column titled "Date", a titleToPropertyMap would be necessary to map the "Date" title, to the "Last Modified" property. Optional.

• oldDateFormat: The date format before that is expected on objects, before formatting is done. Optional.
• dateFormat: The target date format for any columns defined as dates. Optional.
• dateColumns: A list of columns whose's values should follow the date format. Optional.

So what do I have to do in my code?

You'll simply build up a list of these WorkbookSections, pass them to ExcelUtil's buildWorkbook() method, and let the ExcelUtil do its magic.