Excel upload Student data in database code
Asp.net
<table class="table table-striped table-bordered ">
<tr>
<td>
Select File :
</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" ToolTip="Select Only Excel File" />
</td>
<td>
<asp:Button ID="Button1" runat="server" Text="Upload" OnClick="Button1_Click" class="btn btn-success btn-rounded btn-ef btn-ef-6 btn-ef-6c mb-10" />
</td>
</tr>
</table>
<table class="table table-striped table-bordered ">
<tr>
<td>
<p>
<asp:Label ID="Label2" runat="server" Text="label"></asp:Label>
</p>
</td>
</tr>
</table>
----------------------------------------------------------------------
C# Code
public string query;
public string constr;
public SqlCommand com;
public SqlConnection con;
DataSet ds;
DataTable Dt;
public void connection()
{
constr = ConfigurationManager.ConnectionStrings["oConnContactDb"].ToString();
con = new SqlConnection(constr);
con.Open();
}
private bool ValidateDate(string date)
{
try
{
string[] dateParts = date.Split('/');
DateTime testDate = new DateTime(Convert.ToInt32(dateParts[2]), Convert.ToInt32(dateParts[1]), Convert.ToInt32(dateParts[0]));
return true;
}
catch
{
return false;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
Label2.Visible = true;
string filePath = FileUpload1.PostedFile.FileName;
string filename1 = Path.GetFileName(filePath);
string ext = Path.GetExtension(filename1);
string type = String.Empty;
if (!FileUpload1.HasFile)
{
Label2.Text = "Please Select File";
}
else
if (FileUpload1.HasFile)
{
try
{
// Added by vithal wadje for Csharp-Corner contribution
switch (ext)
{
case ".xls":
type = "application/vnd.ms-excel";
break;
case ".xlsx":
type = "application/vnd.ms-excel";
break;
}
if (type != String.Empty)
{
connection();
Stream fs = FileUpload1.PostedFile.InputStream;
BinaryReader br = new BinaryReader(fs);
Byte[] bytes = br.ReadBytes((Int32)fs.Length);
string FileName = FileUpload1.FileName;
string path = string.Concat(Server.MapPath("~/Document/" + FileUpload1.FileName));
FileUpload1.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbcon.Open();
DataTable dtExcelSchema;
dtExcelSchema = OleDbcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
OleDbcon.Close();
OleDbCommand cmd = new OleDbCommand("SELECT * From [" + SheetName + "]", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
ds = new DataSet();
objAdapter1.Fill(ds);
Dt = ds.Tables[0];
for (int i = 0; i < Dt.Rows.Count; i++)
{
// if (System.Text.RegularExpressions.Regex.IsMatch(Dt.Rows[i][0].ToString(), "^[a-zA-Z]"))
//{
if (Dt.Rows[i][0].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Candate Name in row " + RowNo + "');", true);
return;
}
// }
else if (!System.Text.RegularExpressions.Regex.IsMatch(Dt.Rows[i][0].ToString(), "^[a-zA-Z]"))
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('This Candate Name accepts only alphabetical characters');", true);
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][1].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Father Name in row " + RowNo + "');", true);
return;
}
else if (!System.Text.RegularExpressions.Regex.IsMatch(Dt.Rows[i][1].ToString(), "^[a-zA-Z]"))
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('This Father Name accepts only alphabetical characters');", true);
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
string date = DateTime.Parse(Dt.Rows[i][2].ToString()).ToString("dd/MM/yyyy");
if (!ValidateDate(date))
{
int RowNo = i + 2;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Wrong Date format in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
int VAge;
if (int.TryParse(Dt.Rows[i][3].ToString(), out VAge))
{
if (Dt.Rows[i][3].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Age in row " + RowNo + "');", true);
return;
}
}
else
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Age in Numeric');", true);
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][4].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Gender in row " + RowNo + "');", true);
return;
}
else if (!System.Text.RegularExpressions.Regex.IsMatch(Dt.Rows[i][4].ToString(), "^[a-zA-Z]"))
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('This Gender accepts only alphabetical characters');", true);
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][5].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Qualification in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][6].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter SocialCategory in row " + RowNo + "');", true);
return;
}
else if (!System.Text.RegularExpressions.Regex.IsMatch(Dt.Rows[i][6].ToString(), "^[a-zA-Z]"))
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('This SocialCategory accepts only alphabetical characters');", true);
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][7].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Person With Disability in row " + RowNo + "');", true);
return;
}
else if (!System.Text.RegularExpressions.Regex.IsMatch(Dt.Rows[i][7].ToString(), "^[a-zA-Z]"))
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('This Person With Disability accepts only alphabetical characters');", true);
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][8].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Complite Address in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
int vBatchId;
if (int.TryParse(Dt.Rows[i][9].ToString(), out vBatchId))
{
if (Dt.Rows[i][9].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Batch ID in row " + RowNo + "');", true);
return;
}
}
else
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Batch ID in Numeric');", true);
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
int vAadharCardNo;
if (int.TryParse(Dt.Rows[i][10].ToString(), out vAadharCardNo))
{
if (Dt.Rows[i][10].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter AadharCardNo in row " + RowNo + "');", true);
return;
}
}
else
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Aadhar Card No in Numeric');", true);
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
int vphoneNo;
// if (int.TryParse(Dt.Rows[i][11].ToString(), out vphoneNo))
//{
if (Dt.Rows[i][11].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Phone no in row " + RowNo + "');", true);
return;
}
//}
else if (int.TryParse(Dt.Rows[i][11].ToString(), out vphoneNo))
{
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Phone no in Numeric');", true);
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
if (Dt.Rows[i][12].ToString() == "")
{
int RowNo = i + 1;
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('Please enter Email id in row " + RowNo + "');", true);
return;
}
}
for (int i = 0; i < Dt.Rows.Count; i++)
{
DataRow row = Dt.Rows[i];
int columnCount = Dt.Columns.Count;
string[] columns = new string[columnCount];
for (int j = 0; j < columnCount; j++)
{
columns[j] = row[j].ToString();
}
//DateTime Fromdate = Convert.ToDateTime(columns[2]);
//string FromformattedDate = Fromdate.ToString("yyyy-MM-dd");
DateTime Fromdate = DateTime.Parse(columns[2]);
string FromformattedDate = Fromdate.ToString("yyyy-MM-dd");
query = "insert into tbl_BioStudentRegstration(Name_of_the_candidate,Fathers_Name,Date_of_birth,Age,Gender,Qualification,SocialCategory,PersonWithDisability,Candidate_Address,Batch_Id,AadharCardNo,Phoneno,Email_Id,TrainingCenter_Id,Register_for)" + "VALUES('" + columns[0] + "','" + columns[1] + "','" + FromformattedDate + "',Convert(varchar(10),'" + columns[3] + "',103),'" + columns[4] + "','" + columns[5] + "','" + columns[6] + "','" + columns[7] + "','" + columns[8] + "','" + columns[9] + "','" + columns[10] + "','" + columns[11] + "','" + columns[12] + "','" + ValidateRequest.TrainingCID + "','Excel')";
com = new SqlCommand(query, con);
;
com.ExecuteNonQuery();
Label2.ForeColor = System.Drawing.Color.Green;
Label2.Text = "File Uploaded Successfully";
OleDbcon.Close();
}
}
else
{
Label2.ForeColor = System.Drawing.Color.Red;
Label2.Text = "Select Only Excel File having extension .xlsx or .xls ";
}
}
catch (Exception ex)
{
Label2.Text = "Error: " + ex.Message.ToString();
}
}
}
------------------------------------------
Example :
