none
C# Excel Cell borders not being cleared RRS feed

  • Question

  • I have sought out code to remove all the borders external and internal to a part of a row, but neither of the 2 (actually 3) ways below of doing I found on the net actually work! Can someone please help me out with some code that will remove all borders from a range of cells (in C# with Excel). The following do not work- when i print the Excel file I still have borders around and inside the top row range of cells. The 2 ways I found on the net for deleting borders are in Cases 3 and Case 4 respectively. (Actually there are 2 ways of doing so which I have tried in Case 3.) I am using Excel 2013 Interop with C#: 

    (You will notice that I set all borders for the whole sheet at the top of the code - this is correct as I need to do this for the data rows, but then I want to "undo" that for the Header rows as the easiest way of doing so  (with 4 different reports.)) 

    So could someone please help me with a reliable and functioning way of clearing cell borders within a range.

    Excel.Range AllBorders = xlWS.UsedRange;
    AllBorders.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
    RowFarben.Borders.Weight = Excel.XlBorderWeight.xlThin;
    Excel.Borders borders = null;
    // Aber jetzt sollen wir die Header Rows mit kein Borders in dem White Space ausstatten:
    Excel.Range ClearHeaderBorders = null;
    switch (numReport)
    {
        case 1:
        	// Keine White Space in Header Row hier
            break;
        case 2:
        case 3:
            // Barcode Header Reihe ist schon gemacht oben.  Nein - ist es nicht - unten gemacht!
            // Also nur die erste Header Reihe hier hat Whitespace (Einheit)
            ClearHeaderBorders = xlWS.Range[xlWS.Cells[1, DummyCol_Number], xlWS.Cells[1, DummyCol_Number]];
            // Jetzt räumen wir die Header auf. Wir wollen kein Borders innherhalb:
            ClearHeaderBorders.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            foreach (Excel.Border ClearBorder in ClearHeaderBorders.Borders)
            {
                 ClearBorder.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            }
            // Farben von 2 oberen Nr. Cells entfernen - kopieren die Weiss von Neben an
            //ClearHeaderBorders = xlWS.Range[xlWS.Cells[1, 1], xlWS.Cells[1, 1]];
            xlWS.Cells[1, DummyCol_Number].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; 
            //ClearHeaderBorders.Interior.Color = xlWS.Cells[1, 2].Interior.Color; // Sicherer
            break;
        case 4:
            // Barcode Header Reihe ist schon gemacht oben.  Nein - ist es nicht - unten gemacht!
            // Also 2 Header Reihe hier - also 1te (Einheit), 2te (Qualität) Reihe in Worksheet
            ClearHeaderBorders = xlWS.Range[xlWS.Cells[1, DummyCol_Number], xlWS.Cells[2, DummyCol_Number]];
            borders = ClearHeaderBorders.Borders;
            borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            xlWS.Cells[1, DummyCol_Number].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlWS.Cells[2, DummyCol_Number].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; 
            break;
    }

    I have added the following 2 lines of code in to the code above but again it is still not clearing the borders. I have no merged or protected cells in the range. I can also clear them manually on the worksheet via the Excel menus, but this code above is somehow not doing its job, or something else is going on.

    borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
    borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlLineStyleNone;










    • Moved by CoolDadTx Wednesday, July 1, 2015 3:05 PM Office related
    • Edited by Sansevieria Wednesday, July 1, 2015 4:14 PM
    Wednesday, July 1, 2015 1:50 PM

Answers

  • Hi Sansevieria,

    Thanks for feedback for this issue.

    I am able to reproduce this issue using XlBordersIndex enum. Please try the code below and let me know whether it works for you:

     foreach (XlBordersIndex borderIndex in new[] { 
                  (XlBordersIndex)Constants.xlTop, 
                   (XlBordersIndex)Constants.xlRight, 
                    (XlBordersIndex)Constants.xlLeft, 
                    (XlBordersIndex)Constants.xlBottom })
                {
                      Globals.ThisAddIn.Application.ActiveCell.Borders[borderIndex].LineStyle = XlLineStyle.xlLineStyleNone;
                }
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 2, 2015 6:26 AM
    Moderator

All replies

  • Hi Sansevieria,

    Thanks for feedback for this issue.

    I am able to reproduce this issue using XlBordersIndex enum. Please try the code below and let me know whether it works for you:

     foreach (XlBordersIndex borderIndex in new[] { 
                  (XlBordersIndex)Constants.xlTop, 
                   (XlBordersIndex)Constants.xlRight, 
                    (XlBordersIndex)Constants.xlLeft, 
                    (XlBordersIndex)Constants.xlBottom })
                {
                      Globals.ThisAddIn.Application.ActiveCell.Borders[borderIndex].LineStyle = XlLineStyle.xlLineStyleNone;
                }
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 2, 2015 6:26 AM
    Moderator
  • Hello Fei, I gave up! I simply put all the above commands in a for next loop and cycled around all of the cells deleting the borders. Your idea did help - thanks. This range command however did not work for some reason (DummyCol_Number contained something like 17 each time):

    ClearHeaderBorders = (Excel.Range)xlWS.Range[xlWS.Cells[1, DummyCol_Number], xlWS.Cells[2, DummyCol_Number]];

    // Nun ich habe alle diese Methode ausprobiert und keine klappt die Borders zu löschen. // Deswegen läuft jetz eine Schliefe durch alle individuelle Cells! // Barcode Header Reihe ist schon gemacht oben. Nein - ist es nicht - unten gemacht! // Also 2 Header Reihe hier - also 1te (Einheit), 2te (Qualität) Reihe in Worksheet for (int iCols = 1; iCols <= DummyCol_Number; iCols++) // this will apply it from DummyCol_Number to end of columns { for (int iRows = 1; iRows <= 2; iRows++) // this will apply it from DummyCol_Number to end of columns { xlWS.Cells[iRows, iCols].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone; xlWS.Cells[iRows, iCols].Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone; xlWS.Cells[iRows, iCols].Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone; xlWS.Cells[iRows, iCols].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlLineStyleNone; xlWS.Cells[iRows, iCols].Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlLineStyleNone; if (iRow == 1) // Lösch die top Reihe unteren Borders aber 2te Reihe nicht { // sonst verlieren wir die oberen Border von dem Menu. xlWS.Cells[iRows, iCols].Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlLineStyleNone; } xlWS.Cells[iRows, iCols].Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlLineStyleNone; xlWS.Cells[iRows, iCols].Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlLineStyleNone; } }

    // Jetzt setzen wir wieder die Linke Border von dem Linke Seite der Einheit und Qualitäten (als gelöscht) xlWS.Cells[1, DummyCol_Number + 1].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; xlWS.Cells[2, DummyCol_Number + 1].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; break;




    Thursday, July 2, 2015 4:28 PM
  • Hi Sansevieria,

    Based on the code, you are using the the XlBordersIndex collection directly. It will not work.

    Please use the Constants and convert it to XlBordersIndex.  The code sample works well for me, please let me know whether it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 3, 2015 6:55 AM
    Moderator