To run this example you have to catalog the Microsoft Excel Object Library under a module named MSEXCEL.
The PBL code shows how to use Excel from the BPM system.
do
// Open the sheet open MSEXCEL.Application.workbooks using
"C:\\tmp\\invoice.xls"
// Set it visible (for debugging purposes)
// MyExcel.Application.visible = true
// Get the active sheet cells
cells = Worksheet(
MSEXCEL.Application.activeWorkbook.activeSheet).cells
// Ask for some client data, this could be taken
// from a database
input "Name:" name,
"Address:" address,
"State: " state,
"Zip: " zip,
"Phone:" phone,
"Order No.:" orderNo
using title = "Enter product"
// Fill the sheet, the getItem method, takes two args
// (in the case row and column), and returns a
// MSEXCEL.Range object that matches the criteria
// for selection
// Fill Name
getItem cells using 4, 3 returning temp
cell = MSEXCEL.Range(temp)
cell.value = name
// Fill Date
getItem cells using 4, 9 returning temp
cell = MSEXCEL.Range(temp)
cell.value = 'now'
// Fill Address
getItem cells using 5, 3 returning temp
cell = MSEXCEL.Range(temp)
cell.value = address
// Fill State
getItem cells using 5, 5 returning temp
cell = MSEXCEL.Range(temp)
cell.value = state
// Fill Zip
getItem cells using 5, 7 returning temp
cell = MSEXCEL.Range(temp)
cell.value = zip
// Fill Order No.
getItem cells using 5, 9 returning temp
cell = MSEXCEL.Range(temp)
cell.value = orderNo
// Fill Phone
getItem cells using 6, 3 returning temp
cell = MSEXCEL.Range(temp)
cell.value = phone
// Now we enter some data about products
for i in 10..21 do
qty = 0
product = ""
price = 0
// We ask for it
input "Quantity:" qty, "Product:" product, "Unit Price:" price
using title = "Enter product", buttons = ["Ok", "Finish" ]
returning buttonPressed = selection
exit when buttonPressed != "Ok"
// And then we fill the sheet
// Fill Qty.
getItem cells using i, 2 returning temp
cell = MSEXCEL.Range(temp)
cell.value = qty
// Fill product
getItem cells using i, 3 returning temp
cell = MSEXCEL.Range(temp)
cell.value = product
// Fill price
getItem cells using i, 8 returning temp
cell = MSEXCEL.Range(temp)
cell.value = price
end
// We finally ask for the shipping cost
price = 0
input "Shipping cost" price
using title = "Invoice"
// Fill Shipping
getItem cells using 23, 9 returning temp
cell = MSEXCEL.Range(temp)
cell.value = price
display "What do you want to do with this sheet?"
using title = "Invoice finished",
options = ["Preview", "Print"],
default = "Preview"
returning buttonPressed = selection
if buttonPressed == "Print" then
//Print it
printOut Worksheet(
MSEXCEL.Application.activeWorkbook.activeSheet)
else
//Preview it
MSEXCEL.Application.visible = true
printPreview Worksheet(
MSEXCEL.Application.activeWorkbook.activeSheet)
end
//Mark it as saved
MSEXCEL.Application.activeWorkbook.saved = true
// Just in case, we ask Excel to quit
quit MSEXCEL.Application
end