This document describes techniques you can use to interface UnForm
with sdOffice and produce formatted Excel data while a job is being
processed by UnForm. The same techniques could be used to automate other
sdOffice tasks, such as database exporting, or mail merging using Word.
Adding sdOffice to an UnForm installation can turn UnForm into a
powerful report mining tool.
Overview
UnForm is able to interface with sdOffice using the sdofc_*.bb and
sdofc_*.pv programs (.bb programs are for BBx environments, including
bundled installations, while the .pv programs are for ProvideX environments).
For Excel, the program to use is sdofc_e.bb.
The sdofc*.bb programs work in different ways depending on the environment
they are operating in. If UnForm and sdOffice are running on the same
Windows workstation, then the interface will work automatically. If
UnForm and sdOffice are on different systems, then a network socket
is used to communicate. On a Unix system in foreground mode, sdofc_e.bb
can usually figure out the network address of a user's workstation based
on information provided by the who command. However, since UnForm is
generally run in a pipe, there is no user terminal associated with the task.
In this case, the sdofc_e.bb program needs to be told the IP address or host name
where sdOffice is running. It looks for this address in the SDHOST environment
variable or the $sdhost global string (stbl or gbl functions are used to
create global strings).
Details
Most integration to Excel will involve sending row-oriented data derived
from the content of the report. In addition, however, there are some
initialization tasks and closing tasks to perform. The job is therefore
broken down into three types of CALLs:
- CALLs that initialized the connection in a prejob code block.
- CALLs that send data from the report lines.
- CALLs that close the job in a postjob code block.
The following code blocks can be inserted into the sample Aging Report rule set
in sample.rul.
Initialization
At the beginning of the job, initialize Excel and place heading data
in the first row. Note that in practice, you would place the "show"
command at the end of the job for performance reasons. It is used
at the beginning of this sample for demonstration puposes.
prejob{
# set to path to your sdoffice *.bb programs
sdo$="/u0/sdofc/sdofc_e.pv"
# You can set the environment variable SDHOST, or use this
# stbl function to define the sdOffice server address
# x$=stbl("$sdhost","xx.xx.xx.xx")
# initialize excel (if error is returned, assume we can't do it)
call sdo$,err=prejob_done,"newbook","",errmsg$
if errmsg$>"" then goto prejob_done
# sdOffice appears to be live
sdofc_init=1
call sdo$,"show","",""
call sdo$,"setdelim |","",""
call sdo$,"writerow ID|Name|Phone|Over 60|Total","",""
call sdo$,"format row=1,font=Arial,size=12,bold","",""
prejob_done:
}
Data rows
UnForm reads data one page at a time and loads it into the text$[] array.
After the array is filled, the prepage code block is executed. Using the
data in text$[all], export information is sent to Excel. In this example,
using the sample Aging Report, the code block is scanning the report for
two types of lines: customer header lines and customer total lines.
When a header line is found, the customer ID, name, and phone number are
saved in variables. When a total line is found, the numbers on that
line are calculated, and an export line of the customer information and aging
information is sent to Excel.
prepage{
# if prejob hasn't initialized sdoffice, skip this code
if sdofc_init<>1 then goto sdofc_complete
for row=1 to 66
ln$=text$[row]
# customer heading row contain phone numbers
x=mask(ln$,"\(...-...-....\)")
while x
# this is a customer heading row
custid$=mid(ln$,1,6)
custname$=trim(mid(ln$,8,30))
custphone$=trim(mid(ln$,38,14))
x=0
wend
# totals start with 50 plus spaces followed by digit,
# literal ".", and 2 digits
x=mask(ln$,"^"+fill(50)+".*[0-9]\.[0-9][0-9]")
while x
# this is a customer total row
amount60=cnum(mid(ln$,87,11))
amount90=cnum(mid(ln$,98,11))
amount120=cnum(mid(ln$,109,11))
over60=amount60+amount90+amount120
total=cnum(mid(ln$,120,11))
# build export line and send to Excel
export$=custid$+"|"+custname$+"|"+custphone$+"|"
export$=export$+str(over60)+"|"+str(total)
call sdo$,"writerow "+export$,"",""
x=0
wend
next row
sdofc_complete:
}
End of job
When all pages have been processed, UnForm executes the postjob code block.
You can place any number of CALLs to sdofc_e.bb to do final formatting,
printing, or other processing. In this example, the columns are formatted
and a title is inserted. The "leaveopen" command instructs sdOffice to
leave Excel open when the job is complete.
postjob{
# if prejob hasn't initialized sdoffice, skip this code
if sdofc_init<>1 then goto sdofc_complete2
call sdo$,"leaveopen","",""
call sdo$,"format autofit","",""
call sdo$,"format col=1,numberformat=@","",""
call sdo$,"format col=4,numberformat=""###,##0.00""","",""
call sdo$,"format col=5,numberformat=""###,##0.00"",bold","",""
call sdo$,"insertrow 1","",""
call sdo$,"writecell range=A1,value="+$22$ \
+"Over 60 Aging Values as of "+date(0)+$22$,"",""
call sdo$,"mergecells range=A1:E1","",""
call sdo$,"format range=A1:E1,center,size=15,bold","",""
sdofc_complete2:
}
UnForm and CirrusPrint are registered trademarks of SDSI. General and
MailCall are trademarks of SDSI. Other product names
used herein may be trademarks or registered trademarks of their respective
owners.
SDSI uses Google analytics and conversion tracking on some site pages.
Site design by ZiMDesign.
