Asp.net ile Excel Okuma ve Repeater Yükleme

Anasayfa / Asp.net / Asp.net ile Excel Okuma ve Repeater Yükleme

21 Haziran 2021 / 05:24

Burak

Merhaba arkadaşlar web tarafında kod yazarken excel’den import vsss işlemler yapmanız için aşağıdaki kodlar işinizi görücektir. Form tarafından 1 adet fileupload component ile upload işlemini yaptığınız varsayıyorum.Eğer excelden gelen verileri direk repeater bind edicekseniz aşağıdaki kodu kullanıcaksınız.

YÖNTEM 1

Bu yol ile sadece xls uzantıları okuyabilirsiniz.

System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/teknoloji/temp/") + rast + fonk.seo(excel_file.FileName) + ";Extended Properties='Excel 8.0;HDR=Yes'");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sayfa1$] where Urun_Kodu<>''", MyConnection);
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
excel_iceri_aktarim.DataSource = DtSet;
excel_iceri_aktarim.DataBind();
excel_iceri_aktarim.Dispose();

System.Data.OleDb.OleDbConnection MyConnection;

System.Data.DataSet DtSet;

System.Data.OleDb.OleDbDataAdapter MyCommand;

MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/teknoloji/temp/") + rast + fonk.seo(excel_file.FileName) + ";Extended Properties='Excel 8.0;HDR=Yes'");

MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sayfa1$] where Urun_Kodu<>''", MyConnection);

DtSet = new System.Data.DataSet();

MyCommand.Fill(DtSet);

excel_iceri_aktarim.DataSource = DtSet;

excel_iceri_aktarim.DataBind();

excel_iceri_aktarim.Dispose();

Form tarafından Eval kısmında ise <%#Eval(“Excel’deki sütun adı”) %> şeklinde kullanıcaksınız.Yok ben satır satır alıcam diyorsanız aşağıdaki kodu kullanıyoruz.

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/teknoloji/temp/") + rast + fonk.seo(excel_file.FileName) + ";Extended Properties='Excel 8.0;HDR=Yes'";
OleDbConnection objConn = new OleDbConnection(connectionString);
objConn.Open();
OleDbCommand ObjCommand = new OleDbCommand("SELECT * FROM [Sayfa1$] where Urun_Kodu<>''", objConn);
OleDbDataReader objReader = ObjCommand.ExecuteReader();
while (objReader.Read())
{
response.write(objReader["Rulo_Eni"].ToString());
}
objReader.Dispose();
ObjCommand.Dispose();
objConn.Dispose();

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/teknoloji/temp/") + rast + fonk.seo(excel_file.FileName) + ";Extended Properties='Excel 8.0;HDR=Yes'";

OleDbConnection objConn = new OleDbConnection(connectionString);

objConn.Open();

OleDbCommand ObjCommand = new OleDbCommand("SELECT * FROM [Sayfa1$] where Urun_Kodu<>''", objConn);

OleDbDataReader objReader = ObjCommand.ExecuteReader();

while (objReader.Read())

{

response.write(objReader["Rulo_Eni"].ToString());

}

objReader.Dispose();

ObjCommand.Dispose();

objConn.Dispose();

YÖNTEM 2

Bu yol ile hem .xls hemde .xlsx okuyabilirsiniz.

Öncelikle web.config aşağıdaki 2 connection stringleri ekleyiniz.

<connectionStrings>
<add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
<add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>
</connectionStrings>

</connectionStrings>

Form dizaynı

<%@ Page Language="C#" AutoEventWireup="true" Debug="true" CodeFile="CS.aspx.cs" Inherits="_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>Import Excel Data into GridView</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
<asp:GridView ID="GridView1" runat="server"    >
</asp:GridView>
</div>
</form>
</body>
</html>

<%@ Page Language="C#" AutoEventWireup="true" Debug="true" CodeFile="CS.aspx.cs" Inherits="_Default" %>

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

<title>Import Excel Data into GridView</title>

</head>

<body>

<div>

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />

<asp:GridView ID="GridView1" runat="server" >

</asp:GridView>

</div>

</form>

</body>

</html>

Codebehind kısmı

using System;
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.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration; 
public partial class _Default : System.Web.UI.Page 
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = "/burak/temp/"+ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension;
}
}
private void Import_To_Grid(string FilePath, string Extension)
{
string conStr="";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable(); 
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close(); 
//Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath); 
GridView1.DataSource = dt;
GridView1.DataBind();
}
}

using System;

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.Data;

using System.Data.OleDb;

using System.IO;

using System.Configuration;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void btnUpload_Click(object sender, EventArgs e)

{

if (FileUpload1.HasFile)

{

string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

string FolderPath = "/burak/temp/"+ConfigurationManager.AppSettings["FolderPath"];

string FilePath = Server.MapPath(FolderPath + FileName);

FileUpload1.SaveAs(FilePath);

Import_To_Grid(FilePath, Extension;

}

private void Import_To_Grid(string FilePath, string Extension)

{

string conStr="";

switch (Extension)

{

case ".xls": //Excel 97-03

conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;

break;

case ".xlsx": //Excel 07

conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;

break;

}

conStr = String.Format(conStr, FilePath);

OleDbConnection connExcel = new OleDbConnection(conStr);

OleDbCommand cmdExcel = new OleDbCommand();

OleDbDataAdapter oda = new OleDbDataAdapter();

DataTable dt = new DataTable();

cmdExcel.Connection = connExcel;

//Get the name of First Sheet

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

connExcel.Close();

//Read Data from First Sheet

connExcel.Open();

cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

oda.SelectCommand = cmdExcel;

oda.Fill(dt);

connExcel.Close();

//Bind Data to GridView

GridView1.Caption = Path.GetFileName(FilePath);

GridView1.DataSource = dt;

GridView1.DataBind();

}

Eğer herşeyiniz tamam fakat içeri aktar dediğinizde ” Asp.Net Microsoft.ACE.OLEDB.12.0′ provider is not registered on the local machine. ” şeklinde hata alıyorsanız serverınızda veya bilgisayarınızda gerekli bileşenler yok demektir. Servera burdaki bileşeni kurduktan sonra restart ettikten sonra kodlarınız çalışmaya başlayacaktır.