SSRS Report Colours change when exporting to excel

Question

When we export an SSRS 2008 report to excel and then copy a range from that file to another excel file, the colours used in the report are changed to something that is totally different, very annoying when we use 'Conditonal Formatting' to colour some of these cells a specific colour. Any ideas why?

Answer 1

It is because MS started using Excel's custom color palette with SQL Server 2008 instead of the built-in colors. Problem is exactly as you are describing that when copy & pasting colors from one sheet to another you lose the formatting. Unfortunately: No way around that.

See this post: Excel Export copy to another excel discoloration issue for SSRS 2008

Answer 2

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=386055

Suggestion from the SSRS team (in the above link/post) Posted by Microsoft on 5/29/2011 at 3:34 PM The API for COM/.NET is similar. You're calling Worksheet.Copy, which does not provide these options. Take a look at Range.Copy and Range.PasteSpecial.

Anyone having link to any C# code for using Range.Copy or PasteSpecial calls to copy from one workbook/sheet to another workbook?

I suppose the KeepSourceFormat or KeepSOurceScheme would be the trick to copy the sheet from one workbook to another to avoid the dis-color issue in SSRS 2008 R2 Excel report.