2 Read

Functions for reading from a ".xlsx" file.

You can get a specific cell’s value or loop for the whole sheet’s rows.

There is also a complete read and write example included in the GitHub source.

procedure
(with-input-from-xlsx-file xlsx_file_path
user-proc) → void?
xlsx_file_path : path-string?
user-proc : (-> (is-a?/c read-xlsx%) void?)

Loads a ".xlsx" file and calls user-proc with the resulting read-xlsx% object as its only argument.

To make changes to the file, convert the read-xlsx% object to xlsx% using from-read-to-write-xlsx.

procedure
(load-sheet sheet_name xlsx_handler) → void?
sheet_name : string?
xlsx_handler : (or/c (is-a?/c read-xlsx%) (is-a?/c xlsx%))

Load a sheet specified by its sheet name.

This must be called before attempting to read any cell values.

procedure
(get-sheet-names xlsx_handler) → (listof string?)
xlsx_handler : (or/c (is-a?/c read-xlsx%) (is-a?/c xlsx%))

Returns a list of sheet names.

procedure
(get-cell-value cell_axis xlsx_handler) → (or/c string? number?)
cell_axis : string?
xlsx_handler : (or/c (is-a?/c read-xlsx%) (is-a?/c xlsx%))

Returns the value of a specific cell. Numeric values are returned as numbers, except when stored in cells with “Text” format type. The cell-axis should be in the “A1” reference style.

Example:

(with-input-from-xlsx-file "workbook.xlsx"
  (lambda (xlsx)
    (load-sheet "Sheet1" xlsx)
    (get-cell-value "C12" xlsx)))

procedure
(get-cell-formula cell_axis xlsx_handler) → string?
cell_axis : string?
xlsx_handler : (or/c (is-a?/c read-xlsx%) (is-a?/c xlsx%))

Get a cell’s formula (as opposed to the calculated value of the formula). If the cell has no formula, this will return an empty string.

The cell-axis should be in the “A1” reference style.

Limitations: Currently does not support array or shared formulae.

procedure
(oa_date_number->date oa_date_number) → date?
oa_date_number : number?

Convert an xlsx numeric “date” value into Racket’s date? struct. Any fractional portion of oa_date_number is ignored; this function’s precision is to the day only.

Date values in xlsx files are a plain number representing the count of days since 0 January 1900.

Examples:

> (date->string (oa_date_number->date 43359.1212121))
"Monday, September 17th, 2018"
> (parameterize ([date-display-format 'rfc2822])
(date->string (oa_date_number->date 44921.5601)))
"Tue, 27 Dec 2022"

procedure
(get-sheet-dimension xlsx_handler)
→ (cons/c positive-integer? positive-integer?)
xlsx_handler : (or/c (is-a?/c read-xlsx%) (is-a?/c xlsx%))

Returns the current sheet’s dimension as (cons row col), such as '(1 . 4).

procedure
(get-sheet-rows xlsx_handler)
→ (listof (listof (or/c string? number?)))
xlsx_handler : (or/c (is-a?/c read-xlsx%) (is-a?/c xlsx%))

Returns all rows from the current loaded sheet.

procedure
(sheet-name-rows xlsx-file-path sheet-name)
→ (listof (listof (or/c string? number?)))
xlsx-file-path : path-string?
sheet-name : string?

Reads the spreadsheet file specified by xlsx-file-path and returns the data contained in the sheet named sheet-name. If the file or the sheet do not exist, an exception is raised.

This is the most simple function for reading xlsx data. Use it when you don’t need to do any other operations on the file.

procedure
(sheet-ref-rows xlsx-file-path sheet-index)
→ (listof (listof (or/c string? number?)))
xlsx-file-path : path-string?
sheet-index : exact-nonnegative-integer?

Like sheet-name-rows, but uses a numeric index to specify sheet, starting from 0.

class
read-xlsx% : class?

superclass: object%

Class containing data read in from an existing ".xlsx" file. Convert to a xlsx% using from-read-to-write-xlsx.

1	Install
2	Read
3	Write
4	Complete Example