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">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<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;
}}
Chandradev's Blog 
if 100 records are there , it wil enter all excel fields into data base table after 100 records it will taking nulls,
so, how can i restrict only 100 records allow into data base.
thanks
Hi
You write query for selecting top 100 records from excel, then insert into database. If you will get problem, let me know.
Regards
chandradev
i want to upload the excel sheet bulk in the income tax return i want the posseger pls write clear
Hi kiran
do you want to upload sheet without validation? if yes then check this post
http://www.aspdotnet-suresh.com/2010/09/import-data-from-excel-to-sql-database.html