This project has moved. For the latest updates, please go here.

InsertRowsBelow loses the style of the previous line

Feb 7, 2012 at 1:17 PM

Hello,

I have a problem with the instruction InsertRowsBelow.

I'm in an iteration that writes data into the cells of a row and then insert a new row below. The interest is to keep the style of my previous line (with its borders, formats,etc ...) but sometimes I have to put this line in bold and in others not. So after inserting my line, I change the style of my line inserted in non-bold.
The problem is that when I go to the next iteration, the style of my bold lines is lost for each insert !

Here is a simple example code:

    For i As Integer = 7 To 15

            xlSheet.Cell(i, 1).Value = "A"

      xlSheet.Cell(i, 2).Value = "B"

              xlSheet.Cell(i, 3).Value = "C"

              If xlSheetBis.Cell(i, 1).Value = "Bold" Then xlSheet.Row(i).Style.Font.Bold = True

             Dim myRows As Excel.IXLRows = xlSheet.Row(iRow).InsertRowsBelow(1)

             myRows.Style.Font.Bold = False

    Next

Do you have any ideas?

Coordinator
Feb 7, 2012 at 4:23 PM

It may have something to do with the iRow variable. The following works fine:

    var wb = new XLWorkbook();
    var ws = wb.Worksheets.Add("Sheet1");
    var rowsToBold = new HashSet<Int32> { 2, 4, 5, 6, 10 };

    for (Int32 ro = 1; ro <= 15; ro++)
    {
        var currentRow = ws.Row(ro);
        currentRow.Cell(1).Value = "A";
        currentRow.Cell(2).Value = "B";
        currentRow.Cell(3).Value = "C";
        if (rowsToBold.Contains(ro))
            currentRow.Style.Font.Bold = true;

        var inserted = currentRow.InsertRowsBelow(1);
        inserted.Style.Font.Bold = false;
    }



By the way, there's no need to set the line to bold twice so I'd refactor it to this:

    for (Int32 ro = 1; ro <= 15; ro++)
    {
        var currentRow = ws.Row(ro);
        currentRow.Cell(1).Value = "A";
        currentRow.Cell(2).Value = "B";
        currentRow.Cell(3).Value = "C";
        currentRow.Style.Font.Bold = rowsToBold.Contains(ro);
        currentRow.InsertRowsBelow(1);
    }

 

Or:

    Int32 rowStart = 1;
    Int32 rowEnd = 15;
    var currentRow = ws.Row(rowStart);
    for (Int32 rowNum = rowStart; rowNum <= rowEnd; rowNum++)
    {
        currentRow.Cell(1).Value = "A";
        currentRow.Cell(2).Value = "B";
        currentRow.Cell(3).Value = "C";
        currentRow.Style.Font.Bold = rowsToBold.Contains(rowNum);
        currentRow = currentRow.InsertRowsBelow(1).First();
    }

Feb 8, 2012 at 6:25 AM

Thank you for your code but I do not know in advance what will be my lines in bold (I know that during my iteration). In addition to the InsertLineBelow loses bold style but also the borders, etc ... so it will be more complicated.

Thank you anyway I'll try to manage otherwise.

Coordinator
Feb 8, 2012 at 2:08 PM

You don't have to know in advance. Just replace "rowsToBold.Contains(rowNum)" with "FunctionThatReturnsTrueIfThisRowNeedsToBeBold(rowNum)".