com.github.jlangch.venice.excel.venice Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of venice Show documentation
Show all versions of venice Show documentation
Venice, a sandboxed Lisp implemented in Java.
The newest version!
;;;; __ __ _
;;;; \ \ / /__ _ __ (_) ___ ___
;;;; \ \/ / _ \ '_ \| |/ __/ _ \
;;;; \ / __/ | | | | (_| __/
;;;; \/ \___|_| |_|_|\___\___|
;;;;
;;;;
;;;; Copyright 2017-2024 Venice
;;;;
;;;; Licensed under the Apache License, Version 2.0 (the "License");
;;;; you may not use this file except in compliance with the License.
;;;; You may obtain a copy of the License at
;;;;
;;;; http://www.apache.org/licenses/LICENSE-2.0
;;;;
;;;; Unless required by applicable law or agreed to in writing, software
;;;; distributed under the License is distributed on an "AS IS" BASIS,
;;;; WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
;;;; See the License for the specific language governing permissions and
;;;; limitations under the License.
;;;; Venice Excel functions
;;;; Install Apache POI 3rd party libraries:
;;;;
;;;; (load-module :excel-install)
;;;; (excel-install/install :dir (repl/libs-dir) :silent false)
(ns excel)
(import :com.github.jlangch.venice.util.excel.ExcelFacade)
(import :com.github.jlangch.venice.util.excel.ExcelSheetFacade)
(import :com.github.jlangch.venice.util.excel.ExcelFontBuilder)
(import :com.github.jlangch.venice.util.excel.ExcelCellStyleBuilder)
(import :com.github.jlangch.venice.util.excel.ExcelColumnBuilder)
(import :com.github.jlangch.venice.util.excel.DataRecord)
(import :com.github.jlangch.venice.util.excel.CellRangeAddr)
(import :com.github.jlangch.venice.util.excel.chart.LineDataSeries)
(import :com.github.jlangch.venice.util.excel.chart.BarDataSeries)
(import :com.github.jlangch.venice.util.excel.chart.AreaDataSeries)
(import :com.github.jlangch.venice.util.excel.chart.PieDataSeries)
(import :java.io.OutputStream)
(import :java.io.InputStream)
;; the indexed colors supported by XLS and XLSX Excel formats
(def colors { :BLACK1 0
:WHITE1 1
:RED1 2
:BRIGHT_GREEN1 3
:BLUE1 4
:YELLOW1 5
:PINK1 6
:TURQUOISE1 7
:BLACK 8
:WHITE 9
:RED 10
:BRIGHT_GREEN 11
:BLUE 12
:YELLOW 13
:PINK 14
:TURQUOISE 15
:DARK_RED 16
:GREEN 17
:DARK_BLUE 18
:DARK_YELLOW 19
:VIOLET 20
:TEAL 21
:GREY_25_PERCENT 22
:GREY_50_PERCENT 23
:CORNFLOWER_BLUE 24
:MAROON 25
:LEMON_CHIFFON 26
:LIGHT_TURQUOISE1 27
:ORCHID 28
:CORAL 29
:ROYAL_BLUE 30
:LIGHT_CORNFLOWER_BLUE 31
:SKY_BLUE 40
:LIGHT_TURQUOISE 41
:LIGHT_GREEN 42
:LIGHT_YELLOW 43
:PALE_BLUE 44
:ROSE 45
:LAVENDER 46
:TAN 47
:LIGHT_BLUE 48
:AQUA 49
:LIME 50
:GOLD 51
:LIGHT_ORANGE 52
:ORANGE 53
:BLUE_GREY 54
:GREY_40_PERCENT 55
:DARK_TEAL 56
:SEA_GREEN 57
:DARK_GREEN 58
:OLIVE_GREEN 59
:BROWN 60
:PLUM 61
:INDIGO 62
:GREY_80_PERCENT 63
:AUTOMATIC 64 } )
(def col-radix (+ 1 (- (long #\Z) (long #\A))))
;; #############################################################################
;;
;; Excel
;;
;; #############################################################################
;; -----------------------------------------------------------------------------
;; Create / Open
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '("(create type)")
:doc """
Creates a new Excel for the given type :xls or :xlsx.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""")
:see-also '(
"excel/open",
"excel/add-sheet", "excel/add-font", "excel/add-style",
"excel/write->file", "excel/write->stream", "excel/write->bytebuf",
"excel/evaluate-formulas" )}
create [type]
{ :pre [(keyword? type) ] }
(case type
:xls (. :ExcelFacade :createXls)
:xlsx (. :ExcelFacade :createXlsx)
(throw (ex :VncException
(str "Invalid Excel type " type ". Use :xls or :xlsx")))))
(def writer create) ;; alias for backward compatibility
(defn
^{ :arglists '("(open source)")
:doc """
Opens an existing Excel for reading or modifying.
Supported sources are *string file path*, `bytebuf`, `:java.io.File`,
or `:java.io.InputStream`.
* *string file path* -> `(excel/open "/Users/foo/data/test.xlsx")`
* *classpath* -> `(excel/open (io/load-classpath-resource "org/foo/data/test.xlsx"))`
* *:java.io.File* -> `(excel/open (io/file "/Users/foo/data/test.xlsx"))`
* *:java.io.InputStream* -> `(excel/open (io/file-in-stream "/Users/foo/data/test.xlsx"))`
* *bytebuf* -> `(excel/open (io/slurp "/Users/foo/data/test.xlsx" :binary true))`
"""
:examples '(
"""
(do
(load-module :excel)
;; create an excel
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))
;; open the excel and add another row
(let [wbook (excel/open "sample.xlsx")
sheet (excel/sheet wbook "Sheet 1")]
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample1.xlsx")))
""")
:see-also '(
"excel/create",
"excel/add-sheet", "excel/add-font", "excel/add-style",
"excel/write->file", "excel/write->stream", "excel/write->bytebuf",
"excel/evaluate-formulas" )}
open [source]
(cond
(string? source)
(. :ExcelFacade :open (io/file-in-stream source))
(bytebuf? source)
(. :ExcelFacade :open (io/bytebuf-in-stream source))
(io/file? source)
(. :ExcelFacade :open (io/file-in-stream source))
(instance-of? :InputStream source)
(. :ExcelFacade :open source)
:else (throw (ex :VncException
(str "Invalid Excel open source " (type source) )))))
;; -----------------------------------------------------------------------------
;; Save
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '("(write->file wbook f)")
:doc "Writes the excel to a file."
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '("excel/write->stream", "excel/write->bytebuf")}
write->file [wbook f]
{ :pre [(instance-of? :ExcelFacade wbook)
(or (string? f) (io/file? f)) ] }
(. wbook :write (io/file-out-stream f)))
(defn
^{ :arglists '("(write->stream wbook os)")
:doc "Writes the excel to a Java :OutputStream."
:examples '(
"""
(do
(load-module :excel)
(let [os (io/file-out-stream "sample.xlsx")
data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/write->stream wbook os)))
""" )
:see-also '("excel/write->file", "excel/write->bytebuf")}
write->stream [wbook os]
{ :pre [(instance-of? :ExcelFacade wbook)
(instance-of? :OutputStream os)] }
(. wbook :write os))
(defn
^{ :arglists '("(write->bytebuf wbook)")
:doc "Writes the excel to a bytebuf. Returns the bytebuf."
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/write->bytebuf wbook)))
""" )
:see-also '("excel/write->file", "excel/write->stream")}
write->bytebuf [wbook]
{ :pre [(instance-of? :ExcelFacade wbook)] }
(. wbook :writeToBytes))
;; -----------------------------------------------------------------------------
;; Evaluate formulas
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '("(evaluate-formulas wbook-or-sheet)")
:doc "Evaluate all formulas in a workbook or a sheet."
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 101 102] [200 201 202]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))]
(excel/evaluate-formulas wbook)))
""" )
:see-also '(
"excel/evaluate-formula"
"excel/cell-formula"
"excel/remove-formula") }
evaluate-formulas [wbook-or-sheet]
{ :pre [(or (instance-of? :ExcelFacade wbook-or-sheet)
(instance-of? :ExcelSheetFacade wbook-or-sheet))] }
(. wbook-or-sheet :evaluateAllFormulas))
(defn
^{ :arglists '("(evaluate-formula sheet row col)")
:doc "Evaluate the formula a sheet cell."
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:a 100 :b 200 }
{:a 101 :b 201 }
{:a 102 :b 202 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row true })]
(excel/add-column sheet "A" { :field :a })
(excel/add-column sheet "B" { :field :b })
(excel/add-column sheet "C" { :field :c })
(excel/write-items sheet data)
(excel/cell-formula sheet 1 3 "SUM(A1,B1)")
(excel/cell-formula sheet 2 3 "SUM(A2,B2)")
(excel/cell-formula sheet 3 3 "SUM(A3,B3)")
(excel/evaluate-formula sheet 1 3)
(excel/evaluate-formula sheet 2 3)
(excel/evaluate-formula sheet 3 3)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/evaluate-formulas"
"excel/cell-formula"
"excel/remove-formula") }
evaluate-formula [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)] }
(. sheet :evaluateCell row col))
;; -----------------------------------------------------------------------------
;; Write data to excel
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '(
"(write-data sheet data)"
"(write-data sheet data row col)")
:doc
"""
Writes the data of a 2D array to an excel sheet.
Optionally the data can written to a region starting at a row/col
position.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")
dt (time/local-date 2021 1 1)
ts (time/local-date-time 2021 1 1 15 30 45)
data [[100 101 102 103 104 105]
[200 "ab" 1.23 dt ts false]]]
(excel/write-data sheet data)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""",
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 101 102] [200 201 203]])
(excel/write-data sheet [[300 301 302] [400 401 403]] 3 4)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""")
:see-also '("excel/write->stream", "excel/write->bytebuf")}
write-data
([sheet data]
(write-data sheet data 1 1))
([sheet data row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)] }
(let [cell (fn [r c val] (excel/write-value sheet (+ r row) (+ c col) val))]
(map-indexed (fn [r rv] (map-indexed (fn [c cv] (cell r c cv)) rv))
data))))
(defn
^{ :arglists '("(write-items sheet items)")
:doc
"""
Writes the passed data items, a sequence of maps of name/value pairs,
to the sheet.
"""
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/write-item", "excel/write-value", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
write-items [sheet items]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(sequential? items)
(every? map? items)] }
(->> (map #(. :DataRecord :of %) items)
(into! (. :java.util.ArrayList :new))
(. sheet :renderItems)))
(defn
^{ :arglists '("(write-item sheet item)")
:doc "Render a single data item to the sheet"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-item sheet {:first "John" :last "Doe" :age 28 })
(excel/write-item sheet {:first "Sue" :last "Ford" :age 26 })
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/write-items", "excel/write-value", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
write-item [sheet item]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(map? item)] }
(->> (. :DataRecord :of item)
(. sheet :renderItem)))
(defn
^{ :arglists '(
"(write-value sheet row col val)",
"(write-value sheet row col val style)")
:doc
"""
Writes a value with an optional to a specific cell given by its
row and col.
If style is not passed or is `nil` uses a default style to render the
value according to its data type:
* string: no format
* boolean: no format
* integer: #,###0
* double: #,##0.00
* date: dd.mm.yyyy
* datetime: dd.mm.yyyy hh:mm:ss
To use the existing cell's style without changing it when modifying the
cell's value pass `:keep-style` as style!
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-value sheet 1 1 "John")
(excel/write-value sheet 1 2 "Doe")
(excel/write-value sheet 1 3 28)
(excel/write-value sheet 2 1 "Sue")
(excel/write-value sheet 2 2 "Ford")
(excel/write-value sheet 2 3 26)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""",
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-font wbook :italic { :italic true })
(excel/add-font wbook :bold { :bold true })
(excel/add-style wbook :italic { :font :italic })
(excel/add-style wbook :bold { :font :bold })
(excel/write-value sheet 1 1 "John" :italic)
(excel/write-value sheet 1 2 "Doe" :italic)
(excel/write-value sheet 1 3 28 :bold)
(excel/write-value sheet 2 1 "Sue" :italic)
(excel/write-value sheet 2 2 "Ford" :italic)
(excel/write-value sheet 2 3 26 :bold)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/write-values", "excel/write-values-keep-style",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
write-value
([sheet row col val]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)] }
(if (and (map? val) (contains? val :formula))
(cell-formula sheet row col (:formula val))
(. sheet :value row col val)))
([sheet row col val style]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col)(pos? col)
(keyword? style)] }
(if (and (map? val) (contains? val :formula))
(cell-formula sheet row col (:formula val) style)
(if (== :keep-style style)
(. sheet :valueKeepCellStyle row col val)
(. sheet :value row col val (name style))))))
(defn
^{ :arglists '("(write-values sheet row col & vals)")
:doc """
Writes multiples value to a row starting at col and incrementing col
for each value
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/write-value", "excel/write-values-keep-style",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
write-values
[sheet row col & vals]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)] }
(docoll #(write-value sheet row (first %) (second %))
(map vector (lazy-seq col inc) vals)))
(defn
^{ :arglists '("(write-values-keep-style sheet row col & vals)")
:doc """
Writes multiples value to a row starting at col and incrementing col
for each value. Keeps the existing cell styles.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/write-value", "excel/write-values",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
write-values-keep-style
[sheet row col & vals]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)] }
(docoll #(write-value sheet row (first %) (second %) :keep-style)
(map vector (lazy-seq col inc) vals)))
(defn
^{ :arglists '(
"(delete-row sheet row)")
:doc
"Deletes a specific row from a sheet."
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/delete-row sheet 1)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/clear-row", "excel/copy-row", "excel/copy-row-to-end",
"excel/insert-empty-row",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
delete-row [sheet row]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)] }
(. sheet :deleteRow row))
(defn
^{ :arglists '(
"(copy-row-to-end sheet row)"
"(copy-row-to-end sheet row copy-value copy-style)")
:doc
"Copies a specific row from a sheet to end of the sheet."
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/copy-row-to-end sheet 1)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/clear-row", "excel/delete-row",
"excel/copy-row", "excel/insert-empty-row",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
copy-row-to-end
([sheet row ]
(copy-row-to-end sheet row true true))
([sheet row copy-value copy-style]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(boolean? copy-value) (boolean? copy-style)] }
(. sheet :copyRowToEndOfSheet row copy-value copy-style)))
(defn
^{ :arglists '(
"(copy-row sheet row-from row-to)"
"(copy-row sheet row-from row-to copy-value copy-style)")
:doc
"Copies a specific row in a sheet."
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/copy-row sheet 1 2)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/clear-row", "excel/delete-row",
"excel/copy-row-to-end", "excel/insert-empty-row",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
copy-row
([sheet row-from row-to ]
(copy-row sheet row-from row-to true true))
([sheet row-from row-to copy-value copy-style ]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row-from) (pos? row-from)
(long? row-to) (pos? row-to)
(boolean? copy-value) (boolean? copy-style)] }
(. sheet :copyRow row-from row-to copy-value copy-style)))
(defn
^{ :arglists '(
"(clear-row sheet row)"
"(clear-row sheet row clear-value clear-style)")
:doc
"Clears the values and/or styles in a specific row in a sheet."
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/clear-row sheet 2)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/delete-row",
"excel/copy-row", "excel/copy-row-to-end",
"excel/insert-empty-row",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
clear-row
([sheet row]
(clear-row sheet row true true))
([sheet row clear-value clear-style]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(boolean? clear-value) (boolean? clear-style)] }
(. sheet :clearRow row clear-value clear-style)))
(defn
^{ :arglists '(
"(insert-empty-row sheet row)",
"(insert-empty-row sheet row count)")
:doc "Inserts an empty row or multiple empty rows to a sheet."
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/insert-empty-row sheet 2)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/clear-row", "excel/delete-row",
"excel/copy-row", "excel/copy-row-to-end",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
insert-empty-row
([sheet row]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)] }
(. sheet :insertEmptyRow row))
([sheet row count]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? count) (pos? count)] }
(. sheet :insertEmptyRows row count)))
(defn
^{ :arglists '(
"(copy-cell-style sheet cell-from-row cell-from-col cell-to-row cell-to-col)")
:doc "Copies the style from cell-from to cell-to"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/copy-cell-style sheet 1 1 2 1)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/clear-row", "excel/delete-row",
"excel/copy-row", "excel/copy-row-to-end",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
copy-cell-style [sheet cell-from-row cell-from-col cell-to-row cell-to-col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? cell-from-row) (pos? cell-from-row)
(long? cell-from-col) (pos? cell-from-col)
(long? cell-to-row) (pos? cell-to-row)
(long? cell-to-col) (pos? cell-to-col)] }
(. sheet :copyCellStyle cell-from-row cell-from-col cell-to-row cell-to-col))
(defn
^{ :arglists '(
"""
(add-conditional-bg-color sheet
rule
color-html
region-first-row
region-last-row
region-first-col
region-last-col)
""")
:doc "Add a conditional background color"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/add-conditional-bg-color sheet "ISBLANK(A1)" "#CC636A" 1 2 2 2)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/clear-row", "excel/delete-row",
"excel/copy-row", "excel/copy-row-to-end",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
add-conditional-bg-color [sheet
rule color-html
region-first-row
region-last-row
region-first-col
region-last-col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(string? rule)
(string? color-html)
(long? region-first-row) (pos? region-first-row)
(long? region-last-row) (pos? region-last-row)
(long? region-first-col) (pos? region-first-col)
(long? region-last-col) (pos? region-last-col)] }
(. sheet :addConditionalBackgroundColor rule
color-html
region-first-row
region-last-row
region-first-col
region-last-col))
(defn
^{ :arglists '(
"""
(add-conditional-font-color sheet
rule
color-html
region-first-row
region-last-row
region-first-col
region-last-col)
""")
:doc "Add a conditional font color"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 45)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/add-conditional-font-color sheet "C1 > 30" "#CC636A" 1 1 3 3)
(excel/add-conditional-font-color sheet "C2 > 30" "#CC636A" 2 2 3 3)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/clear-row", "excel/delete-row",
"excel/copy-row", "excel/copy-row-to-end",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
add-conditional-font-color [sheet
rule color-html
region-first-row region-last-row
region-first-col region-last-col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(string? rule)
(string? color-html)
(long? region-first-row) (pos? region-first-row)
(long? region-last-row) (pos? region-last-row)
(long? region-first-col) (pos? region-first-col)
(long? region-last-col) (pos? region-last-col)] }
(. sheet :addConditionalFontColor rule
color-html
region-first-row region-last-row
region-first-col region-last-col))
(defn
^{ :arglists '(
"""
(add-conditional-border sheet
rule
border-top-style
border-right-style
border-bottom-style
border-left-style
border-top-color-html
border-right-color-html
border-bottom-color-html
border-left-color-html
region-first-row
region-last-row
region-first-col
region-last-col)
""")
:doc "Add a conditional border"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 45)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/add-conditional-border sheet "C1 > 30"
:thin :thin :thin :thin
nil nil nil nil
1 1 3 3)
(excel/add-conditional-border sheet "C2 > 30"
:thin :thin :thin :thin
nil nil nil nil
2 2 3 3)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/clear-row", "excel/delete-row",
"excel/copy-row", "excel/copy-row-to-end",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
add-conditional-border [sheet
rule
border-top-style
border-right-style
border-bottom-style
border-left-style
border-top-color-html
border-right-color-html
border-bottom-color-html
border-left-color-html
region-first-row
region-last-row
region-first-col
region-last-col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(string? rule)
(keyword? border-top-style)
(keyword? border-right-style)
(keyword? border-bottom-style)
(keyword? border-left-style)
(or (nil? border-top-color-html) (string? border-top-color-html))
(or (nil? border-right-color-html) (string? border-right-color-html))
(or (nil? border-bottom-color-html) (string? border-bottom-color-html))
(or (nil? border-left-color-html) (string? border-left-color-html))
(long? region-first-row) (pos? region-first-row)
(long? region-last-row) (pos? region-last-row)
(long? region-first-col) (pos? region-first-col)
(long? region-last-col) (pos? region-last-col)] }
(. sheet :addConditionalBorder rule
(map-border-style border-top-style)
(map-border-style border-right-style)
(map-border-style border-bottom-style)
(map-border-style border-left-style)
border-top-color-html
border-right-color-html
border-bottom-color-html
border-left-color-html
region-first-row
region-last-row
region-first-col
region-last-col))
(defn
^{ :arglists '(
"""
(add-text-data-validation sheet
strings
empty-cell-allowed?
err-title
err-text
region-first-row
region-last-row
region-first-col
region-last-col)
""")
:doc "Adds a text enumeration validation to a cell region in a sheet"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" "male" 28)
(excel/write-values sheet 2 1 "Sue" "Ford" "female" 26)
(excel/add-text-data-validation sheet
["male" "female" "unknown"]
true ;; allow empty cell
"Invalid gender"
"Use one of: 'male', 'female', 'unknown'"
1 2 3 3)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/clear-row", "excel/delete-row",
"excel/copy-row", "excel/copy-row-to-end",
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
add-text-data-validation [sheet
strings empty-cell-allowed?
err-title err-text
region-first-row region-last-row
region-first-col region-last-col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(sequential? strings)
(every? string? strings)
(boolean? empty-cell-allowed?)
(or (nil? err-title) (string? err-title))
(or (nil? err-text) (string? err-text))
(long? region-first-row) (pos? region-first-row)
(long? region-last-row) (pos? region-last-row)
(long? region-first-col) (pos? region-first-col)
(long? region-last-col) (pos? region-last-col)] }
(. sheet :addTextDataValidation strings empty-cell-allowed?
err-title err-text
region-first-row region-last-row
region-first-col region-last-col))
;; -----------------------------------------------------------------------------
;; Images
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '(
"(add-image sheet row col data type)",
"(add-image sheet row col data type scale-X scale-Y)")
:doc
"""
Adds an image given by its binary data (a `bytebuf`) to a specific
anchor cell given by its row and col. Optionally the image can be
scaled by an X and Y axis factor. The image types `:PNG` and `:JPEG`
are supported.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")
image "com/github/jlangch/venice/images/venice.png"
data (io/load-classpath-resource image)]
(excel/add-image sheet 2 2 data :PNG)
(excel/write->file wbook "sample.xlsx")))
""",
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")
image "com/github/jlangch/venice/images/venice.png"
data (io/load-classpath-resource image)]
(excel/add-image sheet 2 2 data :PNG 0.5 0.5)
(excel/write->file wbook "sample.xlsx")))
""")
:see-also '(
"excel/write-items", "excel/write-item", "excel/cell-formula",
"excel/auto-size-columns", "excel/auto-size-column",
"excel/row-height") }
add-image
([sheet row col data type]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)
(bytebuf? data)
(keyword? type) (or (== :PNG type) (== :JPEG type))] }
(. sheet :image row col data type nil nil))
([sheet row col data type scale-x scale-y]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)
(bytebuf? data)
(keyword? type) (or (== :PNG type) (== :JPEG type))] }
(. sheet :image row col data type scale-x scale-y)))
;; -----------------------------------------------------------------------------
;; Charts
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '(
"""
(add-line-chart sheet chart-title
chart-addr-range
legend-position
category-axis-title
category-axis-position
value-axis-title
value-axis-position
three-dimensional?
vary-colors?
categories-addr-range
series)
""")
:doc
"""
Adds a line chart.
Arguments:
| chart-title | The chart title |
| chart-addr-range | The chart position in the Excel |
| legend-position | The legend position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| category-axis-title | The category axis title |
| category-axis-position | The category axis position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| value-axis-title | The value axis title |
| value-axis-position | The value axis position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| three-dimensional? | Render in 3D: `true` or `false` |
| vary-colors? | Vary the colors: `true` or `false` |
| categories-addr-range | The category names in the Excel |
| series | The value series data. 1 to N series |
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")
data [["Year" "Bears" "Dolphins" "Whales"]
["2017" 8 150 80 ]
["2018" 54 77 54 ]
["2019" 93 32 100 ]
["2020" 116 11 76 ]
["2021" 137 6 93 ]
["2022" 184 1 72 ]]]
(excel/write-data sheet data)
(excel/add-line-chart sheet
"Wildlife Population"
(excel/cell-address-range 10 25 1 10)
:RIGHT
"Year"
:BOTTOM
"Population"
:LEFT
false
true
(excel/cell-address-range 2 7 1 1)
[ (excel/line-data-series
"Bears"
true
:CIRCLE
(excel/cell-address-range 2 7 2 2))
(excel/line-data-series
"Dolphins"
true
:CIRCLE
(excel/cell-address-range 2 7 3 3))
(excel/line-data-series
"Whales"
true
:CIRCLE
(excel/cell-address-range 2 7 4 4)) ])
(excel/write->file wbook "sample.xlsx")))
""")
:see-also '(
"excel/add-bar-chart", "excel/add-area-chart", "excel/add-pie-chart",
"excel/line-data-series", "excel/cell-address-range") }
add-line-chart [sheet chart-title
chart-addr-range
legend-position
category-axis-title
category-axis-position
value-axis-title
value-axis-position
three-dimensional?
vary-colors?
categories-addr-range
series]
{ :pre [(instance-of? :ExcelSheetFacade sheet)] }
(. sheet :lineChart chart-title
chart-addr-range
legend-position
category-axis-title
category-axis-position
value-axis-title
value-axis-position
three-dimensional?
vary-colors?
categories-addr-range
series))
(defn
^{ :arglists '(
"""
(add-bar-chart sheet chart-title
chart-addr-range
legend-position
category-axis-title
category-axis-position
value-axis-title
value-axis-position
three-dimensional?
direction-bar?
grouping
vary-colors?
categories-addr-range
series)
""")
:doc
"""
Adds a bar chart.
Arguments:
| chart-title | The chart title |
| chart-addr-range | The chart position in the Excel |
| legend-position | The legend position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| category-axis-title | The category axis title |
| category-axis-position | The category axis position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| value-axis-title | The value axis title |
| value-axis-position | The value axis position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| three-dimensional? | Render in 3D: `true` or `false` |
| direction-bar? | Render as horizintal bars or vertical columns: `true` or `false` |
| grouping | Bar grouping: `:STANDARD`, `:CLUSTERED`, `:STACKED`, `:PERCENT_STACKED` |
| vary-colors? | Vary the colors: `true` or `false` |
| categories-addr-range | The category names in the Excel |
| series | The value series data. 1 to N series |
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")
data [["Year" "Bears" "Dolphins" "Whales"]
["2017" 8 150 80 ]
["2018" 54 77 54 ]
["2019" 93 32 100 ]
["2020" 116 11 76 ]
["2021" 137 6 93 ]
["2022" 184 1 72 ]]]
(excel/write-data sheet data)
(excel/add-bar-chart sheet
"Bears Population"
(excel/cell-address-range 10 25 1 7)
:RIGHT
"Year"
:BOTTOM
"Population"
:LEFT
false
false
:STANDARD
false
(excel/cell-address-range 2 7 1 1)
[ (excel/bar-data-series
"Bears"
(excel/cell-address-range 2 7 2 2))
(excel/bar-data-series
"Dolphins"
(excel/cell-address-range 2 7 3 3))
(excel/bar-data-series
"Whales"
(excel/cell-address-range 2 7 4 4)) ])
(excel/write->file wbook "sample.xlsx")))
""")
:see-also '(
"excel/add-line-chart", "excel/add-area-chart", "excel/add-pie-chart",
"excel/bar-data-series", "excel/cell-address-range") }
add-bar-chart [sheet chart-title
chart-addr-range
legend-position
category-axis-title
category-axis-position
value-axis-title
value-axis-position
three-dimensional?
direction-bar?
grouping
vary-colors?
categories-addr-range
series]
{ :pre [(instance-of? :ExcelSheetFacade sheet)] }
(. sheet :barChart chart-title
chart-addr-range
legend-position
category-axis-title
category-axis-position
value-axis-title
value-axis-position
three-dimensional?
direction-bar?
grouping
vary-colors?
categories-addr-range
series))
(defn
^{ :arglists '(
"""
(add-area-chart sheet chart-title
chart-addr-range
legend-position
category-axis-title
category-axis-position
value-axis-title
value-axis-position
three-dimensional?
categories-addr-range
series)
""")
:doc
"""
Adds an area chart.
Arguments:
| chart-title | The chart title |
| chart-addr-range | The chart position in the Excel |
| legend-position | The legend position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| category-axis-title | The category axis title |
| category-axis-position | The category axis position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| value-axis-title | The value axis title |
| value-axis-position | The value axis position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| three-dimensional? | Render in 3D: `true` or `false` |
| categories-addr-range | The category names in the Excel |
| series | The value series data. 1 to N series |
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")
data [["Year" "Bears" "Dolphins" "Whales"]
["2017" 8 150 80 ]
["2018" 54 77 54 ]
["2019" 93 32 100 ]
["2020" 116 11 76 ]
["2021" 137 6 93 ]
["2022" 184 1 72 ]]]
(excel/write-data sheet data)
(excel/add-area-chart sheet
"Bears Population"
(excel/cell-address-range 10 25 1 7)
:RIGHT
"Year"
:BOTTOM
"Population"
:LEFT
false
(excel/cell-address-range 2 7 1 1)
[ (excel/area-data-series
"Bears"
(excel/cell-address-range 2 7 2 2)) ])
(excel/write->file wbook "sample.xlsx")))
""")
:see-also '(
"excel/add-line-chart", "excel/add-bar-chart", "excel/add-pie-chart",
"excel/area-data-series", "excel/cell-address-range") }
add-area-chart [sheet chart-title
chart-addr-range
legend-position
category-axis-title
category-axis-position
value-axis-title
value-axis-position
three-dimensional?
categories-addr-range
series]
{ :pre [(instance-of? :ExcelSheetFacade sheet)] }
(. sheet :areaChart chart-title
chart-addr-range
legend-position
category-axis-title
category-axis-position
value-axis-title
value-axis-position
three-dimensional?
categories-addr-range
series))
(defn
^{ :arglists '(
"""
(add-pie-chart sheet chart-title
chart-addr-range
legend-position
three-dimensional?
vary-colors?
categories-addr-range
series)
""")
:doc
"""
Adds a pie chart.
Arguments:
| chart-title | The chart title |
| chart-addr-range | The chart position in the Excel |
| legend-position | The legend position: `:TOP`, `:TOP_RIGHT`, `:RIGHT`, `:BOTTOM`, `:LEFT` |
| three-dimensional? | Render in 3D: `true` or `false` |
| vary-colors? | Vary the colors: `true` or `false` |
| categories-addr-range | The category names in the Excel |
| series | The value series data. 1 series required |
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")
data [["Year" "Bears" "Dolphins" "Whales"]
["2017" 8 150 80 ]
["2018" 54 77 54 ]
["2019" 93 32 100 ]
["2020" 116 11 76 ]
["2021" 137 6 93 ]
["2022" 184 1 72 ]]]
(excel/write-data sheet data)
(excel/add-pie-chart sheet
"Wildlife Population 2017"
(excel/cell-address-range 10 25 1 7)
:RIGHT
false
true
(excel/cell-address-range 1 1 2 4)
[ (excel/pie-data-series
(excel/cell-address-range 2 2 2 4)) ])
(excel/write->file wbook "sample.xlsx")))
""")
:see-also '(
"excel/add-line-chart", "excel/add-bar-chart", "excel/add-area-chart",
"excel/pie-data-series", "excel/cell-address-range") }
add-pie-chart [sheet chart-title
chart-addr-range
legend-position
three-dimensional?
vary-colors?
categories-addr-range
series]
{ :pre [(instance-of? :ExcelSheetFacade sheet)] }
(. sheet :pieChart chart-title
chart-addr-range
legend-position
three-dimensional?
vary-colors?
categories-addr-range
series))
(defn
^{ :arglists '("(line-data-series title smooth? marker data-address-range)")
:doc
"""
Build a line chart data series
Arguments:
| title | The series title |
| smooth? | Smooth rendering (splines): `true` or `false` |
| marker | The marker type: `:CIRCLE`, `:DASH`, `:DIAMOND`, `:DOT`, `:NONE`, `:PLUS`, `:SQUARE`, `:STAR`, `:TRIANGLE` |
| data-address-range | The series data in the Excel |
"""
:examples '(
"""
(excel/line-data-series "Countries"
false
:SQUARE
(excel/cell-address-range 2 2 1 5))
""" )
:see-also '("excel/cell-address-range") }
line-data-series [title smooth? marker data-address-range]
(. :LineDataSeries :new title smooth? marker data-address-range))
(defn
^{ :arglists '("(bar-data-series title data-address-range)")
:doc
"""
Build a bar chart data series
Arguments:
| title | The series title |
| data-address-range | The series data in the Excel |
"""
:examples '(
"""
(excel/bar-data-series "Countries" (excel/cell-address-range 2 2 1 5))
""" )
:see-also '("excel/cell-address-range") }
bar-data-series [title data-address-range]
(. :BarDataSeries :new title data-address-range))
(defn
^{ :arglists '("(area-data-series title data-address-range)")
:doc
"""
Build an area chart data series
Arguments:
| title | The series title |
| data-address-range | The series data in the Excel |
"""
:examples '(
"""
(excel/area-data-series "Countries" (excel/cell-address-range 2 2 1 5))
""" )
:see-also '("excel/cell-address-range") }
area-data-series [title data-address-range]
(. :AreaDataSeries :new title data-address-range))
(defn
^{ :arglists '("(pie-data-series data-address-range)")
:doc
"""
Build a pie chart data series
Arguments:
| data-address-range | The series data in the Excel |
"""
:examples '(
"""
(excel/pie-data-series (excel/cell-address-range 2 2 1 5))
""" )
:see-also '("excel/cell-address-range") }
pie-data-series [data-address-range]
(. :PieDataSeries :new "" data-address-range))
;; -----------------------------------------------------------------------------
;; Formulas
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '("(cell-formula sheet row col formula)")
:doc "Set a formula for a specific cell given by its row and col."
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:a 100 :b 200 }
{:a 101 :b 201 }
{:a 102 :b 202 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row true })]
(excel/add-column sheet "A" { :field :a })
(excel/add-column sheet "B" { :field :b })
(excel/add-column sheet "C" { :field :c })
(excel/write-items sheet data)
(excel/cell-formula sheet 1 3 "SUM(A1,B1)")
(excel/cell-formula sheet 2 3 "SUM(A2,B2)")
(excel/cell-formula sheet 3 3 "SUM(A3,B3)")
(excel/evaluate-formulas wbook)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""",
"""
(do
(load-module :excel)
(let [data [ {:a 100 :b 200 }
{:a 101 :b 201 }
{:a 102 :b 202 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row true })]
(excel/add-font wbook :bold { :bold true })
(excel/add-style wbook :bold { :font :bold })
(excel/add-column sheet "A" { :field :a })
(excel/add-column sheet "B" { :field :b })
(excel/add-column sheet "C" { :field :c })
(excel/write-items sheet data)
(excel/cell-formula sheet 1 3 "SUM(A1,B1)" :bold)
(excel/cell-formula sheet 2 3 "SUM(A2,B2)" :bold)
(excel/cell-formula sheet 3 3 "SUM(A3,B3)" :bold)
(excel/evaluate-formulas wbook)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/addr->string", "excel/sum-formula",
"excel/write-items", "excel/write-item",
"excel/write-value", "excel/auto-size-columns",
"excel/auto-size-column", "excel/row-height") }
cell-formula
([sheet row col formula]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)
(string? formula)] }
(. sheet :formula row col formula))
([sheet row col formula style]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)
(string? formula)
(keyword? style)] }
(. sheet :formula row col formula (name style))))
(defn
^{ :arglists '("(sum-formula sheet row-from row-to col-from col-to)")
:doc "Returns a sum formula for the given cell area"
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:a 100 :b 200 }
{:a 101 :b 201 }
{:a 102 :b 202 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row true })]
(excel/add-column sheet "A" { :field :a })
(excel/add-column sheet "B" { :field :b })
(excel/add-column sheet "C" { :field :c })
(excel/write-items sheet data)
(excel/cell-formula sheet 1 3 (excel/sum-formula sheet 1 1 1 2))
(excel/cell-formula sheet 2 3 (excel/sum-formula sheet 2 2 1 2))
(excel/cell-formula sheet 3 3 (excel/sum-formula sheet 3 3 1 2))
(excel/evaluate-formulas wbook)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '("excel/addr->string") }
sum-formula [sheet row-from row-to col-from col-to]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row-from) (long? row-to)
(long? col-from) (long? col-to)
(pos? row-from) (pos? row-to)
(pos? col-from) (pos? col-to)] }
(. sheet :sumFormula row-from row-to col-from col-to))
(defn
^{ :arglists '("(remove-formula sheet row col)")
:doc "Remove a cell formula"
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:a 100 :b 200 }
{:a 101 :b 201 }
{:a 102 :b 202 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row true })]
(excel/add-column sheet "A" { :field :a })
(excel/add-column sheet "B" { :field :b })
(excel/add-column sheet "C" { :field :c })
(excel/write-items sheet data)
(excel/cell-formula sheet 1 3 (excel/sum-formula sheet 1 1 1 2))
(excel/cell-formula sheet 2 3 (excel/sum-formula sheet 2 2 1 2))
(excel/cell-formula sheet 3 3 (excel/sum-formula sheet 3 3 1 2))
(excel/remove-formula sheet 1 3)
(excel/evaluate-formulas wbook)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/remove-comment"
"excel/remove-hyperlink") }
remove-formula [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? row)
(long? col) (long? col)] }
(. sheet :removeFormula row col))
;; -----------------------------------------------------------------------------
;; Comments
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '("(remove-comment sheet row col)")
:doc "Remove a cell comment"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 45)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/remove-comment sheet 1 1)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/remove-formula"
"excel/remove-hyperlink") }
remove-comment [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? row)
(long? col) (long? col)] }
(. sheet :removeComment row col))
;; -----------------------------------------------------------------------------
;; Hyperlinks
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '("(add-url-hyperlink sheet row col text url)")
:doc "Adds an URL hyperlink to a cell"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-font wbook :hyperlink { :underline true
:color :BLUE })
(excel/add-style wbook :hyperlink { :font :hyperlink })
(excel/write-values sheet 1 1 "John" "Doe")
(excel/write-values sheet 2 1 "Sue" "Ford")
(excel/add-url-hyperlink sheet 1 3 "https://john.doe.org/" "https://john.doe.org/")
(excel/add-url-hyperlink sheet 2 3 "https://sue.ford.org/" "https://sue.ford.org/")
(excel/cell-style sheet 1 3 :hyperlink)
(excel/cell-style sheet 2 3 :hyperlink)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/remove-hyperlink"
"excel/add-email-hyperlink") }
add-url-hyperlink [sheet row col text url]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? row)
(long? col) (long? col)
(string? text)
(string? url)] }
(. sheet :setUrlHyperlink row col text url))
(defn
^{ :arglists '("(add-email-hyperlink sheet row col text url)")
:doc "Adds an email hyperlink to a cell"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-font wbook :hyperlink { :underline true
:color :BLUE })
(excel/add-style wbook :hyperlink { :font :hyperlink })
(excel/write-values sheet 1 1 "John" "Doe")
(excel/write-values sheet 2 1 "Sue" "Ford")
(excel/add-email-hyperlink sheet 1 3 "[email protected]" "[email protected]")
(excel/add-email-hyperlink sheet 2 3 "[email protected]" "[email protected]")
(excel/cell-style sheet 1 3 :hyperlink)
(excel/cell-style sheet 2 3 :hyperlink)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/remove-hyperlink"
"excel/add-url-hyperlink") }
add-email-hyperlink [sheet row col text email]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? row)
(long? col) (long? col)
(string? text)
(string? email)] }
(. sheet :setEmailHyperlink row col text email))
(defn
^{ :arglists '("(remove-hyperlink sheet row col)")
:doc "Remove a cell comment"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 45)
(excel/write-values sheet 2 1 "Sue" "Ford" 26)
(excel/remove-hyperlink sheet 1 1)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/add-url-hyperlink"
"excel/add-email-hyperlink"
"excel/remove-comment"
"excel/remove-formula") }
remove-hyperlink [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? row)
(long? col) (long? col)] }
(. sheet :removeHyperlink row col))
;; -----------------------------------------------------------------------------
;; Utils
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '("(cell-address sheet row col)")
:doc "Returns the cell address in A1 style for a cell at row/col in a sheet"
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:a 100 :b 200 }
{:a 101 :b 201 }
{:a 102 :b 202 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row true })
addr #(excel/cell-address sheet %1 %2)
sum #(str "SUM\u0028" %1 "," %2 "\u0029")]
(excel/add-column sheet "A" { :field :a })
(excel/add-column sheet "B" { :field :b })
(excel/add-column sheet "C" { :field :c })
(excel/write-items sheet data)
(excel/cell-formula sheet 1 3 (sum (addr 1 1) (addr 1 2)))
(excel/cell-formula sheet 2 3 (sum (addr 2 1) (addr 2 2)))
(excel/cell-formula sheet 3 3 (sum (addr 3 1) (addr 3 2)))
(excel/evaluate-formulas wbook)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '("excel/cell-formula") }
cell-address [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(pos? col) (long? col) ] }
(. sheet :cellAddress_A1_style row col))
(defn
^{ :arglists '("(cell-address-range row-first row-last col-first col-last)")
:doc "Build a cell address range"
:examples '("(excel/cell-address-range 1 2 1 10)" )
:see-also '("excel/cell-address") }
cell-address-range [row-first row-last col-first col-last]
{ :pre [(long? row-first) (long? row-last)
(long? col-first) (long? col-last)
(pos? row-first) (pos? row-last)
(pos? col-first) (pos? col-last)] }
(. :CellRangeAddr :new row-first row-last col-first col-last))
(defn
^{ :arglists '("(auto-size-columns sheet)")
:doc "Auto size the width of all columns in the sheet."
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/auto-size-column", "excel/write-items", "excel/write-item",
"excel/write-value", "excel/cell-formula", "excel/row-height") }
auto-size-columns [sheet]
{ :pre [(instance-of? :ExcelSheetFacade sheet)] }
(. sheet :autoSizeColumns))
(defn
^{ :arglists '("(auto-size-column sheet col)")
:doc "Auto size the width of column col (1..n) in the sheet."
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-column sheet 1)
(excel/auto-size-column sheet 2)
(excel/auto-size-column sheet 3)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/auto-size-columns", "excel/write-items", "excel/write-item",
"excel/write-value", "excel/cell-formula", "excel/row-height") }
auto-size-column [sheet col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? col) (pos? col)] }
(. sheet :autoSizeColumn col))
(defn
^{ :arglists '("(hide-columns sheet & columns)")
:doc "Hide columns in the sheet."
:examples '(
"""
;; hide column by column index (1...n)
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/hide-columns sheet 2) ;; hide column #2
(excel/write->file wbook "sample.xlsx")))
""",
"""
;; hide column by column id
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "Last Name" { :field :last, :id "lastname"})
(excel/add-column sheet "First Name" { :field :first, :id "firstname"})
(excel/add-column sheet "Age" { :field :age, :id "age" })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/hide-columns sheet "firstname") ;; hide column "firstname"
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/auto-size-column", "excel/write-items", "excel/write-item",
"excel/write-value", "excel/cell-formula", "excel/row-height") }
hide-columns [sheet & columns]
{ :pre [(instance-of? :ExcelSheetFacade sheet)] }
(let [valid? (fn [c] (or (int? c) (long? c) (string? c)))
invalid-cols (filter #(not (valid? %)) columns)]
(when (pos? (count invalid-cols))
(throw (ex :VncException
(str "Invalid column ref type. Must be int, long, or string"))))
(doseq [c columns] (. sheet :hideColumn c))))
(defn
^{ :arglists '("(row-height sheet row height)")
:doc "Set the height of a row (1..n) in the sheet."
:examples '(
"""
(do
(load-module :excel)
(let [os (io/file-out-stream "sample.xlsx")
data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/row-height sheet 2 50)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/col-width",
"excel/auto-size-columns", "excel/write-items", "excel/write-item",
"excel/write-value", "excel/cell-formula", "excel/auto-size-column") }
row-height [sheet row height]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)] }
(. sheet :rowHeightInPoints row height))
(defn
^{ :arglists '("(col-width sheet col width)")
:doc "Set the width of a column (1..n) in the sheet."
:examples '(
"""
(do
(load-module :excel)
(let [os (io/file-out-stream "sample.xlsx")
data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/col-width sheet 1 80)
(excel/col-width sheet 2 80)
(excel/col-width sheet 3 60)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/row-height",
"excel/auto-size-columns", "excel/write-items", "excel/write-item",
"excel/write-value", "excel/cell-formula", "excel/auto-size-column") }
col-width [sheet col width]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? col) (pos? col)] }
(. sheet :colWidthInPoints col width))
(def column-width col-width) ;; alias for backward compatibility
;; -----------------------------------------------------------------------------
;; Sheet
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '(
"(add-sheet wbook title)"
"(add-sheet wbook title options)" )
:doc
"""
Adds a sheet with optional attributes to an Excel.
Options:
| :no-header-row b | without header row, e.g. true, false |
| :default-column-width n | default column width in points, e.g. 100 |
| :default-header-style s | default header style, e.g. :header |
| :default-body-style s | default body style, e.g. :body |
| :default-footer-style s | default footer style, e.g. :footer |
| :merged-region r | merged region [row-from row-to col-from col-to], \
e.g. [1 1 4 10] |
| :display-zeros b | display zeros, e.g. true, false. Defines if \
a cell should show 0 (zero) when containing \
zero value. When false, cells with zero value \
appear blank instead of showing the number zero.|
"""
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""",
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)]
(excel/add-font wbook :bold { :bold true })
(excel/add-font wbook :italic { :italic true })
(excel/add-style wbook :header { :font :bold })
(excel/add-style wbook :body { :font :italic })
(excel/add-style wbook :footer { :font :bold })
(let [sheet (excel/add-sheet wbook "Sheet 1"
{ :no-header-row false
:default-column-width 100
:default-header-style :header
:default-body-style :body
:default-footer-style :footer
:display-zeros true})]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-column sheet 1)
(excel/auto-size-column sheet 2)
(excel/auto-size-column sheet 3)
(excel/write->file wbook "sample.xlsx"))))
""" )
:see-also '(
"excel/add-column",
"excel/protect-sheet"
"excel/add-merge-region") }
add-sheet
([wbook title]
(add-sheet wbook title {}))
([wbook title options]
{ :pre [(instance-of? :ExcelFacade wbook)
(string? title)] }
(let [sheet (. wbook :withSheet (name title) (class :DataRecord))]
(when-let [x (:no-header-row options)]
(. sheet :noHeader))
(when-let [x (:default-column-width options)]
(. sheet :setDefaultColumnWidthInPoints x))
(when-let [x (:default-header-style options)]
(. sheet :defaultHeaderStyle (name x)))
(when-let [x (:default-body-style options)]
(. sheet :defaultBodyStyle (name x)))
(when-let [x (:default-footer-style options)]
(. sheet :defaultFooterStyle (name x)))
(when-let [x (:merged-region options)]
(. sheet :addMergedRegion (first x) (second x) (third x) (fourth x)))
(when-let [x (:display-zeros options)]
(. sheet :displayZeros x))
(. sheet :end)
sheet)))
(defn
^{ :arglists '("(protect-sheet sheet password)")
:doc
"""
Protect the sheet.
This will ensure that locked cells remain locked and unlocked cells
are editable.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/auto-size-columns sheet)
(excel/protect-sheet sheet "password")
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/add-sheet" ) }
protect-sheet [sheet password]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(string? password)] }
(. sheet :protectSheet password))
(defn
^{ :arglists '(
"(add-merge-region sheet row-from row-to col-from col-to)")
:doc
"Add a merge region to the sheet."
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Population")]
(excel/col-width sheet 2 70)
(excel/col-width sheet 3 70)
(excel/add-merge-region sheet 2 2 2 3)
(excel/write-value sheet 2 2 "Contry Population")
(excel/write-value sheet 3 2 "Country")
(excel/write-value sheet 3 3 "Population")
(excel/write-value sheet 4 2 "Germany")
(excel/write-value sheet 4 3 83_783_942)
(excel/write-value sheet 5 2 "Italy")
(excel/write-value sheet 5 3 60_461_826)
(excel/write-value sheet 6 2 "Austria")
(excel/write-value sheet 6 3 9_006_398)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '("excel/add-sheet") }
add-merge-region [sheet row-from row-to col-from col-to]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row-from) (long? row-to)
(long? col-from) (long? col-to)
(pos? row-from) (pos? row-to)
(pos? col-from) (pos? col-to)] }
(. sheet :addMergedRegion row-from row-to col-from col-to))
(defn
^{ :arglists '(
"(freeze-pane sheet rows cols)")
:doc
"""
Creates a split (freezepane). Any existing freezepane or split pane
is overwritten.
If both rows and cols are 0 then the existing freeze pane is removed.
rows: the number of rows to freeze (starting from the first row)
cols: the number of columns to freeze (starting from the first column)
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row false })]
(excel/write-data sheet [(map #(str "Col " %) (range 1 11))])
(excel/write-data sheet (partition 10 (range 100 500)) 2 1)
(excel/freeze-pane sheet 1 0)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '("excel/add-merge-region") }
freeze-pane [sheet rows cols ]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? rows) (long? cols)
(>= rows 0) (>= cols 0)] }
(. sheet :createFreezePane cols rows))
;; -----------------------------------------------------------------------------
;; Font
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '(
"(add-font wbook font-id)"
"(add-font wbook font-id options)")
:doc
"""
Add font with optional attributes to an Excel.
Options:
| :name s | font name, e.g. 'Arial' |
| :height n | height in points, e.g. 12 |
| :bold b | bold, e.g. true, false |
| :italic b | italic, e.g. true, false |
| :underline b | underline, e.g. true, false |
| :color c | color, either an Excel indexed color or a HTML \
color, e.g. :BLUE, "#00FF00" \
note: only XLSX supports 24 bit colors |
"""
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/create :xlsx)]
(excel/add-font wbook :header { :height 12
:bold true
:italic false
:underline false
:color :BLUE })
(excel/add-style wbook :header { :font :header })
(let [sheet (excel/add-sheet wbook "Sheet 1"
{ :no-header-row false
:default-header-style :header })]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))))
""" )
:see-also '("excel/add-style", "excel/add-sheet") }
add-font
([wbook font-id]
(add-font wbook font-key {}))
([wbook font-id options]
{ :pre [(instance-of? :ExcelFacade wbook)
(keyword? font-id)] }
(let [font-builder (. wbook :withFont (name font-id))]
(when-let [x (:height options)] (. font-builder :heightInPoints x))
(when-let [x (:bold options)] (. font-builder :bold))
(when-let [x (:italic options)] (. font-builder :italic))
(when-let [x (:underline options)] (. font-builder :underline))
(when-let [x (:color options)] (cond
(string? x) (. font-builder :colorHtml x)
(keyword? x) (. font-builder :color (x colors))
(long? x) (. font-builder :color x))
:else (ex :VncException "Invalid font color"))
(. font-builder :end)
font-builder)))
;; -----------------------------------------------------------------------------
;; Style
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '(
"(add-style wbook style-id)"
"(add-style wbook style-id options)")
:doc
"""
Add a style with optional attributes to an Excel.
Options:
| :format s | cell format, e.g. "#0"¶\
Default formats:¶\
- long: "#,##0"¶\
- integer: "#,##0"¶\
- float: "#,##0.00"¶\
- double: "#,##0.00"¶\
- date: "d.m.yyyy"¶\
- datetime: "d.m.yyyy hh:mm:ss" |
| :font r | font name, e.g. :header |
| :bg-color c | background color, either an Excel indexed color or \
a HTML color, e.g. :PLUM, "#00FF00"¶\
Note: only XLSX supports 24 bit colors |
| :wrap-text b | wrap text, e.g. true, false |
| :h-align e | horizontal alignment {:left, :center, :right} |
| :v-align e | vertical alignment {:top, :middle, :bottom} |
| :rotation r | rotation angle [degree], e.g. 45 |
| :border-top s | border top style, e.g. :thin |
| :border-right s | border right style, e.g. :none |
| :border-bottom s | border bottom style, e.g. :thin |
| :border-left s | border left style, e.g. :none |
Available border styles:
| [![width: 15%]] | [![width: 15%]] | [![width: 30%]] | [![width: 50%]] |
| :none | :dotted | :medium-dashed | :medium-dash-dot-dot |
| :thin | :thick | :dash-dot | :slanted-dash-dot |
| :medium | :double | :medium-dash-dot | |
| :dashed | :hair | :dash-dot-dot | |
"""
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :weight 70.5 }
{:first "Sue" :last "Ford" :weight 54.2 } ]
wbook (excel/create :xlsx)]
(excel/add-font wbook :header { :bold true })
(excel/add-style wbook :header { :font :header
:bg-color :GREY_25_PERCENT
:h-align :center
:rotation 0
:border-top :thin
:border-bottom :thin })
(excel/add-style wbook :weight { :format "#,##0.0"
:h-align :right })
(let [sheet (excel/add-sheet wbook "Sheet 1"
{ :no-header-row false
:default-header-style :header })]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Weight" { :field :weight
:body-style :weight })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))))
""" )
:see-also '("excel/add-font", "excel/add-sheet") }
add-style
([wbook style-id]
(add-style wbook style-id {}))
([wbook style-id options]
{ :pre [(instance-of? :ExcelFacade wbook)
(keyword? style-id)] }
(let [cell-style-builder (. wbook :withCellStyle (name style-id))]
(when-let [x (:format options)]
(. cell-style-builder :format x))
(when-let [x (:font options)]
(. cell-style-builder :font x))
(when-let [x (:bg-color options)]
(cond
(string? x) (. cell-style-builder :bgColorHtml x)
(keyword? x) (. cell-style-builder :bgColor (x colors))
(long? x) (. cell-style-builder :bgColor x))
:else (ex :VncException "Invalid style bg color"))
(when-let [x (:wrap-text options)]
(. cell-style-builder :wrapText))
(when-let [x (:h-align options)]
(case x
:left (. cell-style-builder :hAlignLeft)
:center (. cell-style-builder :hAlignCenter)
:right (. cell-style-builder :hAlignRight)
(throw (ex :VncException
(str "Invalid horizontal alignment "
align
". Use one of :left, :center, or :right")))))
(when-let [x (:v-align options)]
(case x
:top (. cell-style-builder :vAlignTop)
:middle (. cell-style-builder :vAlignMiddle)
:bottom (. cell-style-builder :vAlignBottom)
(throw (ex :VncException
(str "Invalid vertical alignment "
x
". Use one of :top, :middle, or :bottom")))))
(when-let [x (:rotation options)]
(. cell-style-builder :rotation x))
(when-let [x (:border-top options)]
(. cell-style-builder :borderTopStyle (map-border-style x)))
(when-let [x (:border-right options)]
(. cell-style-builder :borderRightStyle (map-border-style x)))
(when-let [x (:border-bottom options)]
(. cell-style-builder :borderBottomStyle (map-border-style x)))
(when-let [x (:border-left options)]
(. cell-style-builder :borderLeftStyle (map-border-style x)))
(. cell-style-builder :end)
cell-style-builder)))
(defn- map-border-style [style]
(case style
nil nil
:none :NONE
:thin :THIN
:medium :MEDIUM
:dashed :DASHED
:dotted :DOTTED
:thick :THICK
:double :DOUBLE
:hair :HAIR
:medium-dashed :MEDIUM_DASHED
:dash-dot :DASH_DOT
:medium-dash-dot :MEDIUM_DASH_DOT
:dash-dot-dot :DASH_DOT_DOT
:medium-dash-dot-dot :MEDIUM_DASH_DOT_DOT
:slanted-dash-dot :SLANTED_DASH_DOT
:default :NONE))
(defn
^{ :arglists '(
"(cell-style sheet row col style-id)",
"(cell-style sheet row-from row-to col-from col-to style-id)")
:doc
"Apply a defined cell style to a cell"
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row false })]
(excel/add-font wbook :bold { :bold true
:color "#54039c" })
(excel/add-style wbook :style-1 { :font :bold
:h-align :left
:rotation 0 })
(excel/add-style wbook :style-2 { :bg-color "#cae1fa"
:h-align :center
:rotation 0
:border-top :thin
:border-left :thin
:border-bottom :thin
:border-right :thin})
(excel/add-style wbook :style-3 { :h-align :right
:format "#,##0.00" })
(excel/write-value sheet 2 1 100)
(excel/write-value sheet 2 2 200)
(excel/write-value sheet 2 3 300)
(excel/cell-style sheet 2 1 :style-1)
(excel/cell-style sheet 2 2 :style-2)
(excel/cell-style sheet 2 3 :style-3)
(excel/write->file wbook "sample.xlsx")))
""",
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row false })]
(excel/add-style wbook :style { :bg-color "#cae1fa"
:h-align :center
:format "#,##0.00" })
(excel/write-value sheet 2 2 100)
(excel/write-value sheet 2 3 200)
(excel/write-value sheet 2 4 300)
(excel/write-value sheet 3 2 101)
(excel/write-value sheet 3 3 201)
(excel/write-value sheet 3 4 301)
(excel/cell-style sheet 2 3 2 4 :style)
(excel/write->file wbook "sample.xlsx")))
""" )
:see-also '(
"excel/add-style", "excel/add-font", "excel/write-value") }
cell-style
([sheet row col style-id]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)] }
(. sheet :style row col style-id))
([sheet row-from row-to col-from col-to style-id]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row-from) (pos? row-from)
(long? row-to) (pos? row-to)
(long? col-from) (pos? col-from)
(long? col-to) (pos? col-to)] }
(list-comp [r (range row-from (inc row-to))
c (range col-from (inc col-to))]
(. sheet :style r c style-id))))
(defn
^{ :arglists '(
"(bg-color sheet row col color)"
"(bg-color sheet row col-start col-end color)"
"(bg-color sheet row-start row-end col-start col-end color & colors)")
:doc
"""
Sets a background color for a single cell, a range of columns within
a row, or region of cells.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
;; single cells
(excel/bg-color sheet 1 1 "#27ae60")
(excel/bg-color sheet 1 2 "#52be80")
(excel/bg-color sheet 1 3 "#7dcea0")
;; range of cells in row
(excel/bg-color sheet 1 4 6 "#3498db")
;; area of cells
(excel/bg-color sheet 1 6 7 9 "#aed6f1")
(excel/bg-color sheet 1 6 10 12 "#bb8fce" "#d2b4de")
(excel/bg-color sheet 1 6 13 15 "#f1c40f" "#f4d03f" "#f7dc6f")
(excel/write->file wbook "sample.xlsx")))
""",
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 101 102]
[200 201 203]
[300 301 303]
[400 401 403]
[500 501 503]
[600 601 603]])
(excel/bg-color sheet 1 6 1 3 "#a9cafc" "#d9e7fc")
(excel/write->file wbook "sample.xlsx")))
""")
:see-also '(
"excel/add-style", "excel/add-font", "excel/cell-style" ) }
bg-color
([sheet row col color]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :bgColor row col color))
([sheet row col-start col-end color]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col-start) (long? col-end)
(pos? row) (pos? col-start) (pos? col-end)
(<= col-start col-end)] }
(if (= col-start col-end)
(. sheet :bgColor row col-start color)
(run! #(. sheet :bgColor row % color) (range col-start (inc col-end)))))
([sheet row-start row-end col-start col-end color & colors]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row-start) (long? row-end)
(long? col-start) (long? col-end)
(pos? row-start) (pos? row-end)
(pos? col-start) (pos? col-end)
(<= row-start row-end) (<= col-start col-end)] }
(let [colors (cons color colors)]
(list-comp [row (range 0 (inc (- row-end row-start)))]
(run! (fn [col] (. sheet :bgColor
(+ row row-start)
col
(nth colors (mod row (count colors)))))
(range col-start (inc col-end)))))))
(defn
^{ :arglists '(
"(cell-style-info sheet row col)")
:doc
"""
Returns a map with the cell's styles.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/cell-style-info sheet 1 1)))
""")
:see-also '(
"excel/add-style", "excel/add-font", "excel/cell-style" ) }
cell-style-info [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(let [m1 (into {} (. sheet :getCellStyleInfo row col))
m2 (select-keys m1 [ "cell.type"
"h-align", "v-align",
"border.top", "border.bottom",
"border.left", "border.right",
"fill.pattern" ])
m3 (map-vals keyword m2)
m4 (merge m1 m3)
m5 (map-keys keyword m4)]
m5))
;; -----------------------------------------------------------------------------
;; Cell addresses
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '("(addr->string row col)")
:doc
"""
Returns an Excel A1-style cell address string representation for a
row and column address
"""
:examples '(
"(excel/addr->string 1 3)",
"(excel/addr->string 30 56)",
"""
(do
(load-module :excel)
(let [data [ {:a 100 :b 200 }
{:a 101 :b 201 }
{:a 102 :b 202 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1" { :no-header-row true })
addr #(excel/addr->string %1 %2)
sum #(str "SUM\u0028" %1 "," %2 ")")]
(excel/add-column sheet "A" { :field :a })
(excel/add-column sheet "B" { :field :b })
(excel/add-column sheet "C" { :field :c })
(excel/write-items sheet data)
(excel/cell-formula sheet 1 3 (sum (addr 1 1) (addr 1 2)))
(excel/cell-formula sheet 2 3 (sum (addr 2 1) (addr 2 2)))
(excel/cell-formula sheet 3 3 (sum (addr 3 1) (addr 3 2)))
(excel/evaluate-formulas wbook)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx")))
""")
:see-also '(
"excel/col->string", "excel/cell-formula") }
addr->string [row col]
{ :pre [(long? row) (pos? row) (long? col) (pos? col)] }
(str (col->string col) row))
(defn
^{ :arglists '("(col->string col)")
:doc
"""
Returns an Excel A-style column number string representation for a
column number
"""
:examples '(
"(excel/col->string 1)",
"(excel/col->string 56)")
:see-also '(
"excel/addr->string") }
col->string [col]
{ :pre [(long? col) (pos? col)] }
(loop [acc "" c col]
(if (pos? c)
(let [col0 (dec c)
ch (char (+ (mod col0 col-radix) (long #\A)))
remain (/ col0 col-radix)]
(recur (str ch acc) remain))
acc)))
;; -----------------------------------------------------------------------------
;; Column
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '(
"(add-column sheet title)"
"(add-column sheet title options)")
:doc
"""
Defines a column with optional attributes on the sheet.
*Note:* The column cell value is just read from the passed tabular
dataset. If there is any mapping or conversion needed it has to be
applied to the dataset before writing it to the sheet!
Options:
| :id id | a column id |
| :field f | a field, e.g. :first-name |
| :width n | width in points, e.g. 100 |
| :skip s | skip column, e.g. true, false |
| :header-style r | style name for header row, e.g. :header |
| :body-style r | style name for body rows, e.g. :body |
| :footer-style r | style name for footer row, e.g. :footer |
| :footer-value v | explicit text or numeric value for the column's \
footer cell, e.g. "done", 10000.00M, nil |
| :footer-aggregate e | aggregation mode for the column's footer cell \
value, e.g. {:min, :max, :avg, :sum, :none} |
"""
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :weight 70.5 }
{:first "Sue" :last "Ford" :weight 54.2 } ]
wbook (excel/create :xlsx)]
(excel/add-font wbook :header { :bold true })
(excel/add-style wbook :header { :font :header
:bg-color :GREY_25_PERCENT
:h-align :center })
(excel/add-style wbook :weight { :format, "#,##0.0"
:h-align :right })
(let [sheet (excel/add-sheet wbook "Sheet 1"
{ :no-header-row false
:default-header-style :header })]
(excel/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Weight" { :field :weight
:body-style :weight })
(excel/write-items sheet data)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))))
""" )
:see-also '("excel/add-sheet", "excel/add-font", "excel/add-style") }
add-column
([sheet title]
(add-columnn sheet title {}))
([sheet title options]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(string? title)] }
(let [col-builder (. sheet :withColumn title)]
(when-let [x (:field options)]
(cond
(keyword? x) (. col-builder :colMapper x)
(string? x) (. col-builder :colMapper x)
:else (throw (ex :VncException
(str "A column field must be either a keyword "
"or a string acting as a key to get the "
"column's value from the row data map")))))
(when-let [x (:id options)] (. col-builder :id x))
(when-let [x (:data-field options)] (. col-builder :colMapper x)) ;; compatibility
(when-let [x (:width options)] (. col-builder :widthInPoints x))
(when-let [x (:skip options)] (. col-builder :skip x))
(when-let [x (:header-style options)] (. col-builder :headerStyle (name x)))
(when-let [x (:body-style options)] (. col-builder :bodyStyle (name x)))
(when-let [x (:footer-style options)] (. col-builder :footerStyle (name x)))
(when-let [x (:footer-value options)]
(cond
(nil? x) nil
(string? x) (. col-builder :footerTextValue x)
(number? x) (. col-builder :footerNumberValue x)
:else (throw (ex :VncException
(str "An explicit column footer value must be "
"nil, a string, or a number")))))
(when-let [x (:footer-aggregate options)]
(case x
:min (. col-builder :footerMin)
:max (. col-builder :footerMax)
:avg (. col-builder :footerAverage)
:sum (. col-builder :footerSum)
(throw (ex :VncException
(str "Invalid aggregate mode "
x
". Use one of :min, :max, :avg, or :sum")))))
(. col-builder :end)
col-builder)))
;; #############################################################################
;;
;; Excel Reader
;;
;; #############################################################################
(defn
^{ :arglists '("(sheet-count wbook)")
:doc "Returns the number of sheets in the Excel."
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 101 102] [200 201 202]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))]
(excel/sheet-count wbook)))
""" )
:see-also '("excel/sheet", "excel/evaluate-formulas") }
sheet-count [wbook]
{ :pre [(or (instance-of? :ExcelFacade wbook))] }
(long (. wbook :getNumberOfSheets)))
(defn
^{ :arglists '("(sheet wbook ref)")
:doc
"""
Returns a sheet from the Excel referenced by its name or sheet index.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet1 (excel/add-sheet wbook "Data1")
sheet2 (excel/add-sheet wbook "Data2")]
(excel/write-data sheet1 [[100 101 102] [200 201 202]])
(excel/write-data sheet2 [[100 101 102] [200 201 202]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet1 (excel/sheet wbook "Data1")
sheet2 (excel/sheet wbook 2)]
))
""" )
:see-also '(
"excel/sheet-count", "excel/evaluate-formulas",
"excel/sheet-name", "excel/sheet-row-range",
"excel/sheet-col-range",
"excel/cell-empty?", "excel/cell-type",
"excel/read-string-val", "excel/read-boolean-val",
"excel/read-long-val", "excel/read-double-val",
"excel/read-date-val", "excel/read-datetime-val") }
sheet [wbook s]
{ :pre [(or (instance-of? :ExcelFacade wbook))] }
(cond
(string? s) (. wbook :getSheet s)
(long? s) (. wbook :getSheetAt s)
:else (throw (ex :VncException
(str "Invalid Excel sheet reference type " (type s))))))
(defn
^{ :arglists '("(sheet-index sheet)")
:doc
"""
Returns the index of a sheet.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 101 102] [200 201 202]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
(excel/sheet-index sheet)))
""" ) }
sheet-index [sheet]
{ :pre [(instance-of? :ExcelSheetFacade sheet)] }
(long (. sheet :getIndex)))
(defn
^{ :arglists '("(sheet-name sheet)")
:doc
"""
Returns the name of a sheet.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 101 102] [200 201 202]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
(excel/sheet-name sheet)))
""" ) }
sheet-name [sheet]
{ :pre [(instance-of? :ExcelSheetFacade sheet)] }
(. sheet :getName))
(defn
^{ :arglists '("(sheet-row-range sheet)")
:doc
"""
Returns the first and the last row with data in a sheet as vector.
Returns -1 values if no row exists.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Mary" "Smith" 28)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))
(let [wbook (excel/open "sample.xlsx")
sheet (excel/sheet wbook "Sheet 1")]
(excel/sheet-row-range sheet)))
""" )
:see-also '("excel/sheet-col-range")}
sheet-row-range [sheet]
{ :pre [(instance-of? :ExcelSheetFacade sheet)] }
[(long (. sheet :getFirstRowNum)) (long (. sheet :getLastRowNum))])
(defn
^{ :arglists '("(sheet-col-range sheet)")
:doc
"""
Returns the first and the last col with data in a sheet row as vector.
Returns -1 values if the row does not exist or the row does not have
any columns.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Mary" "Smith" 28)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))
(let [wbook (excel/open "sample.xlsx")
sheet (excel/sheet wbook "Sheet 1")]
(excel/sheet-col-range sheet 1)))
""",
"""
(do
(load-module :excel)
(defn print-cell-meta [sheet row col]
(println (str (excel/addr->string row col) "> "
"type: " (name (excel/cell-type sheet row col))
", format: " (excel/cell-data-format-string sheet row col)
", empty: " (excel/cell-empty? sheet row col)
", locked: " (excel/cell-locked? sheet row col)
", hidden: " (excel/cell-hidden? sheet row col))))
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/write-values sheet 2 1 "Mary" "Smith" 28)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))
(let [wbook (excel/open "sample.xlsx")
sheet (excel/sheet wbook "Sheet 1")
row 1
col-range (excel/sheet-col-range sheet 1)
col-list (range (first col-range) (inc (second col-range)))]
(docoll #(print-cell-meta sheet (first %) (second %))
(map vector (repeat row) col-list))))
""" )
:see-also '("excel/sheet-row-range")}
sheet-col-range [sheet row]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)] }
[(long (. sheet :getFirstCellNum row)) (long (. sheet :getLastCellNum row))])
(defn
^{ :arglists '("(cell-empty? sheet row col)")
:doc
"""
Returns true if the sheet cell given by row/col is empty.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 101 102] [200 201 202]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
[(excel/cell-empty? sheet 1 1)
(excel/cell-empty? sheet 2 1)
(excel/cell-empty? sheet 3 1)]))
""" )
:see-also '(
"excel/cell-hidden?", "excel/cell-locked?",
"excel/cell-type", "excel/read-string-val",
"excel/read-boolean-val", "excel/read-long-val",
"excel/read-double-val", "excel/read-date-val",
"excel/read-datetime-val") }
cell-empty? [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :isCellEmpty row col))
(defn
^{ :arglists '("(cell-type sheet row col)")
:doc
"""
Returns the sheet cell type as one of { :notfound, :blank, :string,
:boolean, :numeric, :formula, :error, or :unknown }
Note:
1. Excel returns cells containing long, double, date or datetime
values as `:numeric`. The reader decides how to read a numeric
cell using either of `excel/read-long-val`, `excel/read-double-val`,
or `excel/read-date-val`.
2. To evaluate formulas to values call `excel/evaluate-formulas` on the
workbook the right after opening the excel document.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 "101" 102.0]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
[(excel/cell-type sheet 1 1)
(excel/cell-type sheet 1 2)
(excel/cell-type sheet 1 3)
(excel/cell-type sheet 1 4)]))
""" )
:see-also '(
"excel/cell-formula-result-type", "excel/cell-empty?",
"excel/cell-hidden?", "excel/cell-locked?",
"excel/read-string-val", "excel/read-boolean-val",
"excel/read-long-val", "excel/read-double-val",
"excel/read-date-val", "excel/read-datetime-val") }
cell-type [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(case (. sheet :getCellType row col)
"notfound" :notfound
"blank" :blank
"string" :string
"boolean" :boolean
"numeric" :numeric
"formula" :formula
"error" :error
"unknown" :unknown
:unknown))
(defn
^{ :arglists '("(cell-data-format-string sheet row col)")
:doc
"""
Returns the sheet cell data format string
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))
(let [wbook (excel/open "sample.xlsx")
sheet (excel/sheet wbook "Sheet 1")]
(excel/cell-data-format-string sheet 1 3)))
""" )
:see-also '(
"excel/cell-formula-result-type", "excel/cell-empty?",
"excel/cell-hidden?", "excel/cell-locked?",
"excel/read-string-val", "excel/read-boolean-val",
"excel/read-long-val", "excel/read-double-val",
"excel/read-date-val", "excel/read-datetime-val") }
cell-data-format-string [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :getDataFormatString row col))
(defn
^{ :arglists '("(cell-formula-result-type sheet row col)")
:doc
"""
Returns the sheet cell type as one of { :notfound, :blank, :string,
:boolean, :numeric, :formula, :error, or :unknown } after formula
cell evaluation. For non formula cells this function is the same as
the `cell-type` function.
"""
:examples '(
"""
(do
(load-module :excel)
(defn create-excel []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[10 20.123 {:formula "SUM(A1,B1)"}]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (create-excel))
sheet (excel/sheet wbook "Data")]
(excel/evaluate-formulas wbook)
(printf "Cell (1,3): %s%n" (excel/cell-type sheet 1 3))
(printf "Cell (1,3): %s (formula result type)%n"
(excel/cell-formula-result-type sheet 1 3))))
""" )
:see-also '(
"excel/cell-type", "excel/cell-empty?",
"excel/read-string-val", "excel/read-boolean-val",
"excel/read-long-val", "excel/read-double-val",
"excel/read-date-val", "excel/read-datetime-val") }
cell-formula-result-type [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(case (. sheet :getCellFormulaResultType row col)
"notfound" :notfound
"blank" :blank
"string" :string
"boolean" :boolean
"numeric" :numeric
"formula" :formula
"error" :error
"unknown" :unknown
:unknown))
(defn
^{ :arglists '("(read-val sheet row col)")
:doc
"""
Returns the sheet cell value.
Returns a *nil*, *string*, *boolean*, or *double* value depending on
the cell's excel type *:blank*, *:string*, *:boolean*, or *:numeri*.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 "101" 102.0]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
(excel/read-val sheet 1 2)))
""" )
:see-also '(
"excel/cell-empty?", "excel/cell-type", "excel/read-string-val",
"excel/read-boolean-val", "excel/read-long-val",
"excel/read-double-val", "excel/read-date-val",
"excel/read-datetime-val")}
read-val [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :getValue row col))
(defn
^{ :arglists '("(read-string-val sheet row col)")
:doc
"""
Returns the sheet cell value as string.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 "101" 102.0]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
(excel/read-string-val sheet 1 2)))
""" )
:see-also '(
"excel/cell-empty?", "excel/cell-type",
"excel/read-boolean-val", "excel/read-long-val",
"excel/read-double-val", "excel/read-date-val",
"excel/read-datetime-val", "excel/read-val")}
read-string-val [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :getString row col))
(defn
^{ :arglists '("(read-boolean-val sheet row col)")
:doc
"""
Returns the sheet cell value as boolean.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 true 102]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
(excel/read-boolean-val sheet 1 2)))
""" )
:see-also '(
"excel/cell-empty?", "excel/cell-type",
"excel/read-string-val", "excel/read-long-val",
"excel/read-double-val", "excel/read-date-val",
"excel/read-datetime-val", "excel/read-val")}
read-boolean-val [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :getBoolean row col))
(defn
^{ :arglists '("(read-long-val sheet row col)")
:doc
"""
Returns the sheet cell value as long.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 101 102]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
(excel/read-long-val sheet 1 2)))
""",
"""
(do
(load-module :excel)
(defn test-xls []
(let [data [ {:a 100 :b 200 } ]
wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data"
{ :no-header-row true })]
(excel/add-column sheet "A" { :field :a })
(excel/add-column sheet "B" { :field :b })
(excel/write-items sheet data)
(excel/cell-formula sheet 1 3 "SUM(A1,B1)")
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
(excel/read-long-val sheet 1 3)))
""" )
:see-also '(
"excel/cell-empty?", "excel/cell-type",
"excel/read-string-val", "excel/read-boolean-val",
"excel/read-double-val", "excel/read-date-val",
"excel/read-datetime-val", "excel/read-val")}
read-long-val [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(long (. sheet :getInteger row col)))
(defn
^{ :arglists '("(read-double-val sheet row col)")
:doc
"""
Returns the sheet cell value as double.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 101.23 102]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
(excel/read-double-val sheet 1 2)))
""" )
:see-also '(
"excel/cell-empty?", "excel/cell-type",
"excel/read-string-val", "excel/read-boolean-val",
"excel/read-long-val", "excel/read-date-val", "excel/read-val") }
read-double-val [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :getFloat row col))
(defn
^{ :arglists '("(read-date-val sheet row col)")
:doc
"""
Returns the sheet cell value as a date (`:java.time.LocalDate`).
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")
dt1 (time/local-date 2021 1 1)
dt2 (time/local-date 2022 4 15)]
(excel/write-data sheet [[100 dt1 dt2 102]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
[(excel/read-date-val sheet 1 2)
(excel/read-date-val sheet 1 3)]))
""" )
:see-also '(
"excel/cell-empty?", "excel/cell-type",
"excel/read-string-val", "excel/read-boolean-val",
"excel/read-long-val", "excel/read-double-val",
"excel/read-datetime-val", "excel/read-val") }
read-date-val [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(time/local-date (. sheet :getDate row col)))
(defn
^{ :arglists '("(read-datetime-val sheet row col)")
:doc
"""
Returns the sheet cell value as a datetime (`:java.time.LocalDateTime`).
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")
ts1 (time/local-date-time 2021 1 1 15 30 45)
ts2 (time/local-date-time 2021 1 31 08 00 00)]
(excel/write-data sheet [[100 ts1 ts2 102]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
[(excel/read-datetime-val sheet 1 2)
(excel/read-datetime-val sheet 1 3)]))
""" )
:see-also '(
"excel/cell-empty?", "excel/cell-type",
"excel/read-string-val", "excel/read-boolean-val",
"excel/read-long-val", "excel/read-double-val",
"excel/read-date-val", "excel/read-val") }
read-datetime-val [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :getDate row col))
(defn
^{ :arglists '("(read-error-code sheet row col)")
:doc
"""
Reads the error code from a cell. Returns a string indicating the error
or nil if the cell is nozt in error state.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Data")]
(excel/write-data sheet [[100 200 {:formula "1 / 0"}]])
(excel/write->bytebuf wbook)))
(let [wbook (excel/open (test-xls))
sheet (excel/sheet wbook "Data")]
(excel/evaluate-formulas wbook)
(excel/read-error-code sheet 1 3))) ;; #DIV/0!
""" )
:see-also '(
"excel/cell-empty?", "excel/cell-type", "excel/read-string-val",
"excel/read-boolean-val", "excel/read-long-val",
"excel/read-double-val", "excel/read-date-val",
"excel/read-datetime-val")}
read-error-code [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :getErrorCode row col))
(defn
^{ :arglists '("(cell-lock sheet row col locked?)")
:doc
"""
Locks/unlocks a cell.
Note: Excel locks new cells by default.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/cell-lock sheet 1 1 false)
(excel/cell-lock sheet 1 2 false)
(excel/cell-lock sheet 1 3 true)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))
(let [wbook (excel/open "sample.xlsx")
sheet (excel/sheet wbook "Sheet 1")]
[(excel/cell-locked? sheet 1 1)
(excel/cell-locked? sheet 1 2)
(excel/cell-locked? sheet 1 3)]))
""" )
:see-also '(
"excel/cell-locked?",
"excel/cell-hidden?",
"excel/cell-empty?",
"excel/cell-type" ) }
cell-lock [sheet row col locked?]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)
(boolean? locked?)] }
(. sheet :lock row col locked?))
(defn
^{ :arglists '("(cell-locked? sheet row col)")
:doc
"""
Returns true if the sheet cell is locked else false.
Note: Excel locks new cells by default.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/cell-lock sheet 1 1 false)
(excel/cell-lock sheet 1 2 false)
(excel/cell-lock sheet 1 3 true)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))
(let [wbook (excel/open "sample.xlsx")
sheet (excel/sheet wbook "Sheet 1")]
[(excel/cell-locked? sheet 1 1)
(excel/cell-locked? sheet 1 2)
(excel/cell-locked? sheet 1 3)]))
""" )
:see-also '(
"excel/cell-lock",
"excel/cell-empty?",
"excel/cell-type" ) }
cell-locked? [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)] }
(. sheet :isLocked row col))
(defn
^{ :arglists '("(cell-hidden? sheet row col)")
:doc
"""
Returns true if the sheet cell is hidden else false.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))
(let [wbook (excel/open "sample.xlsx")
sheet (excel/sheet wbook "Sheet 1")]
(excel/cell-hidden? sheet 1 1)))
""" )
:see-also '(
"excel/cell-locked?", "excel/cell-empty?", "excel/cell-type" ) }
cell-hidden? [sheet row col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? row) (pos? row)
(long? col) (pos? col)] }
(. sheet :isHidden row col))
(defn
^{ :arglists '("(col-hidden? sheet col)")
:doc
"""
Returns true if the sheet column is hidden else false.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/create :xlsx)
sheet (excel/add-sheet wbook "Sheet 1")]
(excel/write-values sheet 1 1 "John" "Doe" 28)
(excel/auto-size-columns sheet)
(excel/write->file wbook "sample.xlsx"))
(let [wbook (excel/open "sample.xlsx")
sheet (excel/sheet wbook "Sheet 1")]
(excel/col-hidden? sheet 1)))
""" )
:see-also '(
"excel/cell-locked?", "excel/cell-empty?", "excel/cell-type" ) }
col-hidden? [sheet col]
{ :pre [(instance-of? :ExcelSheetFacade sheet)
(long? col) (pos? col)] }
(. sheet :isColumnHidden col))