July 5, 2005

Creating Excel Files

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.

Until next time...