Download .xlsx file using Response.TransmitFile()

I'm working on some code that generates an Excel spreadsheet server-side and then downloads it to the user. I'm using ExcelPackage to generate the file.

The generation is working just fine. I can open the generated files using Excel 2007 with no issues. But, I'm having trouble downloading the file with Response.TransmitFile().

Right now, I have the following code:

//Generate the file using ExcelPackage
string fileName = generateExcelFile(dataList, "MyReportData");

Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.ContentType = "application/vnd.xls"
Response.Charset = "";
Response.TransmitFile(fileName);

When Excel 2007 opens the file downloaded as above, it gives the "file format doesn't match extension" warning. After clicking past the warning, Excel displays the raw xml contents of the file.

If I change the file extension, like so

Response.AddHeader("content开发者_StackOverflow-disposition", "attachment;filename=FileName.xlsx");

Excel 2007 gives an "Excel found unreadable content in the file" error, followed by a dialog that offers to locate a converter on the web. If I click "no" on this dialog, Excel is able to load the data.

I've also experimented with different MIME types, like application/vnd.ms-excel and application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, combined with file extensions of .xls and .xlsx. All combinations result in one of the two behaviors mentioned above.

What is the correct combination of file extension and MIME type to use in this scenario? What else could cause this failure, other than an improper MIME type or extension?

FYI, this is occurring with Visual Studio's built-in development web server. I haven't yet tried this with IIS.

---

I can't definitely say that there's anything wrong with your approach, but I'll just share some observations from doing something similar.

Headers are Pascal Case, most browsers shouldn't care but I would change your content-disposition to Content-Disposition. Changing the Charset shouldn't be necessary or relevant. Your content type should be fine, I would only use application/vnd.openxmlformats-officedocument.spreadsheetml.sheet and .xlsx if that is actually the content of the file, otherwise stick with application/vnd.ms-excel and .xls.

Another thing you should consider is sending the browser the Content-Length:

Response.AddHeader("Content-Length", new System.IO.FileInfo("FileName.xlsx").Length);

Also have you tried this with multiple browsers? Just wondering if it's a vendor-specific problem.

As a last ditch effort, you can set your Content-Type to application/octet-stream, and any browser should offer to download it, and then most browsers will let you open it after it's downloaded based on the extension.

---

use this

HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"filename + ".zip" + "\"");
                Response.TransmitFile(zipPath);
                Response.Flush();
                Response.Close();
                Response.End();

in your code is

Response.AddHeader("content-disposition", "attachment;filename=\FileName.xlsx\");

---

Try like this

public void DataTableToExcel(DataTable dt, string Filename)
{
    MemoryStream ms = DataTableToExcelXlsx(dt, "Sheet1");
    ms.WriteTo(HttpContext.Current.Response.OutputStream);
    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + Filename);
    HttpContext.Current.Response.StatusCode = 200;
    HttpContext.Current.Response.End();
}

public static MemoryStream DataTableToExcelXlsx(DataTable table, string sheetName)
{
    MemoryStream result = new MemoryStream();
    ExcelPackage excelpack = new ExcelPackage();
    ExcelWorksheet worksheet = excelpack.Workbook.Worksheets.Add(sheetName);
    int col = 1;
    int row = 1;
    foreach (DataColumn column in table.Columns)
    {
        worksheet.Cells[row, col].Value = column.ColumnName.ToString();
        col++;
    }
    col = 1;
    row = 2;
    foreach (DataRow rw in table.Rows)
    {
        foreach (DataColumn cl in table.Columns)
        {
            if (rw[cl.ColumnName] != DBNull.Value)
                worksheet.Cells[row, col].Value = rw[cl.ColumnName].ToString();
            col++;
        }
        row++;
        col = 1;
    }
    excelpack.SaveAs(result);
    return result;
}