Import Excel File in ASP.Net, C# with MySQL Database.
Excel File Format
Window before File Upload
Window After File Upload
Window after Data Inserted
DataBase Figure
CSS
.successPanel {
border: 1px #000;
color: gray;
font-weight: bold;
}
ASPX Page
<%@ Page Title="" Language="C#" MasterPageFile="~/popupMasterPage.Master" AutoEventWireup="true" CodeBehind="UserExcelpopup.aspx.cs" Inherits="CationHRManagementSystem.UserExcelpopup" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
/********************* JS Link ******************************/
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script>
/******************Method for Close Window***************************/
function fnclose() {
window.close();
window.opener.location.reload
}
/************End of Method for Close Window**********************/
/******************Method for Print Data***************************/
function printdiv(printpage) {
var headstr = "<html><head><title>Excel file Upload Status</title></head><body style='width:100%;height:100%'>";
var footstr = "</body>";
var newstr = document.all.item(printpage).innerHTML;
var oldstr = document.body.innerHTML;
document.body.innerHTML = headstr + newstr + footstr;
window.print();
document.body.innerHTML = oldstr;
return false;
}
/****************End of Method for Print Data***********************/
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<div id="div1" runat="server" style="background: white; overflow: auto; overflow-x: hidden;">
<table style="width: 100%;">
<tr>
<td style="text-align: right">
<button id="btnPrint" class="button-style" onclick="printdiv('div_print');">Print</button> 
<asp:Button ID="btnclose" runat="server" CssClass="button-style" OnClientClick="fnclose();" Text="Close" /></td>
</tr>
</table>
<br />
<br />
<br />
<div id="div_print">
<fieldset>
<legend class="successPanel">Please Select File:
</legend>
<table width="100%">
<tr>
<td colspan="2" style="text-align: center">
<asp:Label ID="lblUploadFile" CssClass="" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td style="height: 40px;" align="left">
<asp:FileUpload ID="userFileUpload" runat="server" />
</td>
<td style="height: 40px;" align="right">
<asp:Button runat="server" ID="UploadButton" Text="Upload" OnClick="UploadButton_Click" CssClass="button-style" />
<asp:Button runat="server" ID="SaveData" Text="Run" OnClick="SaveData_Click" CssClass="button-style" />
</td>
</tr>
<tr id="tr_msg0" runat="server">
<td colspan="2" style="text-align: center;">
<asp:Label runat="server" ID="Label4" Text="<u>Status:</u>" CssClass="successPanel" Style="font-weight: bold; font-size: 12pt;" /></td>
</tr>
<tr id="tr_msg" runat="server">
<td style="width: 30%;">
<asp:Label runat="server" ID="lblSuccessMsg" Text="<u>Successfully Data:</u>" CssClass="successPanel" Style="font-weight: bold;" />
<br />
<asp:Label runat="server" ID="lblmsg" Text="" CssClass="successPanel" />
</td>
<td style="width: 70%; text-align: center;">
<asp:Label runat="server" ID="lblFailMsg" Text="<u>Failed Data:</u>" CssClass="successPanel" Style="font-weight: bold;" />
</td>
</tr>
<tr id="tr_msg1" runat="server">
<td style="vertical-align: top;">
<asp:Label runat="server" ID="lblSuccessData" Text="" CssClass="successPanel" />
</td>
<td>
<table>
<tr>
<td style="vertical-align: top;">
<asp:Label runat="server" ID="Label5" Text="<u>Duplicate Email Ids</u>" CssClass="successPanel" />
<asp:Label runat="server" ID="lblFaildData" Text="" CssClass="successPanel" />
</td>
<td style="vertical-align: top;">
<asp:Label runat="server" ID="Label3" Text="<u>In Sufficient Data</u>" CssClass="successPanel" />
<asp:Label runat="server" ID="lblBlkData" Text="" CssClass="successPanel" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</fieldset>
</div>
</div>
</asp:Content>
C# Code
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Drawing;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;
using System.Configuration;
using Utility;
namespace CationHRManagementSystem
{
public partial class UserExcelpopup : System.Web.UI.Page
{
string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
public string FilePath = "";
protected void Page_Load(object sender, EventArgs e)
{
tr_msg.Visible = false;
tr_msg0.Visible = false;
tr_msg1.Visible = false;
SaveData.Visible = false;
}
//Method For Save Data in Database Using MySql
protected void SaveData_Click(object sender, EventArgs e)
{
lblUploadFile.Text = "";
string sal = "";
string First_Name = "";
string Last_Name = "";
string EMAIL_ID = "";
string USER_PASSWORD = "";
string org_name = "";
string myfile_name = Session["name"].ToString();
string Excel_path = Server.MapPath("~/UserUploadedExcelFiles/" + "\\" + myfile_name);
OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel_path + ";Extended Properties=Excel 8.0;Persist Security Info=False");
my_con.Open();
try
{
OleDbCommand o_cmd = new OleDbCommand("select * from [Sheet1$]", my_con);
OleDbDataReader o_dr = o_cmd.ExecuteReader();
int Fail_cnt = 0;
int cnt = 0;
int blkcnt = 0;
string EmailId = "";
string blnkEmailId = "";
string lnsEmailId = "";
while (o_dr.Read())
{
sal = o_dr[0].ToString();
First_Name = o_dr[1].ToString();
Last_Name = o_dr[2].ToString();
EMAIL_ID = o_dr[3].ToString();
org_name = o_dr[4].ToString();
USER_PASSWORD =o_dr[5].ToString();
if (sal == "" || First_Name == "" || EMAIL_ID == "" || org_name == "" || USER_PASSWORD == "")
{
blkcnt++;
blnkEmailId += "<br>" + EMAIL_ID;
continue;
}
else
{
// check Email Existence
MySqlConnection con = new MySqlConnection(conStr);
con.Open();
string sqlQuery = "select * from user_info where LOWER(EMAIL_ID)='" + EMAIL_ID.Trim().ToLower() + "'";
MySqlConnection Con = new MySqlConnection(conStr);
MySqlDataAdapter da = new MySqlDataAdapter(sqlQuery, Con);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
EmailId += "<br>" + EMAIL_ID;
Fail_cnt++;
continue;
}
else
{
// Insert Data
string Sqlstr = " insert into user_info(USER_SAL, USER_FIRSTNAME, USER_LASTNAME, EMAIL_ID, USER_PASS) "
values('" + sal + "', '" + First_Name + "', '" + Last_Name + "',+ EMAIL_ID + "',
'" + USER_PASSWORD + "');";
MySqlCommand cmd = new MySqlCommand(Sqlstr, con);
int i = cmd.ExecuteNonQuery();
if (i > 0)
{
cnt++;
lnsEmailId += "<br>" + EMAIL_ID;
}
con.Close();
}
}
}
// Labels for Masseges
tr_msg0.Visible = true;
tr_msg.Visible = true;
tr_msg1.Visible = true;
lblSuccessMsg.Text += "-" + cnt.ToString();
int fcnt = blkcnt + Fail_cnt;
lblFailMsg.Text += "-" + fcnt.ToString();
lblSuccessData.Text = lnsEmailId.ToString();
lblSuccessData.ForeColor = System.Drawing.Color.Green;
lblFaildData.Text = EmailId.ToString();
lblBlkData.Text = blnkEmailId.ToString();
lblBlkData.ForeColor = System.Drawing.Color.Red;
lblFaildData.ForeColor = System.Drawing.Color.Red;
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
// Method for Upload excel file
protected void UploadButton_Click(object sender, EventArgs e)
{
string file_name = Path.GetFileName(userFileUpload.PostedFile.FileName);
if (file_name == "")
{
lblUploadFile.Text = "Please select excel file.";
lblUploadFile.ForeColor = System.Drawing.Color.Red;
}
else
{
string fileExtension = Path.GetExtension(userFileUpload.FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
// Excel File Upload
System.IO.FileInfo file = new System.IO.FileInfo(userFileUpload.PostedFile.FileName);
string fname = file.Name.Remove((file.Name.Length - file.Extension.Length));
fname = fname + System.DateTime.Now.ToString("_ddMMyyhhmmss") + file.Extension;
if (fname.Length > 0)
{
userFileUpload.SaveAs(Server.MapPath("~/UserUploadedExcelFiles/" + "\\" + fname));
Session["name"] = fname;
lblUploadFile.Text = "file uploaded Successfully.";
lblUploadFile.ForeColor = System.Drawing.Color.Green;
SaveData.Visible = true;
UploadButton.Visible = false;
}
else
{
lblUploadFile.Text = "Please select excel file.";
lblUploadFile.ForeColor = System.Drawing.Color.Red;
}
}
else
{
lblUploadFile.Text = "Please select excel file.";
lblUploadFile.ForeColor = System.Drawing.Color.Red;
}
}
}
}
}
