When copying and pasting Excel data, colors are lost

Question

At the risk of having this question downvoted into oblivion or closed as "Too Localized", I figured I would post about this issue since my boss complains constantly about it and it has me scratching my head.

We use the Infragistics NetAdvantage Excel control to create an Excel report on the fly. Some cells, we add a fill color like so:

sheet.Rows[i].Cells[0].CellFormat.FillPattern = FillPatternStyle.Solid;
sheet.Rows[i].Cells[0].CellFormat.FillPatternForegroundColor = Color.FromArgb(127, 127, 127);

This works great and the report comes out looking fine:

However, if my manager selects all the cells in Excel, copies the data to the clipboard, then pastes those cells into another Excel file, all the colors are converted into some weird 3bit palette:

Now, every Fill color is either pure red (#FF0000), pure green (#00FF00), pure blue (#0000FF) or yellow (#FFFF00). It's all or nothing of any given color. That dark grey header even becomes completely white.

One interesting thing is if I go and manually change the fill color of one of those cells in Excel, then copy and paste that, the color I manually set is copied over perfectly while the Infragistics generated cells are dithered.

This only occurs when copying between Excel files, not between Worksheets within an Excel document. Has anyone ran into this issue, or do any Excel experts know what could cause this from a technical point of view?

This is Excel 2007, and Infragistics Build 8.2.20082.1000.

Answer 1

Ok I think I have this one figured out after having the same problem described here and other places.......check this out! First go to the page your pasting to and Highlight the entire page, go to print area and "clear print area". Then go to the pasting area and delete the cells even if there is nothing there! Highlight the rows (even though there is nothing there). Delete the rows. Now start from scratch and copy and paste and you should be able to paste bringing over all your original colors and formulas!

Answer 2

So I was having the same issue and couldn't figure out why the copied format would not carry over into another worksheet. So, there is no official "fix" however, I realized that it wasn't carrying over because I was in a totally seperate Excel document. Essentially, you have to have your copy and paste document in the same Excel sheet. So, while you have the item you want to copy open, open your other Excel sheet (File, Open) from your copy sheet. Once you paste, it will now hold all of your formatting. Hope this helps!

Answer 3

I've also found a solution for those not wanting (or not able to afford) to upgrade. Simply creating workbooks like so:

Workbook b = new Workbook(WorkbookPaletteMode.StandardPalette);

Will solve the problem. However, obviously you won't be able to use any totally custom colors. They'll be rounded to the nearest standard color, which was perfectly fine in our case.

Answer 4

This is addressed in NetAdvantage 2012 Volume 1 where there were changes to the color model used by the excel engine. Mike's reply on the Infragistics forums also mentions this.

Your best option to resolve the issue is to use the excel engine from NetAdvantage 2012 Volume 1.

Answer 5

Different versions will render colors differently and this seems to be what you are seeing

This is explained well in this article http://answers.officewriter.com/workbook-colors-are-not-displayed-as-expected-in-older-versions-of-excel

There is a tool for creating a custom palettes which should resolve this for you much easier than creating your own.

http://excelcampus.com/tools/color-palette-conversion

Excel Save preferences allows you to alter, or preserve the colors used