.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> ASP.Net >> Post New QuestionBookmark and Share Subscribe to Forum

How to Import Data Import Excel to GridView

Posted By: santosh Deshmukh     Posted Date: July 17, 2013    Points:5   Category :ASP.Net
Hi
I have Create Application in Which i am give a gridView How to import Excel Data Sheet Data To Asp Grid View

Responses
Author: satyapriyanayak             
Posted Date: July 22, 2013     Points: 20   

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Show_Excel_gridview_insert_to_db._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Table ID="TableButtons" runat="server">
<asp:TableRow ID="TableRow1" runat="server">
<asp:TableCell ID="TableCell1" runat="server">
<asp:Button ID="ButtonUpload" runat="server"
Text="Upload Excel Files" OnClick="ButtonUpload_Click" />
</asp:TableCell>
<asp:TableCell ID="TableCell2" runat="server">
<asp:Button ID="ButtonView" runat="server"
Text="View Excel Data" OnClick="ButtonView_Click" />
</asp:TableCell>

</asp:TableRow>
</asp:Table>
<asp:Panel ID="PanelUpload" runat="server" Visible="False">
<asp:FileUpload ID="FileUploadExcel" runat="server" />
<br />
Please select an Excel file to import:<br />
<asp:Button ID="ButtonUploadFile" runat="server"
Text="Upload File" OnClick="ButtonUploadFile_Click" /><br />
<asp:Label ID="LabelUpload" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Panel ID="PanelView" runat="server" Visible="False">
<asp:Label ID="LabelGrid" runat="server" Text=""></asp:Label>
<asp:GridView ID="GridViewExcel" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84"
BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
</asp:GridView>
</asp:Panel>
<asp:Panel ID="PanelImport" runat="server" Visible="False">
<asp:Label ID="LabelImport" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Label ID="lblError" runat="server" Text=""></asp:Label>
</div>
</form>
</body>
</html>



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 + "<br>" +
"Type: " + FileUploadExcel.PostedFile.ContentType +
" File Size: " + FileUploadExcel.PostedFile.ContentLength +
" kb<br>";
}
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;
}


}
}


If this post helps you mark it as answer
Thanks
Author: Thamilselvan J             
Posted Date: July 25, 2013     Points: 20   

Hello Santosh,

Please follow the below link to get code for your requirement.

http://www.dotnetgallery.com/kb/resource16-Upload-and-display-excel-file-content-in-gridview-using-aspnet.aspx

Author: Sasi Prabhu             
Posted Date: September 11, 2013     Points: 20   

Hi Santhosh,

use "Linq to Excel".

Thanks & Regards,
Sasi Prabhu.
Author: ashleyturnier             
Posted Date: October 11, 2013     Points: 20   

import data from excel file to gridview is,

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

//using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Data.OleDb;
using System.Diagnostics;
using System.Security;

namespace SouthIndianDetails
{
public partial class ImportData : Form
{
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter();
public ImportData()
{
InitializeComponent();
}
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\SouthIndianDetails\\SouthIndianDetails\\InformationDB.mdf;Integrated Security=True;User Instance=True");
// SqlConnection con = new SqlConnection("server =CLTECH-68E6D6DA; Initial Catalog = Details; Uid = sa; password = test");




private void btnUpload_Click(object sender, EventArgs e)
{

OpenFileDialog op1 = new OpenFileDialog();
op1.Multiselect = false;
op1.ShowDialog();

op1.Filter = "allfiles|*.xls|*.xlsm|*.xlsx";
txtUploadFile.Text = op1.FileName;
//
string ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + txtUploadFile.Text + @"; Extended Properties = ""Excel 12.0 Macro; HDR=Yes; ImpoertMixedTypes = Text; TypeGuessRows=0""";
OleDbConnection conn = new OleDbConnection(ConnectionString);
string strcmd = "select * from [Sheet1$A1:U20000]";
OleDbCommand cmd = new OleDbCommand(strcmd, conn);
try
{
conn.Open();
ds.Clear();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
//File not match
txtUploadFile.Text = "";
UploadFileDetails objUFD = new UploadFileDetails();
objUFD.Show();
}
finally
{
conn.Close();
}
//timer1.Start();
//timer1.Enabled = true;
}

private void btnSave_Click(object sender, EventArgs e)
{



if (txtUploadFile.Text == string.Empty)
MessageBox.Show("Please select File!");
if (txtState.Text == "Andhara")
{
SqlConnection connect = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\SouthIndianDetails\\SouthIndianDetails\\FullDetails.mdf;Integrated Security=True;User Instance=True");

using (connect)
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connect;
command.CommandText = "insert into AndharaDetails values( @Name, @Address1, @Address2, @Area, @City, @State, @Pincode, @Phoneno1, @Phoneno2, @Mobileno1, @Mobileno2, @Emails, @Cperson, @Enquirydate, @Followdate, @Verified, @Remarks, @Status, @Catagery)";

command.Parameters.Add("@Name", SqlDbType.NVarChar);
command.Parameters.Add("@Address1", SqlDbType.NVarChar);
command.Parameters.Add("@Address2", SqlDbType.NVarChar);
command.Parameters.Add("@Area", SqlDbType.NVarChar);
command.Parameters.Add("@City", SqlDbType.NVarChar);
command.Parameters.Add("@State", SqlDbType.NVarChar);
command.Parameters.Add("@Pincode", SqlDbType.NVarChar);
command.Parameters.Add("@Phoneno1", SqlDbType.NVarChar);
command.Parameters.Add("@Phoneno2", SqlDbType.NVarChar);
command.Parameters.Add("@Mobileno1", SqlDbType.NVarChar);
command.Parameters.Add("@Mobileno2", SqlDbType.NVarChar);
command.Parameters.Add("@Emails", SqlDbType.NVarChar);
command.Parameters.Add("@Cperson", SqlDbType.NVarChar);
command.Parameters.Add("@Enquirydate", SqlDbType.NVarChar);
command.Parameters.Add("@Followdate", SqlDbType.NVarChar);
command.Parameters.Add("@Verified", SqlDbType.NVarChar);
command.Parameters.Add("@Remarks", SqlDbType.NVarChar);
command.Parameters.Add("@Status", SqlDbType.NVarChar);
command.Parameters.Add("@Catagery", SqlDbType.NVarChar);

connect.Open();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
try
{

command.Parameters["@Name"].Value = row.Cells[1].Value;
command.Parameters["@Address1"].Value = row.Cells[2].Value;
command.Parameters["@Address2"].Value = row.Cells[3].Value;
command.Parameters["@Area"].Value = row.Cells[4].Value;
command.Parameters["@City"].Value = row.Cells[5].Value;
command.Parameters["@State"].Value = row.Cells[6].Value;
command.Parameters["@Pincode"].Value = row.Cells[7].Value;
command.Parameters["@Phoneno1"].Value = row.Cells[8].Value;
command.Parameters["@Phoneno2"].Value = row.Cells[9].Value;
command.Parameters["@Mobileno1"].Value = row.Cells[10].Value;
command.Parameters["@Mobileno2"].Value = row.Cells[11].Value;
command.Parameters["@Emails"].Value = row.Cells[14].Value;
command.Parameters["@Cperson"].Value = row.Cells[16].Value;

command.Parameters["@Enquirydate"].Value = row.Cells[17].Value;
command.Parameters["@Followdate"].Value = row.Cells[18].Value;
command.Parameters["@Verified"].Value = row.Cells[19].Value;
command.Parameters["@Remarks"].Value = row.Cells[12].Value;
command.Parameters["@Status"].Value = row.Cells[13].Value;

command.Parameters["@Catagery"].Value = row.Cells[20].Value;
command.ExecuteNonQuery();
}
catch (Exception ed)
{
//MessageBox.Show(ed.ToString());
MessageBox.Show("Records saved successfully!");
}

}



}


}
}


}




}


private void cmbState_SelectedIndexChanged(object sender, EventArgs e)
{
if (cmbState.SelectedIndex == -1)
{
txtState.Text = "";
MessageBox.Show("Please select any one state!");
}
if (cmbState.SelectedIndex == 0)
txtState.Text = "Database1";
else if (cmbState.SelectedIndex == 1)
txtState.Text = "Database2";
else if (cmbState.SelectedIndex == 2)
txtState.Text = "Database3";



}

private void ImportData_Load(object sender, EventArgs e)
{

}

private void btnRemoveDuplicates_Click(object sender, EventArgs e)
{
// string initialnamevalue = dataGridView1.Rows[0].Cells[cellno].Text;
}






}

}

Author: cathyhill345             
Posted Date: May 13, 2014     Points: 20   


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend