Exporting to Excel in Crystal Reports .NET - Perfect Excel Exports

Introduction

I recently received some feedback regarding a close-to-launch project.  The testers reported that they could not manipulate the Excel spreadsheet that was generated from Crystal Reports.  In particular, they received the error "This operation requires the merged cells to be the same size." when they tried to sort the data.  This report looked great as a PDF, but when I tested the workbook, I saw their problem.  Although the report looked good at first glance, close inspection showed there were all sorts of weird sized columns and rows stuck in here and there.  Therein lies the problem, and fortunately, a solution is not too difficult even in Crystal Reports .NET.  The entire demo will take about 30 minutes.

This article assumes you have a basic working knowledge of the Visual Studio .NET IDE, adding controls to pages, and creating Crystal Reports.  You will also need to have the eXtreme Traders database in some form available to you.  The Access version is installed with your Crystal Reports .NET installation, and we'll use this database in our example.  I use Visual Studio .NET 2003, and although I don't anticipate any differences between VS 2002 and VS 2003, please contact me if there are any.

How Crystal Reports Handles Excel Export

In Crystal Reports .NET, the Excel export attempts to perform a WYSIWYG translation of the report.  This can sometimes lead to some interesting results in Excel, since a Crystal Report places objects using x/y coordinates, but Excel uses line-by-line row/column placements.  I am not sure why Crystal engineers thought WYSIWYG in Excel was a good idea, since we have our choice of PDF, RTF or HTML outputs.  Intuitively (to me anyway), people want data in Excel so they can manipulate it further, not because the report maintains its pretty formatting.  This may have been done in order to entice us to upgrade to a full version of CR, or it may be an example of giving too many features where fewer is better.

Crystal Reports 9 has an "Extended Excel" option, and CR 10 replaces this option with one called "Data Only".  In either case, the objects are rendered as closely as possible to row/column format, making the exported spreadsheet more usable without having to resort to some of the tactics we'll use below.  Since neither of these options is available to us in CR .NET, we will have to compensate with some formatting and design changes.

Business Objects has released a whitepaper regarding Excel exports in CR 10.  This whitepaper can be downloaded from http://support.businessobjects.com/communityCS/TechnicalPapers/cr10_export_excel.pdf.asp, and provides some useful information.  Keep in mind that the whitepaper discusses features found in CR 10 that are not found in CR .NET.  The troubleshooting section in particular is useful and general enough to apply to CR .NET as well as CR 10.  We'll use some of the information from the whitepaper as well as some of my own experience in a simple example.

Setting Up the Demo Project

The instructions in this section are necessarily brief where covered in other articles on this website.  For this demo, we will use the xTreme database that is included with the Crystal Reports .NET installation. In Visual Studio, create a new ASP.Net web application.  In my case, I've named the project "Excel", but you can name yours whatever you like.  Next, add a new Crystal Report to your project.  We'll name this one "bad.rpt" to signify the bad layout.  Use the Standard Report Expert to create your report, and follow these steps: On the "Data" tab, drill down OLE DB (ADO) >> Make New Connection. Choose "Microsoft Jet 4.0 OLD DB Provider", and click "Next". VS 2002 users should browse to "C:/Program Files/Microsoft Visual Studio .NET/Crystal Reports/Samples/Database", and VS 2003 users should browse to "C:/Program Files/Microsoft Visual Studio .NET/Crystal Reports/Samples/Database". Select "xtreme.mdb" and click "Finish". Choose the "Employee Address" table, and click "Next". Add the following fields to the report: ID, City, Region, Country, Postal Code. We'll skip the rest of the options available to us for this sample, so click "Finish". Your report will appear in a default layout.  Save the report file. Open WebForm1.aspx.vb, and add the following imports to the very top of the file: Imports CrystalDecisions.CrystalReports.Engine Imports CrystalDecisions.Shared Add the following code to the Page_Init event after the "InitializeComponent()": Dim rptExcel As New ReportDocumentDim strExportFile As String = Server.MapPath(".") & "/bad.xls" rptExcel.Load(Server.MapPath("bad.rpt")) rptExcel.ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile rptExcel.ExportOptions.ExportFormatType = ExportFormatType.Excel Dim objOptions As DiskFileDestinationOptions = New DiskFileDestinationOptions objOptions.DiskFileName = strExportFile rptExcel.ExportOptions.DestinationOptions = objOptionsrptExcel.Export() objOptions = Nothing rptExcel = NothingResponse.Redirect("bad.xls") Grant "Modify" permissions on your project's directory under IISRoot to the ASPNET user (this is required to you can export an Excel file to the hard disk).  Save all the files, compile and run your project.  If all went well, you should be prompted to open an Excel spreadsheet.  Go ahead and open the Excel spreadsheet.