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 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 do not 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 w E'Ll Use Below. Since Neither of these options is available. We will have to Compensate with some formatting and design.
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 owna 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 whatver 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 Uses 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 ". Choise the" Employee Address "TA Ble, And Click "Next". Add The Following Fields to The Report: ID, City, Region, Country, Postal Code. We'll Skip The rest of the option, so click "finish". Your Report Will Appear in a Default Layout. Open Webform1.aspx.vb, And Add The Following Imports To The Very Top of The File: Imports Crystaldecisions.crystalReports.Engine
Imports crystaldecisions.shared address to the page_init event after the "initializationcomponent ()": DIM RPTEXCEL AS New ReportDocument
DIM 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 = Objoptions
RPTEXCEL.EXPORT ()
Objoptions = Nothing
RPTexcel = Nothing
Response.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.
