xlscell()
Description:
Enter cell values to an Excel file object or read values from it.
Syntax:
xo.xlscell(a:b,s ;t)
Note:
The function enters values to Excel sheet s or read values from it. Parameter xo is an Excel file object read in the non-@r@w way.
When parameter t is present and :b is absent, a means entering string t in cell a; a: means entering string t starting from cell a. Save the entered data using the xlswrite() function.
If t is absent, a:b means reading values from cell a to cell b and return them as a string; when only parameter a is present, the function reads cell a only; if the parameter is written as a:, read cells through to the end..
When both parameters a:b are absent, the function will rename sheet s t.
Parameter:
|
xo |
An Excel file object read in a non-@r@w way |
|
a |
Cell a |
|
b |
Cell b |
|
s |
Sheet number, which should not exceed 31 characters and contain special characters []:/\?*, or sheet name; it is the first page when omitted |
|
t |
The to-be-populated data, which can be omitted; it can be separated by the carriage return “\r”(which means the content will be entered into the next row) or tab“\t” (which means the content will be entered into the next column); t can be a string, a single value, a sequence or a sequence of sequences |
Option:
|
@i |
Insert-row style export; by default, enter the specified data in a row next to the row containing cell a |
|
@w |
Enable returning a sequence of sequences of cell values at read-in |
|
@g |
Parameter :b will be absent and parameter t is blob type when using this option to read in or configure an image; support jpg an png only |
|
@p |
Work with @w to return a sequence of sequences; each sub-sequence is made up of column values |
|
@n |
Remove blank spaces on both sides of a string; read an empty string as null when @w option is also present |
Return value:
No return value/a string/a sequence/blob
Example:
Read data from an Excel object when parameter t is absent:
|
|
A |
|
|
1 |
=file("xc1.xls").xlsopen() |
Read the Excel object and return its content as follows:
Below is content of xc1.xls:
|
|
2 |
=A1.xlscell("A1":"C4",1) |
Read content from cell A1 to cell C4 in the first sheet of xc1.xls and return a string:
|
|
3 |
=A1.xlscell("B1","s2") |
As :b is absent, read content of cell B1 in xc1.xls’s sheet s2, and return a string:
|
|
4 |
=A1.xlscell("C4":,) |
As both parameter s and parameter b are absent, read content from C4 to the end in xc1.xls’s first sheet, and return a string:
|
|
5 |
=A1.xlscell@w("A1":"C3") |
With @w option, cell values are read and returned as a sequence of sequences:
|
|
6 |
=A1.xlscell@wp("A1":"C3") |
With @wp options, columns are read and returned as a sequence of sequences:
|
Use @n option to read value in the trim style:
|
|
A |
|
|
1 |
=file("xc2.xls").xlsopen() |
|
|
2 |
=A1.xlscell@w("A1":,) |
Read content from xc2.xls and return it as a sequence:
The 2nd member value has blank spaces at both ends; the 3rd member value is an empty string |
|
3 |
=A1.xlscell@n("A1":,) |
With @n option, remove blank spaces at reading
|
|
4 |
=A1.xlscell@nw("A1":,) |
With @wn options, read the empty string as null
|
Enter values to an Excel object:
|
|
A |
|
|
1 |
=file("xc1.xls") |
Below is content of xc1.xls:
|
|
2 |
=A1.xlsopen() |
Read the Excel object and return its content as follows:
|
|
3 |
=A2.xlscell("A3":,"s2";"aa\rbb\tcc") |
Enter string aa to cell A3 in sheet s2, string bb to cell A4 in the next row, and string cc to cell B4 in the next column to the right |
|
4 |
=A1.xlswrite(A2) |
Use xlswrite function to save the written content and view the Excel file as follows:
|
|
5 |
=A2.xlscell@i("A3",;"OOOOO") |
Insert a row below cell A3 in the first sheet, which will be row 4, and enter string OOOOO |
|
6 |
=A1.xlswrite(A2) |
Use xlswrite function to save the written content and view the Excel file as follows:
|
|
7 |
=[5,6] |
Return a sequence |
|
8 |
=A2.xlscell("A6":,"s2";A7) |
Enter members of A7’s sequence column by column starting from cell A6 in sheet s2 |
|
9 |
=A1.xlswrite(A2) |
Use xlswrite function to save the written content and view the Excel file as follows:
|
|
10 |
=[[7,49],[8,64]] |
Return a sequence of sequences |
|
11 |
=A2.xlscell("A8":,"s2";A10) |
Enter sequence members of A10’s sequence row by row starting from cell A8 in sheet s2 |
|
12 |
=A1.xlswrite(A2) |
Use xlswrite function to save the written content and view the Excel file as follows:
|
Rename a sheet:
|
|
A |
|
|
1 |
=file("xc1.xls") |
Retrieve the Excel object and return it as follows:
|
|
2 |
=A1.xlsopen() |
|
|
3 |
=A2.xlscell(,"s1";"sheet1") |
As parameters a:b are absent, rename sheet s1 sheet 1 |
|
4 |
=A1.xlswrite(A2) |
Save the modification |
|
5 |
=A1.xlsopen() |
|
Read or set up an image:
|
|
A |
|
|
1 |
=file("tp.xls") |
Below is content of tp.xls:
|
|
2 |
=A1.xlscell@g("B2") |
Return a blob type value and view the image as follows:
|
|
3 |
=file("tp_copy.xls").xlsopen() |
Open tp_copy.xls, which is a blank Excel file, and return an Excel object |
|
4 |
=A3.xlscell@g("A3",;A2) |
Write the blob value returned by A2 to cell A3 in tp_copy.xls |
|
5 |
=file("tp_copy.xls").xlswrite(A3) |
Use xlswrite function to save the written content and view tp_copy.xls as follows:
|
