C# 在 excel 2007 中打开时，Excel 电子表格生成导致“文件格式不同于扩展名错误”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/652377/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel spreadsheet generation results in "different file format than extension error" when opening in excel 2007
提问by Jeff Bloom
The spreadsheet still displays, but with the warning message. The problem seems to occur because Excel 2007 is more picky about formats matching their extensions than earlier versions of Excel.
电子表格仍然显示，但带有警告消息。这个问题似乎是因为 Excel 2007 比早期版本的 Excel 更挑剔与其扩展名匹配的格式。
The problem was initially discovered by an ASP.Net program and produces in the Excel error "The file you are trying to open, "Spreadsheet.aspx-18.xls', is in a different format than specified by the file extension. Verify ...". However, when I open the file it displays just fine. I am using Excel 2007. Firefox identifies the file as an Excel 97-2003 worksheet.
该问题最初是由 ASP.Net 程序发现的，并在 Excel 中产生错误“您尝试打开的文件，‘Spreadsheet.aspx-18.xls’，与文件扩展名指定的格式不同。验证...”。但是，当我打开文件时，它显示得很好。我使用的是 Excel 2007。Firefox 将该文件识别为 Excel 97-2003 工作表。
Here is an ASP.NET page which generates the problem:
这是一个产生问题的 ASP.NET 页面：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Spreadsheet.aspx.cs" Inherits="Spreadsheet" %>
The code behind file looks like:
文件背后的代码如下所示：
public partial class Spreadsheet : System.Web.UI.Page {
protected void Page_Load(object sender, EventArgs e)
{
Response.ContentType = "application/vnd.ms-excel";
Response.Clear();
Response.Write("Field\tValue\tCount\n");
Response.Write("Coin\tPenny\t443\n");
Response.Write("Coin\tNickel\t99\n");
}
}
}
T
吨
采纳答案by Eric H
http://blogs.msdn.com/vsofficedeveloper/pages/Excel-2007-Extension-Warning.aspx
http://blogs.msdn.com/vsofficedeveloper/pages/Excel-2007-Extension-Warning.aspx
That is a link basically describing that MS knows about the problem your describe and that it cannot be suppressed from within ASP.NET code. It must be suppressed/fixed on the client's registry.
这是一个基本上描述 MS 知道您描述的问题的链接，并且它不能从 ASP.NET 代码中被抑制。它必须在客户端的注册表中被抑制/修复。
回答by Gregory A Beamer
I aam more fond of using a Grid and changing the response type I have yet to have a problem with that methodology. I have not used straight tab delimited files. One possibility is the \n might have to be \r\n. Just a blind shot.
我更喜欢使用网格和更改响应类型我还没有遇到这种方法的问题。我没有使用直接制表符分隔的文件。一种可能性是 \n 可能必须是 \r\n。只是一个盲拍。
回答by Danny G
Use
用
content-type=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
内容类型=应用程序/vnd.openxmlformats-officedocument.spreadsheetml.sheet
And specify extension as xlsx
并将扩展名指定为 xlsx
回答by Gavin Miller
If you're like me and generating the Excel Sheet as 2003 XML document, you can remove the warnings by doing the following:
如果您像我一样将 Excel 工作表生成为 2003 XML 文档，您可以通过执行以下操作来删除警告：
Added to the XML output:
添加到 XML 输出：
<?xml version="1.0" encoding="utf-16"?>
<?mso-application progid="Excel.Sheet"?>
...
Added to the download page:
添加到下载页面：
// Properly outputs the xml file
response.ContentType = "text/xml";
// This header forces the file to download to disk
response.AddHeader("content-disposition", "attachment; filename=foobar.xml");
Now Excel 2007 will not display a warning that the file content and file extension don't match.
现在，Excel 2007 不会显示文件内容和文件扩展名不匹配的警告。
回答by AnonUser
I have seen this question asked many times. I ran into the same difficulty today so I fixed the problem using NPOI npoi.codeplex.com/
我已经多次看到这个问题。我今天遇到了同样的困难，所以我使用 NPOI npoi.codeplex.com/解决了这个问题
public static class ExcelExtensions
{
/// <summary>
/// Creates an Excel document from any IEnumerable returns a memory stream
/// </summary>
/// <param name="rows">IEnumerable that will be converted into an Excel worksheet</param>
/// <param name="sheetName">Name of the Ecel Sheet</param>
/// <returns></returns>
public static FileStreamResult ToExcel(this IEnumerable<object> rows, string sheetName)
{
// Create a new workbook and a sheet named by the sheetName variable
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet(sheetName);
//these indexes will be used to track to coordinates of data in our IEnumerable
var rowIndex = 0;
var cellIndex = 0;
var excelRow = sheet.CreateRow(rowIndex);
//Get a collection of names for the header by grabbing the name field of the display attribute
var headerRow = from p in rows.First().GetType().GetProperties()
select rows.First().GetAttributeFrom<DisplayAttribute>(p.Name).Name;
//Add headers to the file
foreach (string header in headerRow)
{
excelRow.CreateCell(cellIndex).SetCellValue(header);
cellIndex++;
}
//reset the cells and go to the next row
cellIndex = 0;
rowIndex++;
//Inset the data row
foreach (var contentRow in rows)
{
excelRow = sheet.CreateRow(rowIndex);
var Properties = rows.First().GetType().GetProperties();
//Go through each property and inset it into a single cell
foreach (var property in Properties)
{
var cell = excelRow.CreateCell(cellIndex);
var value = property.GetValue(contentRow);
if (value != null)
{
var dataType = value.GetType();
//Set the type of excel cell for different data types
if (dataType == typeof(int) ||
dataType == typeof(double) ||
dataType == typeof(decimal) ||
dataType == typeof(float) ||
dataType == typeof(long))
{
cell.SetCellType(CellType.NUMERIC);
cell.SetCellValue(Convert.ToDouble(value));
}
if (dataType == typeof(bool))
{
cell.SetCellType(CellType.BOOLEAN);
cell.SetCellValue(Convert.ToDouble(value));
}
else
{
cell.SetCellValue(value.ToString());
}
}
cellIndex++;
}
cellIndex = 0;
rowIndex++;
}
//Set the width of the columns
foreach (string header in headerRow)
{
sheet.AutoSizeColumn(cellIndex);
cellIndex++;
}
return workbook.GetDownload(sheetName);
}
/// <summary>
/// Converts the NPOI workbook into a byte array for download
/// </summary>
/// <param name="file"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static FileStreamResult GetDownload(this NPOI.HSSF.UserModel.HSSFWorkbook file, string fileName)
{
MemoryStream ms = new MemoryStream();
file.Write(ms); //.Save() adds the <xml /> header tag!
ms.Seek(0, SeekOrigin.Begin);
var r = new FileStreamResult(ms, "application/vnd.ms-excel");
r.FileDownloadName = String.Format("{0}.xls", fileName.Replace(" ", ""));
return r;
}
/// <summary>
/// Get's an attribute from any given property
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="instance"></param>
/// <param name="propertyName"></param>
/// <returns></returns>
public static T GetAttributeFrom<T>(this object instance, string propertyName) where T : Attribute
{
var attrType = typeof(T);
var property = instance.GetType().GetProperty(propertyName);
return (T)property.GetCustomAttributes(attrType, false).First();
}
}
Hope you find this helpful.
希望你觉得这有帮助。
回答by Andriy Gubal
I was trying to resolve this issue during some days. Finally, I've found the solution here: http://www.aspsnippets.com/Articles/Solution-ASPNet-GridView-Export-to-Excel-The-file-you-are-trying-to-open-is-in-a-different-format-than-specified-by-the-file-extension.aspx
我试图在几天内解决这个问题。最后，我在这里找到了解决方案：http: //www.aspsnippets.com/Articles/Solution-ASPNet-GridView-Export-to-Excel-The-file-you-are-trying-to-open-is- in-a-different-format-than-specified-by-the-file-extension.aspx
Namespaces:
命名空间：
using System.IO;
using System.Data;
using ClosedXML.Excel;
Code:
代码：
DataTable dt = new DataTable("GridView_Data");
// Fill your DataTable here...
//Export:
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
