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 is 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:
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 |
