2010 Office Data Connectivity Components

Recently I was creating SharePoint Designer Workflow Activity to import data from Excel. Traditionally I used OLEDB to do that, but my target platform was 64bit and I could not use 2007 OLEDB Drivers (64 bit drivers version has never been implemented). Fortunately Microsoft released 2010 Office System Driver Beta: Data Connectivity Components at the end of 2009. This driver works fine, but instead of "Provider=Microsoft.ACE.OLEDB.14.0" you still have to access it using "Provider=Microsoft.ACE.OLEDB.12.0" and "Extended Properties='Excel 12.0 Xml'". This would be probably fixed in release.

This driver also not always detecting last filled row in Excel, so make sure you are handling that.

Do not forget to go to Central Admin -> Shared Services -> Trusted Data Providers -> add it up there as Provider ID = "Microsoft.ACE.OLEDB.12.0" and Provider Type = "OLE DB".

Here is some sample code to help to work with it:

                            string sConnectionString = "";
                            string FileType = "";
                            switch (Path.GetExtension(sourceItem.File.ServerRelativeUrl))
                            {
                                case ".xlsx":
                                    FileType = "Excel 12.0 Xml;";
                                    break;
                                case ".xlsm":
                                    FileType = "Excel 12.0 Macro;";
                                    break;
                                case ".xlsb":
                                    FileType = "Excel 12.0;";
                                    break;
                                default:
                                    FileType = "Excel 12.0;";
                                    break;
                            }

                            sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + tempFile + ";" + "Extended Properties=\"" + FileType + "HDR=No;IMEX=0;\"";
                            OleDbConnection objConn = new OleDbConnection(sConnectionString);
                            objConn.Open();

                            OleDbDataReader dr = new OleDbCommand("SELECT * FROM [" + this.WorksheetName + "$]", objConn).ExecuteReader();

                            for (int i = 1; i <= RowStart; i++)
                            {
                                dr.Read();
                            }

                            while (dr.Read())
                            {
                                //Put your logic here
                            }
                            objConn.Close();