Best way reading from dirty excel sheets

Question

I have to manipulate some Excel documents with C#. It's a batch process with no user interaction. It's going to parse data into a database, then output nice reports. The data is very dirty and cannot be ready using ADO. The data is nowhere near a nice table format.

Best is defined as the most stable(updates less likely to break)/ clear(succinct) code. Fast doesn't matter. If it runs in less than 8 hours I'm fine.

I have the logic to find the data worked out. All I need to make it run is basic cell navigation and getvalue type functions. Give me X cell value as string, if it matches Y value with levenshtein distance < 3, then give me Z cell value.

My question is, what is the best way to dig into the excel?

VSTO? Excel Objects Library? Third Option I'm not aware of?

Answer 1

VSTO is kind of a pain because of permissions and the fact that your dll becomes hooked to the document you're using. Assuming you're not actually changing the files, and ADO is definitely not an option, I would say that automation through the Excel COM interfaces is your best bet. It lets you program the way you normally would for any other application, and gives you just as many options for data extraction as VSTO.

Answer 2

The Office programs can be loaded as objects in .NET. The following is the coding stub that I used to load Excel into VB6. The code is essentially going to be the same regardless of which MS language you use.

Dim xlApp As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

On Error Resume Next
wb = xlApp.Workbooks.Open("c:\testdata.xls")
If Err.Number > 0 Then
    If Err.Number = 1004 Then
        MsgBox("File not found")
    Else
        MsgBox("Error " & Err.Number & " occurred.")
    End If
    Exit Sub
End If

ws = wb.Sheets("Sheet1")
Text1.Text = ws.Cells(1, 1).Value

wb = Nothing
ws = Nothing
xlApp = Nothing

Answer 3

Well try to see stack over flow question http://stackoverflow.com/questions/2458481/convert-excel-range-to-ado-net-dataset-or-datatable-etc/2460130#2460130