SAP Knowledge Base Article - Public

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 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020

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.
           
      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; or
      • 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.
           
    • 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.
          
    • 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.
          
          
  • For more information, I will suggest to read the following document:

    http://scn.sap.com/docs/DOC-39608

    Also, see the more detailed document on how to design a report that better export to MS Excel format, available at:
       
    http://scn.sap.com/docs/DOC-6687

Keywords

xlsx excel xls wisiwig, CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

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