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

How to find one cell element without using loop in Epplus

Dec 15, 2012 at 7:37 AM
Edited Dec 15, 2012 at 7:39 AM

Hello!

We have big excel file, and file has like 15 sections. We were using office and it was taking quite much time to import data from excel to database. 

In office we are using Find function to get the row number and looping.  In office excel its working like this

 _Worksheet xlWorksheet = xlWorkbook.Sheets[1];                  

Range xlRange = xlWorksheet.UsedRange;

var section1Heading = xlRange.Find("Section 1");

var section2Heading = xlRange.Find("Section 2");

int row = section1Heading.Row;

for(int rowIndex = row; rowIndex<section2Heading.row ; rowIndex++) { }

My Question is this, that how can i achieve the same result using Epplus. We have to use Epplus.

Jul 25, 2014 at 8:50 AM
Hello!

Did you solve your issue? If so, how?

Until now I used the Excel library, but the stability of my programs are not always on top. Usually the cause is implementation of programs with parallel work in Excel users .

So I was wondering how you can work with Excel without using the program library. A little Googling I found EPPlyus. The library at the first approach I found everything I needed except for the Range.Find methods.

How did it happen that such a useful method is not implemented?

Yes, it is possible to compare the data in the cycle, but it works almost an order of magnitude slower search. I have half of the programs conducts a search in the cycle, so the difference in speed of 1 second in a series of 100,000 rows gives slowdown in 27 hours.
Jul 29, 2014 at 10:37 AM
Edited Jul 29, 2014 at 10:39 AM
Hello, I have never used the Find function, but if I understand correctly, it returns a range of cells with a certain value, right?

If so, have you tried using Linq to find the specific cells? I don't know about the performance though.

Example in C#
using (ExcelPackage pkg = new ExcelPackage(new FileInfo("newFile.xlsx")))
{
    ExcelWorksheet ws = pkg.Workbook.Worksheets.Add("NewSheet");

    ws.Cells["G140"].Value = "value you want to find";

    foreach (ExcelRangeBase cell in ws.Cells.Where(c => c.Value == "value you want to find"))
    {
        Console.WriteLine(cell.Address);
    }
    pkg.Save();

}
Jul 30, 2014 at 7:39 AM
Hello, eagle47.

Proposed code works. However, the rate is still suffers. Primarily because of the attempt to view all the used area of ​​the sheet.

How not to look over the entire sheet, but only for a specific range? Excel for example it would look like this:
Excel.Range RangeS;
Excel.Range SearchObj;
RangeS = WorkSheet.get_Range("B7", "B" + (RowNum - 1));
SearchObj = RangeS.Find("value you want to find", Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, false, false);
}
if (SearchObj != null)
{
    // do something
}
In addition, the method of "Find" has many useful options.
Jul 30, 2014 at 8:37 AM
You could replace
ws.Cells.Where(c => c.Value == "value you want to find")
by
ws.Cells["B7:B" + (RowNum - 1).ToString()].Where(c => c.Value == "value you want to find")
to only iterate over a specified range (B1 to B(RowNum-1)). Hopefully, this will perform better.

Using Linq, you can also search for cell with other types of information (basically any property of a ExcelRangeBase in EPPlus) such as
wsData.Cells["B7:B" + (RowNum - 1).ToString()].Where(c => c.Style.Font.Bold && c.Style.Font.Size > 10);
You can also use other Linq methods such as Select, OrderBy, Max, Min, etc. which could be useful.
Jul 30, 2014 at 11:36 AM
Hello, eagle47.

Unfortunately the time to perform the same tasks for the two methods 1 to 6 for the range "A1: A400000" (0.468 sec and 3.18 sec) in favor of the standard library. Of course, when the number of lines is much less, the difference is hardly noticeable. But I assume that the volume of data can be large.

Have to work with the standard library.

Thank you.