I create a worksheet and set cell background colors using (pseudo code):

   style.ForegroundColor = color;
   style.Pattern = BackgroundType.Solid;
   styleFlag.CellShading = true;

   range = worksheet.Cells.CreateRange(...)
   range.ApplyStyle(style, styleFlag);

If I open this worksheet in Excel the background colors are correct. I find that setting foreground
color sets a cell's back color. If I use Excel to copy the worksheet into another workbook then the
background colors for cells in the new worksheet are different.

I've tried also setting background color to no avail:

   style.BackgroundColor = color;

Does anyone know what is causing this problem?

Hi,

Well, actually MS Excel's standard color palette has 56 default colors on it. So, if you are setting some some custom colors for a range of cells in the worksheet, now when the copy operation is performed, the custom colors might be lost in the copied sheet. I think (as a workaround) you should export colors (in the palette) of your input workbook to your second workbook's palette, so that the custom colors should not be missed.

You may see the following sample code for the above scenario.

                        Workbook workbook = new Workbook();
                        .
                        .//Your code goes here.
                        .
                        .
                        //Note: Since Excel color palette has 56 colors on it.
                        //The colors are indexed 0-55.
                        //If a color is not present on the palette, we have to add it
                        //to the palette, so that we may use use.
                        //Add all the colors (including custom colors) of Workbook1 to change the palette of Workbook2.
                        Color[] colors = workbook.Colors;
                        Workbook book2 = new Workbook();
                        for (int i = 0; i < colors.Length; i++)
                            book2.ChangePalette(colors[i], i);

                        book2.Worksheets[0].Copy(workbook.Worksheets[0]);
                        book2.Save("f:\\test\\bookAfterCopy.xls");


Note: This is one of the possible scenario for your issue. But it might be some other scenario for your issue too. So, if you issue is not related to the above scenario. We appreciate if you could you elaborate: are you using an xlsx file and now want to copy its formatted worksheet to other xls file? I think you may post your complete sample code or create a test application, zip it and post here to show the issue, we will check it soon.

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

Hi,

I'm not using Aspose.Cells to copy the worksheet. I am using Excel to copy the worksheet I've created using Aspose.Cells. I wanted to know if you know any reason why the Excel copy process can't copy
the worksheet correctly?

I see the problem occur regardless of whether I've set custom colors or used default colors. Any back
ground colors set using Excel are copied correctly. This makes me think there is something about the
way Aspose.Cells defines cell colors that Excel misinterprets. I will look at what I'm doing with the
color palette in the meantime.

I've attached a workbook containing Balances worksheet which was created using Aspose.Cells. Copy this worksheet to another Workbook using Excel to see the problem.

Thanks,
Damien

Hi Damien,

If you closely look your worksheet "Balances", the colors (in C2:D8 range) are not present in the MS Excel standard color palette as they are custom colors, you may check by clicking the Fill color icon and check the colors on the palette in MS Excel 2003. Aspose.Cells for .NET does work in the similar way as MS Excel e.g 2003. If you could perform all your steps manually in MS Excel without using Aspose.Cells for .NET APIs, e.g create a spreadsheet manually, set some custom colors to apply to some cells shading color, i.e.., you may click the menu option Tools|Options, now click Color tab in the dialog, select a color on its position and click Modify button. Now click the Custom tab and set/edit its RGB values. Finally apply that color (selecting from the Fill color icon on the icon bar) to some cell(s). Now when you copy this worksheet manually to some other workbook (e.g you may open a new workbook named Book1) in MS Excel 2003, you will see that the color does change a bit accordingly. But mind you, if you don't set any custom color to the cells in the worksheet, then a general copy of that worksheet won't change any color at all.

For reference, kindly check my previous post about standard color palette. Also, you may check the document: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/colors-and-palette.html

Kindly let us know if you still have different opinion, we will be happy to check it.

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us

Thanks Amjad - you've been very helpful in identifying what is going on.