Crystalkeen logo; we offer Crystal reports viewers and schedulers
 
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

  Resources  
Best sellers:
cView
cViewSERVER
cViewBATCH

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
cViewSUITE Ppt

 

CrystalReports
on Steroids

Crystal Reports: Export to Excel

Exporting your reports to another format is one of the really useful features of Crystal Reports. We know of reports that generate CSV files to update databases, or even load an ODBC table direct from CR. But when you do this, you have to comply with the limitations of both Crystal Reports and the other application.

For example, Excel is a very popular export format. But a limitation of Excel is that you can’t have more than 65000 rows in a worksheet. So if your Crystal Report has more than that, it will fail to export. If each record is double spaced or takes up more than one Excel Row, the limit drops.

Unset “Free Form Placement” on all your sections to improve your export to Excel. Unticking that option in the Section.

Expert will force you to place objects in your report in a grid of rows, and this will export more cleanly to Excel.

The other useful technique is to set your printer settings to a wide page size (we use A3 landscape). This can give you the extra columns you need for that Excel Export. You do not need to have the wide physical printer on your network.

 

Better Control of your Export to Excel with Grid UFL

One of the things a Crystal Report is often used for is to do a complex data extract or calculations on some data and then to export that to Excel for additional manipulations there. Each new version of Crystal Reports has included improvements to this, but still we hear of it not being in the exact format that the user requires.

To give better granulated control of what is output, we have developed Grid UFL to use in your formulas.

Regular readers will know how we develop and market User Function Libraries to use in the Formula Editor to solve some of these interesting problems.

Our existing Disk Library lets you create a disk file, line by line. With some additional work, you can format the disk lines to any format you like. We have now made this easier if you want your output exported to Excel, CSV, Text or Tab delimited files. We have packaged this up as a Grid User Function Library.

The new functions treat your saved data as a Grid. Use the GridCellClear (rows,cols) to declare a work area of a predetermined size. This formula should normally be in your report header section.

Then use other formulas in your group and details section to save values in the grid.

Use GridCellLoadRawValue (row, col, value) to store a value in the working area. You could also use GridCellLoadDelimitedValue(row, col, value, delimiter) to automatically put quote marks or some other field delimiter around the value.

Then save it all to the disk file with GridCellSave(Filename,Separator). Use a comma, tab character or any other ASCII value to separate each value on each line of your disk file. We have examples for the next edition.

As this processes as the report is processing, you could use the GridCellClear in a Group header section, and the GridCellSave in the matching Group Footer to created a different file for each group value.

Just watch out for those Repeating Group Header sections or you will find your grid work area resetting on as each group repeats on a new page.

A free trial version is available for download. The trial version has a limit of 20 rows and 20 columns in the output file so you can see how it works but the full version has no restriction.

This UFL is compatible with all versions of Crystal Reports (later than the very old version 5).

 

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.