Hi Erich,
On 29.03.2021 21:59, Erich Steinböck wrote:
> Hi Rony, just go ahead.
Fine.
> Maybe rather call the created Excel samp09 instead of demo - less chance of a
> clash and relates
> better to samp09.rex
Good idea!
Named it "samp09_ooRexx.xlsx" this way it is clear where it comes from and uses
the latest
file-type. A few more little changes to demonstrate more:
* made number of lines flexible,
* added a means to have the (english) formula translated to the local
language of MS Excel
(untested yet, need to get a German Excel resp. install the German Display
Language into my
English version) and use that version; otherwise a formula error will
occur, if the target MS
Excel is not English, i.e. the name of the "sum" formula has to be given in
a different
language. So if anyone reading this could test this version against a
non-English MS Excel
version I would appreciate it! Will check it into trunk in a few minutes.
Here the code that I just committed:
excelApplication = .OLEObject~new("Excel.Application")
excelApplication~visible = .true -- make Excel visible
Worksheet = excelApplication~Workbooks~Add~Worksheets[1]
colTitles = "ABCDEFGHI" -- define first nine column
letters
lastLine = 12 -- number of lines to process
sumFormula = "=sum(?2:?"lastLine-1")" -- English formula: question
marks will be changed to column letter
say "sumFormula: " sumFormula "(question marks will be changed to
column letter)"
do line = 1 to lastLine -- 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 = lastLine then do -- last row? yes, build sums
/* set formula, e.g. "=sum(B2:B9)" */
cell~formula = sumFormula~changeStr("?",colLetter) -- adjust formula
to column to sum up
cell~formula = cell~formulaLocal -- make sure formula matches
local language (e.g. sum -> summe in German Excel)
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
stringRange="A2:"colTitles~right(1)lastLine
say "formatting range:" stringRange
WorkSheet~range(stringRange)~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 for environment
variable "USERPROFILE"
fileName = homeDir"\samp09_ooRexx.xlsx" -- build fully qualified
filename
say "fully qualified fileName:" fileName -- show fully qualifed filename
Worksheet~SaveAs(fileName) -- save file
-- let the user inspect the Excel file
say "Excel sheet got saved to file, press enter to continue ..."
parse pull . -- wait for user to press enter
excelApplication~Quit -- close Excel
---rony
_______________________________________________
Oorexx-devel mailing list
Oorexx-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/oorexx-devel
