Excel spreadsheet generation results in “different

Question

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.

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.

Here is an ASP.NET page which generates the problem:

<%@ 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

Answer 1

Use

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

And specify extension as xlsx

Answer 2

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.

Answer 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/

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.

Answer 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

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();
        }
    }

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

Answer 6

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

Added to the XML output:

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