I was working on an application that dumps data out of a database into files. It’s a batch application (meaning that it runs multiple extractions in batches) and until recently only supported outputting the data as delimited files. However, I ran into an instance where I needed to export to native Excel files instead, because some of the fields contained embedded line breaks.

I added support for Excel, which was easy enough, as I’d done Excel automation from .NET in the past.  However, I started running into a problem with an OutOfMemoryException.  It always happened on the same cell.  Eliminating that column from the query made the problem vanish.  I checked my handling of the Excel objects, making sure that I was releasing everything, even going so far as to call System.Runtime.InteropServices.Marshal.ReleaseComObject() against each object, then setting it to null, and finally explicitly calling GC.Collect().  This made no difference.

I started looking at the value that was being pushed into the cell.  It was a string of text just slightly over 1K.  Out of curiosity, I opened Excel, copied the string into the clipboard and then pasted it into the cell manually (note that you have to put the cell in edit mode by hitting F2 first, or each line of the text will be pasted into a different cell).  I got an error saying that the formula was too long.  I checked the text and found that it began with “—–Original Message …”.  It was a comment field and someone had pasted an e-mail message into it.  Excel was seeing the “-” as the first character and treating the text as a formula instead of just text.  Formulae in Excel 2003 are limited to 1,024 characters. The text exceeded that limit, so the exception was thrown.

I programmed around this by enclosing the text in quotation marks if the field was a string beginning with “-” or “+”, causing Excel to interpret the value as text instead of a formula.  For my dataset, at least, that was enough.

I’d like to know how to set the value of the cell programmatically while forcing Excel to treat it as text without enclosing it in quotation marks, but I haven’t had any luck as of yet.