Exscalabur is a Scala library for creating Excel files from data and a template. Exscalabur allows excel layout and formatting to be specified by excel templates, allowing anyone with basic excel knowledge to design and modify templates.
- Java 11 or later
- Scala 2.12 or later
- sbt 1.2.8 or higher
| Command | Description |
|---|---|
~compile |
~ enables hot reloading |
~run |
~ enables hot reloading |
test |
Runs all tests |
testQuick |
Runs tests only affected by your latest code changes |
clean |
Removes generated files from the target directory |
update |
Updates external dependencies |
package |
Creates JAR |
publishLocal |
Publishes to local IVY repository, by default in ~/.ivy2/local |
To use Exscalabur to create Excel files, you first require an Excel Template file.
The Excel template contains formatting and layout but instead of data, there are keys in the cells that indicate where the data will be injected.
Any cell styling/cell borders made to the template will appear in the generated excel file.
An example template sheet:
A template key is a cell with the value prefixed by $KEY. or $REP.
- A
$KEY.cell will be substituted for a single piece of data. - A
$REP.cell will be repeated with formatting for each row of data provided.
Cells that do not contain keys will be copied as is.
In the above example, this template has 6 keys:$KEY.fname, $KEY.lname, $REP.animal, $REP.weight, $KEY.conclusion,and $REP.element.
A schema definition is required, and may be provided as a YAML file with the structure:
KEYNAME1:
dataType: oneOf("string", "double", "long", "date")
excelType: oneOf("string", "number", "date")
KEYNAME2:
dataType: oneOf("string", "double", "long")
excelType: oneOf("string", "number", "date")
# etcKEYNAMEis the key exactly as seen in the template.dataTypeis the runtime type of the data provided to exscalabur.excelTypeis the type of the cell in the final sheet.
com.carta.yaml.YamlReader is provided to read the schema definition yaml files.
For the example template Excel file above, the following is valid schema definition:
$KEY.fname:
dataType: "string"
excelType: "string"
$KEY.lname:
dataType: "string"
excelType: "string"
$KEY.conclusion:
dataType: "string"
excelType: "string"
$REP.animal:
dataType: "string"
excelType: "string"
$REP.weight:
dataType: "double"
excelType: "number"
$REP.element:
dataType: "string"
excelType: "string"Alternatively, this schema may be provided in-code as a Map[String, YamlEntry].
The Map's keys are the KEYNAMEs.
import com.carta.yaml.YamlEntry is a provided case class representing the above YAML structure.
To pass data in to be substituted into a $KEY. template cell, an instance of a DataCell(key: String, value: oneOf(String, Long, Double)) must be created.
key is the key from the template, without the $KEY. prefix.
value is the value to substitute. It's runtime type must match the schema definition.
For the above example, we may have DataCells like:
import com.carta.exscalabur.DataCell
DataCell("fname", "Joe");
DataCell("lname", "Person")
DataCell("conclusion", "EXSCALABUR")To pass data to be substituted into a repeated row, instances of DataRow are passed in, containing DataCells for each cell in the repeated row.
For the example, our DataRow instances might look like:
import com.carta.exscalabur.DataRow
DataRow().addCell("animal", "monkey").addCell("weight", 12.1)
DataRow().addCell("animal", "horse").addCell("weight", 12.2)
DataRow().addCell("element", "hydrogen")
DataRow().addCell("element", "helium")
DataRow().addCell("element", "lithium")Exscalabur supports multi-step writes. This can be done with multiple calls to writeStaticData and writeRepeatedData.
Currently, Exscalabur only supports writing in an append-only manner. So, for the above example, the data for $KEY.fname must be provided before the data for $REP.animal is provided.
There are plans for Exscalabur to support writing to rows out of order, but this has yet to be implemented.
Exscalabur does not support sub-tables arranged horizontally
Lastly, all that's left is to write the data. To do so, create an instance of a Exscalabur object:
import com.carta.exscalabur.Exscalabur
Exscalabur(
templates, // Iterable[String], representing paths to the template sheets
schema, // The in-code schema representation explained above
windowSize // number of rows in the output workbook to keep in memory at a time
)For every sheet to be written to, create a SheetWriter:
exscalabur.getAppendOnlySheetWriter(sheetName) // sheetName is as defined in the template sheet.Note, the sheetName used here comes from the tab in the template. It must match the template exactly
Write static data: sheetWriter.writeStaticData(staticData: Seq[DataCell])
and to write repeated data: sheetWriter.writeRepeatedData(repeatedData: Seq[DataRow])
And write to the output file: exscalabur.exportToFile(outputPath)
Doing so results in the final output sheet:
Currently, Exscalabur supports copying cells containing numeric, boolean, and string values.
Exscalabur currently does not support copying formulas that reference cells on repeated rows.
If a formula contains a reference to a cell, there cannot be any repeated rows between that cell, and the formula cell itself
Formulas must also reference cells on the same sheet.
An example of a legal formula is:
These formulas would not be considered legal if they contained a reference to a cell before row 31, or after 36.
Exscalabur supports copying cell formulas if all referenced cells in the formula are on the same sheet, and there are no repeated rows between any cell references and the formula itself. Formulas may not behave as expected if this requirement is not met.
Cells formula errors will not copied from the template to the output sheet.
- Add support for Exscalabur to copy formulas on single-substitution template cells.