Export GridView with Images from database to Word Excel and PDF Formats

转自： http://www.aspsnippets.com/Articles/Export-GridView-with-Images-from-database-to-Word-Excel-and-PDF-Formats.aspx

In one of my previous articles I explained Export GridView To Word/Excel/PDF/CSV in ASP.Net

In this article I am explaining how to Export GridView to Microsoft Word, Microsoft Excel and Portable Document Format (PDF) which has images and pictures in it.  Here I am exporting a GridView which is displaying images from SQL Server Database using Image Handler. In order to display Images from SQL Server database in GridView refer my articleDisplay images from SQL Server Database in ASP.Net GridView control

Figure below describes how images are stored in the database table.

The concept is quite simple whenever the Word or Excel file is transmitted to the client and the client opens it a call is made to the server and the images are downloaded from the server. Hence instead if the relative URL we will have to use the complete URL. So that the images are downloaded from the server

Here I am using the same GridView which I used in my article  Display images from SQL Server Database in ASP.Net GridView control to display images from database in GridView. The only exporting I will be changing the URL of the image handler from relative to absolute URL. For example in the previous example the URL was

ImageCSharp.aspx?ImageID=1

Now it will be changed to

http://localhost/ImageCSharp.aspx?ImageID=1

 

Since Word, Excel or PDF files need complete URL so that they can download the file from the server

For doing this conversion I have the following function

C#

protected  string GetUrl(string page)

{

    string[] splits = Request.Url.AbsoluteUri.Split('/');

    if (splits.Length  >= 2)

    {

        string url = splits[0] + "//";

        for (int i = 2; i < splits.Length - 1; i++)

        {

            url += splits[i];

            url += "/";

        }

        return url +  page;

    }

    return page;

}

 

VB.Net

        

  Protected Function GetUrl(ByVal page As String) As String

        Dim splits As String() = Request.Url.AbsoluteUri.Split("/"c)

        If splits.Length >= 2 Then

            Dim url As String = splits(0) & "//"

            For i As Integer = 2 To splits.Length - 2

                url += splits(i)

                url += "/"

            Next

            Return url + page

        End If

        Return page

    End Function

 

And I am calling the function in the GridView in the following Manner. As you can see below I am passing the Page Name to the GetUrl function which in returns the complete URL for the handler.

C#

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

Font-Names = "Arial" >

<Columns>

    <asp:BoundField DataField = "ID" ItemStyle-Height = "150"

     HeaderText = "ID" />

    <asp:BoundField DataField = "Name" ItemStyle-Height = "150"

     HeaderText = "Image Name" />

    <asp:TemplateField ItemStyle-Height = "150" ItemStyle-Width = "170"

     HeaderText = "Image Preview">

     <ItemTemplate>

      <asp:Image ID="Image1"  runat="server"

      ImageUrl='<%# Eval("ID", GetUrl("ImageCSharp.aspx?ImageID={0}"))%>' />

     </ItemTemplate>

    </asp:TemplateField>

</Columns>

</asp:GridView>

 

     

VB.Net

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

Font-Names = "Arial" >

<Columns>

    <asp:BoundField DataField = "ID" ItemStyle-Height = "150"

     HeaderText = "ID" />

    <asp:BoundField DataField = "Name" ItemStyle-Height = "150"

     HeaderText = "Image Name" />

    <asp:TemplateField ItemStyle-Height = "150" ItemStyle-Width = "170"

     HeaderText = "Image Preview">

     <ItemTemplate>

      <asp:Image ID="Image1"  runat="server"

      ImageUrl='<%# Eval("ID", GetUrl("ImageVB.aspx?ImageID={0}"))%>' />

     </ItemTemplate>

    </asp:TemplateField>

</Columns>

</asp:GridView>

 

Figure Below displays the GridView with Images that are stored in SQL Server Database

When you do  View Source of the page in Browser you will notice that the relative URL have been converted to absolute one and the GetUrl function has done its job perfectly. Refer the figure below that displays the Source of the page with the complete URL of the handler

 

Now here is the code to Export the GridView in the Word, Excel and PDF Formats

      

 

Word

 

C#

private void Word_Export()

{

    Response.Clear();

    Response.Buffer = true;

    Response.AddHeader("content-disposition",

      "attachment;filename=GridViewExport.doc");

    Response.Charset = "";

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

    StringWriter sw = new StringWriter();

    HtmlTextWriter hw = new HtmlTextWriter(sw);

    GridView1.AllowPaging = false;

    GridView1.DataBind();

    GridView1.RenderControl(hw);

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

    Response.Flush();

    Response.End();

}

 

VB.Net

Private Sub Word_Export()

   Response.Clear()

   Response.Buffer = True

   Response.AddHeader("content-disposition", _

    "attachment;filename=GridViewExport.doc")

   Response.Charset = ""

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

   Dim sw As New StringWriter()

   Dim hw As New HtmlTextWriter(sw)

   GridView1.AllowPaging = False

   GridView1.DataBind()

   GridView1.RenderControl(hw)

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

   Response.Flush()

   Response.End()

End Sub

 

Figure below displays the Word document which contains exported GridView with images.

Excel

 

C#

private void Excel_Export()

{

    Response.Clear();

    Response.Buffer = true;

    Response.AddHeader("content-disposition",

     "attachment;filename=GridViewExport.xls");

    Response.Charset = "";

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

    StringWriter sw = new StringWriter();

    HtmlTextWriter hw = new HtmlTextWriter(sw);

    GridView1.AllowPaging = false;

    GridView1.DataBind();

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

    {

        GridViewRow row = GridView1.Rows[i];

        //Apply text style to each Row

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

    }

    GridView1.RenderControl(hw);

 

    //style to format numbers to string

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

    Response.Write(style);

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

    Response.Flush();

    Response.End();

}

    

                  

VB.Net

 

Private Sub Excel_Export()

   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()

   For i As Integer = 0 To GridView1.Rows.Count - 1

     Dim row As GridViewRow = GridView1.Rows(i)

    'Apply text style to each Row

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

   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

 

Figure below displays the Excel Workbook which contains exported GridView with images.

Portable Document Format (PDF)

For exporting the GridView to PDF format I am using the iTextSharp Library that will be available with the source code of this example. Also you can download it fromhere.

   

      

C#

private void PDF_Export()

{

    Response.ContentType = "application/pdf";

    Response.AddHeader("content-disposition",

        "attachment;filename=GridViewExport.pdf");

    Response.Cache.SetCacheability(HttpCacheability.NoCache);

    StringWriter sw = new StringWriter();

    HtmlTextWriter hw = new HtmlTextWriter(sw);

    GridView1.AllowPaging = false;

    GridView1.DataBind();

    GridView1.RenderControl(hw);

    StringReader sr = new StringReader(sw.ToString());

    Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);

    HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

    PdfWriter.GetInstance(pdfDoc, Response.OutputStream);

    pdfDoc.Open();

    htmlparser.Parse(sr);

    pdfDoc.Close();

    Response.Write(pdfDoc);

    Response.End(); 

}

 

VB.Net

Private Sub PDF_Export()

  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.A4, 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

 

Figure below displays the PDF Document which contains exported GridView with images.

Finally the method that one should not forget is the below otherwise the GridView export will throw the Error

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server.

 

C#.Net

public override void VerifyRenderingInServerForm(Control control)

{

    /* Verifies that the control is rendered */

}

VB.Net

Public Overloads Overrides Sub VerifyRenderingInServerForm(

ByVal control As Control)

        ' Verifies that the control is rendered

End Sub

 

This completes the article. You can download the source in C# and Vb.Net using the link below.

ExportGridViewWithImages.zip (2.93 mb)