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 