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.

Solution 1

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.

Solution 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!

Comments

[Mike Christensen]

So what's the fix? Does my manager run some tool on the Excel file before copying and pasting?

[datatoo]

The links really do explain it best, but I will try to write up an summary and edit this answer to include it.

[Mike Christensen]

The links seemed relevant to compatibility between versions of Excel. We're only using Excel 2007.

[datatoo]

If that is the case then this may not be the cause, but it seems to explain the behavior you see. I would experimentally download the palette-conversion tool and see whether it does indeed fix the way things display. Alternately there may be substitutions set for colors causing this.

[Mike Christensen]

I messed around with the tool for a bit, but it didn't seem to do anything. I think it's just some sort of bug with the Infragistics control.

[datatoo]

I was looking at their control and their support site, and it might be worth asking them, as it may have something to do with the rendering of the file

[Mike Christensen]

I can try that. They'll probably tell us to upgrade, which we don't have the budget to do. But if they can confirm it's a known bug and confirm it was fixed, I would at least then have an answer.

[datatoo]

good luck and if it is the cause it would be nice to have that noted here

[Mike Christensen]

I think it boils down to the fact the palettes are different between what Infragistics generates and the default new file template. I need to figure out how to copy a palette from one file to another.

[datatoo]

You might take a look at the underlying code in the Color Palette Conversion Tool, because the vba is not passworded, and it may give you help in figuring out a way to solve this if you can't use the tool directly

[Mike Christensen]

Thanks! Unfortunately, we don't have the budget to upgrade at the moment but good to know if we ever do, the problem will go away.