Upload the file and create upload button. Below is the functionality of upload button
CS file code(below)
protected void btn_upload_Click(object sender, EventArgs e)
{
if (ViewState["CSRF"] != null && Convert.ToString(ViewState["CSRF"]) == Convert.ToString(Session["randomno"]))
{
DataTable dtExcelData = new DataTable();
if (FileUpload1.HasFile)
{
//Upload and save the file
try
{
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[] {
new DataColumn("Training_partner_id",typeof(string)),
new DataColumn("Training_Partner_Name",typeof(string)),
new DataColumn("Course_Name",typeof(string)),
new DataColumn("Training_Partner_State",typeof(string)),
new DataColumn("KIA_Name",typeof(string)),
new DataColumn("Batch_Code",typeof(string)),
new DataColumn("Salutation",typeof(string)),
new DataColumn("StudentFirstName",typeof(string)),
new DataColumn("StudentMiddleName",typeof(string)),
new DataColumn("StudentLastName",typeof(string)),
new DataColumn("Father_Name",typeof(string)),
new DataColumn("Mother_Name",typeof(string)),
new DataColumn("Guardian_Name",typeof(string)),
new DataColumn("Gender",typeof(string)),
new DataColumn("DOB",typeof(string)),
new DataColumn("Marital_Status",typeof(string)),
new DataColumn("catogory",typeof(string)),
new DataColumn("Phone_No",typeof(string)),
new DataColumn("MobileNo",typeof(string)),
new DataColumn("Email_id",typeof(string)),
new DataColumn("Adharcard_No",typeof(string)),
new DataColumn("Permanent_Address_Line",typeof(string)),
new DataColumn("Country",typeof(string)),
new DataColumn("Permanent_state_Name",typeof(string)),
new DataColumn("Permanent_district_Name",typeof(string)),
new DataColumn("Permanent_City",typeof(string)),
new DataColumn("Permanent_Landmark",typeof(string)),
new DataColumn("Permanent_Pincode",typeof(string)),
new DataColumn("Present_Address_Line",typeof(string)),
new DataColumn("Present_Country",typeof(string)),
new DataColumn("Present_State_Name",typeof(string)),
new DataColumn("Present_district_id",typeof(string)),
new DataColumn("Present_City",typeof(string)),
new DataColumn("Present_Landmark",typeof(string)),
new DataColumn("Present_Pincode",typeof(string)),
new DataColumn("Bank_Name",typeof(string)),
new DataColumn("Branch_Name",typeof(string)),
new DataColumn("Benificiary_Name",typeof(string)),
new DataColumn("Beneficiary_Code",typeof(string)),
new DataColumn("Account_number",typeof(string)),
new DataColumn("Ifsc_Code",typeof(string)),
new DataColumn("MICR_Code",typeof(string)),
new DataColumn("Bank_Address",typeof(string)),
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
int existingsize = FetchSize(drp_batch.SelectedIndex > 0 ? drp_batch.SelectedItem.Text : null);
int Studentinexcel = dtExcelData.Rows.Count;
if (Studentinexcel > existingsize)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "msg", "alert('Excess Student uploaded according to max batch size.....');", true);
}
else
{
string consString = Luminious.Connection.Configuration.ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
for (int i = 0; i < dtExcelData.Rows.Count; i++)
{
dtExcelData.Rows[i]["Training_partner_id"] = Session["trainingpartnercode"] != null ? Convert.ToString(Session["trainingpartnercode"]) : null;
dtExcelData.Rows[i]["Training_Partner_Name"] = Session["TP_Name"] != null ? Convert.ToString(Session["TP_Name"]) : null;
dtExcelData.Rows[i]["Course_Name"] = drp_course.SelectedIndex > 0 ? drp_course.SelectedItem.Text : null;
dtExcelData.Rows[i]["Training_Partner_State"] = Session["TP_State"] != null ? Convert.ToString(Session["TP_State"]) : null;
dtExcelData.Rows[i]["KIA_Name"] = drp_KIA.SelectedIndex > 0 ? drp_KIA.SelectedItem.Text : null;
dtExcelData.Rows[i]["Batch_Code"] = drp_batch.SelectedIndex > 0 ? drp_batch.SelectedItem.Text : null;
}
//Set the database table name
sqlBulkCopy.DestinationTableName = "Student_Registraion_Temp";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Training_partner_id", "Training_partner_id");
sqlBulkCopy.ColumnMappings.Add("Training_Partner_Name", "Training_Partner_Name");
sqlBulkCopy.ColumnMappings.Add("Course_Name", "Course_Name");
sqlBulkCopy.ColumnMappings.Add("Training_Partner_State", "Training_Partner_State");
sqlBulkCopy.ColumnMappings.Add("KIA_Name", "KIA_Name");
sqlBulkCopy.ColumnMappings.Add("Batch_Code", "Batch_Code");
sqlBulkCopy.ColumnMappings.Add("Salutation", "Salutation");
sqlBulkCopy.ColumnMappings.Add("StudentFirstName", "StudentFirstName");
sqlBulkCopy.ColumnMappings.Add("StudentMiddleName", "StudentMiddleName");
sqlBulkCopy.ColumnMappings.Add("StudentLastName", "StudentLastName");
sqlBulkCopy.ColumnMappings.Add("Father_Name", "Father_Name");
sqlBulkCopy.ColumnMappings.Add("Mother_Name", "Mother_Name");
sqlBulkCopy.ColumnMappings.Add("Guardian_Name", "Guardian_Name");
sqlBulkCopy.ColumnMappings.Add("Gender", "Gender");
sqlBulkCopy.ColumnMappings.Add("DOB", "DOB");
sqlBulkCopy.ColumnMappings.Add("Marital_Status", "Marital_Status");
sqlBulkCopy.ColumnMappings.Add("catogory", "catogory");
sqlBulkCopy.ColumnMappings.Add("Phone_No", "Phone_No");
sqlBulkCopy.ColumnMappings.Add("MobileNo", "MobileNo");
sqlBulkCopy.ColumnMappings.Add("Email_id", "Email_id");
sqlBulkCopy.ColumnMappings.Add("Adharcard_No", "Adharcard_No");
sqlBulkCopy.ColumnMappings.Add("Permanent_Address_Line", "Permanent_Address_Line");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
sqlBulkCopy.ColumnMappings.Add("Permanent_state_Name", "Permanent_state_Name");
sqlBulkCopy.ColumnMappings.Add("Permanent_district_Name", "Permanent_district_Name");
sqlBulkCopy.ColumnMappings.Add("Permanent_City", "Permanent_City");
sqlBulkCopy.ColumnMappings.Add("Permanent_Landmark", "Permanent_Landmark");
sqlBulkCopy.ColumnMappings.Add("Permanent_Pincode", "Permanent_Pincode");
sqlBulkCopy.ColumnMappings.Add("Present_Address_Line", "Present_Address_Line");
sqlBulkCopy.ColumnMappings.Add("Present_Country", "Present_Country");
sqlBulkCopy.ColumnMappings.Add("Present_State_Name", "Present_State_Name");
//sqlBulkCopy.ColumnMappings.Add("Present_district_Name", "Present_district_Name");
sqlBulkCopy.ColumnMappings.Add("Present_City", "Present_City");
sqlBulkCopy.ColumnMappings.Add("Present_Landmark", "Present_Landmark");
sqlBulkCopy.ColumnMappings.Add("Present_Pincode", "Present_Pincode");
sqlBulkCopy.ColumnMappings.Add("Bank_Name", "Bank_Name");
sqlBulkCopy.ColumnMappings.Add("Branch_Name", "Branch_Name");
sqlBulkCopy.ColumnMappings.Add("Benificiary_Name", "Benificiary_Name");
sqlBulkCopy.ColumnMappings.Add("Beneficiary_Code", "Beneficiary_Code");
sqlBulkCopy.ColumnMappings.Add("Account_number", "Account_number");
sqlBulkCopy.ColumnMappings.Add("Ifsc_Code", "Ifsc_Code");
sqlBulkCopy.ColumnMappings.Add("MICR_Code", "MICR_Code");
sqlBulkCopy.ColumnMappings.Add("Bank_Address", "Bank_Address");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
if (dtExcelData != null)
{
grd.DataSource = dtExcelData;
grd.DataBind();
}
}
}
}
}
}
catch (Exception ex)
{
ExceptionHandler.WriteException(ex.Message);
lbl_message.ForeColor = System.Drawing.Color.Red;
lbl_message.Text = ex.Message;
}
finally
{
CrossSiteRequestForgery();
CreateRandomHashing();
if (dtExcelData != null)
{
dtExcelData.Dispose();
}
}
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "msg", "alert('Upload File ');", true);
}
}
}
--------------------------------------------------
web.config file
<connectionStrings>
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
</connectionStrings>
------------------------------------------------------------
Database table structure
CREATE TABLE [dbo].[Student_Registraion_Temp](
[id] [int] IDENTITY(1,1) NOT NULL,
[Training_partner_id] [nvarchar](255) NULL,
[Training_Partner_Name] [nvarchar](255) NULL,
[Course_Name] [nvarchar](255) NULL,
[Training_Partner_State] [nvarchar](255) NULL,
[KIA_Name] [nvarchar](255) NULL,
[Batch_Code] [nvarchar](255) NULL,
[Salutation] [nvarchar](255) NULL,
[StudentFirstName] [nvarchar](255) NULL,
[StudentMiddleName] [nvarchar](255) NULL,
[StudentLastName] [nvarchar](255) NULL,
[Father_Name] [nvarchar](255) NULL,
[Mother_Name] [nvarchar](255) NULL,
[Guardian_Name] [nvarchar](255) NULL,
[Gender] [nvarchar](255) NULL,
[DOB] [varchar](50) NULL,
[Marital_Status] [nvarchar](255) NULL,
[catogory] [nvarchar](255) NULL,
[Phone_No] [nvarchar](255) NULL,
[MobileNo] [nvarchar](255) NULL,
[Email_id] [nvarchar](255) NULL,
[Adharcard_No] [nvarchar](255) NULL,
[Permanent_Address_Line] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Permanent_state_Name] [nvarchar](255) NULL,
[Permanent_district_Name] [nvarchar](255) NULL,
[Permanent_City] [nvarchar](255) NULL,
[Permanent_Landmark] [nvarchar](255) NULL,
[Permanent_Pincode] [nvarchar](255) NULL,
[Present_Address_Line] [nvarchar](255) NULL,
[Present_Country] [nvarchar](255) NULL,
[Present_State_Name] [nvarchar](255) NULL,
[Present_district_Name] [nvarchar](255) NULL,
[Present_City] [nvarchar](255) NULL,
[Present_Landmark] [nvarchar](255) NULL,
[Present_Pincode] [nvarchar](255) NULL,
[Bank_Name] [nvarchar](255) NULL,
[Branch_Name] [nvarchar](255) NULL,
[Benificiary_Name] [nvarchar](255) NULL,
[Beneficiary_Code] [nvarchar](255) NULL,
[Account_number] [nvarchar](255) NULL,
[Ifsc_Code] [nvarchar](255) NULL,
[MICR_Code] [nvarchar](255) NULL,
[Bank_Address] [nvarchar](255) NULL,
[Is_Upload] [char](1) NULL,
[stuphoto] [varchar](1000) NULL,
[stusign] [varchar](1000) NULL,
[studentedudoc] [varchar](1000) NULL,
[studentthumb] [varchar](1000) NULL,
[studentCastcertificate] [varchar](1000) NULL,
[isverified] [char](1) NULL,
CONSTRAINT [PK_Student_Registraion_Temp] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-------------------------------------------------------------------
excel file format(below is link)
-------------------------------------------------------------
or we can use below link
No comments:
Post a Comment