This program I need to browse and upload the excel file into database using asp.net web application program. In that method or operation is not implement when i run that program. what shall i do?
{
string SaveLocation = Server.MapPath("Data");
if (!Directory.Exists(SaveLocation))
{
Directory.CreateDirectory(SaveLocation);
}
if (!SaveLocation.EndsWith("\\"))
{
SaveLocation = SaveLocation +
"\\emp.xlsx";
}
else
{
SaveLocation = SaveLocation +
"emp.xlsx";
}
FileUpload1.SaveAs(SaveLocation);
if (!String.IsNullOrEmpty(FileUpload1.PostedFile.FileName))
{
empservice objempservice = new empservice();
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SaveLocation + ";Extended Properties=Excel 12.0";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
OleDbCommand cmd = new OleDbCommand("Select * from[Sheet1$]", excelConnection);
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
bool isValid = true;
string vehicleNotFound = String.Empty;
string noVehicleAssigned = String.Empty;
while (dReader.Read())
{
string vehicleRegNo = dReader["RegNO"].ToString();
string empId = dReader["EmpID"].ToString();
if (String.IsNullOrEmpty(vehicleRegNo) == false)
{
bool IsVehicleFound = objempservice.IsVehicleFound(vehicleRegNo);
if (IsVehicleFound == false)
{
vehicleNotFound = vehicleNotFound +
"," + vehicleRegNo;
IsVehicleFound =
false;
isValid =
false;
}
}
else
{
if (String.IsNullOrEmpty(empId) == false)
{
}
}
}
}
}
Loading
Satyapriya NayakPosted Feb 21, 2013, 4:05 AM
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace Show_Excel_gridview_insert_to_db
{
public partial class _Default : System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand com;
protected void ButtonUploadFile_Click(object sender, EventArgs e)
{
if (FileUploadExcel.HasFile)
{
try
{
FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"));
LabelUpload.Text = "Upload File Name: " +
FileUploadExcel.PostedFile.FileName + "
" +
"Type: " + FileUploadExcel.PostedFile.ContentType +
" File Size: " + FileUploadExcel.PostedFile.ContentLength +
" kb
";
}
catch (System.NullReferenceException ex)
{
LabelUpload.Text = "Error: " + ex.Message;
}
}
else
{
LabelUpload.Text = "Please select a file to upload.";
}
}
protected OleDbCommand ExcelConnection()
{
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection Con = new OleDbConnection(ConnStr);
Con.Open();
OleDbCommand com = new OleDbCommand("SELECT * FROM [sheet1$]", Con);
return com;
}
protected void ButtonView_Click(object sender, System.EventArgs e)
{
PanelUpload.Visible = false;
PanelView.Visible = true;
PanelImport.Visible = false;
OleDbDataAdapter oledbda = new OleDbDataAdapter();
oledbda.SelectCommand = ExcelConnection();
DataSet ds = new DataSet();
oledbda.Fill(ds);
GridViewExcel.DataSource = ds.Tables[0].DefaultView;
GridViewExcel.DataBind();
}
protected void ButtonUpload_Click(object sender, System.EventArgs e)
{
PanelUpload.Visible = true;
PanelView.Visible = false;
PanelImport.Visible = false;
}
protected void btn_insert_Click(object sender, EventArgs e)
{
foreach (GridViewRow g1 in GridViewExcel.Rows)
{
SqlConnection con = new SqlConnection(connStr);
com = new SqlCommand("insert into student(sid,sname,smarks,saddress) values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "')", con);
con.Open();
com.ExecuteNonQuery();
con.Close();
}
Label1.Text = "Records inserted successfully";
}
}
}