excelApplication = .OLEObject~new("Excel.Application")
excelApplication~visible = .true -- make Excel visible
Worksheet = excelApplication~Workbooks~Add~Worksheets[1]
colTitles = "ABCDE" -- define first nine column letters
lastLine = 10 -- 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)"
xlHAlignRight = excelApplication~getConstant("xlHAlignRight") -- get value of "horizontal align right" constant
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 = 38
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~Interior~ColorIndex = 20
end
else do -- a row between 2 and 4: fill with random values
cell~value = random(10) *2 -- create a random decimal value
cell~font~ColorIndex = 11
end
end
end
sumCell = WorkSheet~range("A"lastLine) -- get sum-cell of column A
if sumCell~text = "#NAME?" then
do
say
say "** Excel reports a '#NAME?' error for the 'sum' function! Probable cause: **"
say "** your local Excel user interface language is not set to English, therefore you need **"
say "** to adjust the function name 'sum' in the variable 'sumFormula' to your user interface **"
say "** language and rerun this program (e.g. in German you need to rename 'sum' to 'summe') **"
say "** sumCell~formula:" sumCell~formula
say "** sumCell~text: " sumCell~text
say "** sumCell~value: " sumCell~value
say
end
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
excelApplication~DisplayAlerts = .false -- no alerts from now on
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