This project has moved. For the latest updates, please go here.

Remove named styles

Feb 15, 2016 at 9:38 AM
Hi!

I have several excel files which all have a few hundred named styles; They are all copies of the standard styles. A screenshot to illustrate the situation:

Image

I have really no clue how they were created.

Is there any way to remove these named styles?

Thanks!

Johann
Feb 17, 2016 at 7:14 AM
Ok, after some extensive trial and error I found some things:

I tried to delete all non-builtin style with the following code:
        static void removeNonBuiltInStylesXml(ExcelPackage package)
        {
            var wkb = package.Workbook;
            var cellStyles = wkb.StylesXml["styleSheet"]["cellStyles"];
            var nodeList = new List<System.Xml.XmlElement>();

            foreach (System.Xml.XmlElement item in cellStyles)
            {
                var str = item.Attributes[0].Value;
                if (!item.HasAttribute("builtinId"))
                {
                    Console.WriteLine("Found XML: " + str);
                    nodeList.Add(item);
                }
            }
            foreach (System.Xml.XmlElement item in nodeList)
            {
                cellStyles.RemoveChild(item);
            }
        }
I use the nodeList because if I try to call RemoveChild directly, the foreach loop immediately returns after the first element. But when I create a list with the found elements and delete them later, it seems to work at first glance: If I inspect the cellStyles tree after the loop with RemoveChild, only 41 entries remain. But the resulting excel file still has all the rogue styles in it - apparently the styles are saved somewhere else and rebuild before saving occurs (perhaps because of a call to UpdateXml()?)

My next attempt was really desperate but also somewhat successful;
        static void removeBuiltInStyles(ExcelPackage package)
        {
            var wkb = package.Workbook;
            var namedStyles = wkb.Styles.NamedStyles;

            foreach (var item in namedStyles)
            {
                if (item.BuildInId < 0)
                {
                    Console.WriteLine("Found standard: " + item.Name);
                    item.BuildInId = 0;
                }
            }
        }
After opening the resulting excel file, everything looks ok - All non-builtin styles are gone! Wohoo!
But when I inspect the file contents during debugging, all the cellStyles are still there - That gets me a little worried....

I have the feeling I'm missing something fundamental here - Can someone offer some advice?

Thanks,

Johann