Consider i have an excel file >200000 rows in it. What is the fastest way that can be implemented to search a partcular column value in this file using c# asp.net. Any suggestion.
+2
A:
Assuming 1) you can cache the file contents fine (not too large, file doesn't change, etc) and 2) you don't already have a mechanism for reading the file, I would just read the file once (at application start, or lazy load on demand, or whatever) into memory - I have used and really like the FileHelpers libs from http://www.filehelpers.com/ - see their excel example @ http://www.filehelpers.com/example_exceldatalink.html
as part of the 'read in the file', you'd likely also create some indexes for the later queries. If you only cared about the one column, you could just push it all into a HashSet, for instance, so you can do a Contains later quickly.
James Manning
2010-08-11 04:37:40
+1 for FileHelpers, but note that for Excel support it requires Excel to be installed, and uses Excel Automation (COM Interop) to read Excel. As @John Saunders notes, this is not an officially supported configuration in a production server environment.
technomalogical
2010-08-12 13:30:04
+1
A:
You cannot access an Excel file from ASP.NET at all if you are using the Excel Automation APIs. These were written for use in a desktop application, not in a server application like ASP.NET. They will not work, are not supported, and may very well violate your license agreement with Microsoft.
There are third-party libraries that can access an Excel file safely from ASP.NET. These do not use the Automation APIs.
John Saunders
2010-08-11 04:49:21
Don't forget about about OLEDB queries with ADO.NET. While probably not any faster, they are an option as well.
technomalogical
2010-08-11 11:56:09
@John: OLEDB only requires Jet 4.0, which is included in the Microsoft MDAC; it does not require Excel to be installed, nor does it use Excel Automation. MDAC comes installed by default on Windows 2000 and later, and I presume it also is installed on server versions of windows (2003, 2008)
technomalogical
2010-08-12 13:28:08
A:
You may want to consider using an "OLE DB for Jet 4.0" connection, which you can query via ADO.NET. OLE DB access to Excel is provided via the MDAC component, which comes standard on versions of Windows after 2000. ConnectionStrings.com has OLE DB connection strings for connecting to Excel, as well as information on using Jet in a 64-bit environment.
technomalogical
2010-08-12 13:38:01