Output a formula

[sittim]

If I try to output a worksheet, how do I make the formulas work? for example:

(defn gen-excel-smple []
  (let [wb (create-workbook "Price List"
                              [["Name" "Price"]
                               ["Foo Widget" 100]
                               ["Bar Widget" 200]
                               ["Total" "=sum(B2,B3)"]])   ; <<<<<<<<<<<<< FORMULA
          sheet (select-sheet "Price List" wb)
          header-row (first (row-seq sheet))]
      (set-row-style! header-row (create-cell-style! wb {:background :yellow,
                                                         :font {:bold true}}))
      (save-workbook! "ss.xlsx" wb)))

The line with the Total how can I make =sum(B2,B3) work?

[hermann-p]

I ran into the same problem. I solved it for my quite humble needs by extending the string cell processing:

(ns ...
  (require [dk.ative.docjure.spreadsheet :as docjure]))

(defmethod docjure/set-cell! String [^Cell cell val]
  (let [is-formula-cell? (= (.getCellType cell) CellType/FORMULA)
        is-formula-val? (clojure.string/starts-with? val "=")] 
    (if is-formula-val?
      (.setCellFormula cell ^String (clojure.string/replace val #"^=" ""))
      (do (when is-formula-cell? (.setCellType cell CellType/STRING))
          (.setCellValue cell ^String val)))))

Here we use a leading = sign in the string cell value to guess user wants to insert a formula. If we don't remove the leading =, Cell.setCellFormula will throw an exception. This emulates what happens when you type a formula with a leading = into a spreadsheet.

If anyone is interested, I'm willing to create a PR and some test.

Notice that Apache POI does not support all formulae, so YMMV.