Date formatting not correctly applied

[safoxall]

Hi

There is an issue with date formatting, such that after a number of date values, the date formatting is not applied. This appears to be as a result of the workbook supporting a finite number of styles and after this is exceeded new styles not being applied. See issue described on Stack Overflow:

https://stackoverflow.com/questions/48319626/apache-poi-setting-date-format-not-always-properly-formatting-in-excel

As we can see in the function below styles are created dynamically, for each cell where the value is detected as being of a date type:

(defn apply-date-format! [^
Cell cell ^String format]
  (let [workbook (.. cell getSheet getWorkbook)
        date-style (.createCellStyle workbook)
        format-helper (.getCreationHelper workbook)]
    (.setDataFormat date-style
                    (.. format-helper createDataFormat (getFormat format)))
    (.setCellStyle cell date-style)))

The solution is to create the workbook date style once and then pass it into this function. I noticed reading some of the PRs that you're looking to use a more declarative approach, by using maps to pass around values. This is probably a good use case.

As a workaround have done as follows, not ideal, but fixes the issue:

(def workbook-style (atom nil))

(defn get-wb-style
  [workbook]
  (let [date-style (.createCellStyle workbook)
        format-helper (.getCreationHelper workbook)]
    (.setDataFormat date-style
                    (.. format-helper createDataFormat (getFormat "dd/MMM/yy;@")))
    {:style date-style :workbook workbook}))

(defn apply-date-format! [^Cell cell ^String format]
  (let [workbook (.. cell getSheet getWorkbook)]
    (when (or (nil? @workbook-style) (not= (:workbook @workbook-style) workbook))
      (reset! workbook-style (get-wb-style workbook)))
    (.setCellStyle cell (:style @workbook-style))))

(intern 'dk.ative.docjure.spreadsheet 'apply-date-format! apply-date-format!)

The alternative would have been to intern half the library to pass the style through when creating the workbook.

Hope this helpful :-)

[mjul]

Thanks for the help, I really appreciate you taking the time to share this.
It looks like you are well aware of the pain-points of the current API, so if you would like to give a hand with a more functional API for v2 it would be awesome.