defunkydrummer / lisp-xl Goto Github PK

Getting this error when loading :lisp-xl with quicklisp: Error: #\ů (of type CHARACTER) is not of type BASE-CHAR.

The error is occuring while loading :zip. There seem to be several zip packages, and I'm not clear on which one is being used. I do know it is trying to load this file: quicklisp\dists\quicklisp\software\zip-20150608-git\zip.ofasl.

I'm not super familiar with GitHub, but I'd assume the date code in the pathname would be useful in some way.

Dear Flavio,
Only can I found some read-use under your README-file, for my need,Would you mind give an example for using :LISP-XL to write data to the .xlsx-file,thank you very much

For some files unique strings are not correctly processed.

A Sample file is attached: o1.xlsx

For row 4, instead of:
("#" "SKU" "Description" "Q-ty" "Unit Selling Price" "Total Selling Price" "Config")

(process-sheet) returns:
("#" 3 4 "Q-ty" 6 7 "Config")

Dear Flavio,

searching for a way to read-in .xlsx files using common lisp, since I really want to try to use it in every day life (I am a Bioinformatics Postdoc in Germany),
I encountered some problems.

I want to read-in the bigger file which was created using R's openxlsx package.
And as a test, I created a dummy small file, which contains actually only
the table

A | B | C
1 | 2 | 3
4 | 5 | 6

Reading my xlsx files (test an desired file) in lisp-xl both resulted in empty strings.
The :xlsx package could handle the small test, but not the big, desired, file.
I could also send you the xlsx files if required.

;; I tried the following using
;; SBCL 1.3.1.debian
;; in emacs/SLIME:

;; I created the small file using libreoffice
;; LibreOffice 5.1.6.2 10m0(Build:2)
;; in ubuntu 16.04

;; and the big file using R openxlsx package (the current one)
;; in ubuntu 16.04

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;;;;;;;;;;;;;;;;;;;;;;;;
;; file paths
;;;;;;;;;;;;;;;;;;;;;;;;

(defparameter small-fpath #P"/home/josephus/test/xlsx-files/test.xlsx")
(defparameter big-fpath #P"/home/josephus/test/xlsx-files/nrm-counts-avg--k2-vs-wtn.xlsx")

;;;;;;;;;;;;;;;;;;;;;;;;
;; try the small file
;;;;;;;;;;;;;;;;;;;;;;;;

(ql:quickload :xlsx)
(xlsx:list-sheets small-fpath)

;; => ((1 "Sheet1" "worksheets/sheet1.xml"))

(xlsx:read-sheet small-fpath 1)

;; => (((:A . 1) . "A") ((:B . 1) . "B") ((:C . 1) . "C") ((:A . 2) . 1)
;; ((:B . 2) . 2) ((:C . 2) . 3) ((:A . 3) . 4) ((:B . 3) . 5) ((:C . 3) . 6))

(xlsx:as-matrix (xlsx:read-sheet small-fpath 1))

;; => #2A(("A" "B" "C") (1 2 3) (4 5 6)), (:A :B :C), (1 2 3)

;; the quicklisp package is that from Carlos Ungil
;; add from Akihide Nano's site:
(defun as-alist (xlsx)
"Creates an a-list from a list of cells of the form ((:A1 . 42) (:B1 . 21))"
(mapcar #'(lambda (lst)
(cons (intern (concatenate 'string
(symbol-name (caar lst))
(write-to-string (cdar lst))) :keyword)
(cdr lst)))
xlsx))

(defun as-plist (xlsx)
"Creates a p-list from a list of cells of the form (:A1 42 :B1 21)"
(mapcan #'(lambda (lst)
(cons (intern (concatenate 'string
(symbol-name (caar lst))
(write-to-string (cdar lst))) :keyword)
(list (cdr lst))))
xlsx))

(as-alist (xlsx:read-sheet small-fpath 1))

;; => ((:A1 . "A") (:B1 . "B") (:C1 . "C") (:A2 . 1) (:B2 . 2) (:C2 . 3) (:A3 . 4)
;; (:B3 . 5) (:C3 . 6))

(as-plist (xlsx:read-sheet small-fpath 1))

;; => (:A1 "A" :B1 "B" :C1 "C" :A2 1 :B2 2 :C2 3 :A3 4 :B3 5 :C3 6)

;;;;;;;;;;;;;;;;;;;;;;;
;; now try the big file
;;;;;;;;;;;;;;;;;;;;;;;

(xlsx:read-sheet big-fpath 1)

;; Node does not have a single string child: #S(XMLS:NODE
;; :NAME t
;; :NS http://schemas.openxmlformats.org/spreadsheetml/2006/main
;; :ATTRS ((space
;; preserve))
;; :CHILDREN NIL)
;; [Condition of type SIMPLE-ERROR]

;; Restarts:
;; 0: [RETRY] Retry SLIME interactive evaluation request.
;; 1: [*ABORT] Return to SLIME's top level.
;; 2: [ABORT] abort thread (#<THREAD "worker" RUNNING {1006CA5C33}>)

;; Backtrace:
;; 0: (XMLS:XMLREP-STRING-CHILD #S(XMLS:NODE
;; :NAME "t"
;; :NS "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
;; :ATTRS (("space" "preserve")) :CHILDREN NIL) :ERROR)
;; Locals:
;; IF-UNFOUND = :ERROR
;; TREENODE = #S(XMLS:NODE
;; :NAME "t"
;; :NS "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
;; :ATTRS (("space" "preserve")) :CHILDREN NIL)
;; 1: (XLSX::GET-UNIQUE-STRINGS #S(ZIP:ZIPFILE
;; :STREAM #<SB-SYS:FD-STREAM for
;; "file /home/josephus/test/xlsx-files/nrm-counts-avg--k2-vs-wtn.xlsx"
;; {1003FBF2B3}>
;; :ENTRIES #<HASH-TABLE
;; :TEST EQUAL
;; :COUNT 12 {..
;; Locals:
;; SB-DEBUG::ARG-0 = #S(ZIP:ZIPFILE
;; :STREAM #<SB-SYS:FD-STREAM for "file /home/josephus/test/xlsx-files/nrm-counts-avg--k2-vs-wtn.xlsx"
;; {1003FBF2B3}>
;; :ENTRIES #<HASH-TABLE
;; :TEST EQUAL
;; :COUNT 12 {10088E0403}>)
;; 2: (XLSX:READ-SHEET #P"/home/josephus/test/xlsx-files/nrm-counts-avg--k2-vs-wtn.xlsx" 1)
;; Locals:
;; FILE = #P"/home/josephus/test/xlsx-files/nrm-counts-avg--k2-vs-wtn.xlsx"
;; SHEET = 1
;; 3: (SB-INT:SIMPLE-EVAL-IN-LEXENV (XLSX:READ-SHEET BIG-FPATH 1) #)
;; Locals:
;; SB-DEBUG::ARG-0 = (XLSX:READ-SHEET BIG-FPATH 1)
;; SB-DEBUG::ARG-1 = #
;; 4: (EVAL (XLSX:READ-SHEET BIG-FPATH 1))
;; Locals:
;; SB-DEBUG::ARG-0 = (XLSX:READ-SHEET BIG-FPATH 1)
;; 5: ((LAMBDA NIL :IN SWANK:INTERACTIVE-EVAL))
;; [No Locals]
;; --more--

;; so :xlsx package cannot handle the big file

;; so it was not possible to open the big file using the :xlsx package

(ql:quickload :lisp-xl)
To load "lisp-xl":
Load 1 ASDF system:
lisp-xl
; Loading "lisp-xl"
;;; Checking for wide character support... WARNING: Lisp implementation doesn't use UTF-16, but accepts surrogate code points.
yes, using code points.
..
;;; Checking for wide character support... WARNING: Lisp implementation doesn't use UTF-16, but accepts surrogate code points.
yes, using code points.
;;; Building Closure with CHARACTER RUNES
.....

(defparameter t1 (lisp-xl:read-sheet small-fpath 1))

;; Reading file /home/josephus/test/xlsx-files/test.xlsx
;; Loading metadata into RAM...3 unique strings found.
;; Uncompressing to File [/tmp/lisp-xl-tempGHU3ALSW.tmp] ...

(print t1)

;; #S(LISP-XL::SHEET
;; :UNIQUE-STRINGS #(" " " " " ") ;; the unique strings are empty
;; :NUMBER-FORMATS NIL
;; :DATE-FORMATS NIL
;; :FILE-NAME #P"/tmp/lisp-xl-tempGHU3ALSW.tmp"
;; :LAST-STREAM-POSITION NIL)

(defparameter t2 (lisp-xl:read-sheet big-fpath 1))

;; Reading file /home/josephus/test/xlsx-files/nrm-counts-avg--k2-vs-wtn.xlsx
;; Loading metadata into RAM...45814 unique strings found.
;; Uncompressing to File [/tmp/lisp-xl-tempAAURSO2.tmp] ...

(print t2)

;; #S(LISP-XL::SHEET
;; :UNIQUE-STRINGS #(" " " " " " " " " " " " " " " " " " " " " " " " " " " "
;; " " " " " " " " " " " " " " " " " " " " " " " " " " " "

;; [ ... may left out ...]

;; " " " " " " " " " " " " " " " " " " " " " " " " " " " "
;; " " " " " " " " " " " ")
;; :NUMBER-FORMATS NIL
;; :DATE-FORMATS NIL
;; :FILE-NAME #P"/tmp/lisp-xl-tempAAURSO2.tmp"
;; :LAST-STREAM-POSITION NIL)

(lisp-xl-csv:excel-to-csv small-fpath "/home/josephus/test/xlsx-files/test.csv" 1)

;; gives

, ,
,,
,,

;; as output in test.csv
;; so strings are really empty

;; so somehow no strings are recognized ...
;; I created the small file using libreoffice
;; LibreOffice 5.1.6.2 10m0(Build:2)
;; in ubuntu 16.04

;; and the big file using R openxlsx package (the current one)
;; in ubuntu 16.04

;; would you have a clue, what the problem could be?
;; the desired big file has 45809 rows and 6 columns.

Hey, thank you for the library.

I am trying to read data from an xlsx file, but I have a problem reading a cell in which there is a formula in it. It seems like the formula is read instead of the value produced by the formula. How can I solve this problem?