If you have not read my previous articles, then I insist you read these articles too as it might help you explore other possibilities. I have explained with examples, on how to export data fetched directly from a database table to Excel using Microsoft’s Interop namespace.
Later, I have posted another interesting article, and here I have used Asp.Net DataTable and DataGrid to export data to Excel also, I have shown how to format the excel file using Asp.Net code behind procedures.
Let’s get back this article.
A GridView control often contains many rows and it’s usual to see that paging is enabled to the GridView to make data browsing easy. I have no intension to make this article lengthy. Therefore, we will get straight to the point.
I will add a GridView control to a web page and enable paging to it, and set the page size to “five”. With paging enabled, now I wish to export entire GridView to an Excel file with little formatting too.
<asp:GridView ID="grdBooks" runat="server"
AutoGenerateColumns="False"
DataKeyNames="BookID"
DataSourceID="SqlDataSource1"
AllowPaging="true" PageSize="5">
<Columns>
<asp:BoundField DataField="BookID" HeaderText="BookID" InsertVisible="False"
ReadOnly="True" SortExpression="BookID" />
<asp:BoundField DataField="BookName" HeaderText="BookName"
SortExpression="BookName" />
<asp:BoundField DataField="Category" HeaderText="Category"
SortExpression="Category" />
<asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:DNA_CLASSIFIEDConnectionString %>"
SelectCommand="SELECT [BookID], [BookName], [Category], [Price] FROM [Books]">
</asp:SqlDataSource>
<div>
<input type="button" id="btExportToExcel" value="Export GridView To Excel"
onserverclick="ExportToExcel" runat="server" />
</div>I have populated the GridView with data using SqlDataSource. Binding data to a GridView control using SqlDataSource is simple and perfect for our example. In addition, I have added a Button, which when clicked will call a function at code behind to export the data from the GridView.
using System; using System.Data; // FOR DATABASE. using System.Data.SqlClient; // FOR SQL CONNECTION. using System.Web.UI.WebControls; // FOR DATAGRID. public partial class SiteMaster : System.Web.UI.MasterPage { System.Data.DataTable mytable = new System.Data.DataTable(); System.Data.DataRow dr = null; protected void ExportToExcel(object sender, EventArgs e) { Create_DataTable(); // CREATE A DATATABLE. // CALCULATE VALUES FOR GRAND TOTAL. Decimal dTotalPrice = 0; for (int i = 0; i <= mytable.Rows.Count - 1; i++) { dTotalPrice += mytable.Rows[i].Field<Decimal>(3); } // NOW ASSIGN DATA TO A DATAGRID. DataGrid dg = new DataGrid(); dg.DataSource = mytable; dg.DataBind(); // THE EXCEL FILE. string sFileName = "BooksList-" + System.DateTime.Now.Date + ".xls"; sFileName = sFileName.Replace("/", ""); // SEND OUTPUT TO THE CLIENT MACHINE USING "RESPONSE OBJECT". Response.ClearContent(); Response.Buffer = true; Response.AddHeader("content-disposition", "attachment; filename=" + sFileName); Response.ContentType = "application/vnd.ms-excel"; EnableViewState = false; System.IO.StringWriter objSW = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter objHTW = new System.Web.UI.HtmlTextWriter(objSW); dg.HeaderStyle.Font.Bold = true; // SET HEADER AS BOLD. dg.RenderControl(objHTW); // STYLE THE SHEET AND WRITE DATA TO IT. Response.Write("<style> TABLE { border:dotted 1px #999; } " + "TD { border:dotted 1px #D5D5D5; text-align:center } </style>"); Response.Write(objSW.ToString()); // ADD A ROW AT THE END OF THE SHEET SHOWING A RUNNING TOTAL OF PRICE. Response.Write("<table><tr><td><b>Total: </b></td><td></td><td></td><td><b>" + dTotalPrice.ToString("N2") + "</b></td></tr></table>"); Response.End(); dg = null; } private void Create_DataTable() { int iRowCnt = 0; // CREATE A DATATABLE AND ADD COLUMNS TO IT. mytable.Columns.Add(new System.Data.DataColumn("Book ID", System.Type.GetType("System.String"))); mytable.Columns.Add(new System.Data.DataColumn("Name of the Book", System.Type.GetType("System.String"))); mytable.Columns.Add(new System.Data.DataColumn("Category", System.Type.GetType("System.String"))); mytable.Columns.Add(new System.Data.DataColumn("Price ($)", System.Type.GetType("System.Decimal"))); // REMOVE PAGING TO HELP EXPORT ENTIRE GRIDVIEW TO EXCEL. grdBooks.AllowPaging = false; grdBooks.DataBind(); foreach (GridViewRow row in grdBooks.Rows) { dr = mytable.NewRow(); dr[0] = grdBooks.Rows[iRowCnt].Cells[0].Text; dr[1] = grdBooks.Rows[iRowCnt].Cells[1].Text; dr[2] = grdBooks.Rows[iRowCnt].Cells[2].Text; dr[3] = grdBooks.Rows[iRowCnt].Cells[3].Text; mytable.Rows.Add(dr); iRowCnt += 1; } } }
Option Explicit On Imports System.Data ' FOR DATABASE. Imports System.Data.SqlClient ' FOR SQL CONNECTION. Partial Class Site Inherits System.Web.UI.MasterPage Dim mytable As New Data.DataTable() Dim row As Data.DataRow Protected Sub ExportToExcel(ByVal sender As Object, ByVal e As EventArgs) Call Create_DataTable() ' CREATE A DATATABLE. Dim dTotalPrice As Double = 0 ' CALCULATE VALUES FOR GRAND TOTAL. For i = 0 To mytable.Rows.Count - 1 dTotalPrice += mytable.Rows(i).Item("Price ($)") Next ' NOW ASSIGN DATA TO A DATAGRID. Dim dg As New DataGrid dg.DataSource = mytable : dg.DataBind() ' THE EXCEL FILE. Dim sFileName As String = "BooksList-" & Replace(Date.Now.Date, "/", "") & ".xls" ' SEND OUTPUT TO THE CLIENT MACHINE USING "RESPONSE OBJECT". Response.ClearContent() Response.Buffer = True Response.AddHeader("content-disposition", "attachment; filename=" & sFileName) Response.ContentType = "application/vnd.ms-excel" Me.EnableViewState = False Dim objSW As New System.IO.StringWriter Dim objHTW As New HtmlTextWriter(objSW) dg.HeaderStyle.Font.Bold = True ' SET HEADER AS BOLD. dg.RenderControl(objHTW) ' STYLE THE SHEET AND WRITE DATA TO IT. Response.Write("<style> TABLE { border:dotted 1px #999; } " & _ "TD { border:dotted 1px #D5D5D5; text-align:center } </style>") Response.Write(objSW.ToString()) ' ADD A ROW AT THE END OF THE SHEET SHOWING A RUNNING TOTAL OF PRICE. Response.Write("<table><tr><td><b>Total: </b></td><td></td><td></td><td><b>" & _ Decimal.Parse(dTotalPrice).ToString("N2") & "</b></td></tr></table>") Response.End() dg = Nothing End Sub Private Sub Create_DataTable() Dim iRowCnt As Integer = 0 ' CREATE A DATATABLE AND ADD COLUMNS TO IT. mytable.Columns.Add(New Data.DataColumn("Book ID", System.Type.GetType("System.String"))) mytable.Columns.Add(New Data.DataColumn("Name of the Book", System.Type.GetType("System.String"))) mytable.Columns.Add(New Data.DataColumn("Category", System.Type.GetType("System.String"))) mytable.Columns.Add(New Data.DataColumn("Price ($)", System.Type.GetType("System.String"))) ' REMOVE PAGING TO HELP EXPORT ENTIRE GRIDVIEW TO EXCEL. grdBooks.AllowPaging = False grdBooks.DataBind() 'POPULATE DATATABLE USING GRIDVIEW ROW VALUES. For Each GridViewRow In grdBooks.Rows row = mytable.NewRow row(0) = grdBooks.Rows(iRowCnt).Cells(0).Text row(1) = grdBooks.Rows(iRowCnt).Cells(1).Text row(2) = grdBooks.Rows(iRowCnt).Cells(2).Text row(3) = grdBooks.Rows(iRowCnt).Cells(3).Text mytable.Rows.Add(row) iRowCnt += 1 Next End Sub End Class
Let’s see how it actually works.
When we click the button, it will call the ExportToExcel procedure that we have written at the code behind section of this application. The procedure first calls another private procedure called “Create_DataTable()”. Here, we are creating a DataTable, which will fetch and store data from each row in the GridView.
There are two important things, which I want you to focus and understand. Firstly, while adding columns to the DataTable, I have set the Type of the Price ($) column as Decimal. This would allow me add the Grand Total at the end of the Excel file’s Price column.
mytable.Columns.Add(new System.Data.DataColumn("Price ($)", System.Type.GetType("System.Decimal")));
A String data type would not allow me to get the cumulative values of the price. I am calculating the price inside the ExportToExcel procedure.
Decimal dTotalPrice = 0;
for (int i = 0; i <= mytable.Rows.Count - 1; i++)
{
dTotalPrice += mytable.Rows[i].Field<Decimal>(3);
}Second, in the procedure Create_DataTable() I have set Paging as false. This would temporarily switch of paging allowing me to extract all the rows into the DataTable.
// REMOVE PAGING TO HELP EXPORT ENTIRE GRIDVIEW TO EXCEL.
grdBooks.AllowPaging = false;
grdBooks.DataBind();Finally, concluding the export with little formatting (in fact styling with CSS) of the header and footer of the Excel sheet.
Hope you find this article and its example useful. Let me know your suggestions on this. So, what did we learned today? First, I have populated the GridView using SqlDataSource control. Later I created a DataTable to extract and store data from the GridView control and finally exporting the data in the DataTable to an Excel file. I have also applied a little formatting to the Excel headers and footers before actually exporting.