Docjure (Excel in Clojure)

examples.md

Docjure

(def sheet1-data [["Name" "Alias" "Frequency" "Average"] ["blue" "B" 45 1] [] ["green" "G" 67 2] ["red" "R" 12 7]])

(def workbook (docjure/create-workbook "Unit Details" sheet1-data))

(def sheet1 (docjure/select-sheet "Unit Details" workbook))

(def yellow-cell-style (docjure/create-cell-style! workbook { :background :yellow }))

(def bold-cell-style (docjure/create-cell-style! workbook { :font { :bold true }))

(def B2 (docjure/select-cell "B2" sheet1))

;; highlight B2
(docjure/set-cell-style! B2 yellow-cell-style)
 
(docjure/save-workbook! "test.xlsx" workbook)

(def rows (docjure/row-seq sheet1)) ;; java row objects

(def cells (docjure/cell-seq sheet1)) ;; flat array of string

;; make header row bold
(docjure/set-row-style! (first rows) bold-cell-style)

(map str cells)
;; => ("Name" "Alias" "Frequency" "Average" "blue" "B" "45.0" "1.0" "green" ... )

(docjure/select-columns { :A :number :B :name } sheet1)
;; => [{:name "Alias" :number "Name"} {:name "B" :number "blue"}... ]

Docjure wraps Apache POI, we can use Java interop to call the POI objects:

;; get sheet 0, row 0, cell 0 - aka A1
(.getCell (.getRow (.getSheetAt workbook 0) 0) 0)

;; set the height of the row
(.setHeightInPoints (.getRow (.getSheetAt workbook 0) 0) 100)
(.setHeightInPoints (.getRow (.getSheet workbook "Unit Details") 13) 50)
(.setTabColor sheet1 (docjure/color-index :green))

Show all method for a Java object, e.g. a sheet

(require '[clojure.reflect :as r])
(use '[clojure.pprint :only [print-table]])
(print-table (sort-by :name (filter :exception-types (:members (r/reflect sheet)))))

more.md

Some more examples

(def workbook (docjure/load-workbook "test-template.xlsx"))

(def sheet (docjure/select-sheet "Sheet1" workbook))

(def rows (docjure/row-seq sheet))

(def row (first row))

(def cells (docjure/cell-seq row))

(def cell (first cells))

(docjure/read-cell cell)

(.getRow sheet 1)
(.getActiveCell sheet)
(str (.getActiveCell sheet))

;; zero based x,y
(def new-row (.createRow sheet 0)) ;; A
(def new-cell (.createCell new-row 0)) ;; 1
(.setCellValue "HI" new-cell)
(docjure/save-workbook! "out.xlsx" workbook)

;; shift (move) row 1 (2nd row) down one place (replaces 3rd row)
(.shiftRows sheet 1 1 1)

;; insert blank row *before* 1
(.shiftRows sheet 0 (.getLastRowNum sheet) 1)

;; insert blank row *after* row 1
(.shiftRows sheet 1 (.getLastRowNum sheet) 1)