The xlsx Reference Manual

Table of Contents

The xlsx Reference Manual

This is the xlsx Reference Manual, generated automatically by Declt version 3.0 "Montgomery Scott" on Mon Apr 19 18:18:50 2021 GMT+0.

1 Introduction

;; example: creating a sqlite database from an excel sheet
;; (the contents of the first row are used as column names)

(asdf:load-system :sqlite)

(asdf:load-system :xlsx)

(defun fix-name (str)
  (loop for char in '(#\- #\/ #\Space)
     do (setf str (substitute #\_ char str)))
  (setf str (remove #\.	str))
  (if (digit-char-p (elt str 0))
      (concatenate 'string "_" str)
      str))

(defun xlsx-to-sqlite (file &optional (na "_NA_"))
  (let ((xlsx (probe-file file)))
    (when (null xlsx) (error "~A not found" file))
    (let ((sqlite (merge-pathnames (make-pathname :type "sqlite") xlsx)))
      (when (probe-file sqlite) (error "~A exists already" sqlite))
      (let* ((data (xlsx:as-matrix (xlsx:read-sheet file 1) na))
	     (fields (loop for col from 0 below (array-dimension data 1)
			collect (fix-name (aref data 0 col))))
             (numeric (loop for col from 0 below (array-dimension data 1)
			 collect (every (lambda (x) (or (numberp x) (null x)))
					(loop for row from 1 below (array-dimension data 0)
					   collect (aref data row col)))))
	     (create-sql (format nil "CREATE TABLE data(~{~A~^,~})"
	     		 	 (loop for f in fields for n in numeric
				    collect (if n (format nil "~A NUMBER" f) f))))
	     (insert-sql (format nil "INSERT INTO data VALUES (~{~A~^,~})"
				 (loop repeat (length fields) collect "?"))))
	(sqlite:with-open-database (db sqlite)
	  (sqlite:execute-to-list db create-sql)
	  (loop for row from 1 below (array-dimension data 0)
	     do (apply #'sqlite:execute-non-query db insert-sql
		       (loop for col from 0 below (length fields)
			  for item = (aref data row col)
			  collect (if (equal item na) nil item)))))))))

2 Systems

The main system appears first, followed by any subsystem dependency.

2.1 xlsx

Author

Carlos Ungil <ungil@mac.com>

License

MIT

Description

Basic reader for Excel files.

Dependencies

• zip
• flexi-streams
• xmls

Source

xlsx.asd (file)

Components

• package.lisp (file)
• xlsx.lisp (file)

3 Files

Files are sorted by type and then listed depth-first from the systems components trees.

3.1 Lisp

3.1.1 xlsx.asd

Location

xlsx.asd

Systems

xlsx (system)

3.1.2 xlsx/package.lisp

Parent

xlsx (system)

Location

package.lisp

Packages

xlsx

3.1.3 xlsx/xlsx.lisp

Dependency

package.lisp (file)

Parent

xlsx (system)

Location

xlsx.lisp

Exported Definitions

• as-matrix (function)
• list-sheets (function)
• read-sheet (function)

Internal Definitions

• column-and-row (function)
• excel-date (function)
• get-entry (function)
• get-number-formats (function)
• get-relationships (function)
• get-unique-strings (function)
• list-entries (function)

4 Packages

Packages are listed by definition order.

4.1 xlsx

Source

package.lisp (file)

Use List

common-lisp

Exported Definitions

• as-matrix (function)
• list-sheets (function)
• read-sheet (function)

Internal Definitions

• column-and-row (function)
• excel-date (function)
• get-entry (function)
• get-number-formats (function)
• get-relationships (function)
• get-unique-strings (function)
• list-entries (function)

5 Definitions

Definitions are sorted by export status, category, package, and then by lexicographic order.

5.1 Exported definitions

5.1.1 Functions

Function: as-matrix XLSX &optional NA-STRING

Creates an array from a list of cells of the form ((:A . 1) . 42)
Empty columns or rows are ignored (column and row names are returned as additional values). When a value is equal to na-string, nil is returned instead.

Package

xlsx

Source

xlsx.lisp (file)

Function: list-sheets FILE

Retrieves the id and name of the worksheets in the .xlsx/.xlsm file.

Package

xlsx

Source

xlsx.lisp (file)

Function: read-sheet FILE &optional SHEET

Retrives the contents of the given worksheet as a list of cells of the form ((:A . 1) . 42) A numeric id or name is required unless the file contains a single worksheet.

Package

xlsx

Source

xlsx.lisp (file)

5.2 Internal definitions

5.2.1 Functions

Function: column-and-row COLROW

Package

xlsx

Source

xlsx.lisp (file)

Function: excel-date INT

Package

xlsx

Source

xlsx.lisp (file)

Function: get-entry NAME ZIP

Package

xlsx

Source

xlsx.lisp (file)

Function: get-number-formats ZIP

Package

xlsx

Source

xlsx.lisp (file)

Function: get-relationships ZIP

Package

xlsx

Source

xlsx.lisp (file)

Function: get-unique-strings ZIP

Package

xlsx

Source

xlsx.lisp (file)

Function: list-entries FILE

Package

xlsx

Source

xlsx.lisp (file)

Appendix A Indexes

A.1 Concepts

A.2 Functions

A.3 Variables

A.4 Data types