Export ASP.NET DataGrid To Excel

原创 2004年10月06日 18:11:00

Exporting DataGrids to Excel...

Ken Walker's article on exporting DataSets to Excel has proved very popular. This article by Mike Dolan presents a different technique for getting a DataGrid rather than the DataSet into Excel. We believe you will find it very useful.

By: Mike Dolan Date: August 13, 2003 Download the code. Printer Friendly Version

I have a Datagrid with a Dataset as its datasource that I want to export to Excel. Using Ken Walker's awesome tutorial ( http://www.dotnetjohn.com/articles/articleid36.aspx) on how to pass Datasets to Excel via a component, I was able to easily send a dataset to excel from anywhere in my application. However, a few things didn't end up working out for my application. First, I needed to clean up the formatting. Second, many of my datagrids had columns that were calculated from data in the datasets. Third, the header row in Excel always contained the database column names which were sometimes unintelligible to an ordinary user. My final problem was that often the Datasets contained data necessary for the datagrid generation, but that we didn't want the end user to see.

To remedy all these issues I came up with the a simpler and more adaptable way to export the datagrid itself to Excel. I kept the concept to a component class so that it could easily be used throughout an application.

I created the following component. I used VS.Net and left out the "Component Designer Generated Code Region". What you will find is that the class has one method that takes in two arguments: A datagrid and a response. It works by simply writing a datagrid to the htmltextwriter just like Ken Walker's artice wrote the dataset table to the stringwriter.

I also included sections on how to change formatting. In the application we built, we wanted our Excel exports to have a standardized look. The middle-end section shows different types of formatting changes that can be made at the class level.


Public Class cmpDataGridToExcel
  Inherits System.ComponentModel.Component

Public Shared Sub DataGridToExcel(ByVal dgExport As DataGrid, ByVal response As HttpResponse)
  'clean up the response.object
  response.Charset = ""
  'set the response mime type for excel
  response.ContentType = "application/vnd.ms-excel"
  'create a string writer
  Dim stringWrite As New System.IO.StringWriter()
  'create an htmltextwriter which uses the stringwriter
  Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

  'instantiate a datagrid
  Dim dg As New DataGrid()
  ' just set the input datagrid = to the new dg grid
  dg = dgExport

  ' I want to make sure there are no annoying gridlines
  dg.GridLines = GridLines.None
  ' Make the header text bold
  dg.HeaderStyle.Font.Bold = True

  ' If needed, here's how to change colors/formatting at the component level
  'dg.HeaderStyle.ForeColor = System.Drawing.Color.Black
  'dg.ItemStyle.ForeColor = System.Drawing.Color.Black

  'bind the modified datagrid
  'tell the datagrid to render itself to our htmltextwriter
  'output the html
End Sub

End Class

After the component above was created/compiled, we then moved to the actual page that generated the Datagrid we would pass to the component. One of our datagrids looked like the following:

As you can see we have totals, discounts, and Boolean approval checkboxes. Most of these fields are presented generated from data using functions. Even the store number is output of a function. The Dataset export would have looked nothing like what you see above.

The following in based on a dataset taken from our SQL server. Just like in Ken's article, it doesn't matter how you generate the dataset or datagrid. Just create a datagrid and format it the way you want. Our datagrid above was generated with the following:

For security and copyright reasons, I will only include a small portion of our aspx page. The datagrid we created contains mostly template columns generated in this fashion:

InvoiceApproval.aspx Code Snippet

<asp:BoundColumn DataField="strInvoiceNo" ReadOnly="True" HeaderText="Invoice#"></asp:BoundColumn>

<asp:TemplateColumn HeaderText="Store #">
    <%# GetStore(DataBinder.Eval(Container.DataItem, "intCustomerID")) %>

<asp:TemplateColumn HeaderText="LA">
<%# GetLabor(DataBinder.Eval(Container.DataItem, "dblLabor"), DataBinder.Eval(Container.DataItem, "dblService")) %>

GetStore and GetLabor are simply functions we called passing the database data for the customer id and labor/service charges respectively. You can create your datagrid however you want, this simply shows why a dataset export with intCustomerID and not a Store # as we needed would not work.

A Northwind Database Example

Every tutorial needs a full example from the Northwind or Pubs database. The following is the Example.aspx file that displays the datagrid. We are still using the same component as you see above for the export.

The page contains one datagrid dgToExport and then a button to export. The datagrid is formatted and we expect the formatting and the headers to show exactly like this when we export to Excel.


<%@ Page Language="vb" AutoEventWireup="false" Codebehind="DataGridExport.aspx.vb" Inherits="ExportExample.DataGridExport"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
<div align="center">
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="dgToExport" runat="server" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="3" GridLines="Vertical" AutoGenerateColumns="False">
  <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#008A8C"></SelectedItemStyle>
  <AlternatingItemStyle BackColor="Gainsboro"></AlternatingItemStyle>
<ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle>
  <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084"></HeaderStyle>
  <FooterStyle ForeColor="Black" BackColor="#CCCCCC"></FooterStyle>

  <asp:BoundColumn DataField="EmployeeID" ReadOnly="True" HeaderText="ID"></asp:BoundColumn>
  <asp:TemplateColumn HeaderText="Name">
    <%# ReturnName(DataBinder.Eval(Container.DataItem, "LastName"), DataBinder.Eval(Container.DataItem, "FirstName")) %>
  <asp:BoundColumn DataField="Title" ReadOnly="True" HeaderText="Title"></asp:BoundColumn>

<PagerStyle HorizontalAlign="Center" ForeColor="Black" BackColor="#999999" Mode="NumericPages"></PagerStyle>
<asp:Button id="btnExport" runat="server" Text="Export to Excel"></asp:Button></P>

And now to the codebehind where everything happens. Essentially I have the Northwind database table Employees coming in via the SqlDataAdapter1 to fill DataSet1. Then the datagrid is bound to the dataset. The btnExport handles the export to excel when a user clicks it. You will also see the simple function to combine names. Once the grid is exported, the names will be together in one cell. I left out the Designer code.


Public Class DataGridExport
  Inherits System.Web.UI.Page

  Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
  Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
  Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
  Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
  Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
  Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
  Protected WithEvents dgToExport As System.Web.UI.WebControls.DataGrid
  Protected WithEvents btnExport As System.Web.UI.WebControls.Button
  Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm
  Protected WithEvents DataSet1 As System.Data.DataSet

  ' Web Form Designer Generated Code left out

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
  End Sub

  Sub BindGrid()
    ' Fill our dataset
    ' Assign the dataset to our Datagrid called dgToExport
    dgToExport.DataSource = DataSet1
    ' Finally bind the datagrid
  End Sub

  Function ReturnName(ByVal strLastName, ByVal strFirstName)
    ' This is the function I'm calling in the aspx page to show the difference
    ' between exporting a dataset versus exporting a datagrid. This function is
    ' simply going to combine the first and last names and and return the
    ' full name to the datagrid template column for "Name".
    Dim strReturn As String
    strReturn = strFirstName & " " & strLastName
    Return strReturn
  End Function

  Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
    ' One line handles all of the export. We're simply calling the component (cmpDataGridToExcel),
    ' then we're using it's only method (DataGridToExcel), and we're passing our DataGrid (dgToExport) and the value reponse. Note: If you're using VS.Net, once you
    ' build your solution after creating the component, Intellisense will now include your
    ' component. Just remember you have to build it first.
    ' You could also modify your datagrid here before exporting it. For instance in my
    ' invoice example we had a checkbox in our datagrid. If you have one of those the export
    ' will generate an error so we simply removed the column first like this before exporting:
    ' dgToExport.Columns.Remove(dgToExport.Columns.Item(11))
    cmpDataGridToExcel.DataGridToExcel(dgToExport, Response)
  End Sub

End Class

And that's all there is to it. Just create the component, compile it, and then once you have your datagrid setup one line exports it nice and cleanly to Excel at the click of a button. I hope this works out and possibly improves upon Ken Walker's great article that helped me understand how this whole string writing thing worked.

You may run the program here.
You may download the code here.


Asp.net中把DataTable或DataGrid导出为Excel 当前编码的一个项目中有把查询结果(显示在DataGrid)导出为excel的需求,尝试了几种方法,作为技巧拿来和大家...
  • lhy2199
  • lhy2199
  • 2012年04月18日 16:13
  • 2049

easyUI datagrid数据导出为Excel

功能实现方法与之前的一篇文章类似,但考虑到方便以后使用,直接记下方便查询使用。友情链接function ExporterExcel() { //获取Datagride的所有数据集合 v...
  • why15732625998
  • why15732625998
  • 2017年04月19日 13:12
  • 793

easyUI datagrid导出excel(所见即所得)

实现主要用到网友写的一个扩展jeasyui.extensions.datagrid.export.js调用方法简单,直接下载实例代码吧!http://yunpan.cn/cF7sydr9Kk7fi ...
  • cctvcqupt
  • cctvcqupt
  • 2015年11月03日 16:58
  • 858


Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application...
  • lianchangshuai
  • lianchangshuai
  • 2011年05月11日 19:04
  • 4414


Public Function ExportXLsD(ByVal datagrid As DataGrid) ', ByVal Title As String) 'Dim Mytab...
  • fjnu2008
  • fjnu2008
  • 2012年07月31日 11:18
  • 1367


第一步:添加datagrid 的扩展属性(可以写在通用js文件中) /** Jquery easyui datagrid js导出excel 修改自extgrid导出excel...
  • hb0746
  • hb0746
  • 2015年01月09日 22:33
  • 9116

export data from DataGrid to Excel

This document will show you how to use C# to setup  export data from DataGrid.Steps:1. You can creat...
  • sunnyglen
  • sunnyglen
  • 2006年12月21日 09:43
  • 573


1 把dataset数据保存到excel  protected void Page_Load(object sender, EventArgs e)    {        //建立连接       ...
  • highplayer
  • highplayer
  • 2010年06月10日 19:21
  • 3021

ASP.NET中Web DataGrid的使用指南(转自前沿设计网-找到这个真不容易啊!)

关键词:ASP.NET中Web DataGrid的使用指南 很久以前就想写一些关于DataGrid/DataList的东西,但是一直以来,一方面自感所学未深,另一方面,总觉无从下笔,一拖再拖,离刚开...
  • luqingshuai_eloong
  • luqingshuai_eloong
  • 2015年06月16日 17:31
  • 799

【Jqurey EasyUI+Asp.net】----DataGrid数据绑定,以及增、删、改(SQL)

也懒得打其它字了,直接进入主题吧 1.首先,数据表Rex_Test ID int 自增 tName varchar(10) 姓名 tEmail varchar(80) 邮...
  • yangmingxing980
  • yangmingxing980
  • 2014年06月27日 16:48
  • 2900
您举报文章:Export ASP.NET DataGrid To Excel