xlsexport()

Read(218) 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 will create the file f (cannot create path directory automatically) automatically.

 

If there isn’t a sheet s in f, the program will create one automatically. If sheet s already exists, it will receive the imported data in its format.

Options:

@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

@s

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

Parameters:

f

A 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 or sheet number; when omitted, the exported data will be appended to the first sheet

p

The password for opening the exported Excel file

Example:

 

A

 

1

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

 

2

=file("D:\\EMPLOYEE1.xls").xlsexport(A1;"employee";"123")

Write data to an xls file with an opening password; the password is 123

3

=file("D:\\EMPLOYEE2.xls").xlsexport@t(A1;"employee")

 

With @t option, write the 1st row into the file as the header

4

=file("D:\\ EMPLOYEE4.xls").xlsexport@t(A1,EID,NAME:name;"employee")

Specify and export fields to the sheet of "employee"

5

=file("D:\\EMPLOYEE4.xl").xlsexport@t(A1;"employee")

Cannot determine the file extension, so export data as xls format to"EMPLOYEE4.xl" sheet

6

=demo.cursor("select EID,NAME,SURNAME from EMPLOYEE")

 

7

=file("D:\\EMPLOYEE5.xls").xlsexport(A6;"employee")

A6 is a cursor

8

=file("D:\\EMPLOYEE1.xls").xlsexport@t(A1)

Append the file content after the first page when sheet name isn’t supplied, with the last existing non-empty row overwritten as the header row

9

=file("D:\\big.txt").import@t()

There are millions of data rows in big.txt

10

=file("D:\\EMPLOYEE6.xlsx").xlsexport@st(A9;"employee")

Throw exception when @s option is absent

11

=file("D:\\EMPLOYEE7.xls").xlsexport(A1;#1,#2)

Export the first two columns

Related functions:

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.

Parameters:

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

Page name/page number

Options:

@t

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

Example:

 

A

 

1

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

Return a table sequence

2

=file("E11.xlsx")

 

3

=A2.xlsopen()

Read E11.xlsx

4

=A3.xlsexport@t(A1)

Write A1’s table sequence into E11.xlsx

5

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

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

6

=A2.xlswrite(A3)

Save the pupulated data in E11.xlsx using the xlswrite() function