How can I read data from Excel file reliably?

I'm looking for a way to read (write would be nice too - although not critical) an Excel file. I mean the 97-2003 format since MS provides OO XML toolkit for the 2007 format and I heard the toolkit is fine.

What I have done so far is just used the OleDB (Microsoft Jet) which has many issues and drawbacks that开发者_StackOverflow中文版 it's even hard to believe :)

I know there are many free libraries (i.e. on Codeplex) but they use OldeDb (so they suffer from the same problem).

The only one I've found, but have not tried yet, is http://exceldatareader.codeplex.com/ which apparently does binary reading of the Excel file.

I'd love to hear your recommendations and testimonials for libraries you have used or read about.

EDIT: Sorry if it wasn't obvious, but I mean a .NET library.

I am not sure what your constraints are but if I were you (and this is experience talking, trust me), I would avoid reading Excel files with anything but Excel. My recommendation is, keep it simple and stick to CSV. Most libraries (xlrd included) support only Excel's basic features (sorry, no charts or PivotTables) and so does CSV.

Did you have a particular language preference?
If you're comfortable with Python, I would heartily recommend xlrd.

It doesn't get much easier than the code sample below;
which will search through sheets of a workbook for a particular pattern:

import re, xlrd

def re_search(fname, query):
    ''' iterate through sheets of workbook searching for `query` '''
    book = xlrd.open_workbook(fname) 
    for sheet in book.sheets(): 
        for rowx in xrange(sheet.nrows): 
            for colx in xrange(sheet.ncols): 
                cell = sheet.cell(rowx, colx) 
                if cell.ctype == xlrd.XL_CELL_TEXT and query.search(cell.value):
                    yield cell.value

if __name__ == '__main__':
    my_pattern = re.compile('[A-Z]{3} (.*)')

    for matched_re in xlresearch('my_xl_file.xls', my_pattern):
        print matched_re

Using SSIS? How to import an Excel file into SQL Server 2005 using Integration Services http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205
Open Source? EPPPlus Create advanced Excel 2007/2010 spreadsheets on the server http://epplus.codeplex.com/
C#? How to import data from one column of Excel to listbox using C# How to import data from one column of Excel to listbox using C#
Linked Servers: How to use Excel with SQL Server linked servers and distributed queries http://support.microsoft.com/kb/306397
Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function http://www.codeproject.com/Articles/32581/Import-Data-from-Excel-to-SQL-Server
And Many More!!!

I don't know what is your case, but if you have an excel installed on the machine, you can use it through OLE interface.