C# VSTO: Coloring pivottable cells

Question

HI, I'm trying to make some code color a PivotTable. It works just fine coloring the cells it's supposed to, but if I refresh the table, all the colors disappear, as if the colors haven't been properly attached to the PivotTable.

I have the following code (this is a snip from a larger code):

myPivotTable.PivotSelect("'" + item["Name"].ToString() + "'[All;Total]", XlPTSelectionMode.xlDataAndLabel, true);

((Range)Globals.ThisWorkbook.Application.Selection).Interior.Color = 15962653;

I've tried doing a macro in Excel in VB, and when it runs, it work works perfectly, so I don't understand why the C# VSTO won't work...

ActiveSheet.PivotTables("PivotTable1").PivotSelect "'ItemName'[All;Total]", xlDataAndLabel, True

Selection.Interior.Color = 15962653

Help is much appreciated :)

EDIT

Here's a bit more of the code. BaseVars.GlobalWB is a variable which refers to the active workbook (Globals.ThisWorkBook). This makes it possible to work with 2 Excels at the same time, without the VSTO running code on the wrong workbook.

foreach (DataRow item in myPivotTableFields.Tables[0].Rows)
        {
// Field name from data sheet
            myPivotField = (PivotField)myPivotFields.Item(item["Name"].ToString());
            // Field name in the pivot table
            myPivotField.Caption = item["Caption"].ToString();
            // Their subtotal value
            myPivotField.set_Subtotals(Type.Missing, GenerateSubTotalArray(item["SubTotal"].ToString()));

            #region Attribs

            //Include new items in manual filter
            if (item["Attrib01"].ToString() == "True")
            {
                myPivotField.IncludeNewItemsInFilter = true;
            }
            else
            {
                myPivotField.IncludeNewItemsInFilter = false;
            }

            // Show items labels in outline form
            if (item["Attrib02"].ToString() == "Outline")
            {
                myPivotField.LayoutForm = XlLayoutFormType.xlOutline;
            }
            else
            {
                myPivotField.LayoutForm = XlLayoutFormType.xlTabular;
            }

            // Display labels from the next field in the same column
            if (item["Attrib03"].ToString() == "True")
            {
                myPivotField.LayoutCompactRow = true;
            }
            else
            {
                myPivotField.LayoutCompactRow = false;
            }

            // Display subtotals at the top of each group
            if (item["Attrib04"].ToString() == "AtBottom")
            {
                myPivotField.LayoutSubtotalLocation = XlSubtototalLocationType.xlAtBottom;
            }
            else
            {
                myPivotField.LayoutSubtotalLocation = XlSubtototalLocationType.xlAtTop;
            }

            // Insert blank line after each item label
            if (item["Attrib05"].ToString() == "True")
            {
                myPivotField.LayoutBlankLine = true;
            }
            else
            {
                myPivotField.LayoutBlankLine = false;
            }

            // Show items with no data
            if (item["Attrib06"].ToString() == "True")
            {
                myPivotField.ShowAllItems = true;
            }
            else
            {
                myPivotField.ShowAllItems = false;
            }

            // Insert page break after each item
            if (item["Attrib07"].ToString() == "True")
            {
                myPivotField.LayoutPageBreak = true;
            }
            else
            {
                myPivotField.LayoutPageBreak = false;
            }
            #endregion

            // Set up the pivot table selection
            if (item["Selection"].ToString() != "(blank)")
            {
                myItems = new List<string>();
                myItems = GlobalFunc.Explode(item["Selection"].ToString());
                SetUpPivotTableSelection(myPivotTable, item["Name"].ToString(), myItems);
            }
            else if (item["Selection"].ToString() == "(blank)" && item["Orientation"].ToString() == "Filter")
            {
                myPivotField.ClearAllFilters();
                myPivotField.CurrentPage = "(All)";
            }

try
                {
                    myPivotField.ClearValueFilters();
                    myPivotField.ShowDetail = true;
                }
                catch (Exception ex)
                {
                    GlobalFunc.DebugWriter("Error during Pivot Table Reset: " + ex.Message);
                }

try
                {
                    myPivotTable.PivotSelect("'" + item["Name"].ToString() + "'[All;Total]", XlPTSelectionMode.xlDataAndLabel, true);

                    // Set up the fields borders if it has any
                    myRange = BaseVars.GlobalWB.Application.get_Range(BaseVars.GlobalWB.Application.Selection, BaseVars.GlobalWB.Application.Selection);
                    myRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = (XlLineStyle)InsertLineStyle(item["Attrib12"].ToString());
                    myRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = (XlLineStyle)InsertLineStyle(item["Attrib13"].ToString());
                    myRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = (XlLineStyle)InsertLineStyle(item["Attrib14"].ToString());
                    myRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = (XlLineStyle)InsertLineStyle(item["Attrib15"].ToString());
                }
                catch (Exception ex)
                {
                    GlobalFunc.DebugWriter("<LI>Error occured: " + ex.Message + "</LI>");
                }

                // Insert the colors of the field, gradient or solid
                if (item["Color_Total2"].ToString() != null && item["Color_Total2"].ToString() != "")
                {
                    Base.InsertGradient(myRange, int.Parse(item["Color_Total1"].ToString().Replace("0x", ""), System.Globalization.NumberStyles.HexNumber), int.Parse(item["Color_Total2"].ToString().Replace("0x", ""), System.Globalization.NumberStyles.HexNumber), false);
                }
                else if (item["Color_Total1"].ToString() != null && item["Color_Total1"].ToString() != "")
                {
                    BaseVars.GlobalWB.Application.get_Range(BaseVars.GlobalWB.Application.Selection, BaseVars.GlobalWB.Application.Selection).Interior.Color = int.Parse(item["Color_Total1"].ToString().Replace("0x", ""), System.Globalization.NumberStyles.HexNumber);
                }
}

Answer 1

If you're using C# VSTO, don't use Selection.Interior.Color. Use Selection.Interior.ColorIndex instead. Excel uses a 56-color palette, and any colors you specify in C# is "translated" into one of those palette colors. Valid ColorIndex values are between 1 and 56. Also check out this helpful reference on the color palette and Excel.

http://www.mvps.org/dmcritchie/excel/colors.htm

Answer 2

Hi! I found a totally different solution to this functionality, so I no longer need to color anything in the PivotTable =)

I made a total rewrite, so instead of saving PivotTables to a databse and regenerating them, I simply save the PivotTables to xlsx files and recover them from there.