Crystal Reports .NET - Export to Excel

[nwruiz]

Hello,

I am trying to generate a Crystal Report in VB.NET (without using a ASP.NET web app) that is only used to generate an Excel spreadsheet. The server that contains my program (which is used as a batch process) does not have MS Office.

I would like to generate a Crystal Report on the fly that is populated with data stored in the variables of a custom class object (not directly from a database). Once the Crystal Report is generated, I would like to export it to an Excel spreadsheet.

I was told by a co-worker that this would be the fastest way to generate Excel spreadsheets on a server that does not have MS Office. The program is a class library that will be used to generate over 150 Excel spreadsheets that contain billing information. Also, I have an Excel template that I would like to use to generate the other spreadsheets, but I'm getting ahead of myself. I do not have any experience with Crystal Reports (and am new to .NET) and would first like to make an Excel spreadsheet.

Thanks for your help!

Nick Ruiz
Associate Integrator
PPLSolutions IT Billing and Transactions

 

[ThatRickGuy]

Hmmm. I'm not sure if CR will do an excel export with out the Excel libraries installed on the PC.

The CrystalDecisions.CrystalReports.Engine.ReportDocument object has a method called "ExportToDisk" that takes a CrystalDecisions.Shared.ExportFormatType parameter. Set that to either Excel or Excel Records Only. Excel will attempt to recreate the formating. Records Only will spit the data into an unformated spread sheet.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[nwruiz]

Thanks for your help. Do you know if it is possible to create a Crystal Report as a shell and populate the empty fields on the fly? Also, I will need to create something similar to a data grid, but the record columns would be filled with arrays of a uniform length (each array representing a column is the same length, but that length will vary at runtime, depending on the values in the object.

Nick Ruiz
Associate Integrator
PPLSolutions IT Billing and Transactions

 

[ThatRickGuy]

There are a few ways to work with crystal reports. All have different licensing issues. VS2k2/2k3 come with the CR Report Designer and a single user license. That means that you can create report templates on that machine. You can not distribute that report designer, nor can you distribute the code base report modification libraries (I can't recall their name at the moment). That's not really a big deal as most of us don't have users that are making their own reports.

Next up is the royalty free runtime libraries. These include all the tools you need to generate a CR object in memory. They are free, and can be downloaded from the CR web site. You can distribute them as you please.

After that is the Report Viewer control. I list this one seperate even though it is part of the royalty free libraries. For desktop windows applications, this control is free. In any version prior to CR XI, it is not supported on Terminal Server, and the license requires you to pay server rates (I think it's like $3000) if you do manage to get it to run on Terminal Server. There is also a similar control for web projects. It takes the CR object, exports it to PDF and streams the PDF to the web browser. That control also requires the Server license.

If you are trying to set up a web server that spits out Excel spread sheets generated from CR, you may be treading on thin ice as far as the royalty free license goes. (Although, I've never seen or heard of Business Objects auditing anyone for the library use.)

Now that the license mumbo-jumbo is out of the way: Yes, what you want to do (as I understand it) is possible.

In code, create an object of the type CrystalDecisions.CrystalReports.Engine.ReportDocument. Use the .Load(Report file and path) method to load the report template, apply any parameters you need to with .SetParameterValue(...). Apply any security you need to with .Database.Tables.Item(TableIndex).ApplyLogOnInfo(...). Then use .ExportToDisk(...) with the Excel output type and the target file name.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.