How to Check and merge two rows if next value is same or not in excel with epPlus

Question

I am working on dynamic Excel creation with the help of EPPlus library and I have an excel which data looks like:

Name     EmpCode   Department      attendance
Prashant  111      CSE         70% for Sep
Prashant  111      CSE         90% for Oct
XYZ       112      HR          50% for Sep
XYZ       112      HR          90% for Oct

What I want is: if the current EmpCode is equal to the value of next row then merge this both columns so the expected output will be

I am damn sure that each empCode will be repeated only two times.

The code what I have tried:

for (var rowNum = 1; rowNum <= ws.Dimension.End.Row; rowNum++)
 {
   var row = ws.Cells[string.Format("{0}:{0}", rowNum)];
 }

Psalms

Answer 1

This code will only work if the empcode is repeated twice but you said you're damn sure it will only be repeated twice so it should be okay, just not very scale-able.

Once you get the data in your spreadsheet you have to loop through all the rows in your dataset. At the beginning of the loop you set the range of your current row and at the end of the loop you set the range of your prior row. If the previous range is set, you evaluate the columns of each row to determine if you should merge the cells together.

using (var p = new OfficeOpenXml.ExcelPackage(new FileInfo(@"c:\FooFolder\Foo.xlsx")))
{
    ExcelWorkbook wb = p.Workbook;
    ExcelWorksheet ws = wb.Worksheets[1];

    //create variable for previous range that will persist through each loop
    ExcelRange previousRange = null;

    //set position of first column to merge
    int mergecellBegin = 1;

    //set position of last column to merge
    int mergeCellEnd = 3;

    //create variable to check the cells of your rows
    bool areCellsEqual;

    //iterate through each row in the dataset

    for (var rowNum = 2; rowNum <= ws.Dimension.End.Row; rowNum++)
    {
        ExcelRange currentRange = ws.Cells[rowNum, 1, rowNum, mergeCellEnd];

        //will skip if we haven't set previous range yet
        if (previousRange != null)
        {
            //reset your check variable
            areCellsEqual = true;
            //check if all cells in the ranges are qual to eachother
            for (int i = 1; i <= mergeCellEnd; i++)
            {
                //if the cells from the ranges are not equal then set check variable to false and break the loop
                if (!currentRange[rowNum, i].Value.Equals(previousRange[rowNum - 1, i].Value))
                {
                    areCellsEqual = false;
                    break;
                }
            }

            //if all cells from the two ranges match, merge them together.
            if (areCellsEqual)
            {
                //merge each cell in the ranges
                for (int i = 1; i <= mergeCellEnd; i++)
                {
                    ExcelRange mergeRange = ws.Cells[rowNum - 1, i, rowNum, i];
                    mergeRange.Merge = true;
                }
            }
        }

        //sets the previous range to the current range to be used in next iteration
        previousRange = currentRange;
    }

    p.Save();
}

Jahdai

Slightly change in the requirement, how its going to work for more than two columns? – Psalms

Im looking for solution to merge more than two column ... any update on this ? – Hudson

Answer 2

You can use the following function to merge rows when they have the consecutive values are the same (works to merge more than two columns):

    Public Sub MergeRowSameValues(ByRef sheet As ExcelWorksheet, rowInicial As Integer, rowFinal As Integer, col As Integer)
        Try
            Dim offset As Integer
            With sheet
                For i = rowInicial To rowFinal
                    offset = 0
MergeCells:
                    offset = offset + 1
                    If .Cells(i, col).Value = .Cells(i + offset, col).Value AndAlso .Cells(i + offset, col).Value IsNot Nothing Then
                        sheet.Cells(i, col, i + offset, col).Merge = True
                        GoTo MergeCells
                    End If
                    i = i + offset - 1
                Next
            End With
        Catch ex As Exception
            
        End Try
    End Sub

Where rowIncial is the first row and rowFinal is the last row where do you want to check if they have the same value.

Awad