AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=478&pId=-1
Exporting to Excel in Crystal Reports .NET - Perfect Excel Exports
page
by Richard Dudley
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 255420/ 302

Introduction, and How Crystal Handles Excel Output


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:

  1. On the "Data" tab, drill down OLE DB (ADO) >> Make New Connection.
  2. Choose "Microsoft Jet 4.0 OLD DB Provider", and click "Next".
  3. 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".
  4. Select "xtreme.mdb" and click "Finish".
  5. Choose the "Employee Address" table, and click "Next".
  6. Add the following fields to the report: ID, City, Region, Country, Postal Code.
  7. 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 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.

Analyzing the Bad Output, and Turning Bad Into Good


Analyzing the "Bad" Output

Place your cursor in the Employee ID column.  Notice that it is one cell wide.  Now move your cursor one column to the right, to the City column.  Notice this is four fields wide.  Look at the rest of the fields the same way.  Region is four cells wide, Country is four cells wide, and Postal Code is three cells wide.

Now, highlight all of the data (not the headings--just the data).  Navigate Data > Sort, and choose Column A as your sort column, and click OK.  You should receive an error message that says "This operation requires the merged cells to be identically sized."  This message is the fast track to angry sales managers paying you a visit.  They do not want to slice and dice a spreadsheet so that the columns fit properly.  They just want it to work when the data are exported.

Turning "Bad" into "Good"

There are several report formatting options and document properties you can set programmatically for Excel output.  One of the programming options is "ExcelUseConstantColumnWidth".  When set to true, the Crystal engine will set all Excel fields to the same width, and merge as many fields as necessary to hold the entire field.  Setting this to false will let the Crystal engine put each value in a single cell of a more appropriate width.

Add the following lines of code:

 Dim objExcelOptions As ExcelFormatOptions = New ExcelFormatOptions
objExcelOptions.ExcelUseConstantColumnWidth = False
rptExcel.ExportOptions.FormatOptions = objExcelOptions 

so that your Page_Init now reads

 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 objExcelOptions As ExcelFormatOptions = New ExcelFormatOptions
objExcelOptions.ExcelUseConstantColumnWidth = False
rptExcel.ExportOptions.FormatOptions = objExcelOptions
Dim objOptions As DiskFileDestinationOptions = New DiskFileDestinationOptions 
objOptions.DiskFileName = strExportFile 
rptExcel.ExportOptions.DestinationOptions = objOptions 
rptExcel.Export() 
objOptions = Nothing 
rptExcel = Nothing
Response.Redirect("bad.xls") 

Now, rebuild and run your project.  You'll see now that there are very narrow columns between each of the data columns.  Tabbing from column to column shows that Employee ID uses a single cell, City requires two cells (a narrow one, probably C, and a wide one, probably D), Region and Country each use a wide cell, and Postal Code uses two cells.

What's going on here?  The answer is that the "variable column width" option is not perfect.  Better than before, but not perfect.  When a column is comprised of merged cells, this is usually because the Crystal engine could not figure out how to size the column properly.  There may have been a spacing issue, or the width of the data column was such that the engine couldn't figure out how to best size the column.  Fortunately, with a little design tweaking, we can cure the rest of our problems.

Design factors that influence Excel output include:

  • field width
  • field height
  • spacing between fields
  • alignment of fields in the same column

Looking at the data in Postal Code, we can see the column is wider than necessary--it really only needs one cell.  Perhaps by making it the same width as Employee ID field in our report, it will be the same width in Excel.  To do this, select the header and field for Postal code, then the header and field for Employee ID.  Make sure to select Employee ID last.  Right-click on any of the selected fields, and choose Size > Same Width.  Save your report, rebuild and run your application.  Make sure to close your browser--don't just reload the page.  Sure enough, Postal Code is only one cell wide.  However, the column titles are truncated.  This will take some trial and error on your part by stretching the columns to get right, but now you know the process.  Make sure to keep the header the same width as the field in the report file.

What about City?  Trying the same resizing trick reveals that it's already the same size as Region and Country, yet the latter two only require one cell.  As I mentioned above, another cause of merged cells is odd spacing between columns.  Zoom in to about 150%, and you'll see that all of the data columns are approximately the same distance apart--about 1 grid spacing.  Adjusting the spacing and rebuilding/running shows that we didn't fix a thing.  The answer is actually a little higher on the page--the Print Date field.  Set the Print Date field to the same width as Employee ID, save/rebuild/open, and everything should be nicely arranged, all in columns that are one cell wide.  If you need more room for a report title, stretch the title field so that its width is equal to that of several columns.  When the report is rendered, the stretched field will be merged cells, but the data columns will remain separate.

Besides sizing all the fields in the same column to the same width, these fields should be aligned neatly.  In our simple example, we probably don't have any alignment problems.  But, if you need to align a number of fields, select all the fields you want to align.  Right-click on the field you want to match the others to, and choose Align > Lefts from the menu.  You can align lefts, rights, tops, bottoms.  Try a few options and see what happens (you're not going to break anything, and this is a learning exercise).

If you see very thin rows or columns between other rows or columns, adjust the spacing between the elements.  Try and maintain a consistent spacing horizontally as well as vertically--this helps the Crystal Engine determine where to place the cell boundaries.  Ideally, there should only be one grid space between two elements.

In our little demonstration, cleaning up the few formatting problems (such as truncated column headings) will require a little patience and possibly a few cycles of resize/save/rebuild/run.  You can resize the column headings, making sure the data field is the same width.  When designing production reports, the process actually goes fairly quickly if you know what to look for.

Conclusion
  • Getting the output perfect may be an iterative process, requiring several rounds of making changes and reloading the report.
  • Make sure everything that should appear in one column is aligned neatly, and is the same width.
  • Put as few heading fields as necessary.  Excel output usually doesn't require a page number, print date, report title, etc.  If you don't need them, remove them from the report.  If you do need these fields, you'll need some patience to get everything to size correctly.
  • Try and have uniform sizes for all your fields.  Make the fields as wide as they need to be, but try and keep as few different sizes on your report as possible.  Excel columns can be resized easily with a mouse click, so it's not a big deal to have a lot of large fields (if they'll all fit on your page--see below).
  • Space the data fields as closely as possible--preferably with only one grid spacing between each data field.  Try and maintain uniform spacing, preferably a single grid space, between all elements.
  • You may need to adjust the width of your page to accommodate all the fields.  Since people will be adjusting the column width, and possibly adding summary cells, the actual page width doesn't matter too much.  Try setting the report page to landscape, and use larger paper sizes if necessary.  Visit my blog for a short example of how to "Change Paper Size and Orientation in Crystal Reports .NET".
  • In your application, you may need to build two reports to serve two purposes.  One report formatted nicely with headers and page numbers to be printed, and one report that is intended to export to Excel.

Product Spotlight
Product Spotlight 

©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-21 12:29:12 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search