I have an small ASP.NET application that reads data from a table and sends it out as an excel attachment in an email message. The data is simply HTML table tr td tagged data. The way I am sending the message is by the following code:
byte[] data = GetData(excelData);
MemoryStream memoryStream = new MemoryStream(data);
mm.Attachments.Add(new Attachment(memoryStream, "info.xls", "text/plain"));
This has been working fine in Excel 2003 but for some reason Excel 2007 keeps trying to convert the whole thing to a formula and I get a "Cell Data too Long" error. I've tried searching but couldn't really find anything specific about why this might be happening in 07 or a simple way to fix it. I know 07 probably handles data a little bit different than 03 but I don't have a lot of time to dedicate to reworking this app if possible. Does anyone have any suggestions as to an easy fix for this?
So, the CSV solution was not very ideal for me because I needed to format the data. What I ended up doing was changing the first line of the above code to this:
System.Text.Encoding enc = Encoding.UTF8;
byte[] data = enc.GetBytes(excelData);
UTF8 was the ONLY byte encoding that would correctly display my HTML tables in Excel 2007 with one caveat - for some reason even though it was now using UTF-8 it was not correctly displaying special characters. The fix for this was to add this before beginning my HTML tables in the ExcelData text I was exporting:
<HEAD><META http-equiv=Content-Type content='text/html; charset=utf-8'></HEAD>
I'm not sure why it required the same encoding in 2 different spots to get everything working correctly. There are probably much better ways to go about exporting to Excel than HTML tables but this is a quick and easy solution for a problem that doesn't require anything elegant. Hopefully, this can help some others as well.