step : 1
TAble Name:Excel_table
Id int Null
Name Varchar(50) Allownull True
Designation : Varchar(50) Allow Null True
step:2
Same way Create Excel File with Same Filed Name:
As above
step:3
.aspx Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Import data from EXCEL to SQL Database.aspx.cs" Inherits="Import_data_from_EXCEL_to_SQL_Database" %>
<!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>
<table>
<tr>
<td>
<span style="color: Red">*</span>Attach Excel file
</td>
<td>
<asp:FileUpload ID="Fileupload1" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnSend" runat="server" Text="Export" onclick="btnSend_Click" />
</td>
</tr>
</table>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
step:4
.cs code :
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.SqlClient;
using System.Data.OleDb;
using System.IO;
public partial class Import_data_from_EXCEL_to_SQL_Database : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSend_Click(object sender, EventArgs e)
{
string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string connectionString = "";
if (Fileupload1.HasFile)
{
string fileName = Path.GetFileName(Fileupload1.PostedFile.FileName);
string fileExtension = Path.GetExtension(Fileupload1.PostedFile.FileName);
string fileLocation = Server.MapPath("~/App_Data/" + fileName);
Fileupload1.SaveAs(fileLocation);
//Check whether file extension is xls or xslx
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create OleDB Connection and OleDb Command
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
GridView1.DataSource = dtExcelRecords;
GridView1.DataBind();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Excel_table";
sqlBulk.WriteToServer(dReader);
con.Close();
}
}
}
Excel sheet is the great tool to maintain the data base and all the information in a single format.Most of data entry companies and medical billing companies are using these excel sheet.
ReplyDeleteWebsite Design Companies | Website Designing Company