User-1026236167 posted
i want to import data from excel to gridview but
an error occur path format is not supported
please execute them
here is my code
aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm65.aspx.cs" Inherits="WebApplication14.WebForm65" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id = "form1" runat="server">
<div>
<b>Please Select Excel File: </b>
<asp:FileUpload ID = "fileuploadExcel" runat="server" />
<asp:Button ID = "btnImport" runat="server" Text="Import Data" OnClick="btnImport_Click" />
<br />
<asp:Label ID = "lblMessage" runat="server" Visible="False" Font-Bold="True" ForeColor="#009933"></asp:Label><br />
<asp:GridView ID = "grvExcelData" runat="server">
<HeaderStyle BackColor = "#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.IO;
namespace WebApplication14
{
public partial class WebForm65 : System.Web.UI.Page
{
SqlCommand cmd = new SqlCommand();
SqlConnection con = new SqlConnection();
string connection = System.Configuration.ConfigurationManager.AppSettings["con"].ToString();
public void EstablishConnection(string storeprocedure)
{
con.ConnectionString = connection;
cmd.Connection = con;
cmd.Connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storeprocedure;
}
public void CloseConnection()
{
cmd.Connection.Close();
cmd.Connection.Dispose();
con.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//BindGrid();
}
}
//public void BindGrid()
//{
// using (hospitalEntities ctx = new hospitalEntities())
// {
// grvExcelData.DataSource = (from hospitalEntities in ctx.tb_department_designation
// select hospitalEntities).ToList();
// grvExcelData.DataBind();
// }
//}
protected void btnImport_Click(object sender, EventArgs e)
{
string connString = "";
string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
//string path = fileuploadExcel.PostedFile.FileName;
string path = Server.MapPath("~/C:/Users/Prabhjot Aulakh/OneDrive/Desktop/" + fileuploadExcel.FileName);
fileuploadExcel.SaveAs(path);
//Response.Write("path=" + path);
//string path = Server.MapPath("~/UploadDir/" + fileuploadExcel.FileName);
//fileuploadExcel.SaveAs(path);
//Response.Write("path=" + path); return;
//Connection String to Excel Workbook
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//string query = "SELECT [country],[University Type],[University Name],[Date of Contract],[Expiry Date],[Contact Person],[Contact No.],[Email-Id] FROM [International details$]";
string query = "select * from [Sheet1$]";
OleDbConnection conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed)
conn.Open();
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
grvExcelData.DataSource = ds.Tables[0];
grvExcelData.DataBind();
da.Dispose();
conn.Close();
conn.Dispose();
//BindGrid();
}
}
}
