Asp.net ile Excel Okuma ve Repeater Yükleme

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.

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 FilePath = Server.MapPath("uploads/"+FileName);
            FileUpload1.SaveAs(FilePath);
            
            string conStr = "";
            switch (Extension)
            {
                case ".xls": //Excel 97-03
                    conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Server.MapPath("uploads/"+FileUpload1.FileName) + "';Extended Properties='Excel 8.0'";
                    break;
                case ".xlsx": //Excel 07
                    conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Server.MapPath("uploads/"+FileUpload1.FileName) + "';Extended Properties='Excel 8.0'";
                    break;
            }
            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 FilePath = Server.MapPath("uploads/"+FileName);

FileUpload1.SaveAs(FilePath);

string conStr = "";

switch (Extension)

{

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

conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Server.MapPath("uploads/"+FileUpload1.FileName) + "';Extended Properties='Excel 8.0'";

break;

case ".xlsx": //Excel 07

conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Server.MapPath("uploads/"+FileUpload1.FileName) + "';Extended Properties='Excel 8.0'";

break;

}

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.