> Still, I would like to change the Excel sample09.rex, if no one objects!
>
While having some time on my hand I went ahead and updated the Excel sample
sample09.rex as follows:
excelApplication = .OLEObject~new("Excel.Application")
excelApplication~visible = .true -- make Excel visible
Worksheet = excelApplication~Workbooks~Add~Worksheets[1]
colTitles="ABCDEFGHI" -- define first nine column
letters
do line = 1 to 10 -- iterate over lines
do col = 1 to colTitles~length -- iterate over columns
colLetter = colTitles[col] -- get column letter
cell = Worksheet~Range(colLetter||line) -- e.g. ~Range("A1")
if line = 1 then do -- first row? yes, build title
cell~value = "Type" colLetter -- header in first row
cell~font~bold = .true -- make font bold
cell~Interior~ColorIndex = 36 -- light yellow
xlHAlignRight = excelApplication~getConstant("xlHAlignRight") -- get
right adjust constant
cell~style~horizontalAlignment = xlHAlignRight -- right adjust title
end
else if line = 10 then do -- last row? yes, build sums
/* set formula, e.g. "=sum(B2:B9)" */
cell~formula = "=sum(?2:?9)"~changeStr("?",colLetter) -- adjust
formula to column to sum up
cell~Interior~ColorIndex = 8 -- light blue
end
else do -- a row between 2 and 9: fill with random values
cell~value = random(999999) / 100 -- create a random decimal
value
cell~font~ColorIndex = 11 -- set from black to violet
end
end
end
-- create a format string for our numbers, use thousands and decimal
separators
formatString=
"#"excelApplication~thousandsSeparator"##0"excelApplication~decimalSeparator"00"
say "formatString:" formatString -- show format string
excelApplication~useSystemSeparators = .false -- allow our format string
to be used everywhere
WorkSheet~range("A2:I10")~numberFormat = formatString -- get range and set
its number format
-- make sure that file gets quietly overwritten in case it exists already
excelApplication~DisplayAlerts = .false -- no alerts from now on
/* save sheet in user's home directory */
homeDir=value("USERPROFILE",,"ENVIRONMENT") -- get value from environment
variable
fileName=homeDir"\demo.xls" -- build fully qualified
filename
say "fully qualified fileName:" fileName -- show fully qualifed filename
say "press enter to continue ..."
parse pull .
Worksheet~SaveAs(fileName) -- save in homeDir
excelApplication~Quit -- close Excel
It not only changes the variable names to be self-descriptive and adds more
comments, but also got
some code added in order to demonstrate the following features:
* make Excel visible (if not, programming errors will leave phantom Excel
processes as otherwise
the Excel window is not displayed and therefore cannot be closed),
* more about how to colorize background and foreground,
* right adjust (the column title cells in the first row),
* define a formatting string for numbers with tousand separators, also make
sure that Excel
accepts that format even if it does not match the system separators (will
otherwise cause a
runtime error),
* shows how to turn off the display alerts in the case the file exists
already (if the program
gets run a second time),
* uses the home directory to save the file,
* shows the fully qualified file name,
* waits for the user to press enter, such that she can take a glimpse at the
created
Excel-Spreadsheet which is very instructive if put against the code.
So, if no one objects I would replace the current version of samp09.rex with
the above one.
---rony
_______________________________________________
Oorexx-devel mailing list
Oorexx-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/oorexx-devel
