I'm automating Excel using Excel.Interop. I've created a few styles to cope with most of the cases I have, but I have the odd edge case where I might want an existing style, plus a different number format, or bold, for example.

Is it possible to clone a style and modify it, rather than create one from scratch?

Ideally I'd like to be able to do something like this:

ws.Range[...].Style = myExistingStyle.Clone() { NumberFormat = "#,##0.00#" };

or

ws.Range[...].Style = myExistingStyle.Clone() { Bold = true, Italic = true };

I'm automating Excel using Excel.Interop. I've created a few styles to cope with most of the cases I have, but I have the odd edge case where I might want an existing style, plus a different number format, or bold, for example.

Is it possible to clone a style and modify it, rather than create one from scratch?

Ideally I'd like to be able to do something like this:

ws.Range[...].Style = myExistingStyle.Clone() { NumberFormat = "#,##0.00#" };

or

ws.Range[...].Style = myExistingStyle.Clone() { Bold = true, Italic = true };

• c#
• excel-interop

1 Answer 1

Well, in the lack of an elegant way to do this, this is the brute force way. Style names have to be unique within the Workbook, so it finds a unique style name using a name root, and then copies all of the settable properties from the existing style to the new one. Not the most elegant code I've written, but does the job. The below is in my static ExcelHelper class:

/// <summary>
        /// This clones existingType and returns a new Excel.Style which contains the same config as it.
        /// Styles have to have unique names within the Workbook, so the caller provides a nameRoot, which
        /// this will use to determine a unique name, but suffixing it with increasing integers until one is found.
        /// </summary>
        /// <param name="wb"></param>
        /// <param name="existingStyle"></param>
        /// <param name="nameRoot"></param>
        /// <returns></returns>
        public static Excel.Style CloneStyle(Excel.Workbook wb, Excel.Style existingStyle, string nameRoot)
        {
            int i;
            Excel.Style newStyle;

            try
            {
                // ensure we have a unique name for the style within the Workbook
                i = 1;
                while (StyleExistsWithName(wb, $"{nameRoot}{i}"))
                    i++;

                newStyle = wb.Styles.Add($"{nameRoot}{i}");
                newStyle.AddIndent = existingStyle.AddIndent;
                newStyle.Font.Bold = existingStyle.Font.Bold;
                newStyle.Font.Color = existingStyle.Font.Color;
                newStyle.Font.ColorIndex = existingStyle.Font.ColorIndex;
                newStyle.Font.Italic = existingStyle.Font.Italic;
                newStyle.Font.Name = existingStyle.Font.Name;
                newStyle.Font.OutlineFont = existingStyle.Font.OutlineFont;
                newStyle.Font.Shadow = existingStyle.Font.Shadow;
                newStyle.Font.Size = existingStyle.Font.Size;
                newStyle.Font.Strikethrough = existingStyle.Font.Strikethrough;
                newStyle.Font.Subscript = existingStyle.Font.Subscript;
                newStyle.Font.Superscript = existingStyle.Font.Superscript;
                newStyle.Font.ThemeColor = existingStyle.Font.ThemeColor;
                newStyle.Font.ThemeFont = existingStyle.Font.ThemeFont;
                newStyle.Font.TintAndShade = existingStyle.Font.TintAndShade;
                newStyle.Font.Underline = existingStyle.Font.Underline;
                newStyle.Font.Strikethrough = existingStyle.Font.Strikethrough;
                newStyle.FormulaHidden = existingStyle.FormulaHidden;
                newStyle.HorizontalAlignment = existingStyle.HorizontalAlignment;
                newStyle.IncludeAlignment = existingStyle.IncludeAlignment;
                newStyle.IncludeBorder = existingStyle.IncludeBorder;
                newStyle.IncludeFont = existingStyle.IncludeFont;
                newStyle.IncludeNumber = existingStyle.IncludeNumber;
                newStyle.IncludePatterns = existingStyle.IncludePatterns;
                newStyle.IncludeProtection = existingStyle.IncludeProtection;
                newStyle.Interior.Color = existingStyle.Interior.Color;
                newStyle.Interior.ColorIndex = existingStyle.Interior.ColorIndex;
                newStyle.Interior.Pattern = existingStyle.Interior.Pattern;
                newStyle.Interior.PatternColor = existingStyle.Interior.PatternColor;
                newStyle.Interior.PatternColorIndex = existingStyle.Interior.PatternColorIndex;
                newStyle.Interior.PatternThemeColor = existingStyle.Interior.PatternThemeColor;
                newStyle.Interior.PatternTintAndShade = existingStyle.Interior.PatternTintAndShade;
                newStyle.Interior.ThemeColor = existingStyle.Interior.ThemeColor;
                newStyle.Interior.TintAndShade = existingStyle.Interior.TintAndShade;
                newStyle.Locked = existingStyle.Locked;
                newStyle.NumberFormat = existingStyle.NumberFormat;
                newStyle.NumberFormatLocal = existingStyle.NumberFormatLocal;
                newStyle.Orientation = existingStyle.Orientation;
                newStyle.ReadingOrder = existingStyle.ReadingOrder;
                newStyle.ShrinkToFit = existingStyle.ShrinkToFit;
                newStyle.VerticalAlignment = existingStyle.VerticalAlignment;
                newStyle.WrapText = existingStyle.WrapText;
                return newStyle;
            }
            catch (Exception ex)
            {
                throw new Exception($"Could not clone style using root: '{nameRoot}'", ex);
            }
        }


        private static bool StyleExistsWithName(Excel.Workbook wb, string name)
        {
            try
            {
                for (int i = 1; i <= wb.Styles.Count; i++)
                    if (wb.Styles[i].Name == name) return true;

                return false;
            }
            catch (Exception ex)
            {
                throw new Exception($"An error occurred in StyleExistsWithName. See inner errors.", ex);
            }
        }

It's not possible to clone and assign properties inline as per my original question, I think because Excel is COM, not .Net, but I'm calling it thus to setup a few styles before the main Excel processing:

_tableDataStyle = styles.Add("DataStyle");
_tableDataStyle.Font.Name = FONTNAME;
_tableDataStyle.Font.Size = FONTSIZE;
_tableDataStyle.Interior.Color = Excel.Constants.xlNone;
_tableDataStyle.IncludeBorder = false;

_dateStyle = ExcelHelper.CloneStyle(_wb, _tableDataStyle, "CustomStyle");
_dateStyle.NumberFormat = "dd/MM/yyyy";

_moneyStyle = ExcelHelper.CloneStyle(_wb, _tableDataStyle, "CustomStyle");
_moneyStyle.NumberFormat = "#,##0.00#;(#,##0.00#)";

_intStyle = ExcelHelper.CloneStyle(_wb, _tableDataStyle, "CustomStyle");
_intStyle.NumberFormat = "#,##0;(#,##0)"; // brackets for -ve. 

_styleCcySummaryLabel = ExcelHelper.CloneStyle(_wb, _tableDataStyle, "CustomStyle");
_styleCcySummaryLabel.Font.Bold = true;
_styleCcySummaryLabel.Interior.Color = Color.Yellow;
_styleCcySummaryLabel.IncludeBorder = true;