Write to a particular, specified cell

[yimikailori]

Looking for ways to write to an already specified cell
But didn't see anything like this
Combining add-row! and select-cell worked.
Is there a better way?

(defn add-sel-row! [n ^Sheet sheet values]
(assert-type sheet Sheet)
(let [cellref (CellReference. n)
r (.getRow cellref)
col (.getCol cellref)
row (.createRow sheet r)]
(doseq [[column-index value] (map-indexed #(list %1 %2) values)]
(set-cell! (.createCell row col) value))
row))

[mjul]

I did not encounter this use case as I mostly use it for two use cases: "read to Clojure data" or "write Clojure data structures to a sheet".
I agree that it does not look very elegant, though, so I understand why you want something better.
I think a better API for traversal and updates could be created around a "cursor" concept (using ideas from Haskell's lenses) that would could make this more elegant.
If you would be interested in working on it, it would be a great improvement for a V2-release.

[gwzbenjamin]

is there a V2-release already?

[lokori]

I would like to have this too. My use-case is "read a somewhat tricky excel-template, fill it with some data from clojure, and then write it out". There are empty cells in the template, which resolve to nil with select-cell so set-cell! doesn't work. I have to call createCell as above to set the values. After that, my second use case is the usual "read the excel, do something with it in Clojure".

This seems like a fundamental feature to me if this library wants to allow manipulation of Excel-files, not just generating them from scratch and reading them.

Generating a tricky excel-template is not a feasible option, wouldn't want to try that, but Cocjure import + export didn't break my template file, which says positive things about the underlying Apache library and docjure.

[lokori]

Hmm. As such, this is not suitable for a pull request, but here's an example for what I basically would except and want to have. It's essentially a single-cell version of the proposed change in this issue, which operates on row level.

This function either sets the value to a cell instance if one already exists for the specified location reference. Or creates a new cell object and sets the value + type then.

Caveats

• Doesn't handle the case where the whole row specified by the reference is missing.
• Could dispatch with defmulti based on the type
• Doesn't set the type for existing cell, which may be confusing.

(defn  set-or-create-cell! 
  ([sheet n val type]
    (let [cellref (org.apache.poi.ss.util.CellReference. n)
          r (.getRow cellref)
          col (int (.getCol cellref))
          row (or (.getRow sheet r) (.createRow sheet r))
          cell (or (select-cell n sheet) (.createCell row col type))]
      (set-cell! cell val)))
  ([sheet n val]
    (set-or-create-cell! sheet n val org.apache.poi.ss.usermodel.Cell/CELL_TYPE_STRING)))