CODEWITHSUNDEEP
c#.netexcel
CodeMan5000
CodeMan5000

Reputation: 1087

Find a row containing two values in foreach Excel interop

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

Upvotes: 0

Views: 197

Answers (1)

Ambrish Pathak
Ambrish Pathak

Reputation: 3968

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

Upvotes: 1

Related Questions