Excel Export için ReportModel Yapısı ve Kullanımı

Excel export işlemi için daha öncede yazdığım yazıya buradan ulaşabilirsiniz. Bu yazımda sizlere excel export işleminde kullanabileceğiniz esnek bir ReportModel’den bahsedeceğim. Bu yapıları kullanarak oluşturacağınız bir ReportModel’i excel export işleminde kullanabilirsiniz. Böylece elinizde esnek bir yapı olacaktır. Dilediğiniz şekilde excel tabloları oluşturabilirsiniz.

Verilerinizi veritabanından çektikten sonra Report Model’i oluşturmanız yeterli olacaktır. Excel tablosunda görünmesini istediğiniz özellikleri(property) içeren anonim(anonymous) tipi oluşturmanız yeterlidir.

Öncelikle nasıl kullanıldığını içeren Helper class’ı sizlerele paylaşıyorum. Diğer class’larda yazının ilerleyen kısımların mevcut. Kod aralarında açıklama satırları mevcuttur. Anlaşılacağını umuyorum.

kmb-udemy-reklam

MyExcelHelper.cs

using ExcelExportSample.Model;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExcelExportSample
{
    public class MyExcelHelper
    {
        public void WriteToExcel(RDExportReportModel exportReportModel, string path)
        {
            // Excel dosya erişimi için gerekli tanımlar yapılır.
            Microsoft.Office.Interop.Excel.Application xlApp;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            // Başlık(Header) hücrelerin renklenmesi sağlanır.
            xlWorkSheet.Cells["1:1"].Font.Bold = true;
            xlWorkSheet.Cells["1:1"].Font.Color = Color.Red;

            int colIndex = 65;  //A

            // Excel tablo Header'lar yazılır.
            foreach (RDExportReportColumnModel column in exportReportModel.ExportData.Keys)
            {
                string cellIndex = char.ConvertFromUtf32(colIndex);
                string cellHeader = column.ColumnName;

                xlWorkSheet.Cells[string.Concat(cellIndex, 1)].Value = cellHeader;

                if (column.ColumnWidth > 0)
                    xlWorkSheet.Cells[string.Concat(cellIndex, 1)].ColumnWidth = column.ColumnWidth;

                if (!string.IsNullOrEmpty(column.Format))
                    xlWorkSheet.Cells[string.Concat(cellIndex, ":", cellIndex)].NumberFormat = column.Format;

                colIndex++;
            }

            colIndex = 65;  //A

            // Her bir kolon için dönülerek, kolon kolon veriler yazılır.
            foreach (RDExportReportColumnModel column in exportReportModel.ExportData.Keys)
            {
                string cellIndex = char.ConvertFromUtf32(colIndex);
                int rowIndex = 2;

                // Değerler satır satır hücrelere yazılır.
                // Yazma işlemi kolon kolon yapılır.
                // Her kolon satur satır işlenir, sonra diğer kolona geçilir.
                foreach (RDExportReportCellModel cellModel in exportReportModel.ExportData[column])
                {
                    xlWorkSheet.Cells[string.Concat(cellIndex, rowIndex)].Value = cellModel.Value.ToString();
                    rowIndex++;
                }

                colIndex++;
            }

            // Excel dosya kaydedilir.
            xlWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
        }

        public void SaveSampleExcelExportFile()
        {
            RDExportReportModel reportModel = new RDExportReportModel();
            reportModel.ExportData = new Dictionary>();

            // Örnek veri bu veri veritabanından geliyor olsun.
            var sampleData = new List>() {
                new Tuple("Murat","Başeren"),
                new Tuple("Şükran","Başeren"),
                new Tuple("Belinay","Başeren")
            };

            // Veritabanından gelen veriyi istediğimiz bir model'e dönüştürüyoruz.
            var data = (from x in sampleData
                        select new
                         {
                             FirstName = x.Item1,
                             LastName = x.Item2
                         }).ToList();

            // Kolon ve verisi oluşturulur.
            reportModel.ExportData.Add(
                RDExportReportColumnModel.CreateColumnModel("ExportExcel#", "FirstName"),
                new List(
                    data.Select(x =>
                        new RDExportReportCellModel()
                        {
                            Value = x.FirstName
                        })));


            // Kolon ve verisi oluşturulur.
            reportModel.ExportData.Add(
                    RDExportReportColumnModel.CreateColumnModel("ExportExcel#", "LastName"),
                    new List(
                        data.Select(x =>
                            new RDExportReportCellModel()
                            {
                                Value = x.FirstName
                            })));

            // Excel export yapılır.
            WriteToExcel(reportModel, Application.StartupPath + @"\sample.xls");
        }
    }
}

RDExportReportModel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelExportSample.Model
{
    public class RDExportReportModel
    {
        ///

        /// Verileri kolon ve kolona ait hücre değerleri olarak saklıyoruz. Dolayısı ile satırları elde etmek için aşağıdaki metotlar kullanılır.
        ///

public Dictionary<RDExportReportColumnModel, List> ExportData { get; set; } ///
/// Verilen satır index ‘ine ait satır verilerini getirir. ///

/// /// public RDExportReportRowModel GetRow(int rowIndex) { RDExportReportRowModel rowModel = new RDExportReportRowModel(); rowModel.Cells = new List(); this.ExportData.Values.ToList().ForEach( x => { rowModel.Cells.Add( new RDExportReportCellModel() { Value = x[rowIndex].Value }); }); return rowModel; } ///
/// Tüm satırları verir. ///

/// public List GetRows() { List rows = new List(); int rowCount = this.ExportData[this.ExportData.Keys.First()].Count; for (int i = 0; i < rowCount; i++) { RDExportReportRowModel rowModel = new RDExportReportRowModel(); rowModel.Cells = new List(this.ExportData.Keys.Count); foreach (RDExportReportColumnModel column in this.ExportData.Keys) { rowModel.Cells.Add( new RDExportReportCellModel() { Value = this.ExportData[column][i].Value }); } rows.Add(rowModel); } return rows; } } }

RDExportReportColumnModel.cs

namespace ExcelExportSample.Model
{
    public class RDExportReportColumnModel
    {
        public string ColumnName { get; set; }
        public int ColumnWidth { get; set; }
        public string Format { get; set; }


        ///

        /// 
        ///

/// Localization section. /// Localization key. For example; “{0}#{1}” /// public static RDExportReportColumnModel CreateColumnModel(string section, string key) { return new RDExportReportColumnModel() { ColumnName = Localization.Get(section, key) }; } } public class Localization { public static string Get(string section, string key) { // Burada veritabanından okunan localization eşlemesinin karşılığı elde edilir. Örnek; Login#Username gibi anahtar veritabanından sorgulanır. return section + “#” + key; } } }

RDExportReportRowModel.cs

using System.Collections.Generic;
using System.Drawing;

namespace ExcelExportSample.Model
{
    public class RDExportReportRowModel
    {
        public List Cells { get; set; }
    }
}

RDExportReportCellModel.cs

namespace ExcelExportSample.Model
{
    public class RDExportReportCellModel
    {
        public object Value { get; set; }
    }
}

Örnek kullanım;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelExportSample
{
    class Program
    {
        static void Main(string[] args)
        {
            MyExcelHelper myExcelHelper = new MyExcelHelper();
            myExcelHelper.SaveSampleExcelExportFile();
        }

    }
}

Proje dosyası için tıklayınız..

 

kmb-udemy-reklam

List Tipinden İki Farklı Şekilde Excel Export

İnternette ararken bulduğum iki farklı kütüphane kullanarak yazılmış, List<T> generic tipinden nesnelerinizi excel ‘e export(aktarma) işleminde kullanabileceğiniz kod parçalarını tek bir class içinde topladım. Sürekli ihtiyaç duyulan bir kod olduğu için sizlerinde kıyıya köşeye bir yerlere atmanızı tavsiye ederim.

Kod parçalarından biri Microsoft.Office.Interop.Excel kütüphanesini kullanarak export işlemi yaparken, diğer kod parçası ise DocumentFormat.OpenXml kütüphanesini kullanarak export işlemini gerçekleştiriyor. Örnek bir nesne instance’ı ile denediğimde başarılı ve aynı sonucu aldım. Kod parçaları güzel bir şekilde çalışıyor. List<T> generic tipinde kullandığınız T tipine ait özellikleri kolon olarak ayarlıyor. Sonrasında metoda parametre geçtiğiniz List<T> nesnenizde dönerek, her bir nesneden bir tablo satırı elde ederek excel tablosu şeklinde bir çıktı elde ediyor.

kmb-udemy-reklam

Projenize eklemeniz gereken referansları kod içinde belirttim. Kodları bir class içine koyduktan sonra kullanmak için;

bool result = false;

// Interop.Excel yöntemi ile..
MyHelpers.ExportHelper eh = new MyHelpers.ExportHelper();
result = eh.ExportExcel(fileName, list);

// OpenXml yöntemi ile
result = MyHelpers.ExportHandle.CreateExcelDocument(list, fileName);

Kaynak : http://www.mikesknowledgebase.com

“List Tipinden İki Farklı Şekilde Excel Export” yazısını okumaya devam et

VB – RadGridView – Excel Export İşleminde Sayı Verisi İçerenTarih Kolonu (Excel export that includes the date into number column)

Bazen veritabanında tarih alanını “float” formatında tutarız. Bu durumda GridView ya da RadGridView kontrolünde ekranda tarih formatında göstermede bir sorun yaşanmamaktadır. Fakat RadGridView’ın tarih verisi içeren bu kolonunu Excel Export işlemi için ilgili özelliklerini uygun bir şekilde ayarlamazsak, excel dosyamızda tarih kolonunda “double” sayılar görebiliriz. Bunun için aşağıdaki kod yardımınıza yetişecektir.

image image
'' Excel Export i?leminde Datetime kolonlar?nda say?(double) olarak tutulan verilerin, tarih format?nda excel'e aktar?lmas?.
For Each dc As Telerik.WinControls.UI.GridViewColumn In gridView.Columns    
    If (dc.GetType() Is GetType(Telerik.WinControls.UI.GridViewDateTimeColumn)) Then        
Dim clm As Telerik.WinControls.UI.GridViewDateTimeColumn = TryCast(dc, elerik.WinControls.UI.GridViewDateTimeColumn)
        clm.ExcelExportFormatString = "{0:d}"        
        clm.ExcelExportType = Telerik.WinControls.UI.Export.DisplayFormatType.ShortDate    
    End If
Next

image

VB – Creating My RadGridView User Control

RadGridView kontrolünü kullanarak birçok işlemi yapabiliyoruz.. Bu işlemler;

Excel Export..
Kolon başlığı verme, kolon tipi verme, kolon boyutu, hizalama..
Kayıt Sayısı bilgisi..
Alternatif row ayarı, gruplama..

gibi işlemleri daha pratik yapabileceğim bir user control geliştirdim. Aşağıda bu kontrol ile ilgili bilgileri ve kodları bulabilir sizde kullanabilirsiniz..

Tabii bu kontrolü oluşturmak için Telerik firmasına ait WinControls setine sahip olmanız gerekmektedir.. İlgili referansları eklemeyi unutmayınız..

image

image

ctrlMyRadGridView.Designer.cs

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
    Partial Class ctrlMyRadGridViewInherits System.Windows.Forms.UserControl    

    'UserControl overrides dispose to clean up the component list.     _    
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer    

    Private components As System.ComponentModel.IContainer
    'NOTE: The following procedure is required by the Windows Form Designer    'It can be modified using the Windows Form Designer.      
    'Do not modify it using the code editor.     _    
    Private Sub InitializeComponent()
        Me.pnlRoot = New Telerik.WinControls.UI.RadPanel
        Me.pnlBody = New System.Windows.Forms.Panel
        Me.grvGridView = New Telerik.WinControls.UI.RadGridView
        Me.rssStatusBar = New Telerik.WinControls.UI.RadStatusStrip
        Me.lblGridViewRowCountInfo = New Telerik.WinControls.UI.RadLabelElement
        Me.pnlHead = New System.Windows.Forms.Panel
        Me.cmdExcelExport = New Telerik.WinControls.UI.RadButton
        CType(Me.pnlRoot, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.pnlRoot.SuspendLayout()
        Me.pnlBody.SuspendLayout()
        CType(Me.grvGridView, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.grvGridView.MasterGridViewTemplate, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.rssStatusBar, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.pnlHead.SuspendLayout()
        CType(Me.cmdExcelExport, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()        '        

        'pnlRoot        '        
        Me.pnlRoot.Controls.Add(Me.pnlBody)
        Me.pnlRoot.Controls.Add(Me.pnlHead)
        Me.pnlRoot.Dock = System.Windows.Forms.DockStyle.Fill
        Me.pnlRoot.Location = New System.Drawing.Point(0, 0)
        Me.pnlRoot.Name = "pnlRoot"
        Me.pnlRoot.Size = New System.Drawing.Size(289, 242)
        Me.pnlRoot.TabIndex = 0
        Me.pnlRoot.ThemeName = "Office2007Blue"        '        

        'pnlBody        '        
        Me.pnlBody.Controls.Add(Me.grvGridView)
        Me.pnlBody.Controls.Add(Me.rssStatusBar)
        Me.pnlBody.Dock = System.Windows.Forms.DockStyle.Fill
Me.pnlBody.Location = New System.Drawing.Point(0, 26)        Me.pnlBody.Name = "pnlBody"        
        Me.pnlBody.Size = New System.Drawing.Size(289, 216)

        Me.pnlBody.TabIndex = 3        '        

        'grvGridView        '        
        Me.grvGridView.Dock = System.Windows.Forms.DockStyle.Fill
        Me.grvGridView.EnableAlternatingRowColor = True
        Me.grvGridView.EnableFastScrolling = True
        Me.grvGridView.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(162, Byte))
        Me.grvGridView.Location = New System.Drawing.Point(0, 0)        '        '        '        
        Me.grvGridView.MasterGridViewTemplate.AllowAddNewRow = False
        Me.grvGridView.MasterGridViewTemplate.AllowDeleteRow = False
        Me.grvGridView.MasterGridViewTemplate.EnableFiltering = True
        Me.grvGridView.Name = "grvGridView"
        Me.grvGridView.Size = New System.Drawing.Size(289, 192)
        Me.grvGridView.TabIndex = 1

        'rssStatusBar        '        
        Me.rssStatusBar.AutoSize = True
        Me.rssStatusBar.Items.AddRange(New Telerik.WinControls.RadItem() {Me.lblGridViewRowCountInfo})
        Me.rssStatusBar.LayoutStyle = Telerik.WinControls.UI.RadStatusBarLayoutStyle.Stack
        Me.rssStatusBar.Location = New System.Drawing.Point(0, 192)
        Me.rssStatusBar.Name = "rssStatusBar"
        Me.rssStatusBar.Size = New System.Drawing.Size(289, 24)
        Me.rssStatusBar.SizingGrip = False
        Me.rssStatusBar.TabIndex = 0
        Me.rssStatusBar.Text = "RadStatusStrip1"        '        

        'lblGridViewRowCountInfo        '        
        Me.lblGridViewRowCountInfo.Margin = New System.Windows.Forms.Padding(1)
        Me.lblGridViewRowCountInfo.Name = "lblGridViewRowCountInfo"
        Me.rssStatusBar.SetSpring(Me.lblGridViewRowCountInfo, False)

        Me.lblGridViewRowCountInfo.Tag = "Kay?t Say?s? : {0}"

        Me.lblGridViewRowCountInfo.Text = "Kay?t Say?s? : {0}"

        Me.lblGridViewRowCountInfo.TextWrap = True

        'pnlHead        '        
        Me.pnlHead.Controls.Add(Me.cmdExcelExport)
        Me.pnlHead.Dock = System.Windows.Forms.DockStyle.Top
        Me.pnlHead.Location = New System.Drawing.Point(0, 0)
        Me.pnlHead.Name = "pnlHead"
        Me.pnlHead.Padding = New System.Windows.Forms.Padding(3, 3, 2, 3)
        Me.pnlHead.Size = New System.Drawing.Size(289, 26)
        Me.pnlHead.TabIndex = 2

        'cmdExcelExport    
        Me.cmdExcelExport.Dock = System.Windows.Forms.DockStyle.Right
        Me.cmdExcelExport.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(162, Byte))
        Me.cmdExcelExport.Image = Nothing
        Me.cmdExcelExport.Location = New System.Drawing.Point(182, 3)
        Me.cmdExcelExport.Name = "cmdExcelExport"
        Me.cmdExcelExport.Padding = New System.Windows.Forms.Padding(3, 0, 0, 0)
        Me.cmdExcelExport.Size = New System.Drawing.Size(105, 20)
        Me.cmdExcelExport.TabIndex = 0
        Me.cmdExcelExport.Text = "Excel Export"

        'ctrlMyRadGridView        '        
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.Controls.Add(Me.pnlRoot)
        Me.Name = "ctrlKocRadGridView"
        Me.Size = New System.Drawing.Size(289, 242)
        CType(Me.pnlRoot, System.ComponentModel.ISupportInitialize).EndInit()
        Me.pnlRoot.ResumeLayout(False)
        Me.pnlBody.ResumeLayout(False)
        Me.pnlBody.PerformLayout()
        CType(Me.grvGridView.MasterGridViewTemplate, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.grvGridView, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.rssStatusBar, System.ComponentModel.ISupportInitialize).EndInit()
        Me.pnlHead.ResumeLayout(False)
        CType(Me.cmdExcelExport, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

    Public WithEvents pnlRoot As Telerik.WinControls.UI.RadPanel
    Public WithEvents pnlHead As System.Windows.Forms.Panel
    Public WithEvents cmdExcelExport As Telerik.WinControls.UI.RadButton
    Public WithEvents pnlBody As System.Windows.Forms.Panel
    Public WithEvents grvGridView As Telerik.WinControls.UI.RadGridView
    Public WithEvents rssStatusBar As Telerik.WinControls.UI.RadStatusStrip
    Public WithEvents lblGridViewRowCountInfo As Telerik.WinControls.UI.RadLabelElement
End Class

ctrlMyRadGridView.vb

Public Class ctrlMyRadGridView

#Region "Local Variables"

    Private Const STANDART_COLUMN_WIDTH As Integer = 120

#End Region

#Region "Properties"

    Private _EnabledApplyOptions As Boolean = False
    ''' <summary>
    ''' Options ayarlar?n?n uygulanmas?n? ya da uygulanmamas?n? sa?lar.
    ''' </summary>
    ''' <value>True, aktifle?tirilen option'lar?n uygulanmas?n? sa?lar.
    ''' False, option aktifli?ini etkilemeden ayarlar?n uygulanmamas?n? sa?lar.</value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property EnabledApplyOptions() As Boolean
        Get
            Return Me._EnabledApplyOptions
        End Get
        Set(ByVal value As Boolean)
            Me._EnabledApplyOptions = value
        End Set
    End Property

    Private _EnabledApplyAllOptions As Boolean = False
    ''' <summary>
    ''' T?m Options'lar?n aktifle?mesini ya da pasifle?mesini sa?lar.
    ''' (Enabled = True)
    ''' </summary>
    ''' <value>True, t?m option'lar?n "Enabled" ?zelli?ini aktifle?tirir.
    ''' False, t?m option'lar?n "Enabled" ?zelli?ini pasifle?tirir.</value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property EnabledApplyAllOptions() As Boolean
        Get
            Return Me._EnabledApplyAllOptions
        End Get
        Set(ByVal value As Boolean)
            Me._EnabledApplyAllOptions = value

            Me.EnabledPinnedColumnOption = value
            Me.EnabledTypedColumnOption = value
            Me.EnabledVisibilityColumnOption = value
            Me.EnabledHeaderColumnOption = value
            Me.EnabledMiddleAlignmentColumnOption = value
            Me.EnabledStandartColumnWidthOption = value
        End Set
    End Property

    Private _EnabledPinnedColumnOption As Boolean = False
    Public Property EnabledPinnedColumnOption() As Boolean
        Get
            Return Me._EnabledPinnedColumnOption
        End Get
        Set(ByVal value As Boolean)
            Me._EnabledPinnedColumnOption = value

            If (value AndAlso Me.PinnedColumnList Is Nothing) Then
                Me.PinnedColumnList = New List(Of PinnedColumnOptionObject)
            Else
                Me.PinnedColumnList = Nothing
            End If
        End Set
    End Property

    Private _EnabledTypedColumnOption As Boolean = False
    Public Property EnabledTypedColumnOption() As Boolean
        Get
            Return Me._EnabledTypedColumnOption
        End Get
        Set(ByVal value As Boolean)
            Me._EnabledTypedColumnOption = value

            If (value AndAlso Me.TypedColumnList Is Nothing) Then
                Me.TypedColumnList = New List(Of TypedColumnOptionObject)
            Else
                Me.TypedColumnList = Nothing
            End If
        End Set
    End Property

    Private _EnabledVisibilityColumnOption As Boolean = False
    Public Property EnabledVisibilityColumnOption() As Boolean
        Get
            Return Me._EnabledVisibilityColumnOption
        End Get
        Set(ByVal value As Boolean)
            Me._EnabledVisibilityColumnOption = value

            If (value AndAlso Me.VisibilityColumnList Is Nothing) Then
                Me.VisibilityColumnList = New List(Of VisibleColumnOptionObject)
            Else
                Me.VisibilityColumnList = Nothing
            End If
        End Set
    End Property

    Private _EnabledHeaderColumnOption As Boolean = False
    Public Property EnabledHeaderColumnOption() As Boolean
        Get
            Return Me._EnabledHeaderColumnOption
        End Get
        Set(ByVal value As Boolean)
            Me._EnabledHeaderColumnOption = value

            If (value AndAlso Me.HeaderColumnList Is Nothing) Then
                Me.HeaderColumnList = New List(Of HeaderColumnOptionObject)
            Else
                Me.HeaderColumnList = Nothing
            End If
        End Set
    End Property

    Private _EnabledMiddleAlignmentColumnOption As Boolean = False
    Public Property EnabledMiddleAlignmentColumnOption() As Boolean
        Get
            Return Me._EnabledMiddleAlignmentColumnOption
        End Get
        Set(ByVal value As Boolean)
            Me._EnabledMiddleAlignmentColumnOption = value
        End Set
    End Property

    Private _EnabledStandartColumnWidthOption As Boolean = False
    Public Property EnabledStandartColumnWidthOption() As Boolean
        Get
            Return Me._EnabledStandartColumnWidthOption
        End Get
        Set(ByVal value As Boolean)
            Me._EnabledStandartColumnWidthOption = value
        End Set
    End Property

    Private _PinnedColumnList As List(Of PinnedColumnOptionObject)
    Public Property PinnedColumnList() As List(Of PinnedColumnOptionObject)
        Get
            Return Me._PinnedColumnList
        End Get
        Set(ByVal value As List(Of PinnedColumnOptionObject))
            If (Me._PinnedColumnList Is Nothing) Then _
                Me._PinnedColumnList = New List(Of PinnedColumnOptionObject)

            Me._PinnedColumnList = value
        End Set
    End Property

    Private _TypedColumnList As List(Of TypedColumnOptionObject)
    Public Property TypedColumnList() As List(Of TypedColumnOptionObject)
        Get
            Return Me._TypedColumnList
        End Get
        Set(ByVal value As List(Of TypedColumnOptionObject))
            If (Me._TypedColumnList Is Nothing) Then _
                Me._TypedColumnList = New List(Of TypedColumnOptionObject)
            Me._TypedColumnList = value
        End Set
    End Property

    Private _VisibilityColumnList As List(Of VisibleColumnOptionObject)
    Public Property VisibilityColumnList() As List(Of VisibleColumnOptionObject)
        Get
            Return Me._VisibilityColumnList
        End Get
        Set(ByVal value As List(Of VisibleColumnOptionObject))
            If (Me._VisibilityColumnList Is Nothing) Then _
                Me._VisibilityColumnList = New List(Of VisibleColumnOptionObject)

            Me._VisibilityColumnList = value
        End Set
    End Property

    Private _HeaderColumnList As List(Of HeaderColumnOptionObject)
    Public Property HeaderColumnList() As List(Of HeaderColumnOptionObject)
        Get
            Return Me._HeaderColumnList
        End Get
        Set(ByVal value As List(Of HeaderColumnOptionObject))
            If (Me._HeaderColumnList Is Nothing) Then _
                Me._HeaderColumnList = New List(Of HeaderColumnOptionObject)

            Me._HeaderColumnList = value
        End Set
    End Property

    Private _CustomColumnWidth As Integer = -1
    Public Property CustomColumnWidth() As Integer
        Get
            Return Me._CustomColumnWidth
        End Get
        Set(ByVal value As Integer)
            Me._CustomColumnWidth = value
        End Set
    End Property

    Private _ExportExcelSheetName As String
    Public Property ExportExcelSheetName() As String
        Get
            Return _ExportExcelSheetName
        End Get
        Set(ByVal value As String)
            _ExportExcelSheetName = value
        End Set
    End Property

    Private _ExportExcelFileName As String
    Public Property ExportExcelFileName() As String
        Get
            Return _ExportExcelFileName
        End Get
        Set(ByVal value As String)
            _ExportExcelFileName = value
        End Set
    End Property

#End Region

#Region "Public Methods"
    Public Sub WriteGridViewRowCount()
        Try
            If (Me.grvGridView.DataSource IsNot Nothing) Then

                Dim rowCountText As String = _
                    String.Format(Me.lblGridViewRowCountInfo.Tag.ToString(), _
                    Me.grvGridView.RowCount.ToString())

                Me.lblGridViewRowCountInfo.Text = rowCountText

            Else

                Me.lblGridViewRowCountInfo.Text = String.Empty

            End If

        Catch ex As Exception
            MsgBox(ex.Messages)
        End Try
    End Sub
    Public Sub ExportExcel()
        Me.ExportExcel(Me.ExportExcelSheetName, Me.ExportExcelFileName, "Excel'e Aktar", False, Telerik.WinControls.UI.Export.HiddenOption.DoNotExport, Me.grvGridView)
    End Sub
    Public Sub ExportWord()
        '' Not Implemented..
    End Sub
#End Region

#Region "Private Methods"

    Private Sub ApplyPinnedColumnOption()
        If (Me.EnabledPinnedColumnOption AndAlso Me.PinnedColumnList IsNot Nothing) Then

            If (Me.grvGridView.DataSource IsNot Nothing AndAlso _
                    Me.grvGridView.MasterGridViewTemplate.Columns IsNot Nothing AndAlso _
                    Me.grvGridView.MasterGridViewTemplate.Columns.Count > 0) Then

                If (Me.PinnedColumnList IsNot Nothing) Then

                    For Each optionObj As PinnedColumnOptionObject In Me.PinnedColumnList
                        If (Me.grvGridView.MasterGridViewTemplate.Columns(optionObj.ColumnName) IsNot Nothing) Then _
                            Me.grvGridView.MasterGridViewTemplate.Columns(optionObj.ColumnName).IsPinned = optionObj.IsPinned
                    Next

                End If

            End If

        End If
    End Sub
    Private Sub ApplyTypedColumnOption()
        If (Me.EnabledTypedColumnOption AndAlso Me.TypedColumnList IsNot Nothing) Then

            If (Me.grvGridView.DataSource IsNot Nothing AndAlso _
                    Me.grvGridView.MasterGridViewTemplate.Columns IsNot Nothing AndAlso _
                    Me.grvGridView.MasterGridViewTemplate.Columns.Count > 0) Then

                If (Me.TypedColumnList IsNot Nothing) Then

                    For Each optionObj As TypedColumnOptionObject In Me.TypedColumnList
                        If (Me.grvGridView.MasterGridViewTemplate.Columns(optionObj.ColumnName) IsNot Nothing) Then _
                            Me.grvGridView.MasterGridViewTemplate.Columns(optionObj.ColumnName).DataType = optionObj.ColumnType
                    Next

                End If

            End If

        End If
    End Sub
    Private Sub ApplyVisibleColumnOption()
        If (Me.EnabledVisibilityColumnOption AndAlso Me.VisibilityColumnList IsNot Nothing) Then

            If (Me.grvGridView.DataSource IsNot Nothing AndAlso _
                    Me.grvGridView.MasterGridViewTemplate.Columns IsNot Nothing AndAlso _
                    Me.grvGridView.MasterGridViewTemplate.Columns.Count > 0) Then

                If (Me.VisibilityColumnList IsNot Nothing) Then

                    For Each optionObj As VisibleColumnOptionObject In Me.VisibilityColumnList
                        If (Me.grvGridView.MasterGridViewTemplate.Columns(optionObj.ColumnName) IsNot Nothing) Then _
                            Me.grvGridView.MasterGridViewTemplate.Columns(optionObj.ColumnName).IsVisible = optionObj.IsVisible
                    Next

                End If

            End If

        End If
    End Sub
    Private Sub ApplyHeaderColumnOption()
        If (Me.EnabledHeaderColumnOption AndAlso Me.HeaderColumnList IsNot Nothing) Then

            If (Me.grvGridView.DataSource IsNot Nothing AndAlso _
                    Me.grvGridView.MasterGridViewTemplate.Columns IsNot Nothing AndAlso _
                    Me.grvGridView.MasterGridViewTemplate.Columns.Count > 0) Then

                If (Me.HeaderColumnList IsNot Nothing) Then

                    For Each optionObj As HeaderColumnOptionObject In Me.HeaderColumnList
                        If (Me.grvGridView.MasterGridViewTemplate.Columns(optionObj.ColumnName) IsNot Nothing) Then _
                            Me.grvGridView.MasterGridViewTemplate.Columns(optionObj.ColumnName).HeaderText = optionObj.ColumnNewName
                    Next

                End If

            End If

        End If
    End Sub
    Private Sub ApplyMiddleAlignmentColumnOption()
        If (Me.EnabledMiddleAlignmentColumnOption) Then
            For Each dc As Telerik.WinControls.UI.GridViewDataColumn In Me.grvGridView.MasterGridViewTemplate.Columns
                dc.TextAlignment = Drawing.ContentAlignment.MiddleCenter
            Next
        End If
    End Sub
    Private Sub ApplyColumnWidthOption()
        If (Me.EnabledStandartColumnWidthOption) Then
            For Each dc As Telerik.WinControls.UI.GridViewDataColumn In Me.grvGridView.MasterGridViewTemplate.Columns
                dc.Width = STANDART_COLUMN_WIDTH
            Next
        Else
            If (Me.CustomColumnWidth <> -1) Then
                For Each dc As Telerik.WinControls.UI.GridViewDataColumn In Me.grvGridView.MasterGridViewTemplate.Columns
                    dc.Width = Me.CustomColumnWidth
                Next
            End If
        End If
    End Sub

    Private Sub ApplyOptions()
        If (Me.EnabledApplyOptions) Then
            Me.ApplyMiddleAlignmentColumnOption()
            Me.ApplyPinnedColumnOption()
            Me.ApplyVisibleColumnOption()
            Me.ApplyHeaderColumnOption()
            Me.ApplyColumnWidthOption()
            Me.ApplyTypedColumnOption()
        End If
    End Sub

    ''' <summary>
    ''' Excel Export Metodu.
    ''' </summary>
    ''' <param name="sheetName">Excel sheet ismi.</param>
    ''' <param name="fileName">Excel dosya ismi.</param>
    ''' <param name="saveFileDialogTitle">Dosya kaydetme kutucu?u ba?l???.</param>
    ''' <param name="exportVisualSettings">G?rsel ayarlar? uygula.</param>
    ''' <param name="gridView">Export edilecek RadGridView.</param>
    ''' <returns>True d?nerse, export ba?ar?l?; aksi halde export ba?ar?s?z.</returns>
    ''' <remarks></remarks>
    Private Function ExportExcel(ByVal sheetName As String, ByVal fileName As String, ByVal saveFileDialogTitle As String, ByVal exportVisualSettings As Boolean, ByVal hiddenOption As Telerik.WinControls.UI.Export.HiddenOption, ByVal gridView As Telerik.WinControls.UI.RadGridView) As Boolean

        Dim result As Boolean = False
        Dim exporter As Telerik.WinControls.UI.Export.ExportToExcelML

        Dim sfd As System.Windows.Forms.SaveFileDialog = _
           New System.Windows.Forms.SaveFileDialog()

        sfd.Title = saveFileDialogTitle
        sfd.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        sfd.Filter = "Microsoft Office Excel 97-2003(*.xls)|*.xls|Microsoft Office Excel 2007(*.xlsx)|*.xlsx"
        sfd.FilterIndex = 1
        sfd.FileName = fileName

        Try
            If (sfd.ShowDialog() = Windows.Forms.DialogResult.OK) Then

                exporter = New Telerik.WinControls.UI.Export.ExportToExcelML(gridView)
                exporter.HiddenColumnOption = hiddenOption
                exporter.SheetName = sheetName
                exporter.ExportVisualSettings = exportVisualSettings
                exporter.SheetMaxRows = Telerik.WinControls.UI.Export.ExcelMaxRows._1048576
                exporter.RadGridViewToExport = gridView
                exporter.RunExport(sfd.FileName)

                System.Windows.Forms.MessageBox.Show("Excel Dosya Kaydedildi..", "Dosya Kaydedildi", Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Information, Windows.Forms.MessageBoxDefaultButton.Button1)
                result = True
            End If

        Catch ex As Exception

            System.Windows.Forms.MessageBox.Show("Excel Dosya Kaydedilemedi..", "Dosya Kaydedilemedi", Windows.Forms.MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error, Windows.Forms.MessageBoxDefaultButton.Button1)

        End Try

        Return result
    End Function

#End Region

#Region "Events"

    Private Sub grvGridView_DataBindingComplete(ByVal sender As System.Object, ByVal e As Telerik.WinControls.UI.GridViewBindingCompleteEventArgs) Handles grvGridView.DataBindingComplete

        Me.WriteGridViewRowCount()
        Me.ApplyOptions()

    End Sub
    Private Sub grvGridView_FilterChanged(ByVal sender As Object, ByVal e As Telerik.WinControls.UI.GridViewCollectionChangedEventArgs) Handles grvGridView.FilterChanged
        Me.WriteGridViewRowCount()
    End Sub
    Private Sub cmdExcelExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExcelExport.Click
        Me.ExportExcel()
    End Sub

#End Region

End Class

Public Class TypedColumnOptionObject

#Region "Local Variables"
    Private _ColumnName As String
    Private _ColumnType As System.Type
#End Region

#Region "Properties"
    Public Property ColumnName() As String
        Get
            Return Me._ColumnName
        End Get
        Set(ByVal value As String)
            Me._ColumnName = value
        End Set
    End Property
    Public Property ColumnType() As System.Type
        Get
            Return Me._ColumnType
        End Get
        Set(ByVal value As System.Type)
            Me._ColumnType = value
        End Set
    End Property
#End Region

#Region "Constructors"
    Public Sub New()

    End Sub
    Public Sub New(ByVal pColumnName As String, ByVal pColumnType As System.Type)
        Me.ColumnName = pColumnName
        Me.ColumnType = pColumnType
    End Sub
#End Region

#Region "Overrides Functions"
    Public Overrides Function ToString() As String
        Return Me.ColumnName
    End Function
#End Region

End Class
Public Class VisibleColumnOptionObject

#Region "Local Variables"
    Private _ColumnName As String
    Private _IsVisible As Boolean
#End Region

#Region "Properties"
    Public Property ColumnName() As String
        Get
            Return Me._ColumnName
        End Get
        Set(ByVal value As String)
            Me._ColumnName = value
        End Set
    End Property
    Public Property IsVisible() As Boolean
        Get
            Return Me._IsVisible
        End Get
        Set(ByVal value As Boolean)
            Me._IsVisible = value
        End Set
    End Property
#End Region

#Region "Constructors"
    Public Sub New()

    End Sub
    Public Sub New(ByVal pColumnName As String, ByVal pIsVisible As Boolean)
        Me.ColumnName = pColumnName
        Me.IsVisible = pIsVisible
    End Sub
#End Region

#Region "Overrides Functions"
    Public Overrides Function ToString() As String
        Return Me.ColumnName
    End Function
#End Region

End Class
Public Class HeaderColumnOptionObject

#Region "Local Variables"
    Private _ColumnName As String
    Private _ColumnNewName As String
#End Region

#Region "Properties"
    Public Property ColumnName() As String
        Get
            Return Me._ColumnName
        End Get
        Set(ByVal value As String)
            Me._ColumnName = value
        End Set
    End Property
    Public Property ColumnNewName() As String
        Get
            Return Me._ColumnNewName
        End Get
        Set(ByVal value As String)
            Me._ColumnNewName = value
        End Set
    End Property
#End Region

#Region "Constructors"
    Public Sub New()

    End Sub
    Public Sub New(ByVal pColumnName As String, ByVal pColumnNewName As String)
        Me.ColumnName = pColumnName
        Me.ColumnNewName = pColumnNewName
    End Sub
#End Region

#Region "Overrides Functions"
    Public Overrides Function ToString() As String
        Return Me.ColumnName
    End Function
#End Region

End Class
Public Class PinnedColumnOptionObject

#Region "Local Variables"
    Private _ColumnName As String
    Private _IsPinned As Boolean
#End Region

#Region "Properties"
    Public Property ColumnName() As String
        Get
            Return Me._ColumnName
        End Get
        Set(ByVal value As String)
            Me._ColumnName = value
        End Set
    End Property
    Public Property IsPinned() As String
        Get
            Return Me._IsPinned
        End Get
        Set(ByVal value As String)
            Me._IsPinned = value
        End Set
    End Property
#End Region

#Region "Constructors"
    Public Sub New()

    End Sub
    Public Sub New(ByVal pColumnName As String, ByVal pIsPinned As Boolean)
        Me.ColumnName = pColumnName
        Me.IsPinned = pIsPinned
    End Sub
#End Region

#Region "Overrides Functions"
    Public Overrides Function ToString() As String
        Return Me.ColumnName
    End Function
#End Region

End Class

Resim gördüğünüz buton için Excel ikonu.. Excel-16x16

Projenizi derledikten sonra aşağıdaki kodu örnek bir form’a kopyalarsanız bir test sürüşü yapabilirsiniz..

 

Public Class RadForm1

    Private Sub RadForm1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        '' Creating UserConntrol..
        '' =====================================================================
        Dim ctrl As New ctrlKocRadGridView
        ctrl.Dock = DockStyle.Fill
        Me.Controls.Add(ctrl)

        '' Grid Settings..
        '' =====================================================================
        ctrl.EnabledApplyAllOptions = True
        ctrl.EnabledApplyOptions = True

        ''ctrl.EnabledApplyOptions = True 'A?a??daki se?enekleri topluca a?ar.. (Tek tek a??labilir de)
        ''=============================================================================================================
        ''
        'ctrl.EnabledStandartColumnWidthOption = True       'STANDART WIDTH : 120
        'ctrl.CustomColumnWidth = 150                       'CUSTOM olarak da de?er verilebilir.
        'ctrl.EnabledMiddleAlignmentColumnOption = True     'ALIGNMENT : MIDDLE
        'ctrl.EnabledHeaderColumnOption = True
        'ctrl.EnabledPinnedColumnOption = True
        'ctrl.EnabledVisibilityColumnOption = True
        'ctrl.EnabledTypedColumnOption = True

        'Pinned kolonlar set ediliyor..
        'If (ctrl.PinnedColumnList Is Nothing) Then ctrl.PinnedColumnList = New List(Of PinnedColumnOptionObject)
        'ctrl.PinnedColumnList.AddRange(New PinnedColumnOptionObject() _
        '{ _
        '    New PinnedColumnOptionObject("fld_Ad", True), _
        '    New PinnedColumnOptionObject("fld_Soyad", True) _
        '})

        'Invisible kolonlar set ediliyor..
        If (ctrl.VisibilityColumnList Is Nothing) Then ctrl.VisibilityColumnList = New List(Of VisibleColumnOptionObject)
        ctrl.VisibilityColumnList.AddRange(New VisibleColumnOptionObject() _
        { _
            New VisibleColumnOptionObject("Column1", True), _
            New VisibleColumnOptionObject("Column2", False) _
        })

        'Kolon ba?l?klar? set ediliyor..
        If (ctrl.HeaderColumnList Is Nothing) Then ctrl.HeaderColumnList = New List(Of HeaderColumnOptionObject)
        ctrl.HeaderColumnList.AddRange(New HeaderColumnOptionObject() _
        { _
            New HeaderColumnOptionObject("Column1", "Kolon – 1"), _
            New HeaderColumnOptionObject("Column2", "Kolon – 2") _
        })

        'Kolon tipi ayar? set ediliyor..
        If (ctrl.TypedColumnList Is Nothing) Then ctrl.TypedColumnList = New List(Of TypedColumnOptionObject)

        ctrl.TypedColumnList.Add( _
            New TypedColumnOptionObject( _
            "Column3", GetType(Integer)))

        '' DataTable Binding..
        '' =====================================================================
        Dim dt As New DataTable

        dt.Columns.AddRange( _
            New DataColumn() { _
                New DataColumn("Column1"), _
                New DataColumn("Column2"), _
                New DataColumn("Column3") _
                })

        Dim rnd As New Random

        For i As Integer = 1 To 10

            Dim dr As DataRow = dt.NewRow

            For k As Integer = 0 To 2

                dr(k) = rnd.Next(0, 100)

            Next

            dt.Rows.Add(dr)

        Next

        ctrl.grvGridView.DataSource = dt

    End Sub
End Class