Sebuah aplikasi yang berhubungan dengan database biasanya melampirkan sebuah tombol untuk export ke file excel yang nantinya berguna saat user ingin mendownload file tersebut untuk dikirim keatasan ataupun sebagai hardcopy
Berikut merupakan cara melakukan export Datagridview ke excel.
BAGIAN HTML
bagian html ini merupakan content form dimana telah tersedia master form. bila belum memiliki master form anda perlu membuat masterform terlebih dahulu.
1: <%@ Page Language="vb" AutoEventWireup="false" MasterPageFile="~/Site1.Master" CodeBehind="export_excel.aspx.vb" Inherits="testapp.export_excel" 2: title="Untitled Page" ValidateRequest="false" EnableEventValidation="false" %> 3: <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server"> 4: </asp:Content> 5: <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> 6: <h3>Export data Gridview ke Excel</h3> 7: <asp:Button ID="btn_export" runat="server" Text="export" /> 8: <asp:Image ID="Image1" runat="server" /> 9: <asp:GridView ID="grd_data" runat="server" AutoGenerateColumns="False" 10: AllowPaging="True" PageSize="20" CellPadding="4" 11: ForeColor="#333333" GridLines="None" OnPageIndexChanging="OnPageIndexChanging"> 12: <RowStyle HorizontalAlign="Center" BackColor="#F7F6F3" ForeColor="#333333" /> 13: <Columns> 14: <asp:BoundField DataField="nama_field" HeaderText="judul_field" /> 15: <asp:BoundField DataField="nama_field" HeaderText="judul_field" /> 16: <asp:BoundField DataField="nama_field" HeaderText="judul_field" /> 17: <asp:BoundField DataField="nama_field" HeaderText="judul_field" /> 18: <asp:BoundField DataField="nama_field" HeaderText="judul_field" /> 19: <asp:BoundField DataField="nama_field" HeaderText="judul_field" /> 20: <asp:BoundField DataField="nama_field" HeaderText="judul_field" /> 21: </Columns> 22: <PagerSettings LastPageText=" Last Page" FirstPageText=" First Page" /> 23: <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> 24: <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> 25: <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> 26: <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> 27: <EditRowStyle BackColor="#999999" /> 28: <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> 29: </asp:GridView><br /> 30: <asp:SqlDataSource ID="sdsdaftar" runat="server" ConnectionString="<%$ ConnectionStrings:BackboneConnectionString %>" 31: ></asp:SqlDataSource> 32: </asp:Content>
NAMESPACES
berikut name spaces yang perlu di import ke aplikasi vb anda
Imports System.IO
Imports System.Drawing
Imports System.Data.SqlClient
SCRIPT EXPORT EXCEL
1. Script mengisi data pada gridview
1: Private Sub BindGrid()
2: Dim strConnString As String = ConfigurationManager.ConnectionStrings("BackboneConnectionString").ConnectionString
3: Dim where As String = ""
4: Using con As New SqlConnection(strConnString)
5: Using cmd As New SqlCommand("select * from nama_table")
6: Using sda As New SqlDataAdapter()
7: cmd.Connection = con
8: sda.SelectCommand = cmd
9: Using dt As New DataTable()
10: sda.Fill(dt)
11: grd_data.DataSource = dt
12: grd_data.DataBind()
13: End Using
14: End Using
15: End Using
16: End Using
17: End Sub
2. Meload data saat page load
1: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
2: BindGrid()
3: End Sub
3. Script mengubah page index pada gridview dan overload
1: Protected Sub OnPageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
2: grd_data.PageIndex = e.NewPageIndex
3: Me.BindGrid()
4: End Sub
5: Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
6: End Sub
4. Script mengexport ke exel saat user menekan tombol btn_export
1: Dim namafile As String
2: namafile = "exportke_excel" + DateTime.Now.ToString("dd/MM/yyyy") + ".xls"
3: If grd_data.Rows.Count > 0 Then
4: Response.Clear()
5: Response.Buffer = True
6: Response.AddHeader("content-disposition", "attachment;filename=" + namafile)
7: Response.Charset = ""
8: Response.ContentType = "application/vnd.ms-excel"
9: Using sw As New StringWriter()
10: Dim hw As New HtmlTextWriter(sw)
11: 'To Export all pages
12: grd_data.AllowPaging = False
13: Me.BindGrid()
14: grd_data.HeaderRow.BackColor = Color.White
15: For Each cell As TableCell In grd_data.HeaderRow.Cells
16: cell.BackColor = grd_data.HeaderStyle.BackColor
17: Next
18: For Each row As GridViewRow In grd_data.Rows
19: row.BackColor = Color.White
20: For Each cell As TableCell In row.Cells
21: If row.RowIndex Mod 2 = 0 Then
22: cell.BackColor = grd_data.AlternatingRowStyle.BackColor
23: Else
24: cell.BackColor = grd_data.RowStyle.BackColor
25: End If
26: cell.CssClass = "textmode"
27: Next
28: Next
29: grd_data.RenderControl(hw)
30: 'style to format numbers to string
31: Dim style As String = "<style> .textmode { } </style>"
32: Response.Write(style)
33: Response.Output.Write(sw.ToString())
34: Response.Flush()
35: Response.[End]()
36: End Using
37: End If
38: End Sub
5. Complete script .vb
1: Imports System.IO
2: Imports System.Drawing
3: Imports System.Data.SqlClient
4: Partial Public Class export_excel
5: Inherits System.Web.UI.Page
6: Protected Sub OnPageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
7: grd_data.PageIndex = e.NewPageIndex
8: Me.BindGrid()
9: End Sub
10: Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
11: End Sub
12: Private Sub BindGrid()
13: Dim strConnString As String = ConfigurationManager.ConnectionStrings("BackboneConnectionString").ConnectionString
14: Dim where As String = ""
15: Using con As New SqlConnection(strConnString)
16: Using cmd As New SqlCommand("select * from ihelp_tiket")
17: Using sda As New SqlDataAdapter()
18: cmd.Connection = con
19: sda.SelectCommand = cmd
20: Using dt As New DataTable()
21: sda.Fill(dt)
22: grd_data.DataSource = dt
23: grd_data.DataBind()
24: End Using
25: End Using
26: End Using
27: End Using
28: End Sub
29: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
30: BindGrid()
31: End Sub
32: Protected Sub btn_export_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_export.Click
33: Dim namafile As String
34: namafile = "exportke_excel" + DateTime.Now.ToString("dd/MM/yyyy") + ".xls"
35: If grd_data.Rows.Count > 0 Then
36: Response.Clear()
37: Response.Buffer = True
38: Response.AddHeader("content-disposition", "attachment;filename=" + namafile)
39: Response.Charset = ""
40: Response.ContentType = "application/vnd.ms-excel"
41: Using sw As New StringWriter()
42: Dim hw As New HtmlTextWriter(sw)
43: 'To Export all pages
44: grd_data.AllowPaging = False
45: Me.BindGrid()
46: grd_data.HeaderRow.BackColor = Color.White
47: For Each cell As TableCell In grd_data.HeaderRow.Cells
48: cell.BackColor = grd_data.HeaderStyle.BackColor
49: Next
50: For Each row As GridViewRow In grd_data.Rows
51: row.BackColor = Color.White
52: For Each cell As TableCell In row.Cells
53: If row.RowIndex Mod 2 = 0 Then
54: cell.BackColor = grd_data.AlternatingRowStyle.BackColor
55: Else
56: cell.BackColor = grd_data.RowStyle.BackColor
57: End If
58: cell.CssClass = "textmode"
59: Next
60: Next
61: grd_data.RenderControl(hw)
62: 'style to format numbers to string
63: Dim style As String = "<style> .textmode { } </style>"
64: Response.Write(style)
65: Response.Output.Write(sw.ToString())
66: Response.Flush()
67: Response.[End]()
68: End Using
69: End If
70: End Sub
71: End Class
