SAP Knowledge Base Article - Public

2012010 - Incorrect colors used in MS Excel, when copying data of an exported report to MS Excel from Crystal Reports

Symptom

  • Colors different.
  • Colors not retained when copying data from one MS Excel spreadsheet to another one.
  • When opening an exported report from Crystal Reports to MS Excel format, and copying and pasting the exported result to another MS Excel spreadsheet, the colors are not the same.

Environment

  • SAP Crystal Reports 2011
  • 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. Format a database field, a text object, formula field, etc, to a specific color.
  3. Export the report to MS Excel format.
  4. Open the exported report in MS Excel, notice the colors in the spreadsheet are exactly the same colors on the report.
  5. Copy and paste the information to a new MS Excel spreadsheet.
  6. Notice the colors are now different.

Cause

  • This is happening because MS Excel uses a palette of 40 colors, and when exporting a report to MS Excel format, if there are specific colors that does not exist in the default MS Excel color palette, it creates a specific color palette for the spreadsheet that reflects the colors used on the report. When creating a new spreadsheet, MS Excel will then use the default color palette, and therefore this is why the colors are different when copying the information from the exported spreadsheet, to a new MS Excel spreadsheet.
  • In brief, the reason the colors are different in different MS Excel spreadsheet, it is because the spreadsheet uses different color palette.

Resolution

  • This situation is out of the control of Crystal Reports, as this is happening because a new spreadsheet in MS Excel will always use the default MS Excel palette color. The suggestions to change the colors are to:
    • Change the colors of the objects in MS Excel to the desired colors; or
    • Change the MS Excel color palette to use the same one as the MS Excel spreadsheet generated by Crystal Reports when exporting the report.
          
  • To change the MS Excel color palette for a spreadsheet, consult the Microsoft Knowledge Base 288412

Keywords

CR, colour, colors , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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