How do I export to Excel?

Question

I followed this guide which was recommended in another post about this but I can't quite get it to work. I get a 404 error when trying to redirect to /LogModelsController/ExportData which - as I understood it - was what I was supposed to do.

_LogPartialLayout.cshtml

@using (Html.BeginForm("ExportData", "LogModelsController", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <table class="table">
        <tr>
            <th>
                @Html.ActionLink("message", "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })

            </th>
            <th>
                @Html.ActionLink("timestamp", "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
            <th>
                @Html.ActionLink("level", "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>

        </tr>

        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.message)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.timeStamp)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.level)
                </td>
            </tr>
        }
    </table>
    <input type="submit" name="Export" id="Export" value="Export" />
}

LogModelsController.cs

public ActionResult ExportData()
        {
            GridView gv = new GridView();
            gv.DataSource = db.Log.ToList();
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=Loglist.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();

            return RedirectToAction("~/LogModels/Index");
        }

LogModel.cs

namespace ASDMVC.Models
{
    [Table("Log")]
    public class LogModel
    {
        [Key]
        public long id { get; set; }
        public string message { get; set; }
        public DateTime timeStamp { get; set; }
        public string level { get; set; }
        public int customerId { get; set; }
    }

    public class LogDBContext:DbContext
    {
        public LogDBContext() : base("MySqlConnection")
        {

        }

        public DbSet <LogModel> Log { get; set; }

    }
}

TD;DR: How do I properly export this table to Excel?

Bonus: Does it work with PagedList too?

---

Edit 3 - SOLVED

Index.cshtml:

@using (Html.BeginForm("ExportData", "LogModels"))
{
    <input type="submit" name="Export" id="Export" value="Export" />
}

LogModelsController.cs

public ActionResult ExportData()
{
    DataTable dataTable = GetData();

    using (ExcelPackage package = new ExcelPackage())
    {
        var ws = package.Workbook.Worksheets.Add("My Sheet");
        //true generates headers
        ws.Cells["A1"].LoadFromDataTable(dataTable, true);

        var stream = new MemoryStream();
        package.SaveAs(stream);

        string fileName = "myfilename.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.Position = 0;
        return File(stream, contentType, fileName);
    }
}

public DataTable GetData()
{
    DataTable dt = new DataTable();
    if (ModelState.IsValid)
    {
        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString))
        {
            using (SqlCommand comm = conn.CreateCommand())
            {
                comm.Parameters.AddWithValue("@val1", Session["myID"]);
                comm.CommandText = "SELECT * FROM dbo.Log WHERE CustomerId = @val1";
                try
                {
                    conn.Open();
                    dt.Load(comm.ExecuteReader());
                }
                catch (SqlException e)
                {
                    throw new Exception(e.ToString());
                }
            }
        }
    }
    return dt;
}

I made a mistake in the CommandText and put "Id" instead of "CustomerId" - it now returns the entire table in the Excel file.

Answer 1

The guide doesn't show how to create an Excel file, it shows a hack that creates an HTML file with a Table. Excel will try to import csv or HTML files with tables using default settings which can often fail.

It's far easier to create a proper Excel file using a library like EPPlus. I'm sure this has been answered before but I can't find an answer that shows both how to create an Excel file from data and set the proper content type for an xlsx file.

EPPlus provides the LoadFromDataTable and LoadFromCollection methods to fill an Excel range with data pulled from a DataTable or generic collection. The column or property names are used as headers respectively.

public ActionResult ExportData()
{

    //Somehow, load data to a DataTable

    using (ExcelPackage package = new ExcelPackage())
    {
        var ws = package.Workbook.Worksheets.Add("My Sheet");
        //true generates headers
        ws.Cells["A1"].LoadFromDataTable(dataTable, true);

        var stream = new MemoryStream();
        package.SaveAs(stream);

        string fileName = "myfilename.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.Position = 0;
        return File(stream, contentType, fileName);
    }
}

LoadFromDataTable or LoadFromCollection return an Excel cell range which can be used to format the table:

var range=ws.Cells["A1"].LoadFromDataTable(table);
range.Style.Numberformat.Format = "#,##0.00";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

You can also apply a table style to the range, or pass the desired style to LoadFromDataTable or LoadFromCollection, eg:

ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);

The library's Githup wiki shows how to format the result, generate formulas, PivotTables, handle tables etc.

Saving to a MemoryStream may be an issue if you want to create a relatively large sheet. Writing the same data twice, once to the MemoryStream and then copying it to output, wastes cycles. Writing directly to the output stream from an MVC action is a bad practice though. The trick is to create a custom FileResult that handles EPPlus packages and return that instead of the FileResult returned by File, eg:

public class EpplusResult:FileResult
{
    public EpplusResult(ExcelPackage package)
        : base("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    {
        if (package == null)
        {
            throw new ArgumentNullException("package");
        }

        Package = package;
    }

    public ExcelPackage Package { get; private set; }

    protected override void WriteFile(HttpResponseBase response)
    {
        // grab chunks of data and write to the output stream
        Stream outputStream = response.OutputStream;
        using (Package)
        {
            Package.SaveAs(outputStream);
        }
    }
}

Which allows you to write the following action:

public FileResult ExportData()
{

    ExcelPackage package = new ExcelPackage();
    var ws = package.Workbook.Worksheets.Add("My Sheet");       
    ...
    ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);

    return new EpplusResult(package){FileDownloadName = "SomeFile.xlsx"};
}

Answer 2

Don't create HTML based excel files, they have so many problems.

Insteed use a compact library for that such as epplus.

public ActionResult ExportData()
{

    using (ExcelPackage package = new ExcelPackage())
    {
        var ws = package.Workbook.Worksheets.Add("LogMessages");
       //Headers
        ws.Cells["A1"].Value = "Message";
        ws.Cells["B1"].Value = "TimeStamp";
        ws.Cells["C1"].Value = "Level";


       var rowNumber=1;

        foreach (var log in DbContext.Log)
       {
           ws.Cells[rowNumber, 1].Value = vehicle.message;
           ws.Cells[rowNumber, 2].Value = vehicle.timeStamp;
           ws.Cells[rowNumber, 3].Value = vehicle.level;
           rowNumber++;
       }


        var stream = new MemoryStream();
        package.SaveAs(stream);

        string fileName = "logMessags.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.Position = 0;
        return File(stream, contentType, fileName);
    }
}

I have used Panagiotis Kanavos code structure to implement this solution

Answer 3

Check this out, This library is containing an ExcelResult that will automate the process of creation, and send Excel file to the client by calling Excel() method just like Json().

https://github.com/iabdelkareem/MVC-Excel-Extensions