How to export data to excel sheet in MVC

Question

I am developing an application in MVC and I want to export data to excel sheet. Currently I am using this code-

 public ActionResult ExportToExcel(int id)
        {
            string UserName = (string)HttpContext.Session["loggedUserName"];

            PurchaseOrderService purchaseOrderService = new PurchaseOrderService();
            PurchaseOrderDTO PurchaseOrder = purchaseOrderService.GetById(Convert.ToInt32(id));

            StringBuilder sb = new StringBuilder();
            sb.Append("<table border=`" + "1px" + "`b>");

            sb.Append("<tr>");
            sb.Append("<td><b><font face=Calibri size=3>Created By : " + UserName + "</font></b></td>");
            sb.Append("</tr>");

            sb.Append("<tr>");
            sb.Append("<td><b><font face=Calibri size=3>Purchase Order No. :" + PurchaseOrder.Id.ToString() + "</font></b></td>");
            sb.Append("</tr>");

            sb.Append("<tr>");
            sb.Append("<td><b><font face=Calibri size=3>Vendor Name : " + PurchaseOrder.VendorName.ToString() + "</font></b></td>");
            sb.Append("</tr>");

            sb.Append("<tr>");
            sb.Append("<td><b><font face=Calibri size=3>Purchase Order Date : " + PurchaseOrder.OrderDate.ToString("dd-MMM-yy") + "</font></b></td>");
            sb.Append("</tr>");

            sb.Append("<tr>");
            sb.Append("</tr>");

            sb.Append("<tr>");
            sb.Append("<td><b><font face=Calibri size=3>SR NO.</font></b></td>");
            sb.Append("<td><b><font face=Calibri size=3>PRODUCT CODE</font></b></td>");
            sb.Append("<td><b><font face=Calibri size=3>VP CODE</font></b></td>");
            sb.Append("<td><b><font face=Calibri size=3>PRODUCT DESCRIPTION</font></b></td>");
            sb.Append("<td><b><font face=Calibri size=3>QUANTITY</font></b></td>");
            sb.Append("</tr>");

            int rowCount = 1;
            var quantity = 0;

            foreach (var item in PurchaseOrder.purchaseOrderItemDTOList)
            {
                if (PurchaseOrder.purchaseOrderItemDTOList.Count > 1)
                {
                    sb.Append("<td><font face=Calibri size=" + "11px" + ">" + rowCount.ToString() + "</font></td>");
                    sb.Append("<td><font face=Calibri size=" + "11px" + ">" + item.Product.ProductCode.ToString() + "</font></td>");
                    sb.Append("<td><font face=Calibri size=" + "11px" + ">" + item.Product.VendorProductCode.ToString() + "</font></td>");
                    sb.Append("<td><font face=Calibri size=" + "11px" + ">" + item.Product.Name.ToString() + "</font></td>");
                    sb.Append("<td><font face=Calibri size=" + "11px" + ">" + item.Quantity.ToString() + "</font></td>");
                    quantity = quantity + item.Quantity;

                }
                sb.Append("</tr>");
                rowCount = rowCount + 1;
            }

            sb.Append("<tr>");
            sb.Append("<td  colspan=3></td>");
            sb.Append("<td><b><font face=Calibri size=3>Total Quantity : </font></b></td>");
            sb.Append("<td><font face=Calibri size=" + "11px" + ">" + quantity.ToString() + "</font></td>");
            sb.Append("</tr>");

            sb.Append("</table>");
            HttpContext.Response.AddHeader("content-disposition", "attachment; filename=PO NO_" + PurchaseOrder.Id +"_"+ DateTime.Now.ToString("dd-MMM-yy") + ".xls");
            this.Response.ContentType = "application/vnd.ms-excel";
            byte[] buffer = System.Text.Encoding.UTF8.GetBytes(sb.ToString());
            return File(buffer, "application/vnd.ms-excel");
        }

This code works properly but when I am trying to open the excel sheet it displays -

The file you are trying to open is in different format than specified by the file extension.

Is there any other way to do that?

possible duplicate of [Create Excel (.XLS and .XLSX) file from C#](http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp) — Andreas, Jul 08 '14 at 07:57

score 1 · Answer 1 · answered Jul 08 '14 at 08:02

You are creating NOT an excel sheet, but an HTML-Table. which is annoying for every excel user since error popups when loading the file are unavoidable. Furthermore, advanced formatting, formulas, validation etc etc. is impossible in this approach.

I would recommend the libary EPPLUS, which provides a very good (and fast!) interface for reading / writing xlsx files. The library has a good licencse, so you don't need to open source your project or other crap.

The libary has a good documentation (sample projects).

http://epplus.codeplex.com/

score 0 · Answer 2 · edited May 23 '17 at 10:32

So it looks like you're trying to open an html table as an .xls document, which I don't think works.

The easiest way to get something into Excel (imo) is to export your data as delimited file (typically either tab or comma).

There are several libraries for creating csv files in c#, but it's pretty easy manually too

StringBuilder sb = new StringBuilder();
//headers
sb.AppendLine("column1, column2, column3, column4");

foreach (var item in dataItemsEnumerable)
{
    sb.AppendFormat("{0},{1},{2},{3}\n", item.value1, item.value2, item.value3, item.value4);
}

File.WriteAllText(sb.ToString, "myFile.csv");

There's also some libraries available if you're set on creating an XLS files. Take a look at this one https://code.google.com/p/excellibrary/

Also this question explains your options really well too: Create Excel (.XLS and .XLSX) file from C#

How to export data to excel sheet in MVC

2 Answers2