Using an ASP.NET handler to output a CSV file

I wanted a ASP.NET handler to output some data as a CSV file with the most likely target being to open it in Excel.

I had the following problems to resolve:

1. Force download rather than opening in IE.


2. Numbers beginning with zeros (0) must not be stripped off in Excel.


3. Creating the CSV from an XML file using XSLT.


4. Decoding entities (e.g. &) in XSLT.


5. Accessing Session State from the handler code.

So lets look at these in order ...

1. Force download rather than opening in IE.

This one is fairly simple to resolve with some addition of headers:

context.Response.ContentType = "text/csv";
context.Response.AddHeader("Content-Disposition", "inline; filename=\"file.csv\"");

The only thing to look out for here is to use Response.AddHeader rather than the Response.Headers collection as it won't work on most IIS platforms.

2. Numbers beginning with zeros (0) must not be stripped off in Excel.

This solution is a disappointing but effective one. Enclose the number in quotes with a leading equals sign.

So 01234 becomes ="001234"

This is a very Excel bias solution but was good enough for me. Another solution is to lead with a single quote but unfortunately this will initially show in the Excel cell until the user 'edits' the cell then it disappears.

3. Creating the CSV from an XML file using XSLT.

This is fairly straightforward, here's the XSLT:

<xsl:stylesheet version="1.0" xsl="http://www.w3.org/1999/XSL/Transform" msxsl="urn:schemas-microsoft-com:xslt" prefixes="msxsl">
   <xsl:output method="text" encoding="utf-8">

   <xsl:template match="doc">
       <xsl:apply-templates select="row">
   </xsl:apply-templates>

   <xsl:template match="row">
       <xsl:for-each select="*">
           <xsl:value-of select="." disable-output-escaping="yes" />
           <xsl:if test="position() != last()">
             <xsl:value-of select="','" />
           </xsl:if>
      </xsl:for-each>
      <xsl:text>&#10;</xsl:text>
   </xsl:template>
</xsl:stylesheet>

This assumes an Xml document hierarchy of doc > row > fields.

This is fine except that often you want to output only certain fields, perhaps in a particular format, and show the field headings in the first row. The above can be adapted for this by swapping out the "for-each" for a "value-of" naming each field individually.

4. Decoding entities (e.g. &) in XSLT.

In the above Xslt you will notice that I have added disable-output-escaping which will do just that.

5. Accessing Session State from the handler code.

The class definition for a handler is by default:

public class CsvHandler : IHttpHandler

To indicate that you need session state too you need to add another interface although this is just a marker (probably should have been an attribute), I only need read access so:

public class CsvHandler : IHttpHandler, IReadOnlySessionState

Okay that covers all the issues listed above.

Visual Studio keyboard layout swaps from UK to US

Solution: Hotkey is Shift+Alt

I've been having problems in Visual Studio where my keyboard would suddenly swap from UK to US layout. From that point on the " key would map to @ and vice versa.

I eventually got round to googling the problem and came to this Microsoft connect item: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=269947

So I learn that there is a hot key - Shift+Alt - that does this and so the problem is reversable. But I have to agree with the person who sent in that item. Why does this hot key exist or perhaps why is it such a simple combo? The only thing I can think of is that MS people testing VS wanted this. To me it is just a pain.