Excel Interop - Draw All Borders in a Range

Question

I see from Microsoft's documentation that I can access the particular border edges of a cell using the 'xlBordersIndex' property and for example set the border style for the left edge of a cell:

range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle =     Excel.XlLineStyle.xlContinuous;

But what if I just want to draw all borders? I have tried

range.BorderAround2();

but that just draws a box around the range itself, which I understand. So then I tried

range.Cells.BorderAround2();

thinking that it would go through each of the cells within the range and place all borders around each cell. This is not what occurred. So in order to get all borders around all cells in a range, must I manually access each of the four border indices?

score 23 · Accepted Answer · answered Feb 07 '13 at 20:47

private void AllBorders(Excel.Borders _borders)
    {
        _borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
        _borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
        _borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
        _borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
        _borders.Color = Color.Black;
    }

score 8 · Answer 2 · edited Feb 04 '15 at 03:22

8

oRange = SHEET2.get_Range("a1", "a10");
oRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
oRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
oRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
oRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;

edited Feb 04 '15 at 03:22

chiwangc

3,508
16
24
32

answered Feb 04 '15 at 02:15

E Coder

235
4
5

3

Simplified: oRange.Borders.LineStyle = XlLineStyle.xlContinuous; – Luca Ziegler Jul 17 '17 at 15:55
@LucaZiegler a plus for .get_Item(.. that's the efficient way to do it. – Leo Gurdian Mar 10 '18 at 01:35

score 6 · Answer 3 · answered Feb 02 '13 at 16:33

6

I'm not yet familiar wit C#, but in VBA there are Range.Borders(xlInsideVertical) and Range.Borders(xlInsideHorizontal) properties. Try to use macro-recorder and apply all borders for any workbook region. Perhaps that will help.

answered Feb 02 '13 at 16:33

Peter L.

7,126
5
32
52

That's a good idea, using a macro to see how the native program handles it. I ended up writing a function to draw all borders: – spickles Feb 07 '13 at 20:44
Glad that was helpful buddy) – Peter L. Feb 07 '13 at 20:48

score 6 · Answer 4 · answered Sep 10 '16 at 11:24

6

Finally, I got it. I did this without impacting the performance too. I am taking a simple excel to explain here :

Before

I managed to store the range as A1:C4 in a variable dynamically in exRange and used the below code to give border

((Range)excelSheet.get_Range(exRange)).Cells.Borders.LineStyle = XlLineStyle.xlContinuous;

After

answered Sep 10 '16 at 11:24

Sarath KS

18,103
11
73
80

2

+1! Setting directly the `lineStyle` property in `Borders` is not only simplier but also significantly faster than on each of the borders: in my case (sheets with ~50k cells), the operation went from ~145ms to ~35ms per sheet, which is a nice x4 improvement!. – OxTaz Dec 22 '16 at 18:48

score 1 · Answer 5 · edited Jun 22 '16 at 09:49

1

For Each range In ranges
    For Each row As Range In .Range(range).Rows
        row.Cells.BorderAround(XlLineStyle.xlContinuous)
        row.Cells.Borders.Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous
        row.Cells.Borders.Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous
    Next
Next

edited Jun 22 '16 at 09:49

Tunaki

125,519
44
317
399

answered Jun 22 '16 at 09:49

Isla

11
1

score 1 · Answer 6 · answered Aug 10 '17 at 08:45

Why not to do simply:

Excel.Range tRange = xlWorkSheet.UsedRange;
tRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
tRange.Borders.Weight = Excel.XlBorderWeight.xlThin;

Note: apply border after the row and cell (range) filled with data to get range simply using function .UsedRange()

score 1 · Answer 7 · answered Aug 22 '17 at 12:28

1

Microsoft.Office.Interop.Excel.Range tRange = xlWorkSheet.UsedRange;
        tRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
        tRange.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

answered Aug 22 '17 at 12:28

Rakesh Kumar

11
2

2

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – Vivek Molkar Aug 22 '17 at 12:41

Excel Interop - Draw All Borders in a Range

7 Answers7

Linked