J Talati J Talati - 6 months ago 80
Vb.net Question

Exporting Gridview to Excel, and PDF not working.

My Mockup of the Page with the Grid View:

<div style="overflow:scroll; height: 447px;">
<asp:GridView ID="GridView1" AutoGenerateColumns="true" GridLines="Both" RowStyle-BorderWidth="0" runat="server" CellPadding="4" ForeColor="#333333">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<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>
</div>


My VB Code for export to Excel button option using itextSharp dll:

Protected Sub ExportAsExcel_Click(sender As Object, e As EventArgs) Handles ExportAsExcel.Click
'Exporting the results to an excel spreadsheet
Response.Clear()
Response.Buffer = True

Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"

Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)

GridView1.AllowPaging = False
GridView1.DataBind()

'Change the Header Row back to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF")

'Apply style to Individual Cells
For i As Integer = 0 To GridView1.HeaderRow.Cells.Count - 1
GridView1.HeaderRow.Cells(i).Style.Add("background-color", "green")
Next

For i As Integer = 0 To GridView1.Rows.Count - 1
Dim row As GridViewRow = GridView1.Rows(i)

'Change Color back to white
row.BackColor = System.Drawing.Color.White

'Apply text style to each Row
row.Attributes.Add("class", "textmode")

'Apply style to Individual Cells of Alternating Row
If i Mod 2 <> 0 Then
row.Cells(0).Style.Add("background-color", "#C2D69B")
row.Cells(1).Style.Add("background-color", "#C2D69B")
row.Cells(2).Style.Add("background-color", "#C2D69B")
row.Cells(3).Style.Add("background-color", "#C2D69B")
End If
Next
GridView1.RenderControl(hw)

'style to format numbers to string
Dim style As String = "<style>.textmode{mso-number-format:\@;}</style>"
Response.Write(style)
Response.Output.Write(sw.ToString())
Response.Flush()
Response.End()
End Sub


My code for exporting the page as a pdf using itextsharp dll is below:

Protected Sub ExportAsPDF_Click(sender As Object, e As EventArgs) Handles ExportAsPDF.Click

Response.ContentType = "application/pdf"
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.pdf")
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
GridView1.AllowPaging = False
GridView1.DataBind()
GridView1.RenderControl(hw)
Dim sr As New StringReader(sw.ToString())
Dim pdfDoc As New Document(PageSize.A0, 10.0F, 10.0F, 10.0F, 0.0F)
Dim htmlparser As New HTMLWorker(pdfDoc)
PdfWriter.GetInstance(pdfDoc, Response.OutputStream)
pdfDoc.Open()
htmlparser.Parse(sr)
pdfDoc.Close()
Response.Write(pdfDoc)
Response.End()
End Sub


Not sure what is needed to do here i have the following imports as well.

Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html
Imports iTextSharp.text.html.simpleparser
Imports System.IO


However, the iTextSharp.text.html imported is not being used i dont know why. just a few days ago this code worked perfectly fine. and not it doesn't. i got this code from the following url::
Click here!

Answer

you could try following code. found from www.aspsnippets.com/Articles/Export-GridView-to-Excel-in-ASPNet-with-Formatting-using-C-and-VBNet.aspx

Protected Sub ExportToExcel(sender As Object, e As EventArgs)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Using sw As New StringWriter()
    Dim hw As New HtmlTextWriter(sw)

    'To Export all pages
    GridView1.AllowPaging = False
    Me.BindGrid()

    GridView1.HeaderRow.BackColor = Color.White
    For Each cell As TableCell In GridView1.HeaderRow.Cells
        cell.BackColor = GridView1.HeaderStyle.BackColor
    Next
    For Each row As GridViewRow In GridView1.Rows
        row.BackColor = Color.White
        For Each cell As TableCell In row.Cells
            If row.RowIndex Mod 2 = 0 Then
                cell.BackColor = GridView1.AlternatingRowStyle.BackColor
            Else
                cell.BackColor = GridView1.RowStyle.BackColor
            End If
            cell.CssClass = "textmode"
        Next
    Next

    GridView1.RenderControl(hw)
    'style to format numbers to string
    Dim style As String = "<style> .textmode { } </style>"
    Response.Write(style)
    Response.Output.Write(sw.ToString())
    Response.Flush()
    Response.[End]()
End Using
End Sub

Public Overrides Sub VerifyRenderingInServerForm(control As Control)
' Verifies that the control is rendered
End Sub
Comments