fastest way to read a large spreadsheet

[Gabe Moothart]

I'm writing a web app which needs to be able to read large excel files
(~10k-100k rows) as quickly as possible. I could use the built-in
Excel interop, but I'm concerned about speed. Would it be better to
buy a 3rd-party library for this?

If so, are there any recommendations? Is there a market leader? The
product needs to provide c# bindings.

TIA,
Gabe

 

[Joel]

I would like to know what your mean by READ? Just values?

The 1st method is to copy areas from one worksheet to anther. Excel macros
are optimized to perform area copies quickly and efficiently. The problem is
they may use a lot of PC resources such as memory.

A 2nd method is to randomly access data using rows and columns. It is
slower to access excel spreadsheets by selecting rows and columns but this
method will use less resources.

A third option is to read the files without opening the files using ADO or
ODBC methods. This will use less resources. Basically you are opening up
the workbooks as Access Databases and then using Access macro instructions
from inside Excel VBA. If your data is all in one worksheet (a worksheet in
Access is a table) you can get the tabgle in one instruction.

A fourth option is to perform a query on the workbooks.

I'm not sure which option is going to be the fastest. I suspect the 3rd or
4th option. The problem on PC's to to request memory from the operating
system. When excel needs more memory is must get the extra memory from the
operating system which takes time. This is often is the cause for a program
to run slow. If there isn't available memory the Operating system performs
memory swaps saving blocks of memory temporarily on a hard drive. These
memory swaps significantly slows the PC down. The Real solution is to add
more memory to speed up your programs.

 

[Gabe Moothart]

Joel,
I'll be looping through the worksheet, reading the values of certain
columns in each row.

Regarding the ADO.net method - does that require Excel to be
installed?

TIA,
Gabe

 

[Joel]

What language are you programming in? If you are using C language or VB6
they have built in libraries to work with spreadsheets and don't need excel
installed. Other languages also have built in excel libraries.

I wasn't referering specifically to ADO.net. From Excel you can use the ADO
library to access data.