Find a row containing two values in foreach Excel interop

Question

How can I find the row in an excel sheet using the Excel interop without having to loop over every row? I;d like to loop over my collection using a foreach statement and where the employeeId and charge number exist I would like to update the total hour count in total hour cell for that employee and charge number.

Here's my current code, but man is it slow!!

In place of the nested for loop could I use the excel range to find the row containing the employeeId and the charge number?

var lastLine = sheet.UsedRange.Rows.Count; 

foreach (var timeEntry in filteredList.OrderBy(x =>int.Parse(x.ChargeNumber.Split('.')[2], CultureInfo.CurrentCulture)))
{
    for (var row = 3; row <= lastLine; row++)  //loop over archive sheet
    {
        if (Convert.ToInt32(timeEntry.EmployeeId) == sheet.Cells[row, 1].Value2 && timeEntry.ChargeNumber == sheet.Cells[row, 5].Text.Trim())
        {
            sheet.Cells[row, 6] = timeEntry.TotalHours;

            if (timeEntry.TimeDifferential > 0)
            {
                sheet.Cells[row, 8] = "Add " + timeEntry.TimeDifferential.ToString(CultureInfo.CurrentCulture) + " from " + previousMonthYear;
            }
            else if (timeEntry.TimeDifferential < 0)
            {
                var normalTime = -(timeEntry.TimeDifferential);

                sheet.Cells[row, 8] = "Subtract " + normalTime.ToString(CultureInfo.CurrentCulture) + " from " + previousMonthYear;
            }
            break;
        }
    }
}

Looping over cells is very slow using Interop due to RCW's. Instead get a 2D array of cells and process the `object[,]` array: http://stackoverflow.com/questions/356371/excel-interop-efficiency-and-performance — Jeremy Thompson, May 12 '17 at 04:56

score 1 · Answer 1 · answered May 12 '17 at 05:16

You can use Range.Find Method which is far more efficient than looping.

I was also looping each row in one of my excel automation project but it was too slow, so i used Range.Find

Modify it as per your requirement.

Range Find(
    Object What,
    Object After,
    Object LookIn,
    Object LookAt,
    Object SearchOrder,
    XlSearchDirection SearchDirection,
    Object MatchCase,
    Object MatchByte,
    Object SearchFormat
)

More info - See it here

Examples

Find a row containing two values in foreach Excel interop

1 Answers1