clods-export

https://github.com/jlahd/clods-export.git

git clone 'https://github.com/jlahd/clods-export.git'

(ql:quickload :clods-export)
★2

clods-export

It is sometimes necessary to offer users of e.g. a web service the option to export their data from your application to the outside world. Often, a good format would be a spreadsheet document, as it offers a simple way of displaying and editing tabular data, and people have over the years become quite familiar in misusing Excel for a variety of things Excel was not designed for.

clods-export helps you by writing out OpenDocument Spreadsheet files. It does not try to implement functionality needed by proper spreadsheet applications and it does not support reading in ODS formatted data, but it tries to make creation of ODS files straightforward and easy.

For a quick start, look at example.lisp. A short API reference is given below, but to fully understand how all parameters work I recommend studying the OpenDocument XML format specification as well.

Caveat! Different software (Excel, LibreOffice, OpenOffice) handle ODS data in a different manner, and are a bit incompatible. So, you should check your generated data on all of the applicable programs in order to see that all formatting goes through as you intended.

Document structure

An ODS document consists of three parts: Definitions for fonts, definitions for styles, and the actual data content. These parts must be specified, in this order, to clods-export as well.

Start by wrapping your export functionality inside a with-spreadsheet form. The name of the generated ODS file is given as an argument, along with metadata (name of the application generating the document and the human creator of the data). Inside the spreadsheet, you then define fonts, styles and content:

(clods:with-spreadsheet ("my.ods" :generator "My app" :creator "Me")
  (clods:using-fonts ()
    ...)
  (clods:using-styles (:locale ...)
    ...)
  (clods:with-body ()
    (clods:with-table ("Sheet one")
      ...)
    (clods:with-table ("Sheet two")
      ...)
    ...))

Specifying fonts

Fonts are specified using the clods:font function inside a clods:using-fonts form. clods:font takes the name of the font definition as its first argument, and a number of optional key arguments describing the font properties:

Note that certain font details can be specified along with text properties when defining cell styles, as well.

Excel understands the name of the font definition as a synonym for the font family. That is, if you want to be Excel compatible, you must match the font's name argument with the :family argument:

;; works on LibreOffice and OpenOffice but not on Excel:
(clods:font "normal" :family "Arial")
;; works on all three:
(clods:font "Arial" :family "Arial")

Specifying styles

ODS defines formatting on several levels. On the lowest level, there are data styles (number styles) that specify how data is formatted into strings to be displayed. Then, cell styles specify formatting inside a single cell. Column and row styles define the width/height of the column/row as well as the default cell style to be applied.

Styles are defined hierarchically, so that styles can inherit properties from other styles. However, this inheritance fails spectacularly on LibreOffice and OpenOffice (but does seem to work on Excel), so it is probably a good idea to define every style from the bottom up.

Both data styles and cell styles also contain text properties that define visual aspects of the displayed text.

Locales

Locale, as defined by clods-export, is a simple object that contains the following four slots:

Locales can be created with the function clods:make-locale. For example, the Finnish locale, where a large decimal number is written as “1 234 567,89”, would be defined as follows:

(clods:make-locale "FI" #\space 3 #\,)

Text properties

Text property definitions are lists containing pairs of keywords and values. The set of supported keywords is listed in clods:*text-properties* and they map directly to those defined in the OpenDocument specification.

Number formatting

Numbers can be displayed in three different representations: standard numbers, scientific numbers and fractions. The representation to be used is deduced from the arguments in the number format specification, which is a list of keyword-value pairs. The following keywords are supported:

Note that LibreOffice and OpenOffice require quite complete number specifications in order to display numbers correctly. Thus, you should specify things like :min-integer-digits 1 even though they seem superfluous, if you wish these applications to show your data correctly.

Data styles

There are several different data formatting types. In addition to the type-specific arguments described below, they all accept the keyword arguments :locale (of type locale) and :text-properties (a list that contains text property definitions).

Cell styles

For a cell style, you can define text properties, and a set of other formatting keyword arguments:

(clods:cell-style name parent-style text-properties &key ...)

Cell styles support inheritance, so you can build your styles on top of each other by using the parent-style argument. Note, though, that not all applications obey the inheritance.

The available keyword arguments are:

Row styles

Row styles are defined as follows:

(clods:row-style name parent-style &key ...)

The available keyword arguments are:

Column styles

Column styles are defined as follows:

(clods:column-style name parent-style &key ...)

The available keyword arguments are:

Table styles

Table styles are included for the sake of completeness.

(clods:table-style name parent-style &key ...)

The available keyword arguments are:

Table content

Each worksheet on the document are defined inside a clods:with-table form. The sheet's name, usually shown on a tab at the bottom of the spreadsheet application's view, is given as an argument. Inside the with-table form first the table's columns are defined, followed by the rows containing the actual data in cells:

(clods:with-table ("Table name")
  (clods:with-header-columns ()
    (clods:column ...)
    ...)
  (clods:with-header-rows ()
    (clods:with-row (...)
      (clods:cell ...)
      ...)
    ...)
  (clods:with-row (...)
    (clods:cell ...)
    ...))

The semantic grouping of certain columns and rows into the header columns/rows groups is optional and has no visual effect on the table.

Defining columns

(clods:column &key repeat style visibility cell-style)

Defining rows

(clods:with-row (&key repeat style visibility cell-style) ...)

The keyword arguments are similar to those given to clods:column.

Defining cells

(clods:cell content &key style formula span-columns span-rows link)

Cells contain the actual data on the table. The content of each cell is given in the content argument that may be nil if the cell is empty. The keyword arguments are as follows:

If the cell contains a :style argument, that style is used for formatting. Otherwise, if the current row has specified a :cell-style, that is used. If not, but the current column specifies a :cell-style, it takes effect. Otherwise, only string content is supported.

In addition to the data style specified by the active cell style, the formatting of the cell's content depends on the type of the content argument.

(clods:cells &rest content)

For convenience, a set of adjacent cells on the same row requiring no special formatting can be written out in a single function call to clods:cells.

(clods:covered-cell &optional n)

When a cell has a :span-rows argument larger than 1, the adjacent cells on the following rows in the same column (that is, those cells covered by the spanning cell) have to be marked as covered. clods-export does not take care of this; it is left to the application.

Contact information

If you have a bug to report, or an enhancement to suggest, you can reach me at jussi@lahdenniemi.fi.