none
Change Sheet tab color of excel file using Open XML RRS feed

  • Question

  • Hi,

    I want to change the sheet tab color of an excel Xlsx  document. I am using the following code but it does not set the sheet color. I get object reference exception when I set the sheet tab color.

    public static string filepath = @"C:\Test\Book1.xlsx";
            private static void ChangeSheetcolor()
            {
                try
                {
                    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filepath, false))
                    {
                        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                        //my code
                        WorksheetPart worksheetPart =
                              GetWorksheetPartByName(spreadSheetDocument, "Sheet1");
                        
                        if (worksheetPart != null)
                        {
                         //   worksheetPart.Worksheet.SheetProperties.TabColor.Rgb = DocumentFormat.OpenXml.HexBinaryValue.FromString("Red");
                            worksheetPart.Worksheet.SheetProperties.TabColor.Rgb = DocumentFormat.OpenXml.HexBinaryValue.FromString("#CCCCCC");
                            // Save the worksheet.
                            worksheetPart.Worksheet.Save();
                        }
                   
                      
                    }
                }
                catch (Exception ex)
                { 
                
                }
            
            }
            private static WorksheetPart
                 GetWorksheetPartByName(SpreadsheetDocument document,
                 string sheetName)
            {
                IEnumerable<Sheet> sheets =
                   document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
                   Elements<Sheet>().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                {
                    //does not exist
                    return null;
                }
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)
                     document.WorkbookPart.GetPartById(relationshipId);
                return worksheetPart;
            }

    How to change the sheet tab color using Open XML dlls.

    Thanks

    Ashok

    Friday, May 1, 2015 2:56 PM

Answers

  • Hi Ashok,

    The reason for the reference execption is that there is maybe no sheet property or other intance you are refering to. You can debug it and see which object is null.

    Also I wrote a runable sample to set the color for the sheet tab for your reference:

     public void Main()
            {
                SetColor(@"C:\book1.xlsx", "Sheet1", "00FF00");
                       }
    
            public void SetColor(string filePath, string sheetName, string RGB)
            {
                using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath,true))
                {
                    WorkbookPart wbPart=spreadSheetDocument.WorkbookPart;
                    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
      Where(s => s.Name == sheetName).FirstOrDefault();
    
                  
                    WorksheetPart wsPart =
                       (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    
                                   if (wsPart.Worksheet.SheetProperties!=null)
                    {
                        wsPart.Worksheet.SheetProperties.TabColor=new TabColor(){Rgb=RGB};
                    }else
    
                    {
                          SheetProperties sheetProperties1 = new SheetProperties();
                          TabColor tabColor1=new TabColor(){Rgb=RGB};
                          sheetProperties1.Append(tabColor1);
                          wsPart.Worksheet.SheetProperties = sheetProperties1;
                    }
                }
            }
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 4, 2015 9:47 AM
    Moderator

All replies

  • Hi,

    I want to change the sheet tab color of an excel Xlsx  document. I am using the following code but it does not set the sheet color. I get object reference exception when I set the sheet tab color.

    public static string filepath = @"C:\Test\Book1.xlsx";
            private static void ChangeSheetcolor()
            {
                try
                {
                    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filepath, false))
                    {
                        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                        IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                        //my code
                        WorksheetPart worksheetPart =
                              GetWorksheetPartByName(spreadSheetDocument, "Sheet1");
                        
                        if (worksheetPart != null)
                        {
                         //   worksheetPart.Worksheet.SheetProperties.TabColor.Rgb = DocumentFormat.OpenXml.HexBinaryValue.FromString("Red");
                            worksheetPart.Worksheet.SheetProperties.TabColor.Rgb = DocumentFormat.OpenXml.HexBinaryValue.FromString("#CCCCCC");
                            // Save the worksheet.
                            worksheetPart.Worksheet.Save();
                        }
                   
                      
                    }
                }
                catch (Exception ex)
                { 
                
                }
            
            }
            private static WorksheetPart
                 GetWorksheetPartByName(SpreadsheetDocument document,
                 string sheetName)
            {
                IEnumerable<Sheet> sheets =
                   document.WorkbookPart.Workbook.GetFirstChild<Sheets>().
                   Elements<Sheet>().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                {
                    //does not exist
                    return null;
                }
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)
                     document.WorkbookPart.GetPartById(relationshipId);
                return worksheetPart;
            }

    How to change the sheet tab color using Open XML dlls.

    Thanks

    Ashok

    Friday, May 1, 2015 2:51 PM
  • Hi Ashok,

    The reason for the reference execption is that there is maybe no sheet property or other intance you are refering to. You can debug it and see which object is null.

    Also I wrote a runable sample to set the color for the sheet tab for your reference:

     public void Main()
            {
                SetColor(@"C:\book1.xlsx", "Sheet1", "00FF00");
                       }
    
            public void SetColor(string filePath, string sheetName, string RGB)
            {
                using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath,true))
                {
                    WorkbookPart wbPart=spreadSheetDocument.WorkbookPart;
                    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
      Where(s => s.Name == sheetName).FirstOrDefault();
    
                  
                    WorksheetPart wsPart =
                       (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
    
                                   if (wsPart.Worksheet.SheetProperties!=null)
                    {
                        wsPart.Worksheet.SheetProperties.TabColor=new TabColor(){Rgb=RGB};
                    }else
    
                    {
                          SheetProperties sheetProperties1 = new SheetProperties();
                          TabColor tabColor1=new TabColor(){Rgb=RGB};
                          sheetProperties1.Append(tabColor1);
                          wsPart.Worksheet.SheetProperties = sheetProperties1;
                    }
                }
            }
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, May 4, 2015 9:47 AM
    Moderator
  • ignore


    • Proposed as answer by Sir.Smeal Thursday, October 6, 2016 4:46 PM
    • Edited by Sir.Smeal Thursday, October 6, 2016 5:29 PM Incorrect
    • Unproposed as answer by Sir.Smeal Thursday, October 6, 2016 5:29 PM
    Thursday, October 6, 2016 4:46 PM