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?

Forkey

Answer 1

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

Forkey

Answer 2

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

Telega

+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!. – Churchly

Answer 3

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;

Prepossession

Simplified: oRange.Borders.LineStyle = XlLineStyle.xlContinuous; – Ezequiel

@LucaZiegler a plus for .get_Item(.. that's the efficient way to do it. – Equivalent

Answer 4

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.

Stefa

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: – Forkey

Answer 5

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()

Rapeseed

Answer 6

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

Pentapody

Answer 7

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;

Crosspiece

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. – Tamatamable

Answer 8

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:

Eidson

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Goff