the file you are trying to open is in a different

问题:

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.

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

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.

回答1:

I have used CloseXML to solve the problem.

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.

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

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.

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

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

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.

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.

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

xlsx.Worksheets.Add(dataTable);

xlsx.SaveAs("export.xlsx");