excel-clj

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 "2.2.0"]

Note: Ongoing v1.x support can be found on the version-1 branch.

• Getting Started
  • Tables
  • Trees
  • PDF Generation
  • Redirecting output from print-table
  • Style & Cell Merging
  • What are the options for styling?
  • Grids
  • Templates
• Roadmap
• Development
  • Unit Tests
  • Office Integration Tests
  • All Tests

Getting Started

All of the namespaces have a comment form at the end (as well as throughout) with example usage; they're intended to be browsable and easy to interact with to glean information beyond what's here in a the readme.

You can use this library at two levels. For a low level, Writer-style interface, see poi.clj and the accompanying comment forms. For high level usage, read on!

Start by skimming this and then browsing core.clj.

Tables

Though Excel is much more than a program for designing tabular layouts, a table is a common abstraction imposed on Excel data.

(require '[excel-clj.core :as excel])

(def table-data
  [{"Date" #inst"2018-01-01" "% Return" 0.05M "USD" 1500.5005M}
   {"Date" #inst"2018-02-01" "% Return" 0.04M "USD" 1300.20M}
   {"Date" #inst"2018-03-01" "% Return" 0.07M "USD" 2100.66666666M}])

(let [;; A workbook is any [key value] seq of [sheet-name, sheet-grid].
      ;; Convert the table to a grid with the table-grid function.
      workbook {"My Generated Sheet" (excel/table-grid table-data)}]
  (excel/quick-open! workbook))

Note: The examples here use quick-open! to ... quickly open the workbook. You would use write! to write to some location on disk, or write-stream! for writing elsewhere.

Trees

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 assets
  {"Current Assets" {"Cash"                {2018 100M, 2017 85M}
                     "Accounts Receivable" {2018 5M, 2017 45M}}
   "Investments"    {2018 100M, 2017 10M}
   "Other"          {2018 12M, 2017 8M}})

(def liabilities
  {"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}}})

We might choose to e.g. treat each one as a tree and stack them vertically, with a title at the top:

(let [assets (tree-grid {"Assets" assets})
      lbs (tree-grid {"Liabilities & Stockholders' Equity" liabilities})
      remove-headers rest]
  (quick-open!
    {"Balance Sheet"
     (with-title "Mock Balance Sheet"
       (concat assets (remove-headers lbs)))}))

Trees are pretty flexible — browse the tree.clj namespace for more examples of things to do with them.

In my own usage of this library I frequently find myself manipulating trees of accounting data using tree/fold and then stacking together trees to demonstrate addition, subtraction, and multiplication (e.g. for exchange rates) of different subsets of data, culminating in one "bottom line" tree that contains final result of those calculations.

PDF Generation

If you're on a system that uses a LibreOffice or Apache OpenOffice implementation of Excel, PDF generation works the same was as creating a spreadsheet:

(excel/quick-open-pdf!
  {"Mock Balance Sheet" (excel/tree-grid tree/mock-balance-sheet) 
   "Some Table Data" (excel/table-grid table-data)})

Redirecting output from print-table

For convenience, there's an excel macro which allows you to wrap forms which call into clojure.pprint.print-table or excel-clj.tree/print-table and capture their output in an Excel workbook instead of printing to stdout.

Useful for situations where you would ordinarily print data to the REPL, but sometimes want to generate an Excel file instead.

(excel-clj.core/excel
  (clojure.pprint/print-table
    (map
      (fn [i] {"Ch" (char i) "i" i})
      (range 33 43)))

  (excel-clj.tree/print-table
    (excel-clj.tree/table
      excel-clj.tree/mock-balance-sheet)))

Style & Cell Merging

Each workbook is a map: {sheet-name [[cell]]}. Each cell is either

1. A plain value, e.g. "abc", #inst"2020-01-01", 110.5M, 0.0001; or
2. An embellished value, including style and dimension data, eg:

   (require '[excel-clj.cell :as cell])
   
   (def cell
     (let [header-style {:border-bottom :thin :font {:bold true}}]
       (-> "Header"
           (cell/style header-style)
           (cell/dims {:height 2})
           (cell/style {:vertical-alignment :center}))))
   
   (clojure.pprint/pprint cell)
   ; #:excel{:wrapped? true,
   ;         :data "Header",
   ;         :style
   ;         {:border-bottom :thin,
   ;          :font {:bold true},
   ;          :vertical-alignment :center},
   ;         :dims {:width 1, :height 2}}
   
   

So you could e.g. write an (ugly) grid with:

(let [grid [["A" (cell/style "B" {:font {:bold true}}) "C"]
            [1 2 (cell/dims 3 {:width 3 :height 3})]]]
  (excel/quick-open! {"Sheet 1" grid}))

What are the options for styling?

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 to highlight a cell would be:

{: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]).

Grids

Both tree-grid and table-grid create [[cell]] data structures with default styling and positioning for the cells.

You can use the transpose and juxtapose helpers along with the cell namespace to manipulate grids more manually.

For example, a multiplication table with all of the squares highlighted:

(let [highlight {:fill-pattern :solid-foreground
                 :fill-foreground-color :yellow}

      grid (for [x (range 1 11)]
             (for [y (range 1 11)]
               (cond-> (* x y) (= x y) (cell/style highlight))))

      cols (map #(cell/style % {:font {:bold true}}) (range 1 11))
      ;; Add the top column labels
      grid (concat [cols] grid)
      ;; Add the left-hand column labels
      grid (excel/juxtapose (excel/transpose [(cons nil cols)]) grid)]
  (excel/quick-open!
    {"Transpose & Juxtapose"
     (excel/with-title "Multiplication Table" grid)}))

Templates

The support for templates is not feature packed, but it works well in situations where your template can use formulas to read data from another sheet.

The append! function allows merging in a sheet to a workbook, replacing any other sheet of the same name. So, if your template is a workbook with a main sheet that reads from another data sheet, you can fill in the template by replacing the data sheet.

For example, you can try:

(def example-template-data
  ;; Some mocked tabular uptime data to inject into the template
  (let [start-ts (inst-ms #inst"2020-05-01")
        one-hour (* 1000 60 60)]
    (for [i (range 99)]
      {"Date"                 (java.util.Date. (+ start-ts (* i one-hour)))
       "Webserver Uptime"     (- 1.0 (rand 0.25))
       "REST API Uptime"      (- 1.0 (rand 0.25))
       "WebSocket API Uptime" (- 1.0 (rand 0.25))})))


; The template here has a 'raw' sheet, which contains uptime data for 3 time
; series, and a 'Summary' sheet, wich uses formulas + the raw data to compute
; and plot. We're going to overwrite the 'raw' sheet to fill in the template.
(let [template (clojure.java.io/resource "uptime-template.xlsx")
      new-data {"raw" (excel/table-grid example-template-data)}]
  (excel/append! new-data template "filled-in-template.xlsx"))

Roadmap

• A way to read in a saved workbook to the {sheet-name [[cell]]} format. I'm not sure what the best way to extract style data is, since there are so many possible values.

Development

Unit Tests

Standard unit tests can be run with the following command:

$ lein test

Office Integration Tests

This test selector is designed to run tests that are dependent on the presence of LibreOffice or OpenOffice.

These tests can be run with:

$ lein test :office-integrations

All Tests

Run all tests with the following command:

$ lein test :all