MS Excel 97-2000
Excel is the most popular spreadsheet program for Windows. It makes it easy for the everyday user to take a large amount of data and perform all types of analysis on it as well as creating pivot tables and charting the results. Exporting Crystal Reports to Excel is very popular because it lets you, the report designer, do the work of aggregating a large amount of data into a user friendly format. This makes it easy for an end user to get the report data in an Excel spreadsheet and work with it. If the end user was responsible for taking all the raw data and processing it directly in Excel, it would probably be too difficult for many people. Exporting from Crystal Reports lets less technical users benefit from your Crystal Reports expertise.
The ideal way to export a report to Excel is to do some planning prior to creating the report. By making some design decisions prior to creating the report, the export process can go much easier and give you more accurate results. Of course, you can send any type of report to Excel, but some reports might not convert as well as others. The best thing for you to do is first read an excellent whitepaper written by Business Objects. The 16 page file “scr_ExportExcel.pdf” gives expert advice on report design, shows both good and bad examples, and gives you troubleshooting advice. The web address for the whitepaper is:
If you don’t have time to read their whitepaper, here are a few highlights from it.
- Think of your report as a spreadsheet and place report objects accordingly. Objects should be placed on the report using a grid format and place them side by side without overlapping.
- Turn on rulers and have report objects snap to the guidelines. By simulating the appearance of a spreadsheet in design mode, it is easier to make the report fit the format of a spreadsheet and line up accordingly.
- Give objects in the same row the same height. By using objects with consistent heights, the Excel rows can appear next to each other without creating additional white space between rows.
When exporting to Excel, the export options dialog box lets you modify how to convert report objects into excel columns. It is shown in Figure 15-5.
Figure 15-5. Excel format options.
The top portion sets the column widths. You can either have Crystal Reports analyze the report objects and best match the column widths for each cell, or you can simply set all the column widths to be a specific size. The option you choose is dependent upon your report layout. If the report prints out a series of similar numbers along each column, then you can use a fixed column width for the entire report. After the report is exported you can view it in Excel and it’s very possible that you will only have to make minor changes for it to be finalized. On the other hand, if the report prints a variety of data with different sized report objects, then you want Crystal Reports to format each column based upon the size of the report object.
When choosing the option to make the column widths based on the report objects, you can choose which report objects get priority for setting the column sizes. The default selection, whole report, evaluates the report objects in the report and sets the Excel columns to get the best match. If you choose a particular section (e.g. the Details section), then Crystal Reports gives a greater priority to those report objects when setting column width. In general, it’s a good idea to use the Details section as the primary area for setting column widths since that is where the core data is located. But in many cases, Crystal Reports does a pretty good job of getting most report objects to fit onto the spreadsheet in a way that closely matches the original report.
The third option on the dialog box sets how often page headers and footers appear. You can either have them appear only at the top and bottom of the spreadsheet (i.e. just once for the entire file), or have them repeat throughout the report. The benefit of having them repeat is that the spreadsheet best mimics the look of the report. But if you are expecting to do data analysis in Excel, then don’t repeat the header and footer because you want all the detail data to be together from the beginning to the end.
The lower portion of the dialog box gives you three more ways to format the report. The first option is to have Excel insert page breaks so that when you print the spreadsheet then the pages mimic the original report. The second option lets you convert dates to strings in their original format. Many times when Excel sees a date value in a field, it reformats it. If you find that Excel’s reformatting is conflicting with how you want the dates to appear, enable this option. This forces Excel to show dates exactly how they are displayed on the report. The last option lets you enable or disable the display of gridlines in the spreadsheet. And of course, the dialog box also lets you set the page range to export.
