xlsexport()

@t

Write the first record as the header into a file; when a namesake file with content already exists, the last non-empty row will be treated as the header row.

@c

Import a whole big file (but shouldn’t be too big) with stream style.

@a

Write data after the last row according to the current format if the target Excel file or/and the target sheet exists; if sheet s exists in the original file but it doesn’t exist in the target Excel file, automatically create a sheet with name s in the Excel file.

@k

If the target Excel file exists, retain it and replace the target sheet only. If parameter s is absent, replace the first sheet of the target Excel file; if s is present, replace sheet s

@w

Use this option when parameter A is a sequence of sequences or a string delimited by /tab; it cannot work with both @t and @c, and requires the absence of parameters x:F.

@p

If the table sequence/cursor to be exported is a sequence of sequence, use this option to transpose rows to columns before writing data to a target Excel file; the option must work with @w option.

@m

If the content of A being written to Excel reaches the maximum number of rows an Excel worksheet is allowed, automatically add a new sheet to hold the data.

f

An Excel file.

A

Table sequence/cursor to be exported.

x

Fields to be exported. If omitted, then export all fields which can be textualized in the record sequence A/cursor. The sign # is used to represent a field with a sequence number.

F

Resulting field name; if omitted, then use the original field names.

s

Sheet name, which should not exceed 31 characters and contain special characters []:/\?*; when omitted, the exported data will be appended to the first sheet.

p

The password for opening the exported Excel file.

A

1

=connect("demo").query("select top 10 EID,NAME,SURNAME from EMPLOYEE")

Return a table sequence:

2

=file("e1.xls").xlsexport(A1;"employee";"123"

Write data of table sequence A1 to sheet employee in e1.xls, and set password for opening the Excel file as 123.

A

1

=connect("demo").query("select top 10 EID,NAME,SALARY from EMPLOYEE")

Return a table sequence:

2

=file("e3.xls").xlsexport@a(A1)

Column C in the original e3.xls has a format and its content is as follows;

With @a option, when the target sheet in the target Excel file exists, write data in the end using the format of the existing last row; when parameter s that specifies the sheet name is absent, append data to the end of the first sheet; below is the content after data appending:

3

=file("e3.xls").xlsexport@a(A1;"s2")

With @a option, automatically add a sheet named s2 when there isn’t a sheet names s2 in the target Excel file.

A

1

=connect("demo").query("select top 10 EID,NAME,SURNAME from EMPLOYEE")

Return a cursor as follows；

2

=file("e2.xls").xlsexport@t(A1,EID:id,#2:name)

Export cursor A1’s EID field and its second field to e2.xls, and name the result fields id and name.

3

=5.new(~:f1,~*~:f2)

Return a table sequence:

4

=file("e2.xls").xlsexport@at(A3;"Sheet1")

With @at options, perform append-write when the target sheet in the target Excel file exists and treat the last non-empty row as the title row, which is overwritten by the titles.

A

1

=to(1048580).new(~:ID)

Generate a table sequence having 1048580 rows.

2

=file("e4.xlsx").xlsexport@m(A1)

With @m option, add a new sheet to hold data when the number of rows to which data is written reaches 1048576.

A

1

=file("e1.xls").xlsimport@w(;;"123")

Return a sequence of sequences:

2

=file("e5.xls").xlsexport@w(A1)

Use @w option and export result as follows:

3

=file("e6.xls").xlsexport@wp(A1)

Use @pw options to transpose rows to columns and write data to Excel:

A

1

=3.new(~:ID,~*~:num)

Generate a table sequence:

2

=file("e7.xls")

e7.xls contains two sheets named s1 and s2 respectively; below is content of s2:

3

=A2.xlsexport@kt(A1;"s2")

With @k option, retain target Excel file when it exists and replace the target sheet in it only – that is, write A1’s content to e7.xls’s sheet s2; below is content of s2 after export:

xo

An Excel file object read in the non-@r way.

A

A cursor/a table sequence.

x

To-be-entered field names; all fields will be populated when omitted.

F_i

The column alias; use the original name when omitted.

s

Sheet name, which should not exceed 31 characters and contain special characters []:/\?*, or sheet number.

@t

Export headers; headers will overwrite the last row if there is data already.

@a

Append data according to the current format if the page exists; overwrite the existing data by default.

A

1

=demo.query("select * from scores")

Return a table sequence.

2

=file("E11.xlsx")

3

=A2.xlsopen()

Read E11.xlsx and return an Excel object.

4

=A3.xlsexport@t(A1)

Write A1’s table sequence into an Excel object.

5

=A3.xlsexport@t(A1,CLASS:class,STUDENTID:sid;"Class")

Create a sheet named Class in an Excel object, rename CLASS field and STUDENTID field in A1’s table sequence and write them into sheet Class.

6

=A3.xlsexport@a(A1,STUDENTID,SUBJECT,SCORE; "STUSCORE")

There is a sheet named STUSCORE in E11.xlsx that has data in it, so append STUDENTID, SUBJECT, SCORE columns in A1’s table sequence to this page.

7

=A2.xlswrite(A3)

Save the Excel object in E11.xlsx using the xlswrite() function.