How can I read data from Excel file reliably?

Question

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 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.

Answer 1

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

Answer 2

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.

Answer 3

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.