GridView da Seçili Sütunları Excel e Aktarmak

Merhaba arkadaşlar bu makalemizde Gridview nesnesinde seçili sütunları excel e aktaracağız. Gridview nesnesinde otomatik sütun eklemeyi AutoGenerateColumns = "False"  yapın. Daha sonra GridView sütun başlığına CheckBox ekleyin. Örneğin bu örnek için CustomerID sütun başlığına TemplateField alanında ve Header Template kısmına;

<HeaderTemplate>

    <asp:CheckBox ID="chksutun0" runat="server" Checked = "true" />

    <asp:Label ID="lblsutun0" runat="server" Text = "CustomerID"/>

</HeaderTemplate>

Şeklinde ekleme yapıyoruz.

Ekran Çıktısı

Şekil 1

Şekil 2

Default.aspx.cs

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

using System.Data.OleDb;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

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

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (IsPostBack)

            CheckBoxDurum();

        //Veritabanına bağlanıyoruz.

        OleDbConnection baglan = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source= " + Server.MapPath("~/App_Data/northwind.mdb") + ";");

        baglan.Open();

        OleDbCommand komut = new OleDbCommand();

        komut.Connection = baglan;

        komut.CommandType = CommandType.Text;

        komut.CommandText = "Select CustomerID, CompanyName, ContactName,City, Country From Customers";

        OleDbDataAdapter da = new OleDbDataAdapter();

        da.SelectCommand = komut;

        DataSet ds = new DataSet();

        da.Fill(ds);

        GridView1.DataSource = ds.Tables[0];

        GridView1.DataBind();

    }

    private void CheckBoxDurum()

    {

        //GridView nesnesindeki sütundaki ChecBox sütunlarını

        //buluyoruz.

        CheckBox chksutun0 = (CheckBox)GridView1.HeaderRow.Cells[0]

                                .FindControl("chksutun0");

        CheckBox chksutun1 = (CheckBox)GridView1.HeaderRow.Cells[0]

                                .FindControl("chksutun1");

        CheckBox chksutun2 = (CheckBox)GridView1.HeaderRow.Cells[0]

                                .FindControl("chksutun2");

        CheckBox chksutun3 = (CheckBox)GridView1.HeaderRow.Cells[0]

                                .FindControl("chksutun3");

        CheckBox chksutun4 = (CheckBox)GridView1.HeaderRow.Cells[0]

                                .FindControl("chksutun4");

        ArrayList liste;

        if (ViewState["States"] == null)

        {

            liste = new ArrayList();

        }

        else

        {

            liste = (ArrayList)ViewState["States"];

        }

        liste.Add(chksutun0.Checked);

        liste.Add(chksutun1.Checked);

        liste.Add(chksutun2.Checked);

        liste.Add(chksutun3.Checked);

        liste.Add(chksutun4.Checked);

        ViewState["States"] = liste;

    }

    protected void OnPaging(object sender, GridViewPageEventArgs e)

    {

        GridView1.PageIndex = e.NewPageIndex;

        GridView1.DataBind();

        CheckBoxDurum();

    }

    protected void btnExcel_Click(object sender, EventArgs e)

    {

        Response.Clear();

        Response.Buffer = true;

        Response.AddHeader("content-disposition",

         "attachment;filename=GridViewAktarilan.xls");

        Response.Charset = "";

        Response.ContentType = "application/vnd.ms-excel";

        StringWriter sw = new StringWriter();

        HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;

        GridView1.DataBind();

        //Excel e aktarılacak sütunların başlık renklerini belirliyoruz.

        GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

        GridView1.HeaderRow.Cells[0].Style.Add("background-color", "navy");

        GridView1.HeaderRow.Cells[1].Style.Add("background-color", "navy");

        GridView1.HeaderRow.Cells[2].Style.Add("background-color", "navy");

        GridView1.HeaderRow.Cells[3].Style.Add("background-color", "navy");

        GridView1.HeaderRow.Cells[4].Style.Add("background-color", "navy");

        ArrayList liste = (ArrayList)ViewState["States"];

        GridView1.HeaderRow.Cells[0].Visible = Convert.ToBoolean(liste[0]);

        GridView1.HeaderRow.Cells[1].Visible = Convert.ToBoolean(liste[1]);

        GridView1.HeaderRow.Cells[2].Visible = Convert.ToBoolean(liste[2]);

        GridView1.HeaderRow.Cells[3].Visible = Convert.ToBoolean(liste[3]);

        GridView1.HeaderRow.Cells[4].Visible = Convert.ToBoolean(liste[4]);

        GridView1.HeaderRow.Cells[0].FindControl("chksutun0").Visible = false;

        GridView1.HeaderRow.Cells[1].FindControl("chksutun1").Visible = false;

        GridView1.HeaderRow.Cells[2].FindControl("chksutun2").Visible = false;

        GridView1.HeaderRow.Cells[3].FindControl("chksutun3").Visible = false;

        GridView1.HeaderRow.Cells[4].FindControl("chksutun4").Visible = false;

        for (int i = 0; i < GridView1.Rows.Count; i++)

        {

            //excel e aktaracağımız satırların arka plan renklerini belirliyoruz.

            GridViewRow row = GridView1.Rows[i];

            row.Cells[0].Visible = Convert.ToBoolean(liste[0]);

            row.Cells[1].Visible = Convert.ToBoolean(liste[1]);

            row.Cells[2].Visible = Convert.ToBoolean(liste[2]);

            row.Cells[3].Visible = Convert.ToBoolean(liste[3]);

            row.Cells[4].Visible = Convert.ToBoolean(liste[4]);

            row.BackColor = System.Drawing.Color.White;

            row.Attributes.Add("class", "textmode");

            if (i % 2 != 0)

            {

                row.Cells[0].Style.Add("background-color", "#EFF5F1");

                row.Cells[1].Style.Add("background-color", "#EFF5F1");

                row.Cells[2].Style.Add("background-color", "#EFF5F1");

                row.Cells[3].Style.Add("background-color", "#EFF5F1");

                row.Cells[4].Style.Add("background-color", "#EFF5F1");

            }

        }

        //Gridview nesnesindeki satırları excele render ediyoruz.

        GridView1.RenderControl(hw);

        string style = @"<style> .textmode { mso-number-format:\@; } </style>";

        Response.Write(style);

        Response.Output.Write(sw.ToString());

        Response.End();

    }

    public override void VerifyRenderingInServerForm(Control control)

    {

        return;

    }

}

Default.aspx

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:Panel ID="Panel1" runat="server">

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

AutoGenerateColumns = "False" Font-Names = "Arial"

Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" 

HeaderStyle-BackColor = "green" AllowPaging ="True"

OnPageIndexChanging = "OnPaging" CellPadding="4" ForeColor="#333333" GridLines="None"  >

                 <AlternatingRowStyle BackColor="White" ForeColor="#284775" />

<Columns>

<asp:TemplateField>

<HeaderTemplate>

    <asp:CheckBox ID="chksutun0" runat="server" Checked = "true" />

    <asp:Label ID="lblsutun0" runat="server" Text = "CustomerID"/>

</HeaderTemplate>

<ItemTemplate>

    <asp:Label ID="lblCustomerID" runat="server"

    Text='<%# Eval("CustomerID")%>' />

</ItemTemplate>

</asp:TemplateField>

    <asp:TemplateField>

<HeaderTemplate>

    <asp:CheckBox ID="chksutun1" runat="server" Checked = "true" />

    <asp:Label ID="lblsutun1" runat="server" Text = "CompanyName"/>

</HeaderTemplate>

<ItemTemplate>

    <asp:Label ID="lblCompanyName" runat="server"

    Text='<%# Eval("CompanyName")%>' />

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<HeaderTemplate>

    <asp:CheckBox ID="chksutun2" runat="server" Checked = "true"/>

    <asp:Label ID="lblsutun2" runat="server" Text = "ContactName" />

</HeaderTemplate>

<ItemTemplate>

    <asp:Label ID="lblContactName" runat="server"

    Text='<%# Eval("ContactName")%>'/>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField>

<HeaderTemplate>

    <asp:CheckBox ID="chksutun3" runat="server" Checked = "true" />

    <asp:Label ID="lblsutun3" runat="server" Text = "City"></asp:Label>

</HeaderTemplate>

<ItemTemplate>

    <asp:Label ID="lblCity" runat="server"

    Text='<%# Eval("City")%>' />

</ItemTemplate>

</asp:TemplateField>

    <asp:TemplateField>

<HeaderTemplate>

    <asp:CheckBox ID="chksutun4" runat="server" Checked = "true" />

    <asp:Label ID="lblsutun4" runat="server" Text = "Country"></asp:Label>

</HeaderTemplate>

<ItemTemplate>

    <asp:Label ID="lblCountry" runat="server"

    Text='<%# Eval("Country")%>' />

</ItemTemplate>

</asp:TemplateField>

</Columns>

                 <EditRowStyle BackColor="#999999" />

                 <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

                 <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

                 <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />

                 <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />

                 <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />

                 <SortedAscendingCellStyle BackColor="#E9E7E2" />

                 <SortedAscendingHeaderStyle BackColor="#506C8C" />

                 <SortedDescendingCellStyle BackColor="#FFFDF8" />

                 <SortedDescendingHeaderStyle BackColor="#6F8DAE" />

</asp:GridView>

        </asp:Panel>

        <br />

        <asp:Button ID="btnExcel" runat="server" OnClick="btnExcel_Click" Text="Seçili Sütunları Excel e Aktar" />

    </div>

    </form>

</body>

</html>

Bir makalenin daha sonuna geldik. Bir sonraki makalede görüşmek üzere. Bahadır ŞAHİN