CODEWITHSUNDEEP
c#.netexcelsearch
Nabil Laarif
Nabil Laarif

Reputation: 125

make search in excel files faster

i have a c# .net application that read and write from excel files. my search compare data from 20 cells in each file, so searching in 10000 files tkes 70 seconds which is very long !! any ideas of a better solution to make it faster. here is the read function: 

 public static void OpenExcel(string fileName, bool visibility, FunctionToExecute fn = null)
    {
        string addInPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "Microsoft\\AddIns\\mDF_XLcalendar.xla");

        deleg = fn;
        app = new Excel.Application();

        app.Workbooks.Open(addInPath);
        app.Workbooks.Open(fileName);

        app.ScreenUpdating = true;
        app.DisplayAlerts = true;
        app.Visible = visibility;
        app.UserControl = true;
        app.WindowState = Excel.XlWindowState.xlMaximized;

        EventDel_BeforeBookClose = new Excel.AppEvents_WorkbookBeforeCloseEventHandler(application_WorkbookBeforeClose);
        EventSave_BeforeBookClose = new Excel.AppEvents_WorkbookBeforeSaveEventHandler(Open_ExcelApp_WorkbookBeforeSave);

        app.WorkbookBeforeClose += EventDel_BeforeBookClose;
        app.WorkbookBeforeSave += EventSave_BeforeBookClose;

    }

and here is the code that do the search:

string searchString = ((RichTextBox)searchObject.LstObject[0]).Text.Trim();

            bool contain = ExcelFunctions
                                .RemoveDiacritics(ExcelFunctions.Read(GetSummaryXl, coord))
                                .ToLower()
                                .Contains(ExcelFunctions.RemoveDiacritics(searchString).ToLower());

            return string.IsNullOrEmpty(searchString) || (!string.IsNullOrEmpty(searchString) && contain);

i did many tests and it seems that my read function takes 90% of the search time in one file.

Upvotes: 0

Views: 531

Answers (3)

Allan Elder
Allan Elder

Reputation: 4094

The underlying reason you're suffering performance issues is that every read of a cell is a trip across the COM / .Net boundary, which is very expensive. If OLEDB doesn't suit, try reading all the data in one trip across the boundary; this can be done by returning a 2 dimensional array from a VBA macro and executing the macro via interop.

Another idea is to develop a COM add-in in C++ but that is far more complex.

Upvotes: 0

Johnaudi
Johnaudi

Reputation: 257

I'd prefer Multi-Threading if I was you, it would use up a-lot of CPU for sure, but it's x times faster compared to a single thread.

This is a well explained Multi-Threading tutorial, easy to use too!

Upvotes: 0

Steve
Steve

Reputation: 5545

I would suggest you try to use the OLEDB driver instead of the Office.Interop. It is much faster for reading data from excel.

For examples on how to connect to excel, check out ConnectionString.com

Upvotes: 2

Related Questions