xlsimport()

Read(2339) 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 ordinal 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 an ordinal number.

s

Name or ordinal 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 row.

@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 blank spaces on both sides of a string; read an empty string as null.

Return value:

Table sequence

Example:

Read the whole xls file:

 

A

 

1

=file("emp1.xls").xlsimport()

2

=file("password_abc.xls").xlsimport(;;"abc")

Read an xls file encrypted with the password; the password for opening password_abc.xls is abc.

 

Read the first row of an xls file as the title and retrieve the specified fields:

 

A

 

1

=file("emp1.xls").xlsimport@t(NAME,GENDER;)

Read NAME field and GENDER field.

2

=file("emp1.xls").xlsimport@t(#1,#2;)

Read the first and the second fields from the xls file.

 

Read a multi-sheet xls file:

 

A

 

1

=file("emp2.xlsx").xlsimport@t(;"t1")

emp2.xlsx contains two sheets, whose names are t1 and t2;

Read data from sheet t1 through its name.

When reading a multi-sheet xls file and when parameter s is absent, read the first sheet by default.

2

=file("emp2.xlsx").xlsimport@t(;2)

Read data from the second sheet through the sheet number.

 

Specify the starting row and the ending row:

 

A

 

1

=file("emp2.xlsx").xlsimport(;2,3:6)

Read rows from the third to the sixth in the second sheet of emp2.xlsx:

2

=file("emp2.xlsx").xlsimport@t(;2,3:6)

As @t option works and parameter b is absent, read data in the bth row as the title.

3

=file("emp2.xlsx").xlsimport@t(;"t2",:6)

As parameter b is absent, read the first row as the title and retrieve rows of the data from the first to the sixth; “” should not be omitted.

4

=file("emp2.xlsx").xlsimport(;"t2",7:)

As parameter e is absent, read rows of data from the seventh to the last; “” can be omitted.

5

=file("emp2.xlsx").xlsimport(;"t2",3:-4)

Read rows of data from the third to the fourth to the last.

 

Read content of the xlsx file and return it as a cursor:

 

A

 

1

=file("emp3.xlsx").xlsimport@c()

Return a cursor.

2

=A1.fetch()

 

 

Remove blank rows before or after the Excel content during reading:

 

A

 

1

=file("emp4.xls")

2

=A1.xlsimport@b()

@b option works to remove blank rows before or after the Excel content during reading.

 

Return a sequence of sequences:

 

A

 

1

=file("emp1.xls").xlsimport@w()

@w option works to return a sequence of sequences, where each row is a member of the sequence.

 

Retrun a sequence consisting of Excel columns:

 

A

 

1

=file("emp1.xls").xlsimport@pw()

@wp options work to return a sequence made up of Excel columns, where each column is a member of the sequence.

 

Return a string delimited by /tab:

 

A

 

1

=file("emp1.xls").xlsimport@s()

@s option works to return a string separated by /tab.

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