Excel范围样式:通过VSTO指定边框不起作用 [英] Excel range Style: specifying Borders via VSTO doesn't work

问题描述

我正在尝试使用Excel样式，而不是通过一个接一个地设置各个格式设置属性来设置格式范围，因为它在格式化大量单元格时似乎更快.我定义了一次样式，然后将其应用于范围，如下所示:

Instead of formatting ranges by setting individual formatting properties one by one, I am trying to use Excel Styles, because it seems to be faster at formatting large numbers of cells. I define a Style once, and then apply it to Ranges like this:

var cell = worksheet.Cells[row, column];
cell.Style = "MyCustomStyle";

它非常适合内部颜色和字体，但是在尝试使用Borders时遇到了奇怪的问题.当我尝试定义要在范围上显示的边框以及应如何设置边框格式时，我得到了无法预测的结果，并且找不到控制它的方法.

It works perfectly for Interior Color and Font, but I am running into weird issues when trying to work with Borders. When I try to define what borders to show on a range, and how they should be formatted, I get unpredictable results, and can't find a way to control it.

以下方法创建一个名为ListRowStyle的样式；

The following method creates a Style named ListRowStyle;

private static void CreateListRowStyle(Workbook workbook)
{
    var listRowStyle = workbook.Styles.Add(ListRowStyle);

    listRowStyle.Interior.Color = ColorTranslator.ToOle(Color.LightGray);

    listRowStyle.Font.Color = ColorTranslator.ToOle(Color.DarkBlue);
    listRowStyle.Font.Bold = true;

    listRowStyle.IncludeBorder = true;
    listRowStyle.Borders.Color = ColorTranslator.ToOle(Color.Black);
    listRowStyle.Borders.LineStyle = XlLineStyle.xlContinuous;
    listRowStyle.Borders.Weight = XlBorderWeight.xlMedium;
}

这将创建范围(垂直，水平和对角线)中的每个边框-到目前为止，效果很好.但是，当我尝试使用以下代码仅显示顶部和底部边框时，就会出现问题:

This creates every single border in the range (vertical, horizontal and diagonal) - so far, so good. However, when I try to display only, say, the top and bottom borders, using the following code, problems start happening:

private static void CreateEditableListRowStyle(Workbook workbook)
{
    var editableListRowStyle = workbook.Styles.Add(EditableListRowStyle);
    editableListRowStyle.Interior.Color = ColorTranslator.ToOle(Color.Yellow);

    editableListRowStyle.Font.Color = ColorTranslator.ToOle(Color.Red);
    editableListRowStyle.Font.Bold = false;

    editableListRowStyle.IncludeBorder = true;

    editableListRowStyle.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlLineStyleNone;
    editableListRowStyle.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlLineStyleNone;

    editableListRowStyle.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = XlLineStyle.xlLineStyleNone;
    editableListRowStyle.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = XlLineStyle.xlLineStyleNone;

    editableListRowStyle.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
    editableListRowStyle.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlMedium;

    editableListRowStyle.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
    editableListRowStyle.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
}

发生颜色样式，但没有出现边框.当我修改代码以格式化左右边框时，事情变得更奇怪了:

The color styling happens, but no borders show up. Things get even weirder when I modify the code to format the Left and Right border like this:

private static void CreateEditableListRowStyle(Workbook workbook)
{
    var editableListRowStyle = workbook.Styles.Add(EditableListRowStyle);
    editableListRowStyle.Interior.Color = ColorTranslator.ToOle(Color.Yellow);

    editableListRowStyle.Font.Color = ColorTranslator.ToOle(Color.Red);
    editableListRowStyle.Font.Bold = false;

    editableListRowStyle.IncludeBorder = true;

    editableListRowStyle.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
    editableListRowStyle.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlMedium;

    editableListRowStyle.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
    editableListRowStyle.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlMedium;

    editableListRowStyle.Borders[XlBordersIndex.xlDiagonalDown].LineStyle = XlLineStyle.xlLineStyleNone;
    editableListRowStyle.Borders[XlBordersIndex.xlDiagonalUp].LineStyle = XlLineStyle.xlLineStyleNone;

    editableListRowStyle.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
    editableListRowStyle.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlMedium;

    editableListRowStyle.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
    editableListRowStyle.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
}

那时，顶部和底部边框仍然没有显示；另一方面，我得到一个左边框显示，但没有右边框.嗯?

At that point, the top and bottom borders still don't show up; on the other hand, I get a Left border that shows up, but no Right border. Uh?

那么-我是在做错什么，还是通过VSTO在Style上设置Borders不能正常工作?请注意，以下代码是VBA中VSTO/C#代码的非常接近的翻译，其工作原理与我期望的完全一样.

So - am I doing something wrong, or is setting Borders on a Style via VSTO just not working? Note that the following code, which is a very close translation of the VSTO/C# code in VBA, works exactly as I would expect it to.

Sub Styling()

    ActiveWorkbook.Styles.Add Name:="VbaStyle"

    With ActiveWorkbook.Styles("VbaStyle")
        .IncludeBorder = True
    End With

    ActiveWorkbook.Styles("VbaStyle").Borders(xlLeft).LineStyle = xlNone
    ActiveWorkbook.Styles("VbaStyle").Borders(xlRight).LineStyle = xlNone
    ActiveWorkbook.Styles("VbaStyle").Borders(xlDiagonalDown).LineStyle = xlNone
    ActiveWorkbook.Styles("VbaStyle").Borders(xlDiagonalUp).LineStyle = xlNone

    With ActiveWorkbook.Styles("VbaStyle").Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With

    With ActiveWorkbook.Styles("VbaStyle").Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

End Sub

这是在Windows 7，Excel 2007上.

This is on Windows 7, Excel 2007.

推荐答案

尝试使用xlLeft，xlRight，xlTop，xlBottom代替xlEdgeLeft，xlEdgeRight，xlEdgeTop，xlEdgeBottom

Try using xlLeft, xlRight, xlTop, xlBottom instead of xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlEdgeBottom

这篇关于Excel范围样式:通过VSTO指定边框不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！