System.Data.OleDb.OleDbExcepti...

See more:

C#

MS-Excel

SharePoint2013

Hi,
Below is my code, I want upload a excel file as input.
Then,

step 1:I have saved file in one path.
step 2:Read Excel file.
step 3:Convert excel into data set.
step 4:Insert data set into table using bulkinsert query.

Unspecified error occur while executing the code on the line Connection1.Open();

now code is deployed in share point site throwing error...
Please help me.

private static DataSet GetExcelAsDataSet(string fileName, bool firstRowIsHeader)
        {
            OleDbConnection excelConnection = null;
            OleDbDataAdapter adapter = null;
            DataSet ds = new DataSet();
            System.Data.DataTable dtTables = new System.Data.DataTable();
            //fileName = "C:\\myfolder\\UserInformationList.xlsx";
            string myconnection1 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=YES;'";
            using (OleDbConnection Connection1 = new OleDbConnection(myconnection1))
            {
               
                    Connection1.Open();
                    //to get the schema of the workbook.
                    dtTables = excelConnection.GetSchema();

                    //get the tables in the workbook
                    dtTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    String[] excelSheets = null;
                    if ((dtTables != null))
                    {
                        excelSheets = new String[dtTables.Rows.Count];
                        int i = 0;

                        // Add the sheet name to the string array.
                        foreach (DataRow row in dtTables.Rows)
                        {
                            excelSheets[i] = row["TABLE_NAME"].ToString();
                            i++;
                        }
                    }

                    //prepare dataset from the tables in the workbook
                    foreach (string sheet in excelSheets)
                    {
                        OleDbCommand cmd = new OleDbCommand();
                        cmd.Connection = excelConnection;
                        cmd.CommandText = "Select * from [" + sheet + "]";
                        System.Data.DataTable dtItems = new System.Data.DataTable();
                        string SheetName = sheet.Trim('\'');

                        SheetName = SheetName.Substring(0, SheetName.IndexOf("$"));
                        dtItems.TableName = SheetName;
                        if (!ds.Tables.Contains(SheetName))
                        {
                            adapter = new OleDbDataAdapter();
                            adapter.SelectCommand = cmd;
                            adapter.Fill(dtItems);
                            ds.Tables.Add(dtItems);
                        }
                    }
                
            }
return ds;
}

Note: i have used same code in C# web application its working fine.