xlsexport()

Read(2700) Label: xlsexport,

Here’s how to use xlsexport() function.

f.xlsexport ()

Description:

Write a table sequence or a cursor to an Excel file.

Syntax:

f.xlsexport(A,x:F,…;s;p)

Note:

The function writes table sequence/cursor A to Excel file f. Export all fields if no x is specified. The program creates file f (cannot create path directory automatically) automatically when the target file f does not exists and replaces the target file f when it already exists.

 

If the content of A being written to Excel reaches the maximum number of rows an Excel worksheet is allowed, the write will automatically stop. The maximum number of rows in a worksheet an xlsx file allows is 1048576, and that an xls file allows is 65536.

Option:

@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

Parameter:

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 ordinal 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

Example:

Write to an encrypted xls 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.

 

Use @a option two perform append-write:

 

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.

 

Use @t option to export data as an Excel file with the title:

 

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.

 

Use @m option to create a new sheet to hold data when the number of rows in the target sheet reaches its limit:

 

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.

 

 

Export as a sequence of sequences:

 

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:

 

Use @k option to replace content of the specified sheet while retaining the target Excel file:

 

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:

Related function:

f.export()

f.xlsimport()

xo. xlsexport()

Description:

Write a sequence into an Excel file object.

Syntax:

xo.xlsexport(A, x:Fi,..;s)

Note:

The function writes a sequence to sheet s or the sth sheet in an Excel file. Create sheet s if it doesn’t exist. Parameter xo is an Excel file object. Parameter A can be a cursor or a table sequence when xo is retrieved using @w option; A can only be a cursor when xo is retrieved using a non-@w way.

 

After the write action is finished, use xo.xlswrite() function to save the content written to the Excel sheet before you can view it.

Parameter:

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

Fi

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

Option:

@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

Example:

 

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.