dk.ative.docjure.spreadsheet — dk.ative/docjure 1.19.0

add-name! clj

(add-name! workbook n string-ref)

add-row! clj

(add-row! sheet values)

add-rows! clj

(add-rows! sheet rows)

Add rows to the sheet. The rows is a sequence of row-data, where each row-data is a sequence of values for the columns in increasing order on that row.

add-sheet! clj

(add-sheet! workbook name)

Add a new sheet to the workbook.

apply-date-format! clj

(apply-date-format! cell format)

assert-type clj macro

(assert-type value expected-type)

auto-size-all-columns! clj

(auto-size-all-columns! sheet)

Adjusts the column width to fit the contents for all columns on a sheet.

This is quite slow. It is recommended to use as a post-processing step only.

Note that this may require fonts to be available and thus not work in headless mode.

auto-size-column! clj

(auto-size-column! sheet i)

Adjusts the column width to fit the contents. i is the index of the columnm, typically starting at 0.

This operation is quite slow. Recommended usage is as a post-processing step when a sheet has been fully populated.

Note that this may require fonts to be available and thus not work in headless mode.

border clj

(border kw)

Returns border style

cell-fn clj

(cell-fn outputcell sheet & inputcells)

Turn a cell (ideally containing a formula) into a function. The returned function will take a variable number of parameters, updating each of the inputcells in the sheet with the supplied values and return the value of the cell outputcell. Cell names are specified using Excel syntax, i.e. A2 or B12.

cell-reference clj

(cell-reference cell)

cell-seq clj multimethod

Return a seq of the cells in the input which can be a sheet, a row, or a collection of one of these. The seq is ordered ordered by sheet, row and column. Missing cells will be returned as nil, note this is different from blank cells which have type (CellType/BLANK)

color-index clj

(color-index colorkw)

Returns color index from org.apache.ss.usermodel.IndexedColors from lowercase keywords

column-index-seq clj

(column-index-seq row)

Get the a sequence of the the column indices for a row from the first to the last.

Indices are used to address columns in e.g. auto-size-column!

create-cell-style! clj

(create-cell-style! workbook)

(create-cell-style! workbook styles)

Create a new cell-style in the workbook from options:

:background background colour (as keyword) :font font | fontmap (of font options) :halign :left | :right | :center :valign :top | :bottom | :center :wrap true | false - controls text wrapping :border-left :thin | :medium | :thick :border-right :thin | :medium | :thick :border-top :thin | :medium | :thick :border-bottom :thin | :medium | :thick :left-border-color colour keyword :right-border-color colour keyword :top-border-color colour keyword :bottom-border-color colour keyword :indent number from 0 to 15 :data-format string :quote-prefixed true | false - shows a leading ' when editing formula-like text

Valid colour keywords are the colour names defined in org.apache.ss.usermodel.IndexedColors as lowercase keywords, eg.

:black, :white, :red, :blue, :light_green, :yellow, ...

Examples: I. (def f (create-font! wb {:name "Arial", :bold true, :italic true}) (create-cell-style! wb {:background :yellow, :font f, :halign :center, :wrap true, :border-bottom :thin}) II. (create-cell-style! wb {:background :yellow, :halign :center, :font {:name "Arial" :bold true :italic true}, :wrap true, :border-bottom :thin})

create-font! clj

(create-font! workbook options)

Create a new font in the workbook with options:

:name      font family (string)
:size      font size  (integer)
:color     font color (keyword)
:bold      true | false
:italic    true | false
:underline true | false

Example:

(create-font! wb {:name "Arial", :size 12, :color :blue, :bold true, :underline true})

create-sparse-workbook clj

(create-sparse-workbook sheet-name data)

(create-sparse-workbook sheet-name data & name-data-pairs)

Create a new XLSX workbook. Sheet-name is a string name for the sheet. Data is a vector of vectors, representing the rows and the cells of the rows. Alternate sheet names and data to create multiple sheets.

Spreadsheet rows and cells can be nil, which will create a sparse spreadsheet with non-continuous rows and cells

(This version exists mostly for generating test data, create-workbook will normally do just fine unless you have a specific need for sparseness)

(create-sparse-workbook "SheetName1" [["A1" "A2"] nil ["C1" nil "C3"]] "SheetName2" [["A1" "A2"] nil ["C1" nil "C3"]]

create-workbook clj

(create-workbook sheet-name data)

(create-workbook sheet-name data & name-data-pairs)

Create a new XLSX workbook. Sheet-name is a string name for the sheet. Data is a vector of vectors, representing the rows and the cells of the rows. Alternate sheet names and data to create multiple sheets.

(create-workbook "SheetName1" [["A1" "A2"]["B1" "B2"]] "SheetName2" [["A1" "A2"]["B1" "B2"]]

create-xls-workbook clj

(create-xls-workbook sheet-name data)

Create a new XLS workbook with a single sheet and the data specified.

escape-cell clj

(escape-cell value)

When inputting a cell's value in an xlsx workbook, Excel allows 16-bit unicode characters to be input in an escaped form matching this regular expression: x[0-9A-F]{4}.

For example, the character 'A' can be input as 'x0041', the lowercase greek character pi can be input as 'x03C0'.

If your data contains cell values that match this form that you do not want to be interpreted as 16-bit unicode values, the string '_x005F' needs to be prepended to each occurance. For example, to stop 'x03C0' from being interpreted as the character pi you must input 'x005F_x03C0'.

This function will escape all occurances of the Excel 16-bit unicode escape sequence in the specified cell value.

(escape-cell "foo x0041 bar x03C0") => "foo x005F_x0041 bar x005F_x03C0"

get-row-styles clj

(get-row-styles row)

Returns a seq of the row's CellStyles. Missing cells will return a nil style

horiz-align clj

(horiz-align kw)

Returns horizontal alignment

IFontable clj protocol

A protocol that allows:

1. interchangeable use of fonts and maps of font options
2. getting fonts from either XLS or XLSX cell styles, which normally requires distinct syntax.

as-font clj

(as-font this workbook)

get-font clj

(get-font this workbook)

set-font clj

(set-font this style workbook)

into-seq clj

(into-seq sheet-or-row)

load-workbook clj multimethod

Load an Excel .xls or .xlsx workbook from an InputStream.

load-workbook-from-file clj

(load-workbook-from-file file)

Load an Excel .xls or .xlsx workbook from a file.

load-workbook-from-resource clj

(load-workbook-from-resource resource)

Load an Excel workbook from a named resource. Used when reading from a resource on a classpath as in the case of running on an application server.

load-workbook-from-stream clj

(load-workbook-from-stream stream)

Load an Excel workbook from a stream. The caller is required to close the stream after loading is completed.

read-cell clj multimethod

read-cell-value clj multimethod

remove-all-rows! clj

(remove-all-rows! sheet)

Remove all the rows from the sheet.

remove-row! clj

(remove-row! sheet row)

Remove a row from the sheet. Rows are not shifted up - the removed row will display as blank

row-seq clj

(row-seq sheet)

Return a lazy sequence of the rows in a sheet. Missing rows will be returned as nil filter with e.g. (remove nil? (row-seq ...)) if missing rows are not needed

row-vec clj

(row-vec column-order row)

Transform the row struct (hash-map) to a row vector according to the column order. Example:

(row-vec [:foo :bar] {:foo "Foo text", :bar "Bar text"})

["Foo text" "Bar text"]

row? clj

(row? x)

Return true if and only if x is a row in a sheet.

save-workbook! clj multimethod

Save the workbook into a stream or a file. In the case of saving into a stream, the caller is required to close the stream after saving is completed.

save-workbook-into-file! clj

(save-workbook-into-file! filename workbook)

Save the workbook into a file.

save-workbook-into-stream! clj

(save-workbook-into-stream! stream workbook)

Save the workbook into a stream. The caller is required to close the stream after saving is completed.

select-cell clj

(select-cell n sheet)

Given a Sheet and a cell reference (A1), select-cell returns the cell or nil if the cell could not be found

select-columns clj

(select-columns column-map sheet)

Takes two arguments: column hashmap and a sheet. The column hashmap specifies the mapping from spreadsheet columns dictionary keys: its keys are the spreadsheet column names and the values represent the names they are mapped to in the result.

For example, to select columns A and C as :first and :third from the sheet

(select-columns {:A :first, :C :third} sheet) => [{:first "Value in cell A1", :third "Value in cell C1"} ...]

select-name clj

(select-name workbook n)

Given a workbook and name (string or keyword) of a named range, select-name returns a seq of cells or nil if the name could not be found.

select-sheet clj multimethod

Select a sheet from the workbook by name, regex or arbitrary predicate

set-cell! clj multimethod

set-cell-comment! clj

(set-cell-comment! cell
                   comment-str
                   &
                   {:keys [font width height] :or {width 1 height 2}})

Creates a cell comment-box that displays a comment string when the cell is hovered over. Returns the cell.

Options:

:font (font | fontmap - font applied to the comment string) :width (int - width of comment-box in columns; default 1 cols) :height (int - height of comment-box in rows; default 2 rows)

Example:

(set-cell-comment! acell "This comment should span two lines." :width 2 :font {:bold true :size 12 :color blue})

set-cell-style! clj

(set-cell-style! cell style)

Apply a style to a cell. See also: create-cell-style!.

set-row-style! clj

(set-row-style! row style)

Apply a style to all the cells in a row. Returns the row.

set-row-styles! clj

(set-row-styles! row styles)

Apply a seq of styles to the cells in a row. Cells that are missing won't be assigned a style - if you want to style missing cells, create them first

sheet-name clj

(sheet-name sheet)

Return the name of a sheet.

sheet-name? clj

(sheet-name? x)

Is x a valid sheet name?

sheet-seq clj

(sheet-seq workbook)

Return a lazy seq of the sheets in a workbook.

sheet? clj

(sheet? x)

Return true if and only if x is a sheet in a workbook.

string-cell? clj

(string-cell? cell)

vert-align clj

(vert-align kw)

Returns vertical alignment

whens clj macro

(whens & [test expr :as clauses])

Processes any and all expressions whose tests evaluate to true. Example: (let [m (java.util.HashMap.)] (whens false (.put m :z 0) true (.put m :a 1) true (.put m :b 2) nil (.put m :w 3)) m) => {:b=2, :a=1}

workbook? clj

(workbook? x)

Return true if and only if x is a workbook (also known as a spreadsheet).