Export HTML Table Data to CSV File Using ASP.NET and jQuery

• Download HtmlToCsv - 207.73 KB

Introduction

This article demonstrates how to download the contents of most HTML tables to a csv file. To accomplish this, we use a simple index.html page that utilizes jQuery with the jQuery.post method to request data from the Northwind source tables. An IFrame is added to the index.html page using jQuery.append(). The IFrame src is SaveCSV.aspx plus a query string containing the FileName of the downloaded output file to produce. SaveCSV.aspx.cs contains all of the server-side code that does the Response.Write to produce the data that your browser will handle as content type "text/csv". Another header tag "Content-Disposition" is added to the Response data to tell your browser that this text coming back is an "attachment" that should be download to a file.

Background

My main inspiration for this article comes from Chris Pels' asp.net video that can be found here:
[How Do I:] Export Data to a Comma Delimited (CSV)

The difference from Pels' video is that I dislike using ASP.NET grid view controls and wanted to explore ways to enable downloading data from regular html files using AJAX technology. I work on GIS-based map-viewer pages that derive data from many sources. Since the presentation is generally done in html tables using AJAX to collect the data from various web services, the easiest way to add an option for clients to save the data is by adding an anchor tag on a table that is already rendered. I also want to avoid post-backs of the map viewer page because it can be slow to render the map images.

The sweet spot of the code for this article is the jQuery map method that maps the table contents to JSON objects. I found good instructions on how to use the map method here:

Use jQuery to extract data from HTML lists and tables by Dave Ward

Using the code

The project is set up as a "Web application" project type. I'm a desktop developer too, and I hate the "Web site" project type. I find it very confusing and hard to manage. The "Web application" project is the right way to manage your projects.

The Web.config file contains the path to NWind1.mdb under App_Data. If you copy the project source to "C:\inetpub\wwwroot\TableViewer", you will not have to do anything to the connectionStrings setting. Otherwise, change the path to where you copied the project.

The project is set up for .NET 3.5 in Windows 7. You should be able to get it to run on older versions if you create a new Web-application project and manually add the source files.

Two required references for this project that are not added by default are:
System.Configuration - for ConfigurationManager.ConnectionStrings in SelectionHandler.ashx.cs
System.Web.Extensions - for System.Web.Script.Serialization in SaveCSV.aspx.cs

The web application starts with selecting a table to view. SelectionHandler.ashx.cs is used to get the data from the sample database and Response.Write it back to the client. Then jQuery methods are used to build html tables with the AJAX result. Most people use "$" in place of "jQuery", but I prefer to use "jQuery" spelled out for readability. Javascript is already full of enough special symbols for me.

I'm not going into detail about the first step here because the article is really about the second phase which involves adding the IFrame link to the table header in order to be able to save the contents of the table to a file. The first part is really just part of my test driver for demonstration. I may write another article about this if there is enough interest.

The special part about building the result table is shown here:

var jsonResult = jQuery.parseJSON(result);
var cols = jsonResult.Columns;
var tableName = jQuery("#TableDD option:selected").text();
var queryStr = "FileName=csvFile.csv";

jQuery("#NwindTable thead").append('<tr><th class="header1" colspan="' + 
cols.length + '"><div>' + tableName + 
'<iframe id="PostFrame" src="SaveCSV.aspx?' + queryStr  '" /></div></th></tr>');

Specifically, this IFrame is appended to the first header row in the table:

'<iframe id="PostFrame" src="SaveCSV.aspx?'+ queryStr + '" />...

The PostFrame id is css styled with float: right; width: 100px so that it will stay on the right of the table header row.

As soon as the IFrame src is added to the html page, the server-side-cs code is executed to handle the initial Page_Load event. The "onclick" attribute is added to the lnkExport asp:LinkButton and is set to run the Javascript function DownloadTable() which is located in the index.html page. So, because the asp:LinkButton has a runat="server" attribute, the javascript function will be excuted on the client when the control is clicked, and then the whole SaveCSV.aspx page will post back to be handled in the normal asp.net server-side processing.

The DownloadTable() Javascript function is designed to be a reusable function that should be generic enough to use with most any html table to extract data.

jQuery has a map method that does a nice job of mapping the contents of the thead/tbody rows to JSON objects. See the link referenced in the Background section above for an article about how the map method works.

Here is the code that maps the table data rows to the Rows object:

//assemble comma separated rows in a JSON map
var Rows = jQuery("#NwindTable tbody tr").map(function() {
var cells = "";
  jQuery(this).find('td').each(function() {
    var celValue = jQuery(this).text();
    
    if (cells.length > 0) {
      cells += ',' + celValue.replace(/,/g, ';');
    } 
    else {
      cells += celValue.replace(/,/g, ';');
    }
  });
  return { row: cells };
}).get();

The code here examines each td cell for a row (tr) and extracts the text.
Then the commas are replaced within the text string with ';' and all the values for the row are appended to the cells var with commas between each value. Finally cells is added as a row object to the Rows JSON object.

The Columns object is populated in a similar manner and supports multiple thead rows. Since none of my column names contain commas, I don't need the Javascript replace method for the th cells.

The final bit of Javascript code combines Columns and Rows into the tableCSV object. Then the hidden <input> tag with id="TableData" that is located in the src page (SaveCSV.aspx) of the IFrame (labeled with id="PostFrame" in the index.html page) gets the value of the JSON.stringify()version of the tableCSV object.

Here are the two lines to do all this:

var tableCSV = { "Columns": Columns, "Rows": Rows };

//Save data to Iframe page SaveCSV.aspx before postback to page is done.
//The runat='server' in asp:LinkButton does the postback needed for download

jQuery("#PostFrame").contents().find("#TableData")[0].value = JSON.stringify(tableCSV);

The client-side rendering and Javascript has finished so the last thing that happens at the client is the asp.net postback of the SaveSCV.aspx page.

The sData is extracted from the TableData form object. Then ExportCSV method is called to send the csv data back to the client browser with a header that tells the browser to save or open the data in an external application instead of displaying the data in the browser.

Note that the "FileName" property was saved on the initial page load in a Page.Session object. Take a look at the code:

protected void Page_Load(object sender, EventArgs e)
{
  Session["_bExportCSVCalled"] = "false";
  if (!IsPostBack)
  {
    lnkExport.Attributes.Add(
    "onclick", "window.top.DownloadTable();");
    Session["_FileName"] = Context.Request.QueryString["FileName"];
  }
  else
  {
    string sData = Context.Request.Form["TableData"];
    if (String.IsNullOrEmpty(sData)) { return; }
    ExportCSV(sData);
  }
}

To turn the string data back into a deserialized object, the JavaScriptSerializer Deserialize method is utilized. Here is the code snippet from ExportCSV:

JavaScriptSerializer serializer = new JavaScriptSerializer();
TableData data = serializer.Deserialize<TableData>(sData);

This looks simple enough except that this set of classes has to be crafted first in order for it to work:

public class columns
{
  public string ColumnNames;
}

public class rows
{
  public string row;
}

[Serializable]
public class TableData
{
  public columns[] Columns;
  public rows[] Rows;
}

Back in ExportCSV, the cs TableData object can be used to Response.Write all of the rows in two simple for loops.

The last thing to do is to override the Page Render method to prevent the other usual form data from being posted back with your csv data. The article I referenced just does this with a Response.End (kills off all the rest of the Page processing), but Microsoft recommends against this. They recommend the Context.ApplicationInstance.CompleteRequest()instead.

Thus, the following bit of code completes the server-side "file" download:

protected override void Render(HtmlTextWriter writer)
{
  bool bExportCSVCalled = Convert.ToBoolean(Session["_bExportCSVCalled"]);
  if (bExportCSVCalled)
  {
    Context.Response.Flush();
    return; //stop the rest of the output from being written
  }
  base.Render(writer);
}

Note that I do a Context.Response.Flush() here since I previously set Context.Response.BufferOutput = true.

This causes all of the Response.Write data to be held in memory until Response.Flush() is called. If you don't want this, you can remove the line that sets BufferOutput = true. Then you must also remove the Response.Flush() call.

In the Render method I revert to the usual Render base method if ExportCSV was not called first.

Now if everything worked correctly, the client browser will pop up a message that asks the user if they want to open or save the file. If the open option is chosen, the application that is set up to handle files with the ".csv" extension (e.g. Excel) will open with the data in it.

Points of Interest

It can be very hard to work with DOM elements from the parent page that are contained within an IFrame. I used to spend a lot of time figuring out how to find the references to these elements when using standard Javascript. jQuery makes this pretty easy to do.

I first thought I could use the generic ashx handler to send the response back for the file download, but the browser would not start the download dialog this way. I found the IFrame solution to be a pretty clean workaround for this problem. The IFrame page performs the post back instead of the parent page.

History

First Published January 29, 2012