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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Import.aspx.cs" Inherits="Excel_Import.Import" %><!DOCTYPE html><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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | <?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
1 2 3 4 5 6 7 8 9 10 11 | 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