clerk

clerk provides a Haskell eDSL in a library for declarative spreadsheet generation. clerk is built on top of the xlsx package and extends upon the work of Nickolay Kudasov by making the tables' layout more flexible.

Features

clerk can be used to produce a styled spreadsheet with some data and formulas on it. These formulas are evaluated when the document is loaded into a target spreadsheet system.

The library supports:

• Typed cell references. Example: CellRef Double.
• Type-safe arithmetic operations with them. Example: (a :: CellRef Double) + (b :: CellRef Double) produces a CellRef Double.
• Constructing expressions with given types. Example: (e :: Expr Double) = "SUM" |$| [a |:| b], e translates to SUM(A1:B1) (actual value depends on the values of a and b).
• Conditional styles, formatting, column widths.

The example below demonstrates most of these features.

Example

The goal: describe and generate a spreadsheet that calculates the pressure data given some volume data and constants.

The source code for this example is available in the example directory. The program produces an xlsx file that looks as follows:

Alternatively, with formulas enabled:

The below sections describe how such a spreadsheet can be constructed.

Extensions

We'll need several language extensions.

{-# LANGUAGE OverloadedRecordDot #-} -- access the fields of records like a.b
{-# LANGUAGE ImportQualifiedPost #-}
{-# LANGUAGE RankNTypes #-}
{-# LANGUAGE RecordWildCards #-}
{-# LANGUAGE DuplicateRecordFields #-}
{-# LANGUAGE LambdaCase #-}
{-# LANGUAGE InstanceSigs #-}

Imports

And import the necessary stuff.

module Main (main) where

import Clerk
import Codec.Xlsx qualified as X
import Codec.Xlsx.Formatted qualified as X
import Control.Lens ((%~), (&), (?~))
import Control.Monad (void)
import Data.ByteString.Lazy qualified as L
import Data.Text qualified as T
import Data.Time.Clock.POSIX (getPOSIXTime)

Tables

The tables that we'd like to construct are:

• A table per a constant's value (three of them)
• Volume & pressure table
• Constants' header
• Volume & pressure header

Constants' values

In our case, each constant has the same type of the numeric value - Double. However, it might be the case that in another set of constants, they'll have different types. That's why, in our case, we'll construct a table with a single row per a constant and later stack the constants' tables together. We can keep a constant's data in a record.

data ConstantData a = ConstantData
  { constantName :: String
  , constantSymbol :: String
  , constantValue :: a
  , constantUnits :: String
  }

Next, we can group the constants.

data Constants f = Constants
  { gasConstant :: f Double
  , numberOfMoles :: f Double
  , temperature :: f Double
  }

type ConstantsInput = Constants ConstantData

constants :: ConstantsInput
constants =
  Constants
    { gasConstant = ConstantData "GAS CONSTANT" "R" 0.08206 "L.atm/mol.K"
    , numberOfMoles = ConstantData "NUMBER OF MOLES" "n" 1 "moles"
    , temperature = ConstantData "TEMPERATURE(K)" "T" 273.2 "K"
    }

Furthermore, we'd like to style the constants' tables, so let's prepare the styles. We'll reuse these styles in other tables.

data Colors = LightBlue | LightGreen | Blue | Green
instance Show Colors where
  show :: Colors -> String
  show = \case
    LightBlue -> "90CCFFFF"
    LightGreen -> "90CCFFCC"
    Blue -> "FF99CCFF"
    Green -> "FF00FF00"

blue :: FormatCell
blue = mkColorStyle Blue

lightBlue :: FormatCell
lightBlue = mkColorStyle LightBlue

green :: FormatCell
green = mkColorStyle Green

mixed :: FormatCell
mixed coords idx = mkColorStyle (if even idx then LightGreen else LightBlue) coords idx

Additionally, we compose a transformation of a FormatCell for the number format

use2decimalDigits :: FCTransform
use2decimalDigits fcTransform =
  fcTransform & X.formattedFormat %~ (\format -> format & X.formatNumberFormat ?~ X.StdNumberFormat X.Nf2Decimal)

And a transform for centering the cell contents

alignCenter :: FCTransform
alignCenter = horizontalAlignment X.CellHorizontalAlignmentCenter

Now, we can make a RowBuilder for a constant. We'll later use this builder for each constant separately.

We get a pair of outputs:

• Top left cell of a constant's table. That is, the cell with that constant's name.
• The value of the constant.

Later, the outputs of this and other RowBuilders will be used to relate the positions of tables on a sheet.

constantBuilder :: ToCellData a => RowBuilder (ConstantData a) CellData (Coords, CellRef a)
constantBuilder = do
  refTopLeft <- column lightBlue constantName
  column_ lightBlue constantSymbol
  refValue <- column (lightBlue +> use2decimalDigits) constantValue
  column_ lightBlue constantUnits
  return (unCell refTopLeft, refValue)

Volume & Pressure values

To fill this table, we'll take the some data and combine it with the constants.

newtype Volume = Volume {volume :: Double}

volumeData :: [Volume]
volumeData = take 10 $ Volume <$> [1 ..]

To pass the constants' references in a structured way, we make a helper type.

data ConstantsRefs = ConstantsRefs
  { refGas :: CellRef Double
  , refNumberOfMoles :: CellRef Double
  , refTemperature :: CellRef Double
  }

Next, we define a function to produce a builder for volume and pressure.

valuesBuilder :: ConstantsRefs -> RowBuilder Volume CellData ()
valuesBuilder ConstantsRefs{..} = do
  refVolume <- column mixed volume
  let pressure' = refGas |*| refNumberOfMoles |*| refTemperature |/| refVolume
  column_ (mixed +> use2decimalDigits) (const pressure')

Constants' header

We won't use records here. Instead, we'll put the names of the columns straight into the RowBuilder.

The outputs will be the coordinates of the top left cell and the top right cell of this table.

constantsHeaderBuilder :: RowBuilder () CellData (Coords, Coords)
constantsHeaderBuilder = do
  refTopLeft <- columnWidth 20 (blue +> alignCenter) (const "constant")
  columnWidth_ 8 (blue +> alignCenter) (const "symbol")
  column_ (blue +> alignCenter) (const "value")
  refTopRight <- columnWidth 13 (blue +> alignCenter) (const "units")
  return (unCell refTopLeft, unCell refTopRight)

Volume & Pressure header

For this header, we'll also put the names of columns straight inside the builder.

valuesHeaderBuilder :: RowBuilder () CellData Coords
valuesHeaderBuilder = do
  tl <- columnWidth 12 green (const "VOLUME (L)")
  columnWidth_ 16 green (const "PRESSURE (atm)")
  return (unCell tl)

Sheet builder

The SheetBuilder is used to place RowBuilders onto a sheet and glue them together. Inside SheetBuilder, when a RowBuilder is placed onto a sheet, we can use the references that it produces in the subsequent expressions.

full :: SheetBuilder ()
full = do
  (constantsHeaderTL, constantsHeaderTR) <- placeInput (Coords 2 2) () constantsHeaderBuilder
  (gasTL, gas) <- placeInput (overRow (+ 2) constantsHeaderTL) constants.gasConstant constantBuilder
  (nMolesTL, nMoles) <- placeInput (overRow (+ 1) gasTL) constants.numberOfMoles constantBuilder
  temperature <- snd <$> placeInput (overRow (+ 1) nMolesTL) constants.temperature constantBuilder
  valuesHeaderTL <- placeInput (overCol (+ 2) constantsHeaderTR) () valuesHeaderBuilder
  placeInputs_ (overRow (+ 2) valuesHeaderTL) volumeData (valuesBuilder $ ConstantsRefs gas nMoles temperature)

Result

Finally, we can write the result and get the spreadsheet like the one that you've seen at the top of this tutorial.

writeWorksheet :: SheetBuilder a -> String -> IO ()
writeWorksheet tb name = do
  ct <- getPOSIXTime
  let xlsx = composeXlsx [(T.pack "List 1", void tb)]
  L.writeFile ("example-" <> name <> ".xlsx") $ X.fromXlsx ct xlsx

writeEx :: IO ()
writeEx = writeWorksheet full "1"

main :: IO ()
main = writeEx

Run

cd example
nix develop
cabal run

to get example/example-1.xlsx.

With formulas enabled, example-1.xlsx looks like this:

Contribute

This project provides a dev environment via a Nix flake.

1. With flakes enabled, run:

   nix develop
   cabal build
   

2. This README.md is generated from several files. If you edit them, re-generate it.

   cabal test docs
   

3. (Optionally) Start VSCodium with Haskell extensions.

   1. Write settings and run VSCodium.

      nix run .#writeSettings
      nix run .#codium .
      

   2. Open a Haskell file. Haskell Language Server should soon start giving you hints.

4. Study these links if you'd like to learn more about the tools used in this flake:

   • Prerequisites
   • Haskell project template
   • Haskell