C# Ve VB İle Microsoft Excel 2007

14Şub

Yazan
blu_day1979

Kategori
Okunma Sayısı
C# Ve VB İle Microsoft Excel 2007

0

İlk Önce Excel 2007′yi Açarak Aşağıdaki Kayıtlarımızı Girelim Ve Products Olarak Kaydedelim

ProductID   ProductName   Description
1   ASP.NET 2008   Asp.Net Öğrenmek İçin İdeal Bi Kitap.
2   C# 2008   C#’ın Kalbi Bu Kitapda Atıyor.
3   SQL SERVER 2008   Beklenen Kitap Nihayet Çıktı.
4   JAVA 2006   Java İçin Temel Bir Kaynak.
5   VB 2006   Visual Basic Öğrenmek İsteyenlere Tavsiye Edilir.
6   MySQL 2007   MySQL Bugünlerde Çok Revaçta Öğrenmek Lazım.
7   ACCESS 2007   Vazgeçilemeyen Veritabanı Access Dünyanın Tercihi.
8   PHP 2008   Php İsteyenler Buyrun Buradan.
9   DELPHI 2008   Öğrenilmesi Gereken Bi Yapı.
10   J# 2005   J# Microsoftun Taçsız Kralı.

Visual Studio İle Visual Basic ASP.NET Web Projesi Oluşturalım,Hemen Ardından Daha Önce Oluşturduğumuz Products.xlsx Dosyamızı App_Data İçerisine,
Kopyala Yapıştır Yapalım Daha Sonra web.config Dosyamızda ConnectionStrings Ayarımızı Aşağıdaki Gibi Yapalım.

<connectionStrings>
<add name=”Excel”
connectionString=”Provider=Microsoft.ACE.OLEDB.12.0; Data Source=|DataDirectory|\Products.xlsx; Extended Properties=Excel 12.0″
providerName=”System.Data.OleDb”/>
</connectionStrings>

Default.aspx Sayfamızı’da Aşağıdaki Gibi Tasarlayalım.

<%@ Page Language=”VB” AutoEventWireup=”false” CodeFile=”Default.aspx.vb” 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></title>

<style type=”text/css”>

table { background-color:Black; color:White; }
.fe { background-color:yellowgreen; color:blue; }
.de:hover { font-size:20px; }
.he { color:Blue; font-size:medium; font-family:Sans-Serif Arial Helvetica; }
.style1
{
width: 137px;
}
</style>

</head>
<body>
<form id=”form1″ runat=”server”>
<div>

<table id=”myTable” runat=”server” align=”center”>
<tr>
<td><b>Ürünler</b></td>
<td><asp:DropDownList ID=”drpProducts” Width=”205″ runat=”server”
AutoPostBack=”true” /></td>
</tr>
<tr>
<td><b>Ürün No</b></td>
<td><asp:TextBox ID=”txtProductID” runat=”server” Width=”200″ /></td>
</tr>
<tr>
<td><b>Ürün Adı</b></td>
<td><asp:TextBox ID=”txtProductName” runat=”server” Width=”200″ /></td>
</tr>
<tr>
<td valign=”top”><b>Ürün Açıklaması</b></td>
<td><asp:TextBox ID=”txtDescription” runat=”server” Width=”300″ Height=”100″
TextMode=”MultiLine” MaxLength=”200″ CssClass=”he” /></td>
</tr>
<tr>
<td colspan=”2″ align=”right”>
<asp:Button ID=”btnInsert” CssClass=”fe de” runat=”server” Text=”Kaydet” />  
<asp:Button ID=”btnUpdate” CssClass=”fe de” runat=”server” Text=”Güncelle” />  
<asp:Button ID=”txtClear” CssClass=”fe de” runat=”server” Text=”Temizle” />
</td>
</tr>
</table>

</div>
</form>
</body>
</html>

Tasarım Kısmını’da Tamamladığımıza Göre Projemiz Üzerinde Sağ Click Diyerek Projemize App_Code Klasörünü Ekleyelim
Ve App_Code Klasörü İçerisine Products.vb İsimli Bir Sınıf Oluşturalım Ve Aşağıdaki Gibi Yazalım,

Imports System.Data
Imports System.Data.OleDb
Imports c_Manager = System.Web.Configuration.WebConfigurationManager
Imports Microsoft.VisualBasic

Namespace Silo.Excel

Public Class Products

#Region “Variables For Products”

Private _productID As Double
Private _productName As String
Private _aciklama As String

#End Region

#Region “Properties For Products”

Public Property ProductID() As Double
Get
Return Me._productID
End Get
Set(ByVal value As Double)
Me._productID = value
End Set
End Property

Public Property ProductName() As String
Get
Return Me._productName
End Get
Set(ByVal value As String)
Me._productName = value
End Set
End Property

Public Property Aciklama() As String
Get
Return Me._aciklama
End Get
Set(ByVal value As String)
Me._aciklama = value
End Set
End Property

#End Region

#Region “ConnectionStrings For Products”

Public Shared ReadOnly Property _ConnectionString() As String
Get
Return c_Manager.ConnectionStrings(“Excel”).ConnectionString
End Get
End Property

#End Region

#Region “Fill For DropDownList Method”

Public Shared Sub DoldurDropDownList(ByRef myDropDownList As DropDownList)

Dim dt As DataTable = Nothing

Using cn As New OleDbConnection(_ConnectionString)
Dim strSELECT As String = “SELECT ProductID,ProductName,Description FROM [Sayfa1$]”
Using com As New OleDbCommand(strSELECT, cn)
Dim da As New OleDbDataAdapter(com)
dt = New DataTable()

If com.Connection.State <> ConnectionState.Open Then
com.Connection.Open()
End If
da.Fill(dt)
com.Connection.Close()

myDropDownList.DataTextField = “ProductName”
myDropDownList.DataValueField = “ProductID”

myDropDownList.DataSource = dt
myDropDownList.DataBind()

End Using
End Using
End Sub

#End Region

#Region “Get Products For DropDownList Method”

Public Shared Function FillDropDownList(ByRef myDropDownList As DropDownList) As Products

Dim ProductID As Integer = Integer.Parse(myDropDownList.SelectedValue.ToString())
Dim myProducts As New Products()

Using cn As New OleDbConnection(_ConnectionString)

Dim strSELECT As String = “SELECT ProductID,ProductName,Description FROM [Sayfa1$] WHERE ProductID=?”

Using com As New OleDbCommand(strSELECT, cn)

com.Parameters.AddWithValue(“?”, ProductID)

If com.Connection.State <> ConnectionState.Open Then
com.Connection.Open()
End If

Dim dr As OleDbDataReader = com.ExecuteReader(CommandBehavior.CloseConnection)
Dim dogruMu As Boolean = IIf(dr.HasRows, True, False)

If dogruMu Then
While dr.Read()
myProducts.ProductID = dr.GetDouble(0)
myProducts.ProductName = dr.GetString(1)
myProducts.Aciklama = dr.GetString(2)
End While
End If
dr.Close()

End Using
End Using

Return myProducts

End Function

#End Region

#Region “Save For Products Method”

Public Shared Sub SaveProducts(ByRef myProducts As Products)

Try
Using cn As New OleDbConnection(_ConnectionString)

Dim strSELECT As String = “INSERT INTO [Sayfa1$] VALUES(?,?,?)”

Using com As New OleDbCommand(strSELECT, cn)

com.Parameters.AddWithValue(“?”, myProducts.ProductID)
com.Parameters.AddWithValue(“?”, myProducts.ProductName)
com.Parameters.AddWithValue(“?”, myProducts.Aciklama)

If com.Connection.State <> ConnectionState.Open Then
com.Connection.Open()
End If
com.ExecuteNonQuery()
com.Connection.Close()
HttpContext.Current.Response.Write(“Kayıt Başarılı.”)

End Using
End Using
Catch ex As Exception
HttpContext.Current.Response.Write(“Insert Hatası Oluştu ?<br />” & vbCrLf & ex.Message)
End Try

End Sub

#End Region

#Region “Update For Products Method”

Public Shared Sub UpdateProducts(ByRef myDropDownList As DropDownList, ByRef myProducts As Products)

Try
Using cn As New OleDbConnection(_ConnectionString)

Dim ProductID As Integer = Integer.Parse(myDropDownList.SelectedValue.ToString())
Dim strUPDATE As String = “UPDATE [Sayfa1$] SET ProductID=?, ProductName=?, Description=? WHERE ProductID=?”

Using com As New OleDbCommand(strUPDATE, cn)

com.Parameters.AddWithValue(“?”, myProducts.ProductID)
com.Parameters.AddWithValue(“?”, myProducts.ProductName)
com.Parameters.AddWithValue(“?”, myProducts.Aciklama)
com.Parameters.AddWithValue(“?”, ProductID)

If com.Connection.State <> ConnectionState.Open Then
com.Connection.Open()
End If

com.ExecuteNonQuery()
HttpContext.Current.Response.Write(“Güncelleme Başarılı Oldu.”)
com.Connection.Close()

End Using
End Using

Catch ex As Exception
HttpContext.Current.Response.Write(“Güncelleme Hatası Oluştu ?<br />.” & vbCrLf & ex.Message)
End Try

End Sub

#End Region

#Region “Clear Table Method”

Public Shared Sub ClearTable(ByRef myTable As HtmlTable)

For Each myRow As HtmlTableRow In myTable.Rows

For Each myCell As HtmlTableCell In myRow.Cells

For Each myControl As Control In myCell.Controls

If (TypeOf myControl Is TextBox) Then

Dim myText As TextBox = CType(myControl, TextBox)
myText.Text = “”

End If

Next
Next
Next

End Sub

#End Region

End Class

End Namespace

Products Class’ımızı Tamamladığımıza Göre Default.aspx.vb Kısmını’da Aşağıdaki Gibi Yazalım.

Imports Silo.Excel

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then
Products.DoldurDropDownList(drpProducts)
End If

End Sub

Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click

Dim myProducts As New Products()

myProducts.ProductID = txtProductID.Text
myProducts.ProductName = txtProductName.Text
myProducts.Aciklama = txtDescription.Text

Products.SaveProducts(myProducts)

End Sub

Protected Sub drpProducts_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles drpProducts.SelectedIndexChanged

Dim myProducts As Products = Products.FillDropDownList(drpProducts)

txtProductID.Text = myProducts.ProductID
txtProductName.Text = myProducts.ProductName
txtDescription.Text = myProducts.Aciklama

End Sub

Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

Dim myProducts As New Products()

myProducts.ProductID = txtProductID.Text
myProducts.ProductName = txtProductName.Text
myProducts.Aciklama = txtDescription.Text

Products.UpdateProducts(drpProducts, myProducts)
Products.FillDropDownList(drpProducts)

End Sub

Protected Sub txtClear_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtClear.Click
Products.ClearTable(myTable)
End Sub

End Class

Excel Visual Basic Tarafını Bitirmiş Olduk Şimdi Sıra C# Kısmına Geldi Dilerseniz Vakit Kaybetmeden C# İle Yeni Bir ASP.NET Web Projesi Oluşturalım,
Visual Basic’de Yaptığımız İşlemlerin Aynısını C# İçin’de Yapalım İlk Önce App_Data İçerisinne Products.xlsx Dosyamızı Kopyala Yapıştır Yapıyoruz,
Hemen Ardından ConnectionString Bağlantımızı web.config Dosyamızın İçerisine Yukarıda Visual Basic’de Yaptığımız Gibi Ayarlıyoruz,
Daha Sonra Projemize Sağ Click App_Code Klasörünü Ekliyoruz Ve Urunler İsminde Bir Class Oluşturuyoruz Ve Aşağıdaki Gibi Yazıyoruz,
Unutmadan C# Default.aspx Sayfamızın Tasarımı Aynı Visual Basic’deki Gibidir Sadece Buttonların Click Event’larını Ve DropDownList _SelectedIndexChanged Silip Yeniden Oluşturmamız Gerekli
Urunler Class’ımız Aşağıdaki Gibidir.

using System;
using System.Data;
using System.Configuration;
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 c_Manager = System.Web.Configuration.WebConfigurationManager;

namespace Silo.AspNet
{
public class Urunler
{

#region Variables For Products

private double _productid;
private string _productname;
private string _aciklama;

#endregion

#region Properties For Products

public double ProductID
{ get { return this._productid; } set { this._productid = value; } }

public string ProductName
{ get { return this._productname; } set { this._productname = value; } }

public string Aciklama
{ get { return this._aciklama; } set { this._aciklama = value; } }

#endregion

#region ConnectionStrings For Products

public static string connectionString
{
get { return c_Manager.ConnectionStrings["Excel"].ConnectionString; }
}

#endregion

#region Fill For DropDownList Method

public static void DoldurDropDownList(DropDownList myDropDownList)
{
DataTable myDataTable = null;
using (OleDbConnection cn = new OleDbConnection(connectionString))
{
string strSELECT = “SELECT ProductID,ProductName,Description FROM [Sayfa1$]“;

using (OleDbCommand com = new OleDbCommand(strSELECT, cn))
{
OleDbDataAdapter da = new OleDbDataAdapter(com);
myDataTable = new DataTable();

if (com.Connection.State == ConnectionState.Closed)
com.Connection.Open();

da.Fill(myDataTable);
com.Connection.Close();

myDropDownList.DataTextField = “ProductName”;
myDropDownList.DataValueField = “ProductID”;

myDropDownList.DataSource = myDataTable.DefaultView;
myDropDownList.DataBind();
}
}
}

#endregion

#region Get Products For DropDownList

public static Urunler UrunDoldursana(DropDownList myDropDownList)
{
int ProductID = int.Parse(myDropDownList.SelectedValue.ToString());
Urunler myProducts = new Urunler();

using (OleDbConnection cn = new OleDbConnection(connectionString))
{
string strSELECT = “SELECT ProductID,ProductName,Description FROM [Sayfa1$] WHERE ProductID=?”;

using (OleDbCommand com = new OleDbCommand(strSELECT, cn))
{
com.Parameters.AddWithValue(“?”, ProductID);

if (com.Connection.State == ConnectionState.Closed)
com.Connection.Open();

OleDbDataReader reader = com.ExecuteReader(CommandBehavior.CloseConnection);

bool dogruMu = reader.HasRows ? true : false;

if (dogruMu)
{
while (reader.Read())
{
myProducts.ProductID = reader.GetDouble(0);
myProducts.ProductName = reader["ProductName"].ToString();
myProducts.Aciklama = reader["Description"].ToString();

//ID.Text = reader.GetDouble(0).ToString();
//Ad.Text = reader.GetString(1);
//Aciklama.Text = reader.GetString(2);
}
}
reader.Close();
}
}
return myProducts;
}

#endregion

#region Save For Products Method

public static void UrunKaydetsene(Urunler myProducts)
{
try
{
using (OleDbConnection cn = new OleDbConnection(connectionString))
{
string strSELECT = “INSERT INTO [Sayfa1$] VALUES(?,?,?)”;

using (OleDbCommand com = new OleDbCommand(strSELECT, cn))
{
com.Parameters.AddWithValue(“?”, myProducts.ProductID);
com.Parameters.AddWithValue(“?,”, myProducts.ProductName);
com.Parameters.AddWithValue(“?,”, myProducts.Aciklama);

if (com.Connection.State == ConnectionState.Closed)
com.Connection.Open();

com.ExecuteNonQuery();

com.Connection.Close();
HttpContext.Current.Response.Write(“Başarılı Kayıt !”);
}
}
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(“Kayıt Hatası Oluştu !<br />” + ex.Message);
}

}

#endregion

#region Update For Products Method

public static void UrunGuncellesene(DropDownList myDropDownList, Urunler myProducts)
{
try
{
using (OleDbConnection cn = new OleDbConnection(connectionString))
{
int ProductID = int.Parse(myDropDownList.SelectedValue.ToString());

string strSELECT =
“UPDATE [Sayfa1$] SET ProductID = ?, ProductName = ?, Description = ? ” +
“WHERE ProductID = ?”;

using (OleDbCommand com = new OleDbCommand(strSELECT, cn))
{
com.Parameters.AddWithValue(“?”, myProducts.ProductID);
com.Parameters.AddWithValue(“?,”, myProducts.ProductName);
com.Parameters.AddWithValue(“?,”, myProducts.Aciklama);
com.Parameters.AddWithValue(“?”, ProductID);

if (com.Connection.State == ConnectionState.Closed)
com.Connection.Open();

com.ExecuteNonQuery();

com.Connection.Close();
HttpContext.Current.Response.Write(“Başarılı Güncelleme !”);
}

}
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(“Güncelleme Hatası Oluştu !<br />” + ex.Message);
}
}

#endregion

#region Clear Table Method

public static void Clear(HtmlTable myTable)
{
foreach (HtmlTableRow myRow in myTable.Rows)
{
foreach (HtmlTableCell myCell in myRow.Cells)
{
foreach (Control myControl in myCell.Controls)
{
if (myControl is TextBox)
((TextBox)myControl).Text = “”;
}
}
}

}

#endregion
}
}

Sıra Geldi Default.aspx.cs Tarafına Onuda aşağıdaki Gibi Yazıyoruz.

using System;
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 Silo.AspNet;

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

protected void Page_Load(object sender, EventArgs e)
{
if(!this.Page.IsPostBack)
Urunler.DoldurDropDownList(drpProducts);
}

protected void txtClear_Click(object sender, EventArgs e)
{
Urunler.Clear(myTable);
}

protected void drpProducts_SelectedIndexChanged(object sender, EventArgs e)
{
Urunler myProducts = Urunler.UrunDoldursana(drpProducts);

txtProductID.Text = myProducts.ProductID.ToString();
txtProductName.Text = myProducts.ProductName;
txtDescription.Text = myProducts.Aciklama;
}

protected void btnInsert_Click(object sender, EventArgs e)
{
Urunler myProducts = new Urunler();
myProducts.ProductID = double.Parse(txtProductID.Text);
myProducts.ProductName = txtProductName.Text;
myProducts.Aciklama = txtDescription.Text;

Urunler.UrunKaydetsene(myProducts);
Urunler.DoldurDropDownList(drpProducts);
}

protected void btnUpdate_Click(object sender, EventArgs e)
{
Urunler myProducts = new Urunler();
myProducts.ProductID = double.Parse(txtProductID.Text);
myProducts.ProductName = txtProductName.Text;
myProducts.Aciklama = txtDescription.Text;

Urunler.UrunGuncellesene(drpProducts, myProducts);
Urunler.DoldurDropDownList(drpProducts);
}
}

Evet Artık Her İki Projemizde Hazır Olduğuna Göre Çalıştırıp Sonuca Bakabiliriz
Sonuç Aşağıdaki Gibi Olacaktır.

Bu Makalemizde Excel 2007′yi Hem C# Hemde Visual Basic Kullanarak Aynı Bir Veritabanı Gibi Select,Insert,Update İşlemlerini Gerçekleştirmiş Olduk,
Ne Yazık Ki Microsoft’un Excel İçin Delete Yöntemi Yok Dolayısıyla Delete İşlemini Gerçekleştiremedik,
Bir Makalemizin Daha Sonuna Geldik Arkadaşlar Faydalı Olması Dileğiyle Görüşmek Üzere.

Etiketler:c, Excel 2007, vB, Visual Studio, Web Project

İlgili Konular

Paylaş!

Yorum Yapın

You must be logged in to post a comment.

Pts	Sal	Çar	Per	Cum	Cts	Paz
« Ağu
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

C# Ve VB İle Microsoft Excel 2007

İlgili Konular

Paylaş!

Yorum Yapın

Giris

Kategoriler

Arşivler

Takvim

Son YazIlar

Son Yorumlar

En Çok Okunanlar

Etiket Bulutu

Anket

Reklam AlanI

C# Ve VB İle Microsoft Excel 2007

İlgili Konular

Paylaş!

Yorum Yapın

Giris

Kategoriler

Arşivler

Takvim

Son YazIlar

Son Yorumlar

En Çok Okunanlar

Etiket Bulutu

Anket

Reklam AlanI