当在Excel 2007中打开时，Excel电子表格生成将导致“文件格式与扩展错误不同”

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。产生问题的网页:

<%@ 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"); 

    }

}

6 个解决方案

#1

http://blogs.msdn.com/vsofficedeveloper/pages/Excel-2007-Extension-Warning.aspx

http://blogs.msdn.com/vsofficedeveloper/pages/excel - 2007 -扩展- 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代码。它必须在客户端注册表上被压制/固定。

#2

If you're like me and generating the Excel Sheet as 2003 XML document, you can remove the warnings by doing the following:

如果您像我一样生成2003年XML文档的Excel表，您可以通过以下操作删除警告:

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将不会显示文件内容和文件扩展名不匹配的警告。

#3

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 .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.

希望你能从中受益。

#4

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-excel -the- you-are- tryto -open- in-a- difference -format- special -by- extend .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();
        }
    }

#5

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。只是一个盲人。

#6

-2

Use

使用

content-type=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

- type =应用程序/ vnd.openxmlformats-officedocument.spreadsheetml.sheet

And specify extension as xlsx

并将扩展指定为xlsx

#1