1841430 - How to design a report that will export well to MS Excel from Crystal Reports?

Symptom

When exporting to Excel, extra columns or rows are added, some fields are misaligned, ...
When exporting a report to Excel, the generated spreadsheet looks different than in Crystal Reports.
How to design a report in Crystal Reports that will better export to MS Excel format?

Environment

SAP Crystal Reports 2016
SAP Crystal Reports 2020
SAP Crystal Reports 2025

Cause

Crystal Reports, and MS Excel are 2 different applications.
In Crystal Reports, objects and text can be inserted anywhere on the canvas of the report,
but in MS Excel, the data and text, displays in a row and column format,
therefore when designing a report to export to MS Excel, we need to ensure the objetcs are aligned in a row and columns,
otherwise the data and text will be misaligned when exported to MS Excel format.
To better illustrate this, the following visual examples will work better than words:

First example: Vertical alignment

Two fields A and B in different sections will be exported into 3 columns in MS Excel, Field A will be in two cells and Field B will be in two cells:

But if you will align them vertically on the report in Crystal Reports, then when exporting, it will show in 2 columns in MS Excel:



Second example: Horizontal alignment

Field B will be exported into 3 rows in MS Excel, but the following, but if you align the objects to be the same size, it will export in one row in MS Excel:



Please note that Fields with multiple lines text will be exported according to the number of lines.

Third example: Mix of vertical and horizontal alignment

The following fields show in 5 columns in MS Excel:

But when the fields are aligned as on the picture below on the report, then it will generate two columns when exported to MS Excel:

In biref, the best way to design a report that better export to MS Excel format, it's to properly align the fields horizontally and vertically on the report.

Resolution

To design a report that better export to MS Excel format, the suggestions are:

Ensure each column header align with the corresponding database field on the left, and ensure they have the same width.
( Use guidelines in the ruler to align headers with the database fields )
Ensure each database fields, text fields, in a row line up.
( Use guidelines in the ruler to align text object and fields )
Ensure all the objects in a row have data in them.

In older version, if any object is blank, the objects to the right of it will move to the left to fill up the column. The suggestions to avoid this are:

Set the default values on numeric fields so that they always show up as zeros; or
Account for nulls in any formula used on the report to make sure they return either a zero or a space

If your column header is more than one row high, all of the column headers have to be the same height.
Add a carriage-returns with at least a blank space on second the line.
When exporting with formatting, the best way to help prevent merged cells are:

Use guidelines in the ruler at the top of the report to mark the left and right of the first column.
Using the guide, start the next column at the exact same spot where the previous column ended.
Place a guide to mark the right side of this new column.
Repeat until all columns are in place.
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.
Move the bottom edge of the section up to the bottom of the objects in the row.

Keywords

xlsx, excel, xls, wisiwig, CR, export, alignment, image, logo, icon, jpg, jpeg, bmp, bitmap, crystal, report , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

SAP Crystal Reports 2016 ; SAP Crystal Reports 2020 ; SAP Crystal Reports 2025