xo.xlsimport()

xo

An Excel file object read in non-@w way.

F_i

Excel column name; retrieve all columns when omitted. Use the sequence number to locate a column when it is #.

s

Sheet name or sheet number; use the first sheet when omitted.

b

The starting row; if omitted, retrieve rows from the first to row e; in this case, ":" can be omitted.

e

The ending row; if omitted, retrieve rows from row b to the last; in this case, ":" cannot be omitted; if e is greater than the actual number of rows, use the actual number of rows. Retrieve rows backwards when e<0. It should be a positive integer when the Excel object is retrieved using @r.

@t

Export the first row of the Excel file as field names; if not supplied, use _1, and _2,… as field names. When parameter b is supplied, it is treated as the header row.

@c

Return the retrieved table sequence as a cursor; here the Excel file object must be read with @r option and parameter e should not be less than 0.

@b

Remove blank rows before and after the Excel data when reading content in; it becomes invalid when @c option is also present.

@w

Enable returning a sequence of sequences where members of sub-sequences are cell values; it cannot work with @t, @c and @b options.

@p

Must work with @w and return a sequence of sequences; each sub-sequence is made up of column values.

@s

Enable returning a string delimited by /tab.

@n

Remove white spaces on both sides of a string; read an empty string as null.

A

1

=file("E1.xls").xlsopen()

Read the E1.xls file and return it.

2

=A1.xlsimport()

Read all data in the first sheet as there are no parameters:

3

=A1.xlsimport@t(ID,NAME;2)

Retrieve columns whose names are ID and NAME on the second sheet, and make the first row as the header row.

4

=A1.xlsimport(;"employee",10:20)

Retrieve data from row 10 to row 20 on the employee sheet.

5

D:/excel/emp.xls

Below is the content of emp.xls:

6

=file(A5).xlsopen().xlsimport@tb()

Use @b option to remove blank rows before and after the data when reading it.

7

=file("E2.xlsx").xlsopen@r()

Retrieve data from the Excel file in the @r way.

8

=A7.xlsimport@c()

Return Excel data as a cursor.

9

=file("E3.xls").xlsopen().xlsimport@w(;2)

Use @w option to return a sequence of sequences, whose members are Excel rows:

10

=file("E4.xls").xlsopen().xlsimport@wp()

Use @wp options to return a sequence of sequences, whose members are Excel columns:

11

=file("E5.xls").xlsopen().xlsimport@s()

@s option works to return a string delimited by CR/tab:

12

=file("E6.xls")

Below is content of E6.xls:

13

=A12.xlsopen().xlsimport@n()

Remove white spaces at both ends of string “Rebecca”, and read empty strings as nulls: