SAP Knowledge Base Article - Public

1198296 - Column misaligned when exporting a report to Excel from Crystal Reports

Symptom

  • Columns misaligned.
  • Data does not match the headers.
  • When exporting a report to MS Excel format, from Crystal Reports, the columns are misaligned, and there is blank columns.

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020

Reproducing the Issue

  1. In Crystal Reports, create a report off any data source.
  2. Insert a couple of database fields in the Details section, and headers in the Page Header section.
  3. Export the report to MS Excel format.
  4. When opening the exported report in MS Excel, notice some of the column header does not match the data under.

Cause

  • Columns are misaligned because the way the report is designed.

  • Crystal Reports and MS Excel are two different format. In Crystal Reports you can insert fields and objects anywhere in a section, where in MS Excel, it is a column and row format. Due to the difference in format, objects will not align as expected if the report is not designed in a way that will ensure each objects are aligned together.

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 order version, if any object is blank, the objects to the right of it will move to the left to fill up the column. Below are suggestions to avoid this:
        
      • 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 is to do the following:
      • 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

crystal report export into excel , 3385260 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020