Excel 2007 将导出的内存流数据转换为公式
我有一个小型 ASP.NET 应用程序，它从表中读取数据并将其作为电子邮件中的 Excel 附件发送出去。该数据只是 HTML 表 tr td 标记数据。我发送消息的方式是通过以下代码：
byte[] data = GetData(excelData);
MemoryStream memoryStream = new MemoryStream(data);
mm.Attachments.Add(new Attachment(memoryStream, "info.xls", "text/plain"));
这在 Excel 2003 中工作正常，但由于某种原因，Excel 2007 不断尝试将整个内容转换为公式，并且我收到“单元格数据太长”错误。我尝试过搜索，但找不到任何关于为什么会在 07 中发生这种情况的具体信息或解决此问题的简单方法。我知道 07 处理数据的方式可能与 03 略有不同，但如果可能的话，我没有太多时间专门重新设计这个应用程序。有没有人有任何关于简单解决此问题的建议？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
因此，CSV 解决方案对我来说不是很理想，因为我需要格式化数据。我最终所做的是将上面代码的第一行更改为：
UTF8 是唯一可以在 Excel 2007 中正确显示我的 HTML 表的字节编码，但有一个警告 - 由于某种原因，即使它现在使用 UTF-8无法正确显示特殊字符。解决这个问题的方法是在我导出的 ExcelData 文本中开始 HTML 表之前添加此内容：
我不确定为什么它需要在 2 个不同的位置使用相同的编码才能使一切正常工作。可能有比 HTML 表格更好的导出到 Excel 的方法，但对于不需要任何优雅的问题来说，这是一种快速而简单的解决方案。希望这也能帮助其他一些人。
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:
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:
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.
保存为 .xls 的 HTML 是一种 hack。它可以节省时间，但不能保证它适用于每个版本的 Excel。如果您可以摆脱它，我建议将数据保存到 CSV 中并将其作为 .csv 文件发送出去。当 Excel 转换文件时，用户可能必须单击“确定”几次，但每个版本的 Excel 都可以加载 CSV 文件。
如果您确实需要 .XLS 格式的文件，您可能需要研究第 3 方库，例如 FlexCel，或通过 Excel Interop 进行工作（不建议在服务器上使用）。
The HTML saved as .xls is a hack. It saves time, but there is no guarantee it will work in every version of Excel. If you can get away with it, I recommend saving the data into CSV and sending it out as a .csv file. The user might have to click 'ok' a couple of times as Excel converts the file, but every version of Excel can load CSV files.
If you really need it in .XLS format, you might want to investigate 3rd party libraries such as FlexCel, or work via Excel Interop (not recommended on a server).