Convert Gridview into Excel Using VB.NET

Dear Searcher / User,


Herewith giving you example of converting Gridview control with data into EXCEL.


DataSet / DataTable
[
 Dim DeductionDownload = (From a In db.dbEmployeeDeductions _Join b In db.dbCompanyEmployees On a.CompanyEmployeeID Equals b.CompanyEmployeeID _
Join c In db.dbCompanies On a.CompanyID Equals c.CompanyID _
Join d In db.dbDeductionHeads On a.DeductionHeadID Equals d.DeductionHeadID _
Where a.Status.ToLower = "active".ToLower AndAlso a.Deleted = False _
Select a, b.EmployeeCode, a.ModifiedDateTime, RowType = "Deduction Side", CompanyCode = c.Code, DeductionCode = d.Code, atm = a.Amount)
]

Above is my LINQ to SQL query, you can use DATATABLE, DATASET or LIST object.


EXCEL CONVERSION METHOD CALLING

PrintReports.ExportToExcel(gvDeduction, ".", "Client : " + ddlCompanyName.SelectedItem.Text.ToString, DeductionFileName)



PRINTREPORT METHOD WHICH CONVERT INTO EXCEL


Imports Microsoft.VisualBasic
Imports System.IO

Public Class PrintReports
    Public Shared Sub ExportToExcel(ByVal ctl As Control, ByVal strPeriod As String, ByVal strHeader As String, ByVal strFileName As String)

        HttpContext.Current.Response.Clear()

        HttpContext.Current.Response.Buffer = True

        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + strFileName)

        HttpContext.Current.Response.Charset = ""

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

        Dim stringWrite As New StringWriter

        Dim htmlWrite As New HtmlTextWriter(stringWrite)

        ctl.RenderControl(htmlWrite)

        HttpContext.Current.Response.Write("<table>")

        HttpContext.Current.Response.Write("<tr><td colspan=""3"" >Veritech</td></tr>")

        HttpContext.Current.Response.Write("<tr><td colspan=""3"">" & strHeader & "</td></tr>")

        HttpContext.Current.Response.Write("<tr><td colspan=""3"">" & strPeriod & "</td></tr>")

        HttpContext.Current.Response.Write("</table>")

        HttpContext.Current.Response.Write("<table>")

        Dim str As String = ""

        'If Regex.IsMatch(stringWrite.ToString(), "</a>|<a(.|\n|\w|\s|\d)+?>") Then

        str = Regex.Replace(stringWrite.ToString(), "</a>|<a(.|\n|\w|\s|\d)+?>", String.Empty)

        'End If

        HttpContext.Current.Response.Write(str)

        HttpContext.Current.Response.Write("</table>")

        HttpContext.Current.Response.End()

    End Sub
End Class

VerifyRenderingInServerForm Method Which Required On Your ASPX page where you are calling  PRINTREPORT

 Public Overrides Sub VerifyRenderingInServerForm(control As Control)
        ' Confirms that an HtmlForm control is rendered for the specified ASP.NET
        '     server control at run time.
        Return
    End Sub


Try it on your project, Any problem you can place comment I will answer.
Thank You,
Manoj Kalla
Email ID: mr.manojbkalla@gmail.com
Website: manojbkalla.weebly.com