upload excel and store into database using mvc 5

Khushboo Kumari

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(ds,"ExcelTable");

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dharmraj Thakur8y ago
Accepted 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.
-1
Leon D8y ago
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
0
Amit Kumar8y ago
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.CommandText,connection);
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.
0
Khushboo Kumari8y ago
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
0
Khushboo Kumari8y ago
sir,
I had tried for the same but getting error on

adapter.Fill(ds, "ExcelTable");
0
Amit Kumar8y ago
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
0