Trouble exporting to excel from mvc action

Question

I created a simple action to download some content as excel file:

public FileResult ExportToExcel()
    {
        string filename = "list.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        List<string[]> list = new List<string[]>();
        list.Add(new[] { "col1", "col2", "cols3" });
        list.Add(new[] { "col4", "col5", "cols6" });
        list.Add(new[] { "col7", "col8", "cols9" });

        StringWriter sw = new StringWriter();

        sw.WriteLine("ID,Date,Description");

        foreach (string[] item in list)
        {
            sw.WriteLine("{0},{1},{2}", item[0], item[1], item[2]);
        }

        byte[] fileContents = Encoding.UTF8.GetBytes(sw.ToString());

        return this.File(fileContents, contentType, filename);
    }

I have 2 issues with it:

1. The file is downloaded but I cannot open it and am getting a warning:

Excel cannot open the file ... because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

When I use old excel format:

        string filename = "List.xls";
        string contentType = "application/vnd.ms-excel";

I am able to open the file but after 3 different warnings about file being corrupted etc.

Btw I compared saving and tried to write file as pdf

        string filename = "List.pdf";
        string contentType = "application/pdf";

And I still couldn't open the file - it said format is not valid etc.

2. The contents appear in the file in the second example however the commas are not recognised as column separators and all data in a row is written as one column.

What separator to use for excel format or how to write data to file to have it in a table excel format?

Ideal solution for me would be just return exported view (strongly typed) but I didn't find out how to do it so far.

--- EDIT: Working solution ---

public FileResult ExportToExcel()
    {
        string filename = "List.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        List<string[]> titles = new List<string[]>() { new[] { "a", "be", "ce" } };
        List<string[]> list = new List<string[]>
                                  {
                                      new[] { "col1", "col2", "cols3" },
                                      new[] { "col4", "col5", "cols6" },
                                      new[] { "col7", "col8", "cols9" },
                                      new[] { "col10", "col11", "cols12" }
                                  };

        XLWorkbook wb = new XLWorkbook();
        XLTables xt = new XLTables();
        var ws = wb.Worksheets.Add("List");
        ws.Cell(1, 1).InsertData(titles);
        ws.Cell(2, 1).InsertData(list);
        ws.Columns().AdjustToContents();

        var stream = new MemoryStream();
        wb.SaveAs(stream);
        stream.Seek(0, SeekOrigin.Begin);
        wb.Dispose();

        return this.File(stream, contentType, filename);
    }

Answer 1

The reason why it is not being correctly rendered is because you cannot just return the mime type and expect the framework to figure out the rest.

I would go with a nuget package called closedXML which will allow you to create an excel file in memory and stream it back to the client.

it comes with a full documentation (here) for more information.

Using this package you can do something like

XLWorkbook wb = new XLWorkbook();
XLTables xt = new XLTables();
var ws = wb.Worksheets.Add("Sheet 1");
var firstCell = ws.Cell(1, 1);
var lastCell = ws.Cell(3, list.Count);
var table = ws.Range(firstCell.Address, lastCell.Address).AsTable();
table.Cell(2, 1).InsertData(list);
table.CreateTable();
ws.Columns().AdjustToContents();

using(var stream = new MemoryStream())
{
    wb.SaveAs(stream);
    stream.Seek(0, SeekOrigin.Begin);
    wb.Dispose();
    return File(stream , contentType, filename);
}