| Creating Excel Files | KEVIN RESCHENBERG
07-05-2005 |
Many users prefer to work with Excel files rather than
printed reports, and so you may be asked to produce
some reports in that format. Whether you are using
SQR, PeopleCode, or another development tool, there
are several ways of doing this.
This week I'll describe the format of what we
call an "Excel file," and then next Monday I'll get
into the actual coding details.
The simplest Excel-format file is a "CSV" or
comma-separated values file. If you produce a file in
this format and name it with a .CSV extension, Excel will
know how to handle it. In a CSV file, the fields are
separated with commas. A value that contains a comma
must be delimited with quotation marks ("). (Quotes can
also be used if the value doesn't contain commas but
could contain them. This can simplify your coding.)
Empl ID,Name,Address
12345,"Doe,John","123 Main St."
23456,"Smith,Mary","456 Maple St."
Even though this is a simple text file,
be sure to name it with the .CSV extension. Otherwise
Excel will ask the user about the delimiters or will
simply load each line into a single cell, depending on
how the user opens the file.
Some people prefer to avoid the quoted string complication
by using a different delimiter such as a pipe (|):
Empl ID|Name|Address
12345|Doe,John|123 Main St.
23456|Smith,Mary|456 Maple St.
Excel doesn't recognize a pipe
as a normal delimiter. If you use a pipe (or some other
delimiter), don't name your file with the .CSV or
.XLS delimiters. Call it .TXT (or something else)
instead. Then start up Excel and open the file from
within Excel. A dialog will lead you through the
process of identifying the delimiter and describing
the format of each column.
Normally we'll avoid using pipes or other non-standard
delimiters for this reason—it's extra work for the
user. However, there is one situation in which you
might want to do this. If you have fields that
consist of digits with leading zeros, Excel will
try to convert these to numbers and will drop the
leading zeros. Opening the file and going through
the delimiter/format dialog allows you to specify the
column as type "text" and keep the leading zeros.
This eliminates the #1 annoyance of using Excel
with generated data files.
One good delimiter is the tab (ASCII character 9).
With this delimiter, you don't need to quote the
strings and Excel will open the file directly.
The example might look like this if you open the
file in a text editor:
Empl ID Name Address
12345 Doe,John 123 Main St.
23456 Smith,Mary 456 Maple St.
Note that the columns might not
"line up" consistently when viewed in a text editor. (Compare
the alignment of the column headings with the data in this
example.)
If you use tab
characters as delimiters, be sure to name your file with a
.XLS extension. This will cause the file to be
opened directly by Excel when the user double-clicks
on the file name, with no questions to the
user.
To summarize:
- If using comma delimiters,
- Name the file .CSV
- Quote strings containing commas
- Open the file by double-clicking on it
- If using tab delimiters,
- Name the file .XLS
- Don't quote strings
- Open the file by double-clicking on it
- If using other delimiters,
- Name the file .TXT
- Don't quote strings
- Open the file from within Excel
- Identify columns as "text" where appropriate
The information given here should give you enough to write
the code needed to produce a simple Excel file. But there are
at least four different ways of doing this.
Some of these methods support cell formatting with
colors, fonts, etc. (Neither the CSV format nor
tab-delimited .XLS files provide this formatting.)
Next week we'll continue this discussion with details
on the various methods.
|
