The xlsx Reference Manual

Table of Contents

Next: , Previous: , Up: (dir)   [Contents][Index]

The xlsx Reference Manual

This is the xlsx Reference Manual, generated automatically by Declt version 3.0 "Montgomery Scott" on Tue Dec 22 15:31:42 2020 GMT+0.


Next: , Previous: , Up: Top   [Contents][Index]

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)))))))))


Next: , Previous: , Up: Top   [Contents][Index]

2 Systems

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


Previous: , Up: Systems   [Contents][Index]

2.1 xlsx

Author

Carlos Ungil <ungil@mac.com>

License

MIT

Description

Basic reader for Excel files.

Dependencies
Source

xlsx.asd (file)

Components

Next: , Previous: , Up: Top   [Contents][Index]

3 Files

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


Previous: , Up: Files   [Contents][Index]

3.1 Lisp


Next: , Previous: , Up: Lisp files   [Contents][Index]

3.1.1 xlsx.asd

Location

xlsx.asd

Systems

xlsx (system)


Next: , Previous: , Up: Lisp files   [Contents][Index]

3.1.2 xlsx/package.lisp

Parent

xlsx (system)

Location

package.lisp

Packages

xlsx


Previous: , Up: Lisp files   [Contents][Index]

3.1.3 xlsx/xlsx.lisp

Dependency

package.lisp (file)

Parent

xlsx (system)

Location

xlsx.lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Top   [Contents][Index]

4 Packages

Packages are listed by definition order.


Previous: , Up: Packages   [Contents][Index]

4.1 xlsx

Source

package.lisp (file)

Use List

common-lisp

Exported Definitions
Internal Definitions

Next: , Previous: , Up: Top   [Contents][Index]

5 Definitions

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


Next: , Previous: , Up: Definitions   [Contents][Index]

5.1 Exported definitions


Previous: , Up: Exported definitions   [Contents][Index]

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)


Previous: , Up: Definitions   [Contents][Index]

5.2 Internal definitions


Previous: , Up: Internal definitions   [Contents][Index]

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)


Previous: , Up: Top   [Contents][Index]

Appendix A Indexes


Next: , Previous: , Up: Indexes   [Contents][Index]

A.1 Concepts

Jump to:   F   L   X  
Index Entry  Section

F
File, Lisp, xlsx.asd: The xlsx․asd file
File, Lisp, xlsx/package.lisp: The xlsx/package․lisp file
File, Lisp, xlsx/xlsx.lisp: The xlsx/xlsx․lisp file

L
Lisp File, xlsx.asd: The xlsx․asd file
Lisp File, xlsx/package.lisp: The xlsx/package․lisp file
Lisp File, xlsx/xlsx.lisp: The xlsx/xlsx․lisp file

X
xlsx.asd: The xlsx․asd file
xlsx/package.lisp: The xlsx/package․lisp file
xlsx/xlsx.lisp: The xlsx/xlsx․lisp file

Jump to:   F   L   X  

Next: , Previous: , Up: Indexes   [Contents][Index]

A.2 Functions

Jump to:   A   C   E   F   G   L   R  
Index Entry  Section

A
as-matrix: Exported functions

C
column-and-row: Internal functions

E
excel-date: Internal functions

F
Function, as-matrix: Exported functions
Function, column-and-row: Internal functions
Function, excel-date: Internal functions
Function, get-entry: Internal functions
Function, get-number-formats: Internal functions
Function, get-relationships: Internal functions
Function, get-unique-strings: Internal functions
Function, list-entries: Internal functions
Function, list-sheets: Exported functions
Function, read-sheet: Exported functions

G
get-entry: Internal functions
get-number-formats: Internal functions
get-relationships: Internal functions
get-unique-strings: Internal functions

L
list-entries: Internal functions
list-sheets: Exported functions

R
read-sheet: Exported functions

Jump to:   A   C   E   F   G   L   R  

Next: , Previous: , Up: Indexes   [Contents][Index]

A.3 Variables


Previous: , Up: Indexes   [Contents][Index]

A.4 Data types

Jump to:   P   S   X  
Index Entry  Section

P
Package, xlsx: The xlsx package

S
System, xlsx: The xlsx system

X
xlsx: The xlsx system
xlsx: The xlsx package

Jump to:   P   S   X