您试图打开的文件的格式与Asp.Net中的文件扩展名指定的格式不同

the file you are trying to open is in a different format than specified by the file extension c# error when trying to open file in excel.

在尝试在excel中打开文件时，您试图打开的文件的格式与文件扩展名c#错误指定的格式不同。

Here is my code

这是我的代码

public ActionResult Export(string filterBy)
{
    MemoryStream output = new MemoryStream();
    StreamWriter writer = new StreamWriter(output, Encoding.UTF8);

    var data = City.GetAll().Select(o => new
    {
        CountryName = o.CountryName,
        StateName = o.StateName,
        o.City.Name,
        Title = o.City.STDCode
    }).ToList();
    var grid = new GridView { DataSource = data };
    grid.DataBind();
    var htw = new HtmlTextWriter(writer);

    grid.RenderControl(htw);

    writer.Flush();
    output.Position = 0;

    return File(output, "application/vnd.ms-excel", "test.xls");

}

when am trying to open excel i get this error

当我试图打开excel时，我得到了这个错误

the file you are trying to open is in a different format than specified by the file extension

您试图打开的文件的格式与文件扩展名指定的格式不同

After clicking on Yes the file open properly. but i don't want this msg to appear.

点击Yes后，文件被正确打开。但我不希望这个味精出现。

4 个解决方案

#1

I have used CloseXML to solve the problem.

我用CloseXML解决了这个问题。

public static void ExportToExcel(IEnumerable<dynamic> data, string sheetName)
{
    XLWorkbook wb = new XLWorkbook();
    var ws = wb.Worksheets.Add(sheetName);
    ws.Cell(2, 1).InsertTable(data);
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx",sheetName.Replace(" ","_")));

    using (MemoryStream memoryStream = new MemoryStream())
    {
        wb.SaveAs(memoryStream);
        memoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
        memoryStream.Close();
    }

    HttpContext.Current.Response.End();
}

Installed ClosedXML in my project using Nuget Package Manager.

在我的项目中使用Nuget包管理器安装了ClosedXML。

#2

the file you are trying to open is in a different format than specified by the file extension

您试图打开的文件的格式与文件扩展名指定的格式不同

You are constantly getting that warning message because the file that got created is not an actual excel file. If you will look into the generated file, it's just a bunch of html tags. Remember that a GridView's RenderControl will generate an html table.

您不断地得到警告消息，因为创建的文件不是实际的excel文件。如果您要查看生成的文件，它只是一堆html标记。请记住，GridView的RenderControl将生成一个html表。

To fix your issue, you need to either use a third party tool that creates a real excel file (one tool you might want to use is NPOI) or create a comma-delimited file, or simply a csv file, and return that file.

要解决这个问题，您需要使用第三方工具创建一个真正的excel文件(您可能希望使用的工具之一是NPOI)，或者创建一个逗号分隔的文件，或者只是一个csv文件，然后返回该文件。

#3

In case someone else stumbles across this... I needed to convert blobs back into files on-the-fly in C#. Pdf's worked well and excel gave me this same error as OP explains.

万一有人偶然发现……我需要在c#中实时将blobs转换回文件。Pdf的工作很好，excel也给了我同样的错误。

This is the code I wrote which handles excel differently from other file types.

这是我编写的代码，它处理excel与其他文件类型不同。

Giving excel application/octet-stream with an actual filename solved my issue. Probably not the cleanest way to do it but it was good enough for my purposes.

给excel应用程序/octet-stream一个实际的文件名解决了我的问题。也许不是最干净的方法，但这对我来说已经足够好了。

string theExt = Path.GetExtension(theDoc.documentFileName).ToUpper();

Response.Clear();

if (theExt == ".XLS" || theExt == ".XLSX"){
    Response.ContentType = "application/octet-stream";
    Response.AddHeader("Content-Disposition", string.Format("inline; filename={0}", theDoc.documentFileName));
    }
else{
    Response.ContentType = theDoc.documentMimeType;
    Response.AddHeader("Content-Disposition", string.Format("inline; filename={0}", theDoc.documentTitle));
}

using (MemoryStream stream = new MemoryStream(theDoc.file))
{
    stream.WriteTo(Response.OutputStream);
    stream.Close();
};

Response.End();

#4

In case someone needs to export a dataset as excel file with CloseXML.

如果某人需要用CloseXML将数据集导出为excel文件。

Dataset ds = { your data from db }
var xlsx = new XLWorkbook();
var dataTable = ds.Tables[0];

xlsx.Worksheets.Add(dataTable);

xlsx.SaveAs("export.xlsx");