When you export a report from SQL Server Reporting Services 2008 to an Excel file, and then use either Move or Copy operation or Copy/Paste operations to copy the exported report sheet to another workbook, you will notice that the colors in the copied version appear differently than those in the original version.
The top portion of the following image shows an image of a report right after exporting the report to Excel using SQL Server Business Intelligent Developer Studio (BIDS) and the bottom portion shows the same version of the report after it has been copied over to another workbook.
The problem occurs because both SQL Server Reporting Services 2008 and SQL Server Reporting services 2008 R2 use a customized Color Palette instead of the built-in palette that ships with Excel. The first 56 unique colors used in the report are defined in a custom palette.
You can work around this problem using one of the following methods depending on the version of Excel you use in your environment:
Excel 2007 or Excel 2010: Use one of the following methods:
Method 1: Use Ctrl+C and Ctrl+V combination with the following steps:
Copy the source data by pressing Ctrl+C in the source workbook.
In the new workbook use one of the following methods to retain source formatting:
Right click and choose Paste Special option from the Edit Menu and then paste the data by selecting Using all source theme option.
After pressing Ctrl+V in the new worksheet choose Keep source formatting in Paste Options.
One caveat that you need to be aware of when using this method is that the row height and column width may not carry over to the new workbook. To keep row height and column width, you can first do Move or Copy Sheet to the new workbook to ensure column width and row height are acceptable and then copy the content using source format again.
Method 2:Use Move or Copy Sheet operations with the following additional steps:
After doing Move or Copy Sheet to copy the sheet to another workbook do the following:
Go to File menu and select Options in the left pane.
In the Excel Options page, select Save option.
Click Colors… button under Preserve visual appearance of the workbook.
In the Color page, in the Copy colors from list box, select the source excel file that is exported from SQL Server Reporting Services.
Click OK twice to close all the option windows.
You can also automate this workaround using a Macro Code that is similar to the following:
ActiveWorkbook.Colors = Workbooks("OriginalExcelSheetExportedFromReport.xls").Colors
Excel 2003: Use Move or Copy Sheet operations with the following additional steps:
After doing Move or Copy sheet to copy the sheet to another workbook do the following:
Go to Tools menu and click Options.
Select the Color tab, and in the Copy colors from list box, select the source excel file that is exported from SQL Server Reporting Services. Click OK to close the dialog box.