Step1 :-
Create new Empty project(asp.net web application .net framework) with the name of Excel_Import
Step 2:-
After that you need to add MySql.Data.dll reference steps given below I also provide MySql.Data.dll file on project source code
Right click on References then click On Add References
Then click on Browse
Then go to location and select MySql.Data.dll
Step 3:-
After that add a web form with the name of Import.aspx and past below code
Import.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Import.aspx.cs" Inherits="Excel_Import.Import" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button Text="Upload" OnClick="Upload" runat="server" />
</div>
</form>
</body>
</html>
Import.aspx.cs
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Excel_Import
{
public partial class Import : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Upload(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Uploads/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("EmployeeId", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Salary", typeof(decimal)) });
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(consString))
{
con.Open();
if (dtExcelData.Rows.Count > 0)
{
foreach (DataRow row in dtExcelData.Rows)
{
MySqlCommand cmd = new MySqlCommand("insert into tbl_employee(Name,Salary) values('" + row["Name"].ToString() + "','" + row["Salary"].ToString() + "')", con);
cmd.ExecuteNonQuery();
}
con.Close();
}
}
}
}
}
}
Web.config
<?xml version="1.0" encoding="utf-8"?>
<!--
For more information on how to configure your ASP.NET application, please visit
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="constr" connectionString="Data Source=127.0.0.1;Database=excel_imports;User Id=root;password=;" providerName="System.Data.MySqlClient" />
<add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name ="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.7.2" />
<httpRuntime targetFramework="4.7.2" />
</system.web>
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />
<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+" />
</compilers>
</system.codedom>
</configuration>
After That Create database with name of excel_imports then create table
CREATE TABLE `tbl_employee` (
`EmployeeId` int(11) NOT NULL,
`Name` varchar(100) NOT NULL,
`Salary` decimal(10,3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `tbl_employee`
ADD PRIMARY KEY (`EmployeeId`);
ALTER TABLE `tbl_employee`
MODIFY `EmployeeId` int(11) NOT NULL AUTO_INCREMENT;
Then right click on your folder name and create a folder Uploads
Then run project now you see the output like below image
This excel file also available on source code file