none
Pivot table cell coloring and applying styles RRS feed

  • Question

  • Hi,
    I have created excel file  using openXML in C# from template excel (which has pivot table) .

    --> i do want to color the pivot table cells based on its text.

    i got the cells text by using below lines:

    "Row Redrow = worksheet1.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == 3).First();
                            Cell colorcell1 = Redrow.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, "A3" ) == 0).First();
                            Cell colorcell2 = Redrow.Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, "B3" , true) == 0).First();
                            var value = colorcell1.InnerText;

                            var stringTable = myworkbookpart.
                       GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

                            if (stringTable != null)
                            {
                                value = stringTable.SharedStringTable.
                                  ElementAt(int.Parse(value)).InnerText;
                            }
    "

    --> i am coloring the cell by using below code:
    "if (value != null)
                            {

                                if (value == "RED") { colorcell1.StyleIndex = (UInt32Value)1U; colorcell2.StyleIndex = (UInt32Value)1U; }
                                else if (value == "ORANGE ") { colorcell1.StyleIndex = (UInt32Value)3U; colorcell2.StyleIndex = (UInt32Value)3U; }"

    And saved the worksheet "wsp1.Worksheet.Save();"

    When i open the excel it showing the colors as expected. but when i refreshed the data using  "refresh button" manually it doesn't showing the color.

    please explain with example.

    thanks,
    Elangovan P

    Saturday, January 7, 2012 2:28 PM

Answers

  • Hi again,

    When your above run, the logic expression would check the value of the sharedStringTablePart, and assign  the color for the cells according to your customization. But are you aware of this, however, your logic expression hasn't been written into your workbook, which is also cannot be implemented. So absolutely, the color of the cells only changes after your code run.

    For your situation, you might need to use Conditional Formatting of Excel, something like this:

    Create flexible, effective conditional formats in Excel 2007

    You can implement this customization for you workbook via either Excel UI, Object model, or Open XML.

    The Open XML code for your requirement is something like:

            public void addConditionalFormatting(string fileName)
            {
                SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(fileName, true);
    
                //retrieve worksheetpart
                Spread.Sheet sheet1 = spreadDocument.WorkbookPart.Workbook.Descendants<Spread.Sheet>().Where(s => s.Name == "Sheet1").First();
                WorksheetPart sheetPart = spreadDocument.WorkbookPart.GetPartById(sheet1.Id) as WorksheetPart;
                Spread.Worksheet worksheet1 = sheetPart.Worksheet;
    
                //add two style to the workbook for further use by Conditional formatting
                WorkbookStylesPart bookStylePart = spreadDocument.WorkbookPart.WorkbookStylesPart;
                AddStyle(bookStylePart);
    
    
                Spread.PageMargins pageMargins1 = worksheet1.GetFirstChild<Spread.PageMargins>();
    
                //creat a conditional formatting part which refer to "A3:B3" range
                Spread.ConditionalFormatting CF = new Spread.ConditionalFormatting();
                ListValue<StringValue> LValue = new ListValue<StringValue>();
                LValue.InnerText = "A3:B3";
                CF.SequenceOfReferences = LValue;
    
                //the first rule indicate that when the value of cell A1 is orange,
                //change the color of range which specified before to orange
                Spread.ConditionalFormattingRule CFRule1 = new Spread.ConditionalFormattingRule();
                CFRule1.Type = Spread.ConditionalFormatValues.Expression;
                CFRule1.FormatId = 1;
                CFRule1.Priority = 1;
                Spread.Formula formula1 = new Spread.Formula();
                formula1.Text = "IF($A$1= \"orange\",\"true\",\"false\")";
                CFRule1.Append(formula1);
    
                //the first rule indicate that when the value of cell A1 is red,
                //change the color of range which specified before to red
                Spread.ConditionalFormattingRule CFRule2 = new Spread.ConditionalFormattingRule();
                CFRule2.Type = Spread.ConditionalFormatValues.Expression;            
                CFRule2.FormatId =0;
                CFRule2.Priority = 2;
                Spread.Formula formula2 = new Spread.Formula();
                formula2.Text = "IF($A$1= \"red\",\"true\",\"false\")";
                CFRule2.Append(formula2);
    
                CF.Append(CFRule1);
                CF.Append(CFRule2);
    
                worksheet1.InsertBefore(CF, pageMargins1);
    
                //save the workbook and close it
                spreadDocument.WorkbookPart.Workbook.Save();
                spreadDocument.Close();
    
            }
    
            public void AddStyle(WorkbookStylesPart bookStytePart) 
            {
                Spread.Stylesheet styleSheet = bookStytePart.Stylesheet;
                Spread.Fonts fonts = styleSheet.GetFirstChild<Spread.Fonts>();
    
                //spefify adding two fonts to the Fonts colloction
                uint fontsCount = fonts.Count;
                fonts.Count = fontsCount + 2;
    
    
                //the font with its color is red
                Spread.Font font1 = new Spread.Font();
                Spread.Color color1 = new Spread.Color();
                color1.Rgb = "FFFF0000";
                
                font1.Append(color1);
                fonts.Append(font1);
    
                //the font with its color is orange
                Spread.Font font2 = new Spread.Font();
                Spread.Color color2 = new Spread.Color();
                color2.Rgb = "FFFFC000";
    
                font2.Append(color2);
                fonts.Append(font2);
            }
    

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 9, 2012 9:03 AM
    Moderator