2

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:

Working Image

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:

enter image description here

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.

ApplePie
  • 8,814
  • 5
  • 39
  • 60
Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
  • I've had nightmares when I created colors via macro in Excel. My personal hell was when I would save them and send them to another person, they would appear as different colors when they loaded them. I don't have an answer, but I would suspect Excel just as much as your 3rd party add-in. It's been a while since I did it, but I seem to recall better success using the vb constants over RGB colors. – Perry Tew Jul 16 '12 at 22:06
  • @PerryTew - Yup, using like `Color.Wheat` instead of `Color.FromArgb()` makes no difference. I wouldn't expect it to, unless Excel happened to use the same color constants as the .NET Framework. I bet everything just gets converted into RGB in the generated file since the Infragistics control would have no way to know if you used a color constant or RGB value in code. – Mike Christensen Jul 16 '12 at 22:21
  • Does this also happen if you set colors manually instead of with VBA code ? – ApplePie Jul 16 '12 at 22:54
  • Have you played with the colors button under SAVE preferences? It seems to be capable of doing color substitution for previous versions. It may be that you could try altering things there to see if you have a different result. It also allows copying colors from what seems to be a file location, so you may be able to change this behavior – datatoo Jul 16 '12 at 23:03
  • it actually says Preserve visual appearance of the workbook and allows color palette loading – datatoo Jul 16 '12 at 23:05
  • pretty sure this is the issue look at http://answers.officewriter.com/workbook-colors-are-not-displayed-as-expected-in-older-versions-of-excel – datatoo Jul 16 '12 at 23:07
  • @AlexandreP.Levasseur - I'm not using any VBA code. This is all generated through C#. – Mike Christensen Jul 17 '12 at 00:51
  • @datatoo - I can't find *Save Preferences*. Can you elaborate? – Mike Christensen Jul 17 '12 at 15:35
  • sorry Preferences, I meant Options – datatoo Jul 17 '12 at 15:55

5 Answers5

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.

alhalama
  • 3,218
  • 1
  • 15
  • 21
1

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!

Sid
  • 11
  • 1
0

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

enter image description here

Excel Save preferences allows you to alter, or preserve the colors used enter image description here

datatoo
  • 2,019
  • 2
  • 21
  • 28
  • So what's the fix? Does my manager run some tool on the Excel file before copying and pasting? – Mike Christensen Jul 17 '12 at 00:49
  • The links really do explain it best, but I will try to write up an summary and edit this answer to include it. – datatoo Jul 17 '12 at 02:52
  • The links seemed relevant to compatibility between versions of Excel. We're only using Excel 2007. – Mike Christensen Jul 17 '12 at 02:54
  • 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. – datatoo Jul 17 '12 at 03:04
  • 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. – Mike Christensen Jul 17 '12 at 15:19
  • 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 – datatoo Jul 17 '12 at 15:29
  • 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. – Mike Christensen Jul 17 '12 at 15:51
  • good luck and if it is the cause it would be nice to have that noted here – datatoo Jul 17 '12 at 15:54
  • 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. – Mike Christensen Jul 17 '12 at 17:44
  • 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 – datatoo Jul 17 '12 at 18:06
0

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.

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
0

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!

user5675731
  • 1