How to Upload Excel File in your Database
How to Export data from database table to Excel file. Now in this article, I’ve covered a brief introduction about importing data from Excel File to database. There are lots of ways for Importing data from Excel to SQL server database
CS code write in your page:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text.RegularExpressions;
using System.Xml.Linq;
using System.IO;
using System.Data.OleDb;
protected string valid(OleDbDataReader myreader, int stval)//if any columns are //found null then they are replaced by zero
{
object val = myreader[stval];
if (val != DBNull.Value)
return val.ToString();
else
return Convert.ToString(0);
}
protected void insertdata_Click(object sender, EventArgs e)
{
string fileName = "";
if (FileUpload1.HasFile)
{
try
{
fileName = FileUpload1.FileName;
if (FileUpload1.PostedFile.ContentLength < 5300000)
{
//fileName = FileUpload1.PostedFile.FileName;
string savePath = Server.MapPath("../img/xls/");
dUT.folderEx(savePath);
// Get the name of the file to upload.
//string fileName = FileUpload1.FileName;
// Create the path and file name to check for duplicates.
string pathToCheck = savePath + fileName;
// Create a temporary file name to use for checking duplicates.
string tempfileName = "";
// Check to see if a file already exists with the
// same name as the file to upload.
if (System.IO.File.Exists(pathToCheck))
{
int counter = 2;
while (System.IO.File.Exists(pathToCheck))
{
// if a file with this name already exists,
// prefix the filename with a number.
tempfileName = counter.ToString() + fileName;
pathToCheck = savePath + tempfileName;
counter++;
}
fileName = tempfileName;
// Notify the user that the file name was changed.
//StatusLabel.Text = "A file with the same name already exists." + "<br />Your file was saved as " + fileName;
}
else
{
// Notify the user that the file was saved successfully.
// StatusLabel.Text = "Your file was uploaded successfully.";
}
// Append the name of the file to upload to the path.
savePath += fileName;
FileUpload1.PostedFile.SaveAs(savePath);
// StatusLabel.Text = "Upload status: File uploaded!";
}
else
{
string ext = Path.GetExtension(fileName);
lblmsg.Text = "<div class='info'>Upload status:" + ext + " files are not accepted!</div>";
lblmsg.ForeColor = System.Drawing.Color.Red;
// StatusLabel.Text = "Upload status:" + ext + " files are not accepted!";
return;
}
}
catch (Exception ex)
{
///StatusLabel.Text = "Upload status: The file could not be uploaded. The following error occured: " + ex.Message;
return;
}
string Upfile = fileName;
string Mmsg = "";
OleDbConnection oconn = new OleDbConnection
(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../img/xls") + "/" + Upfile + ";Extended Properties=Excel 8.0");//OledbConnection and
// connectionstring to connect to the Excel Sheet
try
{
//After connecting to the Excel sheet here we are selecting the data
//using select statement from the Excel sheet
OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
oconn.Open(); //Here [Sheet1$] is the name of the sheet
//in the Excel file where the data is present
OleDbDataReader odr = ocmd.ExecuteReader();
string fname = "";
string mobnum = "";
// string city = "";
//string state = "";
//string zip = "";
string sql;
int i=0;
while (odr.Read())
{
int prodid;
string error="";
//Here we are calling the valid method
//srno TestCode Date RollNo TotalQuestion Right Left Wrong Mark Out OF Marks Percentage % Remark Rank
//string modelno = valid(odr, 0);
string testcode = valid(odr, 0);
if (testcode != "" && testcode != "0" && testcode != null)
{
string datt = valid(odr, 1);
DateTime tesdate = Convert.ToDateTime(datt.ToString());
string rollno = valid(odr, 2);
string TQ = valid(odr, 3);
string RQ = valid(odr, 4);
string LQ = valid(odr, 5);
string WQ = valid(odr, 6);
string MARKS = valid(odr, 7);
string OUTOfMARKS = valid(odr, 8);
string percentage = valid(odr, 9);
string Remark = valid(odr, 10);
string Rank = valid(odr, 11);
string stream = valid(odr, 12);
string types = valid(odr, 13);
if (!UT.IsNumber(TQ))
{
error = error + "Please enter TotalQuestion in numeric<br>";
}
//if (!UT.IsNumber(RQ))
//{
// error = error + "Please enter Right in numeric<br>";
//}
//if (!UT.IsNumber(LQ))
//{
// error = error + "Please enter Left in numeric<br>";
//}
//if (!UT.IsNumber(WQ))
//{
// error = error + "Please enter Wrong in numeric<br>";
//}
// double extr = Convert.ToDouble(txtextra.Text.ToString());
string strSql = "SELECT * FROM master where Code='" + testcode + "' and Roll_no='" + rollno + "'";
DataTable dti = dUT.getDataTable(strSql);
if (dti.Rows.Count < 1)
{
i = i + 1;
sql = "insert into master(Code,Test_Date,Roll_No,Stream,Total_No_Q,Right_Q,";
sql = sql + " Wrong_Q,Left_Q,Marks,Out_of_marks,percents,Remark,rank,Edate,SMS,Test_Type)values(";
sql = sql + "'" + testcode + "'";
sql = sql + ",'" + tesdate + "'";
sql = sql + ",'" + rollno + "'";
sql = sql + ",'" + stream + "'";
sql = sql + ",'" + TQ + "'";
sql = sql + ",'" + RQ + "'";
sql = sql + ",'" + WQ + "'";
sql = sql + ",'" + LQ + "'";
sql = sql + ",'" + MARKS + "'";
sql = sql + ",'" + OUTOfMARKS + "'";
sql = sql + ",'" + percentage + "'";
sql = sql + ",'" + Remark + "'";
sql = sql + ",'" + Rank + "'";
sql = sql + ",getdate()";
sql = sql + ",'0'";
sql = sql + ",'" + types + "')";
int rs = dUT.executeSql(sql);
}
}
}
Mmsg = "<div class='success'>User Upload file: " + Upfile + " With " + i + " No of Result</div>";
oconn.Close();
}
catch (DataException ee)
{
lblmsg.Text = ee.Message;
lblmsg.ForeColor = System.Drawing.Color.Red;
}
finally
{
lblmsg.Text = "<div class='success'>Data Inserted Sucessfully.</div>";
lblmsg.ForeColor = System.Drawing.Color.Red;
//Response.Redirect("xls_upload.aspx?mas=1");
}
}
else
{
lblmsg.Text = "<div class='error'>Please Select File</div>";
lblmsg.ForeColor = System.Drawing.Color.Red;
}
}
show like this design to upload excel file
No comments:
Post a Comment