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.
;;;; __ __ _
;;;; \ \ / /__ _ __ (_) ___ ___
;;;; \ \/ / _ \ '_ \| |/ __/ _ \
;;;; \ / __/ | | | | (_| __/
;;;; \/ \___|_| |_|_|\___\___|
;;;;
;;;;
;;;; 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.ExcelReader)
(import :com.github.jlangch.venice.util.excel.ExcelSheetReader)
(import :com.github.jlangch.venice.util.excel.ExcelWriter)
(import :com.github.jlangch.venice.util.excel.ExcelSheetWriter)
(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 Writer
;;
;; #############################################################################
;; -----------------------------------------------------------------------------
;; ExcelWriter
;; -----------------------------------------------------------------------------
(defn
^{ :arglists '("(writer type)")
:doc """
Creates a new Excel builder for the given type :xls or :xlsx.
"""
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:first "John" :last "Doe" :age 28 }
{:first "Sue" :last "Ford" :age 26 } ]
wbook (excel/writer :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/add-sheet", "excel/add-font", "excel/add-style",
"excel/write->file", "excel/write->stream", "excel/write->bytebuf",
"excel/evaluate-formulas" )}
writer [type]
(case type
:xls (. :ExcelWriter :createXls)
:xlsx (. :ExcelWriter :createXlsx)
(throw (ex :VncException
(str "Invalid Excel type " type ". Use :xls or :xlsx")))))
(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/writer :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? :ExcelWriter 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/writer :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? :ExcelWriter wbook)
(instance-of? :OutputStream os)] }
(. wbook :write os))
(defn
^{ :arglists '("(write->bytebuf wbook os)")
: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/writer :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? :ExcelWriter wbook)] }
(. wbook :writeToBytes))
(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/writer :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/writer :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? :ExcelSheetWriter sheet)
(pos? row) (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 '("(evaluate-formulas it)")
:doc "Evaluate all formulas in the Excel."
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/writer :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/writer") }
evaluate-formulas [it]
{ :pre [(or (instance-of? :ExcelWriter it)
(instance-of? :ExcelReader it)
(instance-of? :ExcelSheetReader it)
(instance-of? :ExcelSheetWriter it))] }
(. it :evaluateAllFormulas))
;; -----------------------------------------------------------------------------
;; ExcelSheetWriter
;; -----------------------------------------------------------------------------
(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/writer :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? :ExcelSheetWriter sheet)] }
(->> (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/writer :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? :ExcelSheetWriter sheet) (map? item)] }
(->> (. :DataRecord :of item)
(. sheet :renderItem)))
(defn
^{ :arglists '("(write-value sheet row col val)")
:doc "Writes a value to a specific cell given by its row and col."
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/writer :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-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/writer :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/add-column sheet "First Name" { :field :first })
(excel/add-column sheet "Last Name" { :field :last })
(excel/add-column sheet "Age" { :field :age })
(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-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? :ExcelSheetWriter sheet)
(long? row) (long? col) (pos? row) (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? :ExcelSheetWriter sheet)
(long? row) (long? col)
(pos? row) (pos? col)
(keyword? style)] }
(if (and (map? val) (contains? val :formula))
(cell-formula sheet row col (:formula val) style)
(. sheet :value row col val (name style)))))
(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/writer :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/writer :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? :ExcelSheetWriter sheet)
(long? row) (long? col) (pos? row) (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? :ExcelSheetWriter sheet)
(long? row) (long? col) (pos? row) (pos? col)
(bytebuf? data)
(keyword? type) (or (== :PNG type) (== :JPEG type))] }
(. sheet :image row col data type scale-x scale-y)))
(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/writer :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? :ExcelSheetWriter 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/writer :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? :ExcelSheetWriter 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/writer :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? :ExcelSheetWriter 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/writer :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? :ExcelSheetWriter sheet)] }
(. sheet :pieChart chart-title
chart-addr-range
legend-position
three-dimensional?
vary-colors?
categories-addr-range
series))
(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/writer :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/writer :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? :ExcelSheetWriter sheet)
(long? row) (long? col) (pos? row) (pos? col)
(string? formula)] }
(. sheet :formula row col formula))
([sheet row col formula style]
{ :pre [(instance-of? :ExcelSheetWriter sheet)
(long? row) (long? col) (pos? row) (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/writer :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? :ExcelSheetWriter 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 '("(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/writer :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 ")")]
(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? :ExcelSheetWriter sheet)
(long? row) (long? col) (pos? row) (pos? 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 '("(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))
(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/writer :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? :ExcelSheetWriter 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/writer :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? :ExcelSheetWriter 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/writer :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/writer :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? :ExcelSheetWriter 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/writer :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? :ExcelSheetWriter 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/writer :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? :ExcelSheetWriter sheet)
(long? col) (pos? col)] }
(. sheet :colWidthInPoints col width))
(defn
^{ :arglists '("(convert->reader builder)")
:doc
"""
Converts an excel or sheet builder to the corresponding reader.
"""
:examples '(
"""
(do
(load-module :excel)
(let [data [ {:a 100 :b 200 }
{:a 101 :b 201 }
{:a 102 :b 202 } ]
wbook (excel/writer :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)")
(let [reader (excel/convert->reader sheet)]
(excel/evaluate-formulas reader)
(excel/read-long-val reader 1 3))))
""" )
:see-also '() }
convert->reader [builder]
{ :pre [(or (instance-of? :ExcelWriter builder)
(instance-of? :ExcelSheetWriter builder))] }
(. builder :reader))
;; -----------------------------------------------------------------------------
;; 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/writer :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/writer :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/add-merge-region") }
add-sheet
([wbook title]
(add-sheet wbook title {}))
([wbook title options]
{ :pre [(instance-of? :ExcelWriter 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 '(
"(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/writer :xlsx)
sheet (excel/add-sheet wbook "Population")]
(excel/column-width sheet 2 70)
(excel/column-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? :ExcelSheetWriter 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/writer :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? :ExcelSheetWriter 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 |
| :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/writer :xlsx)]
(excel/add-font wbook :header { :height 12
:bold true
:italic 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? :ExcelWriter 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 (: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/writer :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? :ExcelWriter 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
: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/writer :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/writer :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? :ExcelSheetWriter sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :style row col style-id))
([sheet row-from row-to col-from col-to style-id]
{ :pre [(instance-of? :ExcelSheetWriter 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)] }
(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/writer :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/writer :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? :ExcelSheetWriter sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :bgColor row col color))
([sheet row col-start col-end color]
{ :pre [(instance-of? :ExcelSheetWriter 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? :ExcelSheetWriter 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)))))))
;; -----------------------------------------------------------------------------
;; 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/writer :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/writer :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? :ExcelSheetWriter 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 '("(open source)")
:doc
"""
Opens an Excel from a source and returns an Excel reader.
Supported sources are string file path, bytebuf, `:java.io.File`,
or `:java.io.InputStream`.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook (excel/open "sample.xlsx")]
(println "Sheet count: " (excel/sheet-count wbook)))
""" )
:see-also '(
"excel/sheet-count", "excel/sheet", "excel/evaluate-formulas") }
open [source]
(cond
(string? source) (. :ExcelReader :open (io/file-in-stream source))
(bytebuf? source) (. :ExcelReader :open (io/bytebuf-in-stream source))
(io/file? source) (. :ExcelReader :open (io/file-in-stream source))
(instance-of? :InputStream source) (. :ExcelReader :open source)
:else (throw (ex :VncException
(str "Invalid Excel open source " (type source) )))))
(defn
^{ :arglists '("(convert->writer reader)")
:doc
"""
Converts an excel or sheet reader to the corresponding writer.
"""
:examples '(
"""
(do
(load-module :excel)
(let [wbook-rd (excel/open "sample.xlsx")
wbook-wr (excel/convert->writer wbook-rd)
sheet-wr (excel/sheet wbook-wr 1) ]
(excel/write-value sheet-wr 1 1 "foo")
(excel/auto-size-columns sheet-wr)
(excel/write->file wbook-wr "sample.xlsx")))
""" )
:see-also '() }
convert->writer [reader]
{ :pre [(or (instance-of? :ExcelReader reader)
(instance-of? :ExcelSheetReader reader))] }
(. reader :writer))
(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/writer :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? :ExcelReader wbook)
(instance-of? :ExcelWriter wbook))] }
(long (. wbook :getNumberOfSheets)))
(defn
^{ :arglists '("(sheet wbook ref)")
:doc
"""
Returns a sheet from the Excel reader referenced by its name or
sheet index.
"""
:examples '(
"""
(do
(load-module :excel)
(defn test-xls []
(let [wbook (excel/writer :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? :ExcelReader wbook)
(instance-of? :ExcelWriter 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/writer :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 [(or (instance-of? :ExcelSheetReader sheet)
(instance-of? :ExcelSheetWriter 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/writer :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 [(or (instance-of? :ExcelSheetReader sheet)
(instance-of? :ExcelSheetWriter 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)
(defn test-xls []
(let [wbook (excel/writer :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-row-range sheet)))
""" )
:see-also '("excel/sheet-col-range")}
sheet-row-range [sheet]
{ :pre [(or (instance-of? :ExcelSheetReader sheet)
(instance-of? :ExcelSheetWriter 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)
(defn test-xls []
(let [wbook (excel/writer :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-col-range sheet 1)))
""" )
:see-also '("excel/sheet-row-range")}
sheet-col-range [sheet row]
{ :pre [(or (instance-of? :ExcelSheetReader sheet)
(instance-of? :ExcelSheetWriter 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/writer :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-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? :ExcelSheetReader 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/writer :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/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? :ExcelSheetReader 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-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/writer :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? :ExcelSheetReader 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/writer :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? :ExcelSheetReader 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/writer :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? :ExcelSheetReader 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/writer :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? :ExcelSheetReader 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/writer :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/writer :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? :ExcelSheetReader 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/writer :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? :ExcelSheetReader 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/writer :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? :ExcelSheetReader 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/writer :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? :ExcelSheetReader 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/writer :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? :ExcelSheetReader sheet)
(long? row) (long? col) (pos? row) (pos? col)] }
(. sheet :getErrorCode row col))