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 24 · 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 10 · Answer 2 · answered Sep 10 '16 at 11:24

10

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 Subramanian

20,027
11
82
86

3

+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 9 · Answer 3 · edited Feb 04 '15 at 03:22

9

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,566
16
26
32

answered Feb 04 '15 at 02:15

E Coder

295
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 4 · 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,276
5
34
53

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

score 3 · Answer 5 · 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 6 · 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

132,869
46
340
423

answered Jun 22 '16 at 09:49

Isla

11
1

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

score 0 · Answer 8 · edited Aug 31 '22 at 23:34

0

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

That way is not working, you will get something like this:

edited Aug 31 '22 at 23:34

T.S.

18,195
11
58
78

answered Aug 27 '22 at 18:55

Zirkevin

1
1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/32586603) – Hoppeduppeanut Sep 02 '22 at 03:56

Excel Interop - Draw All Borders in a Range

8 Answers8

Linked