Export GridView To WordExcelPDFCSV in ASP.Net
来源:互联网 发布:云南省精准扶贫大数据 编辑:程序博客网 时间:2024/05/29 14:14
http://www.aspsnippets.com/Articles/Export-GridView-To-WordExcelPDFCSV-in-ASP.Net.aspx
In this article, I will explain how to export GridView to Word, Excel, PDF and CSV formats.
Exporting to Word, Excel and CSV can be easily achieved using ASP.Net without any third party tools, but for exporting GridView to PDF I am usingiTextSharp which is a free library for exporting html to PDF.
To start with I have a GridView in which I am showing Customers records from the NorthWind Database.
The HTML markup of the GridView is as shown below
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns = "false" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" AllowPaging ="true"
OnPageIndexChanging = "OnPaging" >
<Columns>
<asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"
HeaderText = "CustomerID" />
<asp:BoundField ItemStyle-Width = "150px" DataField = "City"
HeaderText = "City"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "Country"
HeaderText = "Country"/>
<asp:BoundField ItemStyle-Width = "150px" DataField = "PostalCode"
HeaderText = "PostalCode"/>
</Columns>
</asp:GridView>
In the figure below the GridView is shown with four buttons
1. Export To Word
2. Export To Excel
3. Export To PDF
4. Export To CSV
Export to Microsoft Word Format
C#
protected void btnExportWord_Click(object sender, EventArgs e)
{
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
Protected Sub btnExportWord_Click(ByVal sender As Object,
ByVal e As EventArgs)
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
The above function renders the GridView contents as Microsoft Word format. You will notice I have disabled paging before exporting, so that all the pages are exported.
The Output Exported File
Export to Microsoft Excel Format
For exporting the document to Excel if you do it directly as done in case of word the row background color is applied throughout to all the columns in the Excel Sheet hence in order to avoid it. I have done a workaround below.
First I am changing the background color of each row back to white.
Then I am applying the background color to each individual cell rather than the whole row. Thus when you export now you will notice that the formatting is applied only to the GridView cells and not all
Also I am applying textmode style class to all cells and then adding the styleCSS class to the GridView before rendering it, this ensures that all the contents of GridView are rendered as text.
protected void btnExportExcel_Click(object sender, EventArgs e)
{
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();
//Change the Header Row back to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Apply style to Individual Cells
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");
for (int i = 0; i < GridView1.Rows.Count;i++ )
{
GridViewRow row = 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 % 2 != 0)
{
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");
}
}
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
Protected Sub btnExportExcel_Click(ByVal sender As Object,
ByVal e As EventArgs)
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
GridView1.HeaderRow.Cells(0).Style.Add("background-color", "green")
GridView1.HeaderRow.Cells(1).Style.Add("background-color", "green")
GridView1.HeaderRow.Cells(2).Style.Add("background-color", "green")
GridView1.HeaderRow.Cells(3).Style.Add("background-color", "green")
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
The Output Exported File
Export to Portable Document Format
For exporting the GridView to PDF I am using the iTextSharp Library. You will need to Add Reference for the iTextSharp Library in your Website.
Then import the following Namespaces
C#
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
VB.Net
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html
Imports iTextSharp.text.html.simpleparser
By default the iTextSharp Library does not support background color of table cells or table rows
Hence when you render it as PDF your GridView is rendered without any formatting.
Recently I read an article on hamang.net where the author has provided the snippet to modify the iTextSharp so that it exports the HTML with background color.
For this tutorial, I have already modified the iTextSharp Library DLL so that the GridView is rendered with all the background color used. You can refer the code for exporting GridView to PDF below
C#
protected void btnExportPDF_Click(object sender, EventArgs e)
{
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
Protected Sub btnExportPDF_Click(ByVal sender As Object,
ByVal e As EventArgs)
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
The Output Exported File
Export to Text/CSV
Finally comes exporting GridView to CSV or Text File delimited by a separator like comma.
To export the GridView as CSV, I am running a two for loops. While looping through the GridView columns and appending comma after each column and while looping through rows appending new line character. Refer the code below.
C#
protected void btnExportCSV_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.csv");
Response.Charset = "";
Response.ContentType = "application/text";
GridView1.AllowPaging = false;
GridView1.DataBind();
StringBuilder sb = new StringBuilder();
for (int k = 0; k < GridView1.Columns.Count; k++)
{
//add separator
sb.Append(GridView1.Columns[k].HeaderText + ',');
}
//append new line
sb.Append("\r\n");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int k = 0; k < GridView1.Columns.Count; k++)
{
//add separator
sb.Append(GridView1.Rows[i].Cells[k].Text + ',');
}
//append new line
sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
}
VB.Net
Protected Sub btnExportCSV_Click(ByVal sender As Object,
ByVal e As EventArgs)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.csv")
Response.Charset = ""
Response.ContentType = "application/text"
GridView1.AllowPaging = False
GridView1.DataBind()
Dim sb As New StringBuilder()
For k As Integer = 0 To GridView1.Columns.Count - 1
'add separator
sb.Append(GridView1.Columns(k).HeaderText + ","c)
Next
'append new line
sb.Append(vbCr & vbLf)
For i As Integer = 0 To GridView1.Rows.Count - 1
For k As Integer = 0 To GridView1.Columns.Count - 1
'add separator
sb.Append(GridView1.Rows(i).Cells(k).Text + ","c)
Next
'append new line
sb.Append(vbCr & vbLf)
Next
Response.Output.Write(sb.ToString())
Response.Flush()
Response.End()
End Sub
The Output Exported File
When you run the application first time and click export you might receive the following error
To avoid the error you will need to add this event which ensures that the GridView is Rendered before exporting.
C#
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 view the live demohere
The source code is available in C# and VB.Net here
GridViewExport.zip (1.15 mb)
- Export GridView To WordExcelPDFCSV in ASP.Net
- How to Create a Second Level GridView in ASP.NET
- Export ASP.NET DataGrid To Excel
- csharp: Export DataTable to Excel using OpenXml 2.5 in asp.net
- Export GridView to Excel
- Asp.net DataTable To GridView
- Export ASP.NET Gridview data into MS-Excel sheet
- 5 Solutions to Export Data to Excel for ASP.NET
- Collapsible GridView in ASP.Net 2.0
- How to get Hidden Column Value in GridView(ASP.net GridView如何获取隐藏列的值)
- Select Single Radio Button in Gridview in Asp.Net
- ASP:Export to Excel (1)
- ASP: Export to Excel (2)
- Export GridView to Excel within an UpdatePanel
- How to Print in ASP.NET 2.0
- Check/Uncheck All Checkboxes in Asp.Net Gridview
- export qt object to javascript in webkit
- How to convert XML to JSON in ASP.NET C#
- 项目登录、注册页面的收获
- A*启发式算法模拟实现
- List、Map、Set基本操作
- computer vision resources
- 复制,粘贴 -- 调用浏览器 -- 根据包名,去软件市场查找 -- Toast一直显示 -- 自定义Dialog
- Export GridView To WordExcelPDFCSV in ASP.Net
- c++
- 《c和指针》3.1.3 二 、字符串常量 :不能用赋值语句将一个字符串常量或字符数组直接给一个字符数组赋值
- GingerBread 定制之乱弹
- Linux英文版显示中文
- 创建 修改镜像文件
- 多态反射机制
- dijkstra 求最短路径算法
- java连.net webService