Declarative generation of Excel documents & PDFs with Clojure from higher level abstractions (tree, table) or via a manual grid specification, with boilerplate-free common sense styling.
CHANGELOG | Uses Break Versioning
Lein:
[org.clojars.mjdowney/excel-clj "1.1.2"]
All of the namespaces have an example function at the end; they're intended to
be browsable and easy to interact with to glean information beyond what's here
in a the readme.
Start by skimming this and then browsing core.clj.
Tests are run with
lein testThough Excel is much more than a program for designing tabular layouts, a table is a common abstraction that we impose on our data.
(require '[excel-clj.core :as excel])
=> nil
(def table-data
[{"Date" "2018-01-01" "% Return" 0.05M "USD" 1500.5005M}
{"Date" "2018-02-01" "% Return" 0.04M "USD" 1300.20M}
{"Date" "2018-03-01" "% Return" 0.07M "USD" 2100.66666666M}])
=> #'user/table-data
(let [;; A workbook is any [key value] seq of [sheet-name, sheet-grid].
;; Convert the table to a grid with the table function.
workbook {"My Generated Sheet" (excel/table table-data)}]
(excel/quick-open workbook))
Sometimes -- frequently for accounting documents -- we use spreadsheets to sum categories of numbers which are themselves broken down into subcategories.
For example, a balance sheet shows a company's assets & liabilities by summing the balances corresponding to an account hierarchy.
(def balance-sheet
["Mock Balance Sheet"
[["Assets"
[["Current Assets"
[["Cash" {2018 100M, 2017 85M}]
["Accounts Receivable" {2018 5M, 2017 45M}]]]
["Investments" {2018 100M, 2017 10M}]
["Other" {2018 12M, 2017 8M}]]]
["Liabilities & Stockholders' Equity"
[["Liabilities"
[["Current Liabilities"
[["Notes payable" {2018 5M, 2017 8M}]
["Accounts payable" {2018 10M, 2017 10M}]]]
["Long-term liabilities" {2018 100M, 2017 50M}]]]
["Equity"
[["Common Stock" {2018 102M, 2017 80M}]]]]]]])
=> #'user/balance-sheet
(excel/quick-open {"Balance Sheet" (excel/tree balance-sheet)})
Trees are pretty flexible — the only requirement that we impose is that their
leaves have the format [string-label, map-of-numbers]. We construct trees
using the same arguments we'd give to clojure.core/tree-seq, plus a walk
function.
We could make a tree for some part of our file system for example:
(require '[excel-clj.tree :as tree] '[clojure.java.io :as io])
=> nil
(let [src-tree
(tree/walk
(fn [f xs]
(if-not (seq xs)
[(.getName f) {:size (.length f)}]
[(str (.getName f) "/") xs]))
#(.isDirectory %) #(.listFiles %) (io/file "."))]
(excel/quick-open
{"Source Tree" (excel/tree ["Source" [src-tree]] :data-format :number)}))
If you're on a system that uses an OpenOffice implementation of Excel, PDF generation is similarly simple.
(excel/quick-open-pdf
{"Mock Balance Sheet" (excel/tree balance-sheet)
"Some Table Data" (excel/table table-data)})
The table function provides hooks to add custom styling without touching
the generated grid, within the context of the table abstraction.
(More on the syntax of the style data in Manual Styling.)
- The
:data-stylekeyword arg is a fn(row-map, column-name) => style map - The
:header-stylekeyword arg is a fn(column name) => style map
For instance, to highlight rows in our table where percent return is less than 5%:
(letfn [(highlight-below-5% [row-data col-name]
(when (< (row-data "% Return") 0.05M)
{:fill-pattern :solid-foreground
:fill-foreground-color :yellow}))]
(excel/quick-open
{"My Generated Sheet" (excel/table table-data :data-style highlight-below-5%)}))
The tree function provides similar hooks to style the tree elements based on
their nested depth. Both keyword arguments expect a fn
(integer-depth) => style map where the integer depth increases with nesting.
:total-formattersis a function that controls styling for tree rows that display totals of multiple subcategories:formattersis a function that controls styling for the rest of the tree
The code in this library wraps Apache POI. For styling, the relevant POI object is CellStyle.
In order to insulate code from Java objects, style specification is done via maps, for instance the style we saw above to highlight a row was:
{:fill-pattern :solid-foreground, :fill-foreground-color :yellow}Under the hood however, all of the key/value pairs in the style maps correspond
directly to setters within the POI objects. So if you browse the CellStyle
documentation, you'll see CellStyle::setFillPattern and
CellStyle::setFillForegroundColor methods.
The map attributes are camel cased to find the appropriate setters, and the corresponding values are run through the multimethod excel-clj.style/coerce-to-obj which dispatches on the attribute name and returns some value that's appropriate to hand to POI.
If you're interested in greater detail, see the namespace documentation for
style.clj, otherwise it's sufficient to know that enums are keyword-ized and
colors are either given as keywords (:yellow) or as RGB three-tuples
([255 255 255]).
The functions table and tree convert the source data into the grid format
which can go directly into the workbook map. The grid is [[cell]], where each
[cell] represents a row.
The cell data are either plain values (String, Date, Number, etc.) or a map that includes optional style data / cell merging instructions.
(let [title-style {:font {:bold true :font-height-in-points 10} :alignment :center}]
(excel/quick-open
{"Some Grid"
[ [{:value "This is a Title" :width 5 :style title-style}] ;; Title row
["This" "Is" "A" "Row"] ;; Another row
]}))
-
Templates! There's no reason to do all of the styling work programmatically. We should be able to download some cool Google Sheets template as an
.xlsxfile, edit it to indicate whereexcel-cljshould fill in tables, trees, or individual cells, and then write code that reads the template from aresources/some-template.xlsxand produces a filled in sheet.Not at all a fully formed spec yet, but something along the lines of filling in the following sheet:
Using some simple data:
{:title "The Title" :start-date (Date. (- (System/currentTimeMillis) (* 1000 60 60 24 7))) :end-date (Date.) :employee-name "Foo" :employee-id "Bar" :manager-name "Baz" :department "Accounting" :expense-purpose "Trip" :tbl [{:date (Date.) :category "Cat A" :desc "Misc expenses" :notes "" :amount 500.00M} {:date (Date.) :category "Cat B" :desc "Other expenses" :notes "" :amount 100.00M}]}To get the following result:
-
Reading & editing existing spreadsheets. This should go hand in hand with template generation.
-
Formulas! We don't have them. I'm envisioning a syntax where a table column or a cell contain a
:valueof(excel/formula ...). The interior of the formula could allow relative coordinate generation with the placeholders*x*and*y*— which could be operated on to find relative coordinates (e.g.(- *x* 2)) — and would then be replaced during rendering with the column and row. E.g.(def table-data [{"Foos" 10, "Bars" 5, "Total" (excel/formula "=(- *x* 2)*y* + (- *x* 1)*y*")} {"Foos" 12, "Bars" 8, "Total" (excel/formula "=(- *x* 2)*y* + (- *x* 1)*y*")}])
would result in the formulas being rendered in the C column after the title row, the first being
=A1+B1and the second being=A2+B2. -
Java wrapper? Uncertain if this would be useful / how it would look.
