How to upload the bulk excel sheet data into database with validation of excel sheet field?


Hi
In one project,there was requirement to upload the complete excel sheet data to database with following conditions
1. Excel field should not contain the special character like ‘XXXX’ or ‘-‘
2. It should dynamically read the excel sheet name
3. It should not hit more to database
4. Excel sheet will be 2003 or 2007

I did like this
Step1: Take file Upload, Button and label control. On click event write this code

Design page is like this

<html xmlns="http://www.w3.org/1999/xhtml"&gt;
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" /> &nbsp;&nbsp;&nbsp;&nbsp;
<asp:Button ID="BtnSubmit" runat="server" Text="Submit"
onclick="BtnSubmit_Click" />

<br />
<br />
<asp:Label ID="LblError" runat="server" Text=""></asp:Label>

</div>
</form>
</body>
</html>

Code behind default page is like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public partial class Excel_Test_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void BtnSubmit_Click(object sender, EventArgs e)
{
SaveExcel_Database();
}
protected void SaveExcel_Database()
{
try
{
if (FileUpload1.HasFile)
{
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

string path = FileUpload1.PostedFile.FileName;
ExcelUpload objbd = new ExcelUpload();

string flag = objbd.BudgetExcelUpload(Extension, path);

LblError.Text = flag;
}
else
{
LblError.Text = "Unable to upload the selected file. Please check the selected file path or confirm that the file is not blank!";
}
}
catch
{
LblError.Text = "Excel file is not in expected formate";
}

}

}

Step2: Create one class in App Code folder i.e ExcelUpload.cs
Write the method for getting excel sheet Name, Excel field validation and bulk excel insert to database like this
Note:Here in excel sheet I have taken the field I.e Id,EmpName,EmpSal
also create the same structure table in database i.e tblEmp

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ExcelUpload
/// </summary>
public class ExcelUpload
{

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Test;Integrated Security=True");

//This method is used to get the ExcelSheetNames
private string GetExcelSheetNames(string conStr)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
string aa = "";

try
{
objConn = new OleDbConnection(conStr);
// Open connection with the database.

objConn.Open();
// Get the data table containg the schema guid.

dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt == null)
{
return null;
}

String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

// Add the sheet name to the string array.

foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}

// Loop through all of the sheets if you want too…

for (int j = 0; j < excelSheets.Length; j++)
{
// Query each excel sheet.
aa = dt.Rows[0][2].ToString();
}

return aa;
}
catch (Exception ex)
{
return null;
}
finally
{
// Clean up.

if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}

public string BudgetExcelUpload(string Extesion, string path)
{
string msg="";

string Extension1 = Extesion;

string path1 = path;

string conStr = "";
switch (Extension1)
{
case ".xls": //Excel 97-03
conStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path1 + "; Extended Properties=\"Excel 8.0;IMEX=1\";";
break;
case ".xlsx": //Excel 07
conStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + "; Extended Properties=\"Excel 12.0;IMEX=1\";";
break;
}

OleDbConnection oconn = new OleDbConnection(conStr);
string worksheetName = GetExcelSheetNames(conStr);
OleDbCommand ocmd = new OleDbCommand("select * from [" + worksheetName + "]", oconn);

OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [" + worksheetName + "]", oconn);
System.Data.DataSet excelDataSet = new DataSet();
DataTable dt = new DataTable();
cmd.Fill(dt);
cmd.Fill(excelDataSet);

excelDataSet.AcceptChanges();

for (int i = 0; i < excelDataSet.Tables[0].Rows.Count; i++)
{
string Id =excelDataSet.Tables[0].Rows[i][0].ToString();
Id= CheckexcelData(Id);
if (Id == "Invalid")
{
msg = "Invalid";
break;
}
string EmpName = excelDataSet.Tables[0].Rows[i][1].ToString();

EmpName = CheckexcelData(EmpName);
if (EmpName == "Invalid")
{
msg = "Invalid";
break;
}
string EmpSal = excelDataSet.Tables[0].Rows[i][2].ToString();

EmpSal = CheckexcelData(EmpSal);

if (EmpSal == "Invalid")
{
msg = "Invalid";
break;
}
}

if (msg != "Invalid")
{
//code for bulk data insert in "tblEmp" table.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
con.Open();
bulkCopy.DestinationTableName = "tblEmp";
bulkCopy.WriteToServer(dt);
con.Close();
msg = "Data has been uploaded sucessfully";
}

}

return msg;

}

protected string CheckexcelData(string input)
{

switch (input)
{
case "xxxx":
input ="Invalid";
break;

case "-":
input = "Invalid";
break;
}
return input;
}

}

How to split the sentence without special char in asp.net?


Hi
We can split the sentence without special char using “System.Text.RegularExpressions” namespace in c#. It also gives very good performance and easy to implement

Code behind code is like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text.RegularExpressions;

public partial class Program_SpliteStringUsingRegExp : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
string input = TextBox1.Text;
string[] w = splitword(input);
foreach (string s in w)
{
Label1.Text+=s+ "<br/>";
}

}

static string[] splitword(string s)
{
//it will split all non word character and return an array of character.
return Regex.Split(s, @"\W+");
}
}

How to split the sentence on basis of space in asp.net?


Hi
In so many time, we will get requirement to split the sentence into words. We can split the sentence on basis of space like this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;

public partial class Program_Spliting_of_Text : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
string str = TextBox1.Text;
//split the string o basis of ‘ ‘ (space)
string[] words= str.Split(‘ ‘);
foreach (string s in words)
{
Label1.Text += s + "<br/>";
}
}
}

How to check palindrome in asp.net?


Hi
In so many time, they will ask this question in interview to check the programming skill of developer.

How can check the palindrome string like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;

public partial class Program_Palindrom_of_String : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
string s = TextBox1.Text.ToString();
string str = string.Empty;
for (int j = s.Length-1; j >= 0; j–)
{
//store the reverse string
str = str + s[j];
}
//check with input value
if (s == str)
{
Label1.Text = s + " is palindrom";
}
else
{
Label1.Text = s + " is not palindrom";
}
}
}

How to display the number in sequential order in asp.net?


Hi
we can display the no in sequential order of 10 like this.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Program_NumberDisplay : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
int n = Convert.ToInt32(TextBox1.Text);
int count = 0;
for ( int i = 1; i <=n; i++)
{
Label1.Text += i+" " ;
count = count + 1;
//This is used for counting the no and if 10 will come,then it will break it.
if (count == 10)
{
Label1.Text += "<br/>";
count = 0;
}

}

}
}

How to reverse the String in asp.net ?


Hi
In interview and in our daily work, so many time we will require the basic, like how to reverse the string in asp.net. we can do in a simple manner like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Program_Revserse_String : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
string a = TextBox1.Text;
TextBox2.Text = reverse(a);

}

public string reverse(string s)
{
//take one new string
string str = string.Empty;
for (int j = s.Length – 1; j >= 0; j–)
{
//store the reverse character in seperate string
str = str + s[j];
}
return str;

}
}

Microsoft Community Contributor Award – 2011


Hi
I’m very pleased and honored to share this with all of you that I have received the Microsoft Community Contributor Award for the year 2011 today morning. This was a very big and very special surprise to me. In the coming days, I will continue to contribute the community. I feel really happy to help other friends in the community by online or offline

Thank you Microsoft.

What is the “Microsoft Community Contributor Award” ?
https://www.microsoftcommunitycontributor.com/overview.aspx

For FAQ of Microsoft Community Contributor Award.
https://www.microsoftcommunitycontributor.com/faq.aspx

Posted in Award. 2 Comments »