Read from Excel sheet c#

Question

I am trying to read an excel sheet using this code

Dictionary<string, DataTable> tables = new Dictionary<string, DataTable>();//Microsoft.Jet.OLEDB.4.0 
string sConnection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'", filePath);
OleDbConnection oleExcelConnection = new OleDbConnection(sConnection);

foreach(string sheet in Sheets)
{
    DataTable dt = GetDataTable("SELECT * from [" + sheet + "$]", sConnection);

    tables.Add(sheet, dt); 
}

After I run it got this exception The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

and I downloaded the component from here 'https://www.microsoft.com/en-us/download/confirmation.aspx?id=23734' and worked correctly but when I deploy it it doesn't work anyone can help ?

http://stackoverflow.com/questions/17716207/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine-w/17716238#17716238 — Steve, Apr 26 '16 at 15:17
Thanks, but this exception arises only from the deployed code, I don't know what's the problem with that . — Hana, Apr 26 '16 at 15:40
I am just testing that on the server locally after I deployed, and I have installed the driver on the server from the link that I posted above .. — Hana, Apr 26 '16 at 15:53
why don't you do a google search on the following error and you will find tons of solutions / suggestions on how to fix your issue. `C# 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.` — MethodMan, Apr 26 '16 at 16:23
You can see in the question I faced this exception and found a solution for it that was the need to install the driver , and then worked correctly after the deploy we have a problem @MethodMan — Hana, Apr 26 '16 at 16:33
I have done much search with no benefit , that is way I am asking here @MethodMan — Hana, Apr 26 '16 at 16:35
you must be searching incorrectly in all due respect.. here try looking here http://stackoverflow.com/questions/6649363/microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine — MethodMan, Apr 26 '16 at 16:37
OK I read that answer before and I installed the driver as I said million times above :) , please don't judge,if you just couldn't read the question carefully please sir — Hana, Apr 26 '16 at 16:48
@Hana Did you download that file on your production server..? — Rob, Apr 29 '16 at 03:08
@MethodMan That question is in relation to operations within excel, not programatically. While they may stem from the same issue, they are not duplicates. — Rob, Apr 29 '16 at 03:09
@Hana The issue can be resolved via Steve's recommended duplicate target. It doesn't work on deployment because clearly the architecture of the two servers (your local and production) are different. Please read it clearly — Rob, Apr 29 '16 at 03:10
@Rob I am testing on the production sever, am not accessing it remotely through my local machine's browser , and I have downloaded that file on the production server also. I changed the debugging version and that didn't work also , I even tried to enable the application bool 32 bit — Hana, Apr 29 '16 at 12:15
@Hana Did you check the version of the driver? Is your production server 64 bit? What version is the driver? — Rob, Apr 29 '16 at 12:50

score 0 · Answer 1 · answered Apr 26 '16 at 16:41

Please use execlDatareader for nugget package and use this code .Please change path to user file location.

execldatareader work everywhere but ole db must be installed on machine if u goes live with ole db can give u error.

string path1 = Path.Combine(HttpContext.Server.MapPath("~/App_Data/" + sessionManagement.GetUserId()), Path.GetFileName(model.FileName));
                FileStream stream = System.IO.File.Open(path1, FileMode.Open, FileAccess.Read);

                IExcelDataReader reader = null;
                DataSet result = new DataSet();
                try
                {
                    if (path1.EndsWith(".xls"))
                    {
                        reader = ExcelReaderFactory.CreateBinaryReader(stream);
                        reader.IsFirstRowAsColumnNames = true;
                    }
                    if (path1.EndsWith(".xlsx"))
                    {
                        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                        reader.IsFirstRowAsColumnNames = true;
                    }
                    result = reader.AsDataSet();
                    reader.Close();
                }
                catch (Exception ex)
                {
                    Dispose();
   }
                DataTable dt = result.Tables[0];

thanks @Harvinder , i will try this method but please do you know if I can read all the sheets in the excel file or get one by name ? — Hana, Apr 26 '16 at 16:50
I tried this code and I could just read the first sheet in the excel , do you know how can I read all the sheets there ? — Hana, Apr 27 '16 at 10:16
Can you use for each when add into datatable ...in my code I specify the DataTable dt = result.Tables[0] — Harvinder Singh, Apr 27 '16 at 16:51
when I try to access the second sheet DataTable dt = result.Tables[1] it gives me exception , that it wasn't found , I can see there's only one sheey loaded in the dataset ,, I don't know why it doesn't load all the sheets — Hana, Apr 28 '16 at 13:30

score 0 · Answer 2 · answered Apr 29 '16 at 14:02

Your MS ACE driver version (x86 / x64) must match targeted platform of your VS solution. Click on Project menu>Properties. Select "Build" tab. On your screen, locate a.combobox placed immediate right of label [Platform target:].change value of combobox from "Any CPU" to x86 or x64 whichever version you have and you will get rid of that error.

Read from Excel sheet c#

2 Answers2