|
the software that grows
with you
| |
|
V32ExcelScript()
|
| Creates a sheet in the Excel workbook, or
number of sheets in a workbook through an array of user defined scripts,
virtually offering the precise control over the contents of the Excel
sheet(s). It also allows the use of formulaes provided by Excel API. |
|
Syntax
|
| V32ExcelScript( <cFileExcel>,
<aScript>, [lDispAlerts] ) |
|
Arguments
|
| cFileExcel |
Character string to represent the .XLS file associated
with script. If <cFileExcel> is not found, a new file will be
created with that name. |
| aScript |
A two dimentional array containing the command and
parameters script. All rows in the array can be of variable
elements. First element of each script array will be the command as
a character string and all other elements will become the
parameters. As each command will have different number of
parameters, all script commands need be explained in detail, as
follows:
|
'font' |
| cFont |
Valid font name installed on the machine.
Font name can carry its attribute as bold or italics
preceded by a period. For example, 'Ariel.bold' is a
valid font name with attribute. This font will be used
with all 'say' commands if font name is not supplied
with the 'say' command. |
|
|
'size' |
| nSize |
Size of the cFont to be used. It can be
any value from 6 to 72. |
|
|
'align' |
| nAlign |
The possible values are 1-Left, 4-Right,
7-Center. |
|
|
'borders' |
| cRange |
Cells range in the format
"B4:C7" |
| nRow |
Row position of the cell within the active
sheet. Please note that cRange and nRow,nCol are
mustually exclusive. If cRange is provided, nRow,nCol
will be ignored. |
| nCol |
Column position of the cell corresponding
to 'A','B',,,'Z' cells. |
| nStyle |
One of the possible value from 1 to 5. I
exactly do not know what each value will produce which
type of border. Please make a little experiment |
|
|
'combine' |
| cRange |
Cell range to merge as one cell. |
|
|
'columnwidth' |
| nCol |
Column number. 1=A, 2=B, 13=M, 26=Z. |
| nWidth |
Width of the nCol. Please make some
experiments as what is the proper width parameter for a
desired field. |
|
|
'multiline' |
| nRow |
Row position of the cell. |
| nCol |
Column position of the cell. 1=A, 2=B, ...
, 26=Z. |
|
|
'say' |
| nRow |
Row position of the cell. |
| nCol |
Column position of the cell. 1=A, 2=B, ...
, 26=Z. |
| xValue |
Value to be displayed in the cell. |
| cFont |
Font name used to display the value.
Default is the font set by 'font' property. |
| nSize |
Size of cFont 4-72. Default is the size
set by 'size' property. |
| lBold |
Whether cFont has the bold attribute.
Default is FALSE. |
| lItalic |
Whether cFont has the italic attribute.
Default is FALSE. |
| lUnderline |
Whether cFont has the underline attribute.
Default is FALSE. |
| nAlign |
Numeric alignment within the cell. 1-Left
(Default), 4-Right, 7-Center. |
| nColor |
RGB color index as cFont attribute.
Default is rbg(0,0,0)=BLACK. |
|
|
'get' |
| nRow |
Row position of the cell. |
| nCol |
Column position of the cell. |
| cType |
Type of value to be returned.
'C'=Character, 'N'=Numeric. |
|
|
'addcomment' |
| nRow |
Row position of the cell. |
| nCol |
Column position of the cell. |
| cText |
Character string as comment value for the
cell. |
|
|
'rangecolor' |
| cRange |
Rnage of the cells. 'A3:B7'. |
| nColor |
RGB color index to denote the background
color of the cRange cells. |
|
|
'print' |
|
|
'save' |
|
|
'end' |
|
|
'visible' |
| lVisible |
Logical to specify whether Excel window be
visible or hidden. |
|
|
'setfont' |
| cFont |
Valid font name installed on the machine.
It will set the global font name for the current active
sheet. |
|
|
'cellformat' |
| nRow |
Row position of the cell. |
| nCol |
Column position of the cell. |
| nColor |
RGB color index to specify the background
color of the cell. |
|
|
'autofit' |
| nCol |
Column position of the active sheet. 1=A,
2=B, ..., 26=Z. |
|
|
'ranmultiline' |
| cRange |
Range of the cells which will accept the
multiline text data. |
|
|
'setsheet' |
| cSheet |
Set cSheet as the active sheet . |
|
|
'namesheet' |
| cSheet |
Name of the sheet which is to be renamed. |
| cName |
New name for the cSheet. |
|
|
'numberformat' |
| nCol |
Column position of the active sheet. 1=A,
2=B, ..., 26=Z. |
| cFormat |
Character string specifying format to be
used for numeric values. "0.00". |
|
|
'select' |
| cRange |
Range of the cessl to be selected
currently. |
| nRow |
Row position of the cell to be selected.
cRange and nRow,nCol are mustually exclusive options. If
cRange is provided, nRow,nCol is ignored. |
| nCol |
Column position of the cell. 1=A,
2=B, ..., 26=Z. |
|
|
| lDispAlerts |
Logical to instruct Excel whether alert dialogs be
displayed or not, e.g., 'File Save' dialog while closing the Excel
with close button. Default is TRUE. |
|
|
Returns
|
| NIL |
|
Description
|
| The function provides a precise control
over the contents, look and feel of MS Excel sheets. It also make it
possible to insert formulaes contained in Excel API with the 'say'
command. Comments can also be inserted.
Though at this moment, pictures and graph objects could not been
manipulated with Excel sheets, but hope in near future it will also be
possible.
|
|
Usage
|
local cFile, aScript, nColor
cFile := 'C:\Windows\Temp\V32Scrpt.xls'
aScript := {}
nColor := V32rgb(0,255,0)
aadd( aScript, { 'font' , 'Courier' } )
aadd( aScript, { 'size' , 16 } )
aadd( aScript, { 'align', 7 } )
aadd( aScript, { 'combine', 'A1:F1' } )
aadd( aScript, { 'borders', 'A1:F1', , , 1 } )
aadd( aScript, { 'say' , 1, 1, 'Vouch32.lib and MS Excel' } )
aadd( aScript, { 'align', 1 } )
aadd( aScript, { 'font', 'Tahoma' } )
aadd( aScript, { 'size', 10 } )
aadd( aScript, { 'say', 4, 1, 'Item' } )
aadd( aScript, { 'say', 4, 2, 'Quantity' } )
aadd( aScript, { 'say', 4, 3, 'Amount' } )
aadd( aScript, { 'say', 5, 1, 'Tables' } )
aadd( aScript, { 'say', 6, 1, 'Chairs' } )
aadd( aScript, { 'say', 7, 1, 'Cabinets' } )
aadd( aScript, { 'say', 9, 1, 'Totals', 'Ariel.bold', 14, .t., .f.,
.f., 1, nColor } )
aadd( aScript, { 'say',11, 1, 'Average', 'Ariel', 14, .t., .t. } )
aadd( aScript, { 'align', 4 } )
aadd( aScript, { 'font', 'Courier' } )
aadd( aScript, { 'numberformat', 3, '0.00' } )
aadd( aScript, { 'say', 5, 2, 10 } )
aadd( aScript, { 'say', 6, 2, 122 } )
aadd( aScript, { 'say', 7, 2, 2055 } )
aadd( aScript, { 'say', 5, 3, 210.25 } )
aadd( aScript, { 'say', 6, 3, 2055.00 } )
aadd( aScript, { 'say', 7, 3, 10545.12 } )
aadd( aScript, { 'say', 9, 2, '=SUM(B5:B7)' } )
aadd( aScript, { 'say', 9, 3, '=SUM(C5:C7)' } )
aadd( aScript, { 'say', 11, 2, '=AVERAGE(B5:B7)' } )
aadd( aScript, { 'say', 11, 3, '=AVERAGE(C5:C7)' } )
aadd( aScript, { 'align', 1 } )
aadd( aScript, { 'select', , 9, 3 } )
aadd( aScript, { 'autofit', 1 } )
aadd( aScript, { 'autofit', 2 } )
aadd( aScript, { 'autofit', 3 } )
aadd( aScript, { 'visible', .t. } )
aadd( aScript, { 'namesheet', 'sheet1', 'Vouch32' } )
// aadd( aScript, { 'print' } )
// aadd( aScript, { 'rangecolor', 'A1:F15', nColor } )
V32ExcelScript( cFile, aScript, .f. )
|
|
Tips
|
|
|