Export to Excel and Show running total using Asp.Net C# and Vb

Last update: 13th May 2024

One of simplest way to export data to an excel file is by using classes like StringWriter of System.IO namespace and HtmlTextWriter of System.Web.UI namespace in .Net. We can extract and export data without much difficulty. Although this approach limits Auto formatting of the excel file, however we can use a workaround to do some basic formating to the worksheet.

Export to Excel using Asp.Net DataGrid and DataTable

Scroll down to see the demo.

The above image explains it all, particularly the details shown with arrows. Yes, you will also learn how to show running total at the bottom of the Excel sheet, for the exported data. We are also going to show you how to bold the Excel headers using HMTL tags, dynamically.

Formatting and Styling the Excel Sheet

To format and style the sheet we will use HTML <select></select> tag. This will allow us to create decent borders arround each cells and the entire sheet. Also since we are using a dynamic DataGrid control as a container and the actual source of data for the excel sheet, we will style the grid’s header which in turn style the active sheets header.

Dynamically Show Running Total in Excel using Asp.Net

Let us now see example on "how to add a row" at the end of the excel sheet, showing a running total of a particular column or columns. For this purpose, we are going to use the DataTable class of System.Data namespace, which when loaded with a Data source can give us a detail view of columns and rows.

The Markup

<!DOCTYPE html>
<html>
<body>
    <form id="form1" runat="server">
        <div>
            <input type="button" 
                id="btExportToExcel" 
                value="Export To Excel" 
                onserverclick="btExportToExcel_Click" 
                runat="server" />
        </div>
    </form>
</body>
</html>

Code Behind (C#)

using System;
using System.Data;                      // FOR DATABASE
using System.Data.SqlClient;            // FOR SQL CONNECTION.
using System.Web.UI.WebControls;        // FOR DATAGRID.

public partial class _Default : System.Web.UI.Page 
{
    protected void btExportToExcel_Click(object sender, EventArgs e)
    {
        // SET DATABASE CONNECTION.
        using (SqlConnection con = new SqlConnection("Data Source=dna;Persist Security Info=False;" +
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;"))
        {
            using (SqlCommand cmd = con.CreateCommand())
            {
                // GET THE DATA FROM SQL SERVER TABLE.
                cmd.CommandText = "SELECT BookID [Book ID], BookName [Name of the Book], " +
                    "Price [Price ($)] FROM dbo.Books";
                con.Open();

                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;

                DataTable dt = new DataTable();
                sda.Fill(dt);

                // CALCULATE RUNNING TOTAL (WILL DISPLAY AT THE FOOTER OF EXCEL WORKBOOK.)
                Decimal dTotalPrice = 0;
                for (int i = 0; i <= dt.Rows.Count - 1; i++) {
                    dTotalPrice += dt.Rows[i].Field<Decimal>(2);
                }

                // NOW ASSIGN DATA TO A DATAGRID.
                DataGrid dg = new DataGrid();
                dg.DataSource = dt; 
                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 EXCEL HEADERS 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><b>" +
                    dTotalPrice.ToString("N2") + "</b></td></tr></table>");

                Response.End();
                dg = null;
            }
        }        
    }
}

See this demo

Look carefully at this piece of the code. To display a running total at the end of the Excel workbook (for column “Price ($)”), I am creating an HTML table with a single row.

Response.Write("<table><tr><td><b>Total: </b></td><td></td><td><b>" +
    dTotalPrice.ToString("N2") + "</b></td></tr></table>");

Code behind (VB)

Option Explicit On
Imports System.Data                 ' FOR DATABASE
Imports System.Data.SqlClient       ' FOR SQL CONNECTION.

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub btExportToExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
        Using con As SqlConnection = New SqlConnection("Data Source=dna;Persist Security Info=False;" & _
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;")

            Dim strQuery As String = "SELECT BookID [Book ID], BookName [Name of the Book], " & _
                "Price [Price ($)] FROM dbo.Books"

            Using cmd As SqlCommand = New SqlCommand(strQuery)
                Dim sda As SqlDataAdapter = New SqlDataAdapter

                cmd.Connection = con : con.Open()
                sda.SelectCommand = cmd

                Dim dt As DataTable = New DataTable
                sda.Fill(dt)

                ' CALCULATE RUNNING TOTAL (WILL DISPLAY AT THE FOOTER OF EXCEL WORKBOOK.)
                Dim dTotalPrice As Double = 0
                For i = 0 To dt.Rows.Count - 1
                    dTotalPrice += dt.Rows(i).Item("Price ($)")
                Next

                ' NOW ASSIGN DATA TO A DATAGRID.
                Dim dg As New DataGrid
                dg.DataSource = dt : 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 EXCEL HEADERS 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><b>" & _
                    Decimal.Parse(dTotalPrice).ToString("N2") & "</b></td></tr></table>")

                Response.End()
                dg = Nothing
            End Using
        End Using
    End Sub
End Class

The Response object

You must be quite familiar with the Response object if you have worked with Classic Asp before. One of the most common methods we have used in ASP is the .write() method for writing a string to an output like the browser. The .Redirect() method would redirect a user to another URL.

In the above example we have used the "Response" object and its methods for various purposes, such as, assigning a name to the file using .AddHeader() and to write data into the file using the .Write() method. The property .ContentType of Response object will be used to set the https content type. Moreover, in this context we have assigned the type as application/vnd.ms-excel, since we are the data into an Excel sheet.

See this demo

← PreviousNext →