| Coding Excel File Output | KEVIN RESCHENBERG
07-11-2005 |
Last week's
post discussed two of the most common formats for
Excel-ready files—the CSV and tab-delimited formats.
Today I'll show how to write the code to build them
and describe four different methods for writing files
for Excel.
The first method is to open an output file and
explicitly write both the data and the delimiters.
If using SQR, be sure to create the file using the
OPEN, WRITE and CLOSE statements—not PRINT.
open 'myfile.csv' as 1 for-writing record=1000:vary
...
write 1 from $emplid ','
'"' $name '",'
'"' $address '",'
...
! Output record looks like this:
! 12345,"Doe,John","123 Main St." ...
close 1
This could be simplified if we used
the tab character as a delimiter, since then the
surrounding quotes wouldn't be necessary. I'll also
change the code to use a STRING statement instead of
specifying the delimiter after each field:
let $tab = chr(9)
string $emplid
$name
$address
by $tab into $record
write 1 from $record
The second way to write a CSV file
works with SQR only. If the user always chooses output type
"CSV" when submitting the request to the Process
Scheduler, we can use very simple code to create the
file. This time we use PRINT instead of WRITE. Just
PRINT the values as if you were writing a normal report. The column positions
don't matter—we could simply PRINT the first value in
column 1, the second in column 2, and so on. SQR will
then automatically add the comma delimiters and
surround values with quotes where necessary.
One disadvantage of all of the options discussed so far
is that they don't allow for any type of control over
the appearance of the output. You can't specify colors
or fonts, for example. However, there are ways
of dealing with this issue. We can create a SYLK (symbolic link) file
or an XML file.
The SYLK and XML formats are understood by Excel and
support many different types of cell formatting. These
file formats are a little complicated, though.
Fortunately, some people have done the heavy lifting
for us and have created procedure libraries for us
to use.
Take a look at the following. From each link you can
download the procedure libraries (SQCs), examples,
and other documentation. After trying the examples,
you should be able to choose the method
that is easiest for you to use and gives you the formatting control
you need.
XML method, by David Vandiver: www.sourceforge.net/projects/sqr2xml
SYLK method, by James Womeldorf: www.ontko.com/sqr/sylk.html
|
