Excel export of gridview data with styling header and adding caption and image in excel.

Following code will be used for giving gridview excel export with some customization in the excel file like changing header colour, header style showing logo image in excel file.

The most important thing while doing or playing with excel file is by considering excel as the combination of rows and columns matrix.

i.e in Html <tr></tr> indicates row in excel and <td></td> indicates cell in excel when you convert. 

So lets start excel export of gridview (datatable)

Here is code for excel export if wanna go in depth end of the code there is explanation provided.

CODE BLOCK

Protected Sub btnExport_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.ServerClick

        Response.Charset = ""

        Response.Cache.SetCacheability(HttpCacheability.NoCache)

        Dim dtExport As DataTable

        Dim stringWrite As New System.IO.StringWriter()

        Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)

        If ddlExport.SelectedIndex = "0" Then

            Response.AddHeader("content-disposition", "attachment;filename=" & reportheader.ToUpper() & ".xls")

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

        ElseIf ddlExport.SelectedIndex = "1" Then

            Response.AddHeader("content-disposition", "attachment;filename=" & reportheader.ToUpper() & ".pdf")

            Response.ContentType = "application/pdf"

        End If

        If Not ddlExport.SelectedIndex = "-1" Then

            If Not ViewState("dtReport") Is Nothing Then

                dtExport = CType(ViewState("dtReport"), DataTable)

                dg.HeaderStyle.Font.Bold = True

                dg.HeaderStyle.Font.Name.ToUpper()

                dg.HeaderStyle.BorderStyle = BorderStyle.Groove

                dg.HeaderStyle.BorderColor = Drawing.Color.Black

                dg.HeaderStyle.ForeColor = Drawing.Color.AliceBlue

                dg.HeaderStyle.BackColor = Drawing.Color.Navy

                dg.DataSource = dtExport

                dg.DataBind()

                dg.ShowFooter = True

                dg.FooterStyle.BackColor = Drawing.Color.Black

                If fromdate.Length > 9 Then

                    fromdate = Convert.ToDateTime(fromdate).ToString("dd/MM/yyyy hh:mm:ss")

                Else

                    fromdate = Convert.ToDateTime(fromdate).ToString("dd/MM/yyyy")

                End If

                If todate.Length > 9 Then

                    todate = Convert.ToDateTime(todate).ToString("dd/MM/yyyy hh:mm:ss")

                Else

                    todate = Convert.ToDateTime(todate).ToString("dd/MM/yyyy")

                End If

                Dim strLogo As String = "http://localhost:14647/image/logo.gif"

                Dim imagepath As String = "<img src='" & strLogo & "' width='100' height='80'/>"

                dg.Caption = "<div style='color:blue'><tr style='color:blue;font-size:medium;'><td colspan=" & dtExport.Columns.Count & " align='center' style='height:100px' >" & reportheader & "</br>" & imagepath & " </td></tr> <tr><td  >VehicleNumber:</td><td colspan=" & dtExport.Columns.Count - 1 & ">" & "VH10" & "</td></tr><tr><td >Duration:From </td><td align='left'>" & fromdate & "</td><td>To</td><td colspan=" & dtExport.Columns.Count - 2 & "align='left' >" & todate & "</td></tr><tr><td >Group:</td><td colspan=" & dtExport.Columns.Count - 1 & " align='left'>" & "test grp" & "</td></tr><tr><td >Interval:</td><td  align='left'>" & interval & "</td><td colspan=" & dtExport.Columns.Count - 3 & ">Report Export Time:" & DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss") & " </td></tr></div> "

                dg.CaptionAlign = TableCaptionAlign.Left

                dg.Caption.ToUpper()

                dg.RenderControl(htmlWrite)

            End If

            Response.Flush()

            Response.Write(stringWrite.ToString())

            Response.[End]()

        End If

    End Sub

Explaination

lets go in depth of each line

to give excel output of the gridview you need to define gridview object

and need to use other gridview to display on page

Dim dg as gridview
First lets clear all response by this line

1.        Response.Charset = ""

2.       Response.Cache.SetCacheability(HttpCacheability.NoCache)

This line tells browser not to catch any information or data about this page in browser history.

3.      Dim dtExport As DataTable

4.      Dim stringWrite As New System.IO.StringWriter()

In above line we are declaring datatable and creating object for stringwriter used to writing information to a string.

5.      Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)

Writes a sequential series of HTML-specific characters and text on a Web Forms page

6.

   Response.AddHeader("content-disposition", "attachment;filename=" & reportheader.ToUpper() & ".xls")

This line of code is used for disposing the attached content means poping the save file dialog.

if you look at second parameter there I am proving filename from variable name reportheader and giving type as “.xls”

 ----"attachment;filename=" & reportheader.ToUpper() & ".xls"

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

Here we need to show the type of content in response. There are different values for contentType.

Common content types are

<%response.ContentType="text/HTML"%>
<%response.ContentType="image/GIF"%>
<%response.ContentType="image/JPEG"%>
<%response.ContentType="text/plain"%>

Following code is used to make the changes in style of excel header

8.

 dg.HeaderStyle.Font.Bold = True

 dg.HeaderStyle.Font.Name.ToUpper()

 dg.HeaderStyle.BorderStyle = BorderStyle.Groove

 dg.HeaderStyle.BorderColor = Drawing.Color.Black

 dg.HeaderStyle.ForeColor = Drawing.Color.AliceBlue

 dg.HeaderStyle.BackColor = Drawing.Color.Navy

 next bind the datatable to gridview control.

To add details int the caption to the excel file about report like total records,fromdate and todate use caption property of gridview and design using html tags

9.Desinging Caption

dg.Caption = "<div style='color:blue'><tr style='color:blue;font-size:medium;'><td colspan=" & dtExport.Columns.Count & " align='center' style='height:100px' >" & reportheader & "</br>" & imagepath & " </td></tr> <tr><td  >VehicleNumber:</td><td colspan=" & dtExport.Columns.Count - 1 & ">" & "VH10" & "</td></tr><tr><td >Duration:From </td><td align='left'>" & fromdate & "</td><td>To</td><td colspan=" & dtExport.Columns.Count - 2 & "align='left' >" & todate & "</td></tr><tr><td >Group:</td><td colspan=" & dtExport.Columns.Count - 1 & " align='left'>" & "test grp" & "</td></tr><tr><td >Interval:</td><td  align='left'>" & interval & "</td><td colspan=" & dtExport.Columns.Count - 3 & ">Report Export Time:" & DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss") & " </td></tr></div> "

                dg.CaptionAlign = TableCaptionAlign.Left

                dg.Caption.ToUpper()

10.Rendering control

dg.RenderControl(htmlWrite)

This line will render the Html string to Htmlfile

11.and Finally flushing 

            Response.Flush()-will send buffered output immediately .

            Response.Write(stringWrite.ToString())

            Response.[End]()