Skip to Content

How to WYSIWYG SAP Crystal Reports Export to XLS

Purpose

The purpose of this Jive Document is to describe best possible lay out for reports such that an export to Excel will match the report view.

Overview

Correct placement of fields on the report design pane is critical to WYSIWYG export to Excel file format. The information in this Jive Document is based on an explanation and an internal document used by Vitaly Izmaylov, Senior Support Engineer AGS Primary Support.

Recommended Placement of Fields on the Report Design Pane

Example 1; Two fields A and B in different sections

When this report is exported to Excel, field A will be in two cells and field B will be in two cells, thus resulting in a total of three columns being taken up in the exported file. To resolve this issue, align fields A and B vertically:

Example 2; Horizontal alignment

When this report is exported to Excel, field B will be exported into 3 Excel lines. To resolve the issue align the fields as below:

Note that fields with multiple lines of text will be exported according to the number of lines. See the following example:

Example 3; three column alignment

A report design such as above will result in five columns being taken up in an Excel export. To resolve this issue, align the fields in the report as follows:


Additional Information

The following information has been added on January 22, 2014. The information is a straight copy and paste from Dell Stinnett-Christy's Facebook:

Formatting a Crystal Report for Export to MS Excel

Crystal Reports enables the export of data to MS Excel in two different ways – with formatting and “data only” without

formatting. One of the major frustrations I’ve had is that columns don’t line up after the export. The other is that when

exporting with formatting, there are lots of merged cells making it impossible to manipulate the data without first doing

some major reformatting. So, I’ve developed a set of simple formatting guidelines that I use to make sure my reports

export to Excel cleanly.

1 If at all possible, use a Cross-Tab. They’re guaranteed to export well without any special formatting.


2. Make sure the data and column header in each column line up exactly on the left and that each of the objects is the

same width. Set up guides on the ruler above the report to help with this.


3. Make sure that all of the objects in a row line up exactly. Set up guides on the ruler to the left of the report to help

with this.


4. ALL of the objects in a row MUST have data in them. If any object is blank, the objects to the right of it will move to

the left to fill up the column. I’ve done this a number of ways:

a. Set the default values on numeric fields so that they always show up as zeros.
b. Account for nulls in any formula used on the report to make sure they return either a zero or a space.
c. Sometimes I’ve had to put a tilde (~) or other meaningless character in a text block that is the same size and

location as the object that may be blank. Set the font to the same color as the background so that it doesn’t show

up when viewing the report and suppress the text block when the object it’s replacing is not null.

5. If your column header is more than one row high, ALL of the column headers have to be the same height. Just

adding carriage-returns, doesn’t solve the issue, there has to be at least a blank space on the line.


6. When exporting with formatting, the best way to help prevent merged cells is to do the following:

a. Use guide in the ruler at the top of the report to mark the left AND right of the first column.
b. Using the guide, start the next column at the exact same spot where the previous column ended.
c. Place a guide to mark the right side of this new column.
d. Repeat until all columns are in place.
e. For rows, make sure that the objects are placed at the very top of the section –right click on an object; select

“Size and Position”; set the Y coordinate to 0.0. Then align all of the other objects in the row with the top of the

one just set.
f. Move the bottom edge of the section up to the bottom of the objects in the row.

Designing a Crystal Report so that it exports well to MS Excel requires a little bit of planning and a lot of precision.

Some of the techniques I’ve outlined will cause a report to not look as clean when viewing it in Crystal, but will make

it export in a clean, usable fashion.

Conclusion

When designing a report, proper alignment of fields and other object will result in correct WYSIWYG export to Excel file format.

Related Content

Related Documents

Exporting to Microsoft Excel with Crystal Reports 10 and Later

<a href="http://scn.sap.com/community/crystal-reports/blog/2013/03/27/tips-to-export-crystal-report-rpt-to-excel?utm_source=twitterfeed&utm_medium=twitter#comment-348026">Tips to Export Crystal Report (.rpt) to Excel</a>

Related Notes (KBAs)

1701429 - Display string values are not export to MS Excel data only format from crystal Cross-Tab report

1233966 - Export difficulties when exporting a Crystal report to Excel; a conditionally formatted crosstab summary field is ignored

1216567 - Is it possible to export one report to multiple Excel worksheets in Crystal Reports?

1339812 - Export to Excel Data Only, does not adjust the column width

Tags: