Excel范围样式：通过VSTO指定边框无法工作

Question

我正在尝试使用Excel样式来格式化单元格范围，而不是逐个设置单独的格式属性，因为这似乎可以更快地格式化大量单元格。我只需定义一次样式，然后像这样将其应用于范围：

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

它在内部颜色和字体方面运行得非常完美，但是当我尝试处理边框时，遇到了奇怪的问题。当我尝试定义应在范围上显示哪些边框以及如何格式化它们时，我得到了不可预测的结果，并且找不到控制它的方法。

以下方法创建一个名为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;
}

这将创建范围内的每个边框（垂直、水平和对角线）- 到此为止，一切顺利。然而，当我尝试仅显示顶部和底部边框时，使用以下代码，问题开始发生：

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;
}

颜色样式出现了，但没有边框显示。当我修改代码以这种方式格式化左右边框时，事情变得更加奇怪：

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;
}

此时，顶部和底部边框仍然不显示；另一方面，我得到了一个显示左侧边框，但没有右侧边框。嗯？

那么，我是做错了什么，还是通过VSTO设置样式的边框根本不起作用？请注意，以下代码是VBA中VSTO/C＃代码的非常相似的翻译，它完全按照我的预期工作。

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。

- Mathias

Answer 1

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

- gecosulmuro

Answer 2

我尝试了一段时间，偶然看到了你的问题，得到了一些启示。非常感谢。 使用 basedOn 的可选参数，我成功地创建了以下样式：

var activeSheet = workbook.ActiveSheet as Worksheet;
Range first = activeSheet.Range["A1"];
first.Borders.Item[XlBordersIndex.xlEdgeBottom].Color = Color.FromArgb(0, 16, 80);
first.Borders.Item[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;
first.Borders.Item[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
Style myStyle = o9Workbook.Styles.Add("MyStyle",first);
//reset the first to normal style
first.Style = "Normal";

希望能对某些人有所帮助！

- maicalal