xlsx issueshttps://gitlab.common-lisp.net/cungil/xlsx/-/issues2023-09-21T07:53:51Zhttps://gitlab.common-lisp.net/cungil/xlsx/-/issues/2Can't parse some spreadsheets2023-09-21T07:53:51ZDaniel KochmaĆskiCan't parse some spreadsheets
Hey, thanks for developing this project - it is very useful to me. It works
really well. Today I've encountered the edge case that prevents a successful
read of the document (sorry, I can't disclose the actual file!).
After a bit of ti...
Hey, thanks for developing this project - it is very useful to me. It works
really well. Today I've encountered the edge case that prevents a successful
read of the document (sorry, I can't disclose the actual file!).
After a bit of tinkering I've found the immediate cause. Here's the definition
of `get-number-formats`:
```common-lisp
(defun get-number-formats (zip)
(let ((format-codes (loop for fmt in (xmls:xmlrep-find-child-tags
:numFmt (xmls:xmlrep-find-child-tag
:numFmts (get-entry "xl/styles.xml" zip) nil))
collect (cons (parse-integer (xmls:xmlrep-attrib-value "numFmtId" fmt))
(xmls:xmlrep-attrib-value "formatCode" fmt)))))
(loop for style in (xmls:xmlrep-find-child-tags
:xf (xmls:xmlrep-find-child-tag
:cellXfs (get-entry "xl/styles.xml" zip)))
collect (let ((fmt-id (parse-integer (xmls:xmlrep-attrib-value "numFmtId" style))))
(cons fmt-id (if (< fmt-id 164)
:built-in
(cdr (assoc fmt-id format-codes))))))))
```
In my case the call
```
(xmls:xmlrep-find-child-tag :numFmts (get-entry "xl/styles.xml" zip) nil)
```
returns NIL which is preasumbly allowed because the third argument `if-unfound`
is specified as `nil` instead of `:error`. That leads to a call
```
(xmls:xmlrep-find-child-tags :numfmt nil)
```
That obviously fails because `nil` can't return any children - the function
`xmls:xmlrep-find-child-tags` calls `xmlrep-children` on the treenode.
After reworking this function to ensure that the treenode is never ~nil~ the
files parses correctly. Here is how the function looks like after refactor:
```
(defun get-number-formats (zip)
(let* ((styles (get-entry "xl/styles.xml" zip))
(numfmt (and styles (xmls:xmlrep-find-child-tag :numFmts styles nil)))
(fcodes (and numfmt (loop for fmt in (xmls:xmlrep-find-child-tags :numFmt numfmt)
collect (cons (parse-integer (xmls:xmlrep-attrib-value "numFmtId" fmt))
(xmls:xmlrep-attrib-value "formatCode" fmt))))))
(loop for style in (xmls:xmlrep-find-child-tags :xf (xmls:xmlrep-find-child-tag :cellXfs styles))
collect (let ((fmt-id (parse-integer (xmls:xmlrep-attrib-value "numFmtId" style))))
(cons fmt-id (if (< fmt-id 164)
:built-in
(cdr (assoc fmt-id fcodes))))))))
```
I've patched the system locally and it parses other files without an issue. It
would be great if this fix could be incorporated upstream, or perhaps address
the issue in a more appropriate way (I have not read xlsx standard at all).
This should probably address #1 as well.
Best regards,
Danielhttps://gitlab.common-lisp.net/cungil/xlsx/-/issues/1xlsx:get-number-format error with missing tag numFmt2023-09-21T07:46:05ZRoland Everaertxlsx:get-number-format error with missing tag numFmtHello,
At work, an application is generating an Excel document without the numFmt(s?) tag(s?) in the styles.xml file. Unfortunately, When calling readsheet, the function get-number-format generate an error.
This does not prevent Excel ...Hello,
At work, an application is generating an Excel document without the numFmt(s?) tag(s?) in the styles.xml file. Unfortunately, When calling readsheet, the function get-number-format generate an error.
This does not prevent Excel or OpenOffice from opening the document and displaying the cells content correctly, though.
The document contains essentially strings, date and time values, everything in one sheet.
So, shouldn't, the function get-number-format also silently ignore the missing tags and return a default value. Another possibility, could be to allow instructing the library about a strictness level to enforce.