C#VSTO : Coloring pivottable cells

Question

您好，我正在尝试为数据透视表添加一些代码颜色。它可以很好地为单元格着色，但如果我刷新表格，所有颜色都会消失，就好像这些颜色没有正确附加到数据透视表一样。

我有以下代码(这是从一个更大的代码中截取的):

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

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

我试过在 VB 的 Excel 中做一个宏，当它运行时，它工作得很好，所以我不明白为什么 C# VSTO 不能工作...

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

Selection.Interior.Color = 15962653

非常感谢您的帮助:)

编辑

这里有更多的代码。BaseVars.GlobalWB 是一个引用事件工作簿 (Globals.ThisWorkBook) 的变量。这使得同时使用 2 个 Excel 成为可能，而 VSTO 不会在错误的工作簿上运行代码。

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

如果您使用的是 C# VSTO，请不要使用 Selection.Interior.Color。请改用 Selection.Interior.ColorIndex。 Excel 使用 56 色调色板，您在 C# 中指定的任何颜色都将“转换”为其中一种调色板颜色。有效的 ColorIndex 值介于 1 和 56 之间。另请查看有关调色板和 Excel 的有用引用。

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