CODEWITHSUNDEEP
c#excelvsto
Luc Touraille
Luc Touraille

Reputation: 82041

How can I create a style with borders from VSTO code?

I am developing an Excel addin in C#, using VSTO. In this addin, I create some styles and add them to the current workbook. Everything works fine until I try to set some borders on my style.

This is the code I use:

var style = workbook.styles.Add("My style");

style.IncludeAlignment = false;
style.IncludeFont = false;
style.IncludeNumber = false;
style.IncludeProtection = false;
style.IncludePatterns = false;

style.IncludeBorder = true;
foreach (XlBordersIndex borderIndex in new[] { 
                XlBordersIndex.xlEdgeLeft, 
                XlBordersIndex.xlEdgeRight, 
                XlBordersIndex.xlEdgeTop, 
                XlBordersIndex.xlEdgeBottom })
{
    style.Borders[borderIndex].LineStyle = XlLineStyle.xlContinuous;
}

I would expect this code to create a style with the four borders set, but it seems that only the left edge is set (I can see that by looking at the style object from the debugger after the loop, and in Excel by editing "My style").

I tried recording a VBA macro to see the code generated by Excel and I obtained this:

ActiveWorkbook.Styles.Add Name:="My style"
With ActiveWorkbook.Styles("My style")
    .IncludeNumber = False
    .IncludeFont = False
    .IncludeAlignment = False
    .IncludeBorder = True
    .IncludePatterns = False
    .IncludeProtection = False
End With
With ActiveWorkbook.Styles("My style").Borders(xlLeft)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
End With
With ActiveWorkbook.Styles("My style").Borders(xlRight)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
End With
With ActiveWorkbook.Styles("My style").Borders(xlTop)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
End With
With ActiveWorkbook.Styles("My style").Borders(xlBottom)
    .LineStyle = xlContinuous
    .TintAndShade = 0
    .Weight = xlThin
End With

This macro works as expected. I noticed that it uses xlTop, xlLeft, etc. instead of xlEdgeTop, xlEdgeLeft, but I can't find any documentation about these constants and they are not available in the VSTO enumeration XlBordersIndex. From what I found, it seems that the latter represents the edge of the range, while the former represents the edge of each cell, but I'm not sure about this and I think the difference does not make much sense when talking about styles, which applies to a single cell AFAICT.

Why do I have a different behavior between my C# addin and this VBA macro? How can I create a style with borders from my VSTO code?

Upvotes: 0

Views: 643

Answers (1)

Luc Touraille
Luc Touraille

Reputation: 82041

According to this discussion, it seems that there is a difference between the borders of a style and the borders of a range: the latter are indexed by XlBordersIndex (xlEdgeTop, xlEdgeLeft,...), as stated in the documentation, but the former are indexed by Constants (xlTop, xlLeft,...).

This can make sense if we consider that a style applies to individual cells and not to a range, but this also means that to access a style borders, we must bypass the interface of the Borders interface using unrelated constants. This workaround, proposed by AnushRudaa here, seems to work:

foreach (XlBordersIndex borderIndex in new[] { 
                (XlBordersIndex)Constants.xlLeft, 
                (XlBordersIndex)Constants.xlRight, 
                (XlBordersIndex)Constants.xlTop, 
                (XlBordersIndex)Constants.xlBottom })
{
    style.Borders[borderIndex].LineStyle = XlLineStyle.xlContinuous;
}

Upvotes: 1

Related Questions