Topic: Read Excel Sheet to DataTable

[shers]

Hi,

I want to read an Excel sheet into a datatable. But I cannot go forward as it gives me an error, as follows.

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

What is the other option?

Thanks

[ChrisCarlson]

https://www.microsoft.com/en-gb/download/details.aspx?id=13255

This download will install a set of components that can be used to facilitate transfer of data between 2010 Microsoft Office System files and non-Microsoft Office applications.

[dgorsman]

Careful with that.  The x64 ACE provider cannot be installed with x32 MS Office 2010.  You cannot use x32 ACE with x64 AutoCAD (or other programs like Navisworks).  Reportedly newer versions of Office will allow the two to co-exist but you might need to use x64 MS Office.

[shers]

Using x64 MS Office is ruled out as the app is going to be distributed. I think I should find another tool to read excel.

[MexicanCustard]

Careful with that.  The x64 ACE provider cannot be installed with x32 MS Office 2010.  You cannot use x32 ACE with x64 AutoCAD (or other programs like Navisworks).  Reportedly newer versions of Office will allow the two to co-exist but you might need to use x64 MS Office.

You can install both on the same system you just have to install them via the command line with the "/passive" switch.  If like us you have 32 bit Office and 64 bit Autodesk.

[MexicanCustard]

Using x64 MS Office is ruled out as the app is going to be distributed. I think I should find another tool to read excel.

This is your best option. If you meet the community TOS, Syncfusion is a great product.  If not, might I suggest ClosedXml.

[ChrisCarlson]

Careful with that.  The x64 ACE provider cannot be installed with x32 MS Office 2010.  You cannot use x32 ACE with x64 AutoCAD (or other programs like Navisworks).  Reportedly newer versions of Office will allow the two to co-exist but you might need to use x64 MS Office.

You can install both on the same system you just have to install them via the command line with the "/passive" switch.  If like us you have 32 bit Office and 64 bit Autodesk.

Have you read anything about Office 2016 and the access libraries?

[n.yuan]

Reading/writing data from Excel worksheet as AutoCAD data source might not be a best solution, but is one of the mostly encountered scenario by us as AutoCAD programmers.

Most CAD app use either COM automation or DAO/ADO/ADO.NET to access data in Excel sheet, which unfortunately tie the CAD app to heavy dependencies: MS Offce/Excel installation, MS Access data engine (not to mention the complexity due to 32/64 bit).

With today's technology, if the CAD app still expects data from Excel sheet, there is no need to depend the app on those dependencies. OpenXML (or some free or not free libraries built on top of it; there are tons of them) can be used easily to access Excel sheet data. The only thing the CAD app project needs to do is to set reference to OpenXml dll, which will goes with your CAD DLL together.

[CADbloke]

If not, might I suggest ClosedXml.

EPPlus is another open-source option for Excel. I've had a good run with it. Definitely avoid any dependency on Excel etc. ClosedXML & EPPlus don't need Excel to run. Also, COM is much slower than anything else, apart from Lisp.

[dgorsman]

Careful with that.  The x64 ACE provider cannot be installed with x32 MS Office 2010.  You cannot use x32 ACE with x64 AutoCAD (or other programs like Navisworks).  Reportedly newer versions of Office will allow the two to co-exist but you might need to use x64 MS Office.

You can install both on the same system you just have to install them via the command line with the "/passive" switch.  If like us you have 32 bit Office and 64 bit Autodesk.

As noted, it doesn't work on Office 2010.  Some have reported it works after some hacking but I've found the drawbacks worse than the solution.

[kaefer]

Most CAD app use either COM automation or DAO/ADO/ADO.NET to access data in Excel sheet, which unfortunately tie the CAD app to heavy dependencies: MS Offce/Excel installation, MS Access data engine (not to mention the complexity due to 32/64 bit).

I'm advocating late-bound interop (COM) which avoids version dependencies. I need Excel installed anyway because it's my editor of choice before writing parts of the sheet's contents back to the respective Autodesk product.

Actually answering the question at hand, this is how a DataTable can be created from the object returned by the Value2 property of an Excel Range object. No error handling, assumes presence of named column headers.

Code - F#: [Select]

1. module DataTable =
2.     let ofArray2D o =
3.         let dt = new System.Data.DataTable()
4.         match o with
5.         | null -> ()
6.         | o ->
7.             let a =
8.                 match box o with
9.                 | :? (obj[,]) as a -> a
10.                 | o -> Array2D.create 1 1 o
11.             let jbase = a.GetLowerBound 0
12.             let jrange = {jbase + 1 .. a.GetUpperBound 0}
13.             let ibase = a.GetLowerBound 1
14.             let irange = {ibase .. a.GetUpperBound 1}
15.             for i in irange do
16.                 dt.Columns.Add(
17.                     string a.[jbase, i],
18.                     typeof<string> )
19.                 |> ignore
20.             for j in jrange do
21.                 let row = dt.NewRow()
22.                 for i in irange do
23.                     row.[i - ibase] <- a.[j, i]
24.                 dt.Rows.Add row    
25.         dt