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

原创 2004年10月20日 07:34:00

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.ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile 
rptExcel.ExportOptions.ExportFormatType = ExportFormatType.Excel 
Dim objOptions As DiskFileDestinationOptions = New DiskFileDestinationOptions 
objOptions.DiskFileName = strExportFile 
rptExcel.ExportOptions.DestinationOptions = objOptions
objOptions = Nothing 
rptExcel = Nothing

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.

Crystal Report中导出Excel遇到的问题和解决方法

最近的项目中用到了Crystal Report导出Excel,而且user对Excel format的格式要求非常高,以下是我们遇到的和解决方法 线和线框无法导出。在Crystal Report...
  • sqlxx
  • sqlxx
  • 2009年12月09日 23:14
  • 3997


近期在做水晶报表时,作者用到了水晶报表文件导出功能。在网上一搜,资料还真多,这些资料不仅多,而且非常的杂乱,让人看得非常不舒服。于是在此将其作了一点汇总和整理,以供大家参考。写的不好,请多包涵。 命...
  • SunCherryDream
  • SunCherryDream
  • 2015年04月29日 18:46
  • 3380

[转]Crystal Reports basic for Visual Studio 2008部署

本文转自:http://www.cnblogs.com/whhcode/archive/2008/01/08/1030661.html 原作者:红着眼的狼近日帮朋友写了一个基于Web的小程序,其中...
  • babyt
  • babyt
  • 2008年02月19日 15:03
  • 2998

Crystal Report 在 VS 2010 中的使用

  • moon66sun
  • moon66sun
  • 2013年12月31日 14:17
  • 1034

Crystal Report制作使用

Crystal Report制作使用本文主要划分为以下六部分:一、Crystal Report for .NET 的功能二、Crystal Report总体结构三、报表数据访问执行模式四、报表类型五、...
  • jackeyabc
  • jackeyabc
  • 2007年10月25日 09:12
  • 2638

Crystal Reports for Eclipse(1)

Crystal Reports for Eclipse(1) Crystal Reports for Eclipse(1)1、介绍Crystal Reports for Eclipse用于和数据库共同...
  • chszs
  • chszs
  • 2007年02月14日 17:42
  • 3972

Crystal Reports Runtime Packages----businessobjects

Crystal Reports Runtime Packages 转至:http://resources.businessobjects.com/support/additional_download...
  • lJean
  • lJean
  • 2011年06月03日 13:34
  • 7353

Crystal Reports 产品家族

目录:一、Crystal Reports 9二、Crystal Analysis Professional三、Crystal Enterprise 9四、Crystal Enterprise For ...
  • haibodotnet
  • haibodotnet
  • 2004年01月02日 19:44
  • 4310

Crystal Reports basic for Visual Studio 2008部署包

  vs2008自带的水晶报表是10.5版的,部署包位置 在C:/Program Files/Microsoft SDKs/Windows/v6.0A/Bootstrapper/Packages 目录...
  • xiaoK
  • xiaoK
  • 2008年09月10日 10:09
  • 6282

如何利用Crystal Report程序来生成PDF版本?(

 如何利用Crystal Report程序来生成PDF版本?(How to generate PDF version of Crystal Report programmatically?) Repo...
  • yanhuaju9
  • yanhuaju9
  • 2007年04月24日 09:41
  • 959
您举报文章:Exporting to Excel in Crystal Reports .NET - Perfect Excel Exports