Printing with MS Excel Files

One of the data sources that you can print from is a MS Excel file. Crystal makes it simple to print using the worksheets within a spreadsheet file. It references each worksheet as if it were a table within the data source. However, there are a few quirks that you have to work around to make it function properly.

The most obvious way of printing from an Excel file is to use the Database Expert and go to the category More Data Sources. Within this category you find an option for connecting to MS Excel via DAO (data access objects). Select this option and then find the Excel file you want to print from. It will list each worksheet that has data on it. Add the worksheets to print from into the Selected Tables window. Once the worksheets have been selected, proceed to design the report as normal.

At this point, if you run your program and try to print the report, a Database Login dialog box appears. It is asking you to enter the user credentials for the Excel spreadsheet. Of course, there are no user credentials to enter so you just click on Finish. This causes a Logon Failed dialog box to appear and it won’t let you print the report. The problem is that as of the time this book was printed, Crystal Reports can’t print from an Excel file via OLE DB. Of course, this will be corrected in a future service pack.

Crystal Reports can only print Excel files via a DSN that was created using the ODBC Administrator. Unfortunately, if you create the report using a DSN as your data source then Crystal Reports can’t query the file to determine what spreadsheets are available. This means that there won’t be a list of tables that can be moved to the Selected Tables window within the Database Expert. You have to manually enter a SQL statement that extracts the data from the spreadsheet. While this is certainly possible, it isn’t pleasant. For example, the following listing shows the SQL statement to select two columns from Sheet1 and one column from Sheet2. It joins the two sheets via the ISBN column.

SELECT `Sheet1_`.`Title`, `Sheet1_`.`Publisher`, `Sheet2_`.`ISBN`
FROM `Sheet1$` `Sheet1_` INNER JOIN `Sheet2$` `Sheet2_`
ON `Sheet1_`.`ISBN`=`Sheet2_`.`ISBN`
ORDER BY `Sheet1_`.`Publisher`

In summary, each option has pros and cons. Adding an Excel file to the report via DAO gives you the benefit of letting Crystal Reports create the SQL statement to pull data from the spreadsheet and it also queries the spreadsheet to determine what fields are available for printing. The drawback is that it crashes when running the report. The other alternative is to connect via ODBC. The benefit is that this lets you print a report, but the tradeoff is that you have to do all the work of creating the SQL statement and debugging it.

The optimum solution is to use a combination of the two methods. The workaround involves the following steps.

Connect to the Excel file using DAO. In the Database Expert choose the More Data Sources category and then select the Excel option. Use the Browse button to find the file on the hard drive. Behind the scenes, Crystal Reports builds a valid SQL statement for to select data from the spreadsheet.

Design the report as you normally would with the DAO connection. All the fields will be listed in the report’s Field Explorer window and you can drag and drop them onto the report as you would any other data source.

Once you are finished designing the report and you want to run it, you have to swap out the DAO connection for the ODBC connection. This gets around the problem of not being able to run the report using DAO. If you haven’t created the System DSN via the ODBC Administrator, then do so now. When the DSN has been created, right click on the report and select Database | Set Location. Within this dialog box choose the System DSN you created as the new connection and click on Replace to swap it with the OLE DB connection. Now you can run the report without being asked to enter logon credentials for the spreadsheet. Of course, once a service pack is released to fix the problem with DAO then this work-around won’t be necessary.