Exporting data to excel; error FILE FORMAT MISMATCH

Question

Tools: MVC 3, Linq to Sql, Asp.net, C#.net Status: Almost newbie to MVC and Linq to sql

Background: Have created a webgrid which shows data from database (sql server 2012).

Problem: trying to export that data to Excel sheet, it's done successfully, Excel shows all data but before all excel throws error ? enter image description here

and after clicking yes it displays data

enter image description here

Controller:

    namespace EmployeeAttendance_app.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = "Precise Technology Consultants";
            var DataContext = new EmployeeAtdDataContext();
            //var EmployeeAtd = DataContext.GetAttendance_Sp();
            IEnumerable<GetAtdRecord_SpResult> EmployeeAtd = DataContext.GetAtdRecord_Sp(null).ToList();
            return View(EmployeeAtd);

        }

        public ActionResult About()
        {
            return View();
        }

        public ActionResult ToExcel()
        {
            var DataContext = new EmployeeAtdDataContext();
            IEnumerable<GetAtdRecord_SpResult> EmployeeAtd = DataContext.GetAtdRecord_Sp(null).ToList();
            System.Web.UI.WebControls.GridView gv = new System.Web.UI.WebControls.GridView();
            gv.DataSource = DataContext.GetAtdRecord_Sp(null).ToList();
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=AttendanceSheet.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
            return RedirectToAction("Index");
        }
    }
}

View:

 @using EmployeeAttendance_app.Models

<div>

@{

    var grid = new WebGrid(ViewData.Model, defaultSort: "EmplID");

 }

@grid.GetHtml()

</div>

Now this code given below is an old one and am trying to use it here in my code, so how ?

public ActionResult ExportData()
        {
            GridView gv = new GridView();
            gv.DataSource = db.Studentrecord.ToList();
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=Marklist.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("StudentDetails");
        }

Community · Accepted Answer · 2017-05-23 12:30:40Z

0

I just saw this worked for some one else here, you just have to incorporate your linq to sql query in there Export data to Excel file with ASP.NET MVC 4 C# is rendering into view

 public ActionResult ExportToExcel()
{
    var products = new System.Data.DataTable("teste");
    products.Columns.Add("col1", typeof(int));
    products.Columns.Add("col2", typeof(string));

    products.Rows.Add(1, "product 1");
    products.Rows.Add(2, "product 2");
    products.Rows.Add(3, "product 3");
    products.Rows.Add(4, "product 4");
    products.Rows.Add(5, "product 5");
    products.Rows.Add(6, "product 6");
    products.Rows.Add(7, "product 7");


    var grid = new GridView();
    grid.DataSource = products;
    grid.DataBind();

    Response.ClearContent();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");
    Response.ContentType = "application/ms-excel";

    Response.Charset = "";
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);

    grid.RenderControl(htw);

    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();

    return View("MyView");
}

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Jan 22, 2014 at 5:59

StackTrace

9,44636 gold badges123 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Evil rising Over a year ago

it works amazingly but throws error in excel before opening. Check my edited code

Omkar Khair Over a year ago

Funny that I am working on the same problem right now. Came here to look for it. Your error is probably because of the code creating an HTML datatable with xls extension. Excel is powerful enough to understand it and show the appropriate sheet, but warns that the extension and content do not match.

donquijote Over a year ago

was there a solution for exporting excel file without the Excel warning on the format?

Omkar Khair · Accepted Answer · 2014-01-22 10:11:17Z

0

The code you are using only creates a HTML text file with your DataTable along with a .xls entsion. Excel is powerful enough to understand the content but warns that the content and extension name are not right.

answered Jan 22, 2014 at 10:11

Omkar Khair

1,3842 gold badges18 silver badges40 bronze badges

Collectives™ on Stack Overflow

Exporting data to excel; error FILE FORMAT MISMATCH

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related