xlsimport()

Read(732) Label: xlsimport,

Here’s how to use xlsimport() function.

f. xlsimport()

Description:

Retrieve contents of an Excel file and return them as a table sequence.

Syntax:

f.xlsimport()

 

f.xlsimport(Fi,…;s,b:e;p)

Retrieve rows from b to e from Excel file f and return them as a table sequence. "e<0" represents the reciprocal number, and "Fi,…" are the to-be-retrieved fields; by default, all fields will be retrieved. s is the name or sequence number of a sheet.

Note:

The function retrieves all rows from Excel file f as records and returns them as a table sequence.

Parameter:

f

An Excel file

Fi

To-be-retrieved fields; by default all fields will be retrieved.The sign # is used to represent a field with a sequence number

s

Name or sequence number of a sheet; if omitted, use the first Excel sheet

b

The starting row; if omitted, retrieve rows from row one to row e will; in this case, ":" can be omitted

e

The ending row; if omitted, retrieve rows from row b to the last row; in this case, ":" cannot be omitted; if e is greater than the actual number of rows, use the actual number of rows. If both b and e are omitted, retrieve rows from the first one to the last one

p

The password for opening the Excel file

Option:

@t

Export the first row in f as the field names; if not supplied, then use _1, and _2,… as field names; with parameter b supplied, row b is treated as the header ro

@c

Return result as a cursor; support xlsx format only; in this case, parameter e should be greater 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

@s

Enable returning a string delimited by /tab

@p

Work with @w to return a sequence of sequences; each sub-sequence is made up of column values

@n

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

Return value:

A table sequence whose records are rows of an Excel file

Example:

 

A

 

1

=file("D:\\EMPLOYEE1.xls").xlsimport()

2

=file("D:\\EMPLOYEE2.xls").xlsimport@t()

3

=file("D:\\EMPLOYEE1.xls").xlsimport(;"employee",3:6)

Retrieve data from a sheet named “employee” by specifying a starting row and an ending row

4

=file("D:\\EMPLOYEE2.xls").xlsimport@t(EID;"employee",3:6)

Retrieve a specified field from the "employee" sheet, with the starting and ending rows defined and with the first row being the headers

5

=file("D:\\EMPLOYEE2.xls").xlsimport@t(EID;"employee",:6)

With b omitted, retrieve rows from the first one which is treated as the headers; here ":" cannot be omitted

6

=file("D:\\EMPLOYEE2.xls").xlsimport(;"employee",490:)

With e omitted, retrieve rows till the end, with row 490 treated as the headers; here ":" can be omitted

7

=file("D:\\EMPLOYEE2.xls").xlsimport(;"employee",490:-5)

Retrieve from the 490th row to the 5th row counted from the last

8

=file("D:\\EMPLOYEE2.xls").xlsimport@t(EID;"employee")

With both b and e omitted, retrieve all rows while making the first row the headers

9

=file("D:\\EMPLOYEE4.xl").xlsimport()

Retrieve the "EMPLOYEE4.xl" file in xls format

10

=file("D:\\test.xlsx").xlsimport@c()

Return result as a cursor

11

=file("D:\\EMPLOYEE5.xls").xlsimport(#1,#2;2)

There are several sheets in EMPLOYEE5.xls; import the first columns of data from the second sheet

12

D:/excel/emp.xls

Below is the content of emp.xls:

13

=file(A12).xlsimport@tb()

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

14

=file(A12).xlsimport@w()

Return a sequence of sequences

15

=file(A12).xlsimport@w()

Return a string delimited by /tab

16

=file(A12).xlsimport@wp()

Use @wp options to return a sequecne of sequences whose members are column values

Related functions:

f.import()

f. xlsexport ()

xo. xlsimport()

Description:

Retrieve a table sequence from an Excel file object.

Syntax:

xo.xlsimport(Fi,..;s,b:e)

Note:

The function retrieves data from row b to row e on sheet s or on the sth sheet in an Excel file, and returns a table sequence. When all parameters are absent, get the table sequence on the first sheet. xo is an Excel file object read in a non-@w way.

Parameter:

xo

An Excel file object read in non-@w way

Fi

Excel column name; retrieve all fields when omitted

s

Page name/page number

b

Row number; the first row by default

e

Row number; when e < 0, it is the row counted backwards; retrieve data to the last row when omitted

Option:

@t

The first row is the header row; when parameter b is present, the header row is row b

@c

Return the retrieved table sequence as a cursor; here the Excel file object must be read with @r option

@b

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

Return value:

A table sequence

Example:

 

A

 

1

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

Read the E1.xls file and return it

2

=A1.xlsimport()

Read the table sequence on the first sheet as there are no parameters

3

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

Retrive 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 in