upload excel and store into database using mvc 5

Question

can you some one please direct me how to resolve the issue. i am building an application that upload the excel file and read the data from excel and store the value into database in mvc 5 c# . but i am getting error on var adapter = new OleDbDataAdapter(SELECT * FROM [Sheet1$] connectionString); var ds = new DataSet(); adapter.Fill(ds ExcelTable); getting error on adapter.Fill(dsExcelTable);

Dharmraj Thakur · Answer

Hi Khushboo see... https://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB If you see this error: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. Try one of these: If you running 64 bit of Windows change the target platform of build to x86 or The Engine should be installed by default but in case it is not manually download Microsoft Access Database Engine 2010 Redistributable choose the correct build (x86 or x64) and install it.

Leon D · Answer

The better way is to use a 3rd party library. Free Spire.XLS supports to import data to excel and export excel to datatable with several lines of code you can give it a try. Guidance for your reference: https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Excel-Import/Export-Import-Data-from-Excel-to-Datatable.html https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html

Amit Kumar · Answer

Hi Can do onething that use this for your connection string. First: use it and try string connectionStringV4 = Provider=Microsoft.Jet.OLEDB.4.0; Data source={0};Extended Properties=Excel 8.0;IMEX=1;; Second : public static DataTable exceldata(string filePath) { DataTable dtexcel = new DataTable(); bool hasHeaders = false; string HDR = hasHeaders ? Yes : No; string strConn; if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == .xlsx) strConn = Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + filePath + ;Extended Properties=\Excel 12.0;HDR= + HDR + ;IMEX=0\; else strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + filePath + ;Extended Properties=\Excel 8.0;HDR= + HDR + ;IMEX=0\; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables new object[] { null null null TABLE }); //Looping Total Sheet of Xl File /*foreach (DataRow schemaRow in schemaTable.Rows) { }*/ //Looping a first Sheet of Xl File DataRow schemaRow = schemaTable.Rows[0]; string sheet = schemaRow[TABLE_NAME].ToString(); if (!sheet.EndsWith(_)) { string query = SELECT * FROM [ + sheet3 + ]; OleDbDataAdapter daexcel = new OleDbDataAdapter(query conn); dtexcel.Locale = CultureInfo.CurrentCulture; daexcel.Fill(dtexcel); } conn.Close(); return dtexcel; } Third: private void button1_Click(object sender EventArgs e) { //connect database OleDbConnection connection = new OleDbConnection(); connection.ConnectionString @Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb;User Id=;Password= OleDbCommand command = new OleDbCommand(); command.CommandText = select * from parts; DataSet dataSet = new System.Data.DataSet(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandTextconnection); dataAdapter.Fill(dataSet); DataTable t = dataSet.Tables[0]; //export datatable to excel Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; sheet.InsertDataTable(t true 1 1); book.SaveToFile(insertTableToExcel.xls); System.Diagnostics.Process.Start(insertTableToExcel.xls); } After that you problem is not shortout then post your whole code and error screen shot.

Khushboo Kumari · Answer

sir Actually i am working on a system that does not have/installed ms-office .so is it possible to work for the same. Getting error as screen schot i am uploading

Khushboo Kumari · Answer

sir I had tried for the same but getting error on adapter.Fill(ds ExcelTable);

Amit Kumar · Answer

Hi Khushboo Type to create an object of table and then put it where DataAdapter is fill like. DataTable dt= new DataTable(); var adapter = new OleDbDataAdapter(SELECT * FROM [Sheet1$] connectionString); var ds = new DataSet(); adapter.Fill(ds dt); Regards Amit