xlsexport()
Here’s how to use xlsexport() function.
f.xlsexport ()
Description:
Write a sequence or a cursor to an Excel file.
Syntax:
f.xlsexport(A,x:F,…;s;p)
Note:
The function writes sequence/cursor A to Excel file f. The program creates file f (cannot create path directory automatically) automatically when the target file f does not exist, and overwrites 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 into a file as column headers; by default, the headers are recorded as _1, _2, …, and so on.. |
|
@c |
Export a big file in the stream style. When file f is present, it needs to be writable and cannot be too large. |
|
@a |
Write data after the last row according to the current format if the target Excel file or/and the target sheet exists. If @t option is also present, the last non-empty row of the original file/sheet is treated as headers and will be overwritten by headers of the target file. |
|
@k |
If the target Excel file exists, retain it. When parameter s is present, replace sheet s in the original Excel file only; when it is absent, replace the first sheet of the original Excel file. |
|
@w |
Use this option when parameter A is a sequence of sequences or a string delimited by CR/tab; it does not work with both @t and @c, and does not need parameters x:F. |
|
@p |
When parameter A is a sequence of sequences, use this option to transpose “first-column-then-row” form of data structure before writing data to a target Excel file; the option must work with @w option. Ignore this option when parameter A is a string delimited by CR/tab. |
|
@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 |
A sequence/Cursor. |
|
x |
Fields to be exported. If omitted, then export all fields. 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 or sheet number. Create the sheet if the sheet name or number does not exist; and append the exported data to the first sheet when omitted. The sheet name should not exceed 31 characters and contain special characters []:/\?*, and its first and last characters should not be a single quotation mark. |
|
p |
The password. |
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 titles:
|
|
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 or as a string delimited by CR/tab:
|
|
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("d1.xls").xlsimport@w(;2) |
Return a sequence of sequences:
|
|
4 |
=file("e6.xls").xlsexport@wp(A3) |
A3’s sequence of sequences records data in the form of “first-column-then-row:
Use @wp options to transpose A3 and write data to the target Excel file:
|
|
5 |
=file("e7.xls").xlsimport@s(;1) |
Return a string delimited by CR/tab:
|
|
6 |
=file("e8.xls").xlsexport@w(A5) |
Use @w option and export the following result:
|
|
7 |
=file("e9.xls").xlsexport@wp(A5) |
A4 returns a string delimited by CR/tab; ignore @p option in this case and the result is the same as A5. |
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 the original sheet s1 in e7.xls and write A1’s content to e7.xls’s sheet s2; below is content of s2 after export:
|
Related function:
xo. xlsexport()
Description:
Write a sequence/cursor to an Excel file object.
Syntax:
xo.xlsexport(A, x:Fi,..;s)
Note:
The function writes a sequence to sheet s in an Excel file. Parameter A can be a cursor when parameter xo is an Excel file object retrieved using @w option.
Parameter:
|
xo |
An Excel file object read in the non-@r way. |
|
A |
A sequence/cursor. |
|
x |
To-be-exported fields; all fields will be exported when omitted. It is a sequence number for locating the field when specified as #. |
|
Fi |
Result field names; use the original field names when this parameter is absent. |
|
s |
Sheet name or sheet number. Create the sheet if the sheet name or number does not exist; and write data to the first sheet when omitted. The sheet name should not exceed 31 characters and contain special characters []:/\?*, and its first and last characters should not be a single quotation mark. |
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. |
|
@w |
Use this option when parameter A is a sequence of sequences or a string delimited by CR/tab; it does not work with @t option, and does not need parameters x:F. |
|
@p |
When parameter A is a sequence of sequences, use this option to transpose “first-column-then-row” form of data structure before writing data to a target Excel file; the option must work with @w option. Ignore this option when parameter A is a string delimited by CR/tab. |
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 |
[[1,2,3],[4,5,6],[7,8,9]] |
|
|
8 |
=A3.xlsexport@w(A7) |
Write A7’s sequence of sequences to A3’s Excel file:
|
|
9 |
=A3.xlsexport@wp(A7) |
Transpose the “first-column-then-row” content and write it to the Excel file:
|
|
10 |
=file("e7.xls").xlsimport@s(;1) |
Return a string delimited by CR/tab:
|
|
11 |
=A3.xlsexport@w(A10) |
Write A10’s string to A3’s Excel file:
|
|
12 |
=A3.xlsexport@wp(A10) |
As A10 returns a string delimited by CR/tab, ignore @p option and the export result is the same as A11. |
|
13 |
=A2.xlswrite(A3) |
Save the Excel object in E11.xlsx using the xlswrite() function. |
