SKEW formula in Excel results in exception instead of :FUNCTION_NOT_IMPLEMENTED

[takapai]

I have a simple Excel worksheet that has a formula cell with the function SKEW. Screenshot below:

Trying to read in this Excel file will result in an exception using the following code, which is mostly straight from the README.

(defn load-xls
  "Load Excel spreadsheet and return a list of lists"
  [fname]
  (->> (xls/load-workbook fname)
       (xls/sheet-seq)
       (first)
       xls/row-seq
       (remove nil?)
       (map xls/cell-seq)
       (map #(map xls/read-cell %))))

The result is:

((1.0)Error printing return value (NotImplementedFunctionException) at org.apache.poi.ss.formula.functions.NotImplementedFunction/evaluate (NotImplementedFunction.java:40).
SKEW

I believe this should result in the cell to have the keyword value :FUNCTION_NOT_IMPLEMENTED.

[mjul]

Hello Takapai

Thank you for the bug report.

I believe it is a problem with the underlying Apache POI library. It does not fully support all Excel formulas. You can read more about it here: https://poi.apache.org/components/spreadsheet/eval-devguide.html

SKEW is not mentioned in the list of supported functions in Appendix A at the bottom of that page.

There are two possible approaches:

1) Fix the problem in the POI library. This would be best for everyone including people using POI without Docjure. 2) Work around the issue in Docjure. Perhaps it would be possible to use the cached value for the formula result from the spreadsheet when we encounter this error.

I encourage you to explore these options if you have the time and motivation.

All the best, Martin

[kimim]

option 2, could define this method with a try...catch, but need an additional function read-cell-cached:

(defmethod read-cell CellType/FORMULA   [^Cell cell]
  (let [evaluator (.. cell getSheet getWorkbook
                      getCreationHelper createFormulaEvaluator)]
    (try
      (let [cv (.evaluate evaluator cell)]
        (if (and (= CellType/NUMERIC (.getCellType cv))
                 (DateUtil/isCellDateFormatted cell))
          (.getDateCellValue cell)
          (read-cell-value cv false)))
      (catch Exception e
        (read-cell-cached cell)))))