0

I am trying to import data from excel to mvc

public void DownloadAsExcelOrderReports()
{
    try
    {
        var list = _reportWork.GetList();
        GridView gv = new GridView();
        gv.DataSource = list;
        gv.DataBind();
        Response.ClearContent();
        Response.Buffer = true;
        Response.ContentEncoding = System.Text.Encoding.UTF32;
        Response.AddHeader("content-disposition", "attachment; filename=Marklist.xls");
        Response.ContentType = "application/ms-excel";
        Response.Charset = "";
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                gv.RenderControl(htw);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
        }
    }
    catch (Exception ex)
    {

    }
}

This is the code I found online.I get an output like this. enter image description here Normally it has to fill in the table itself automatically into excel.I can't see, what i missed.

Selman
  • 97
  • 2
  • 12
  • it seems it doesnt like the encoding given to the file. in the first row of values I see an &nbsp, the second has a &amp and an &nbsp. this means that the encoding has broken the result. – Headhunter Xamd Nov 10 '16 at 08:51
  • **I trying export data to excel from MVC. I don't understand why;**. what? – SᴇM Nov 10 '16 at 08:51
  • @SeM There is explanation below, focus on the subject – Selman Nov 10 '16 at 09:00
  • @HeadhunterXamd Could you give a little more detail? – Selman Nov 10 '16 at 09:03
  • 1
    Some of the characters do not exist in the encoding excel uses, I think this view is caused by those encoding errors, it finds an unreadable character and then just stops parsing, resulting in this view. The best way to solve this is to set an encoding in the HTMLWriter or the StringWriter. – Headhunter Xamd Nov 10 '16 at 09:11
  • @HeadhunterXamd there are no encoding issues with *Excel*. Problems appear when people try to fake it by generating HTML tables instead of actual XLSX files. – Panagiotis Kanavos Nov 10 '16 at 09:29
  • 1
    @SelmanErhan your code creates HTML, not Excel. It's one of the hacks used to fake Excel generation. There is no need at all though, just use a tool like EPPlus to generate *real* Excel files. There's even a sample [that shows how to generate an Excel file on the server](http://epplus.codeplex.com/wikipage?title=WebapplicationExample) – Panagiotis Kanavos Nov 10 '16 at 09:30
  • @PanagiotisKanavos your answer is correct solution thanks. – Selman Nov 10 '16 at 13:00
  • @HeadhunterXamd The problem is exactly where you say thanks – Selman Nov 10 '16 at 13:03

1 Answers1

1

GridView is a WebControl. You put data into it (DataBind) and it emits html (RenderControl) which is supposed to be sent to the browser and the browser will show it as a table (tr and td tags).

In your case you're trying to show that html code within an excel document and that makes no sense.

The best I've found is to use EPPlus, which you can get from NuGet Package Manager. You have to tie together your data to what you want the excel doc to look like. It's a bit fiddly but there's no alternative.

Trevor Cousins
  • 233
  • 2
  • 7
  • It's not fiddly, it's a *lot* easier than attempts to fake Excel files. What's fiddly about `LoadFromDataTable` ? – Panagiotis Kanavos Nov 10 '16 at 09:29
  • Perhaps it's best not to add answers to a question that has already been marked as a duplicate. Also this is a general suggestion of a library or tool, rather than a tangible answer to the direct question. – Quintin Balsdon Nov 10 '16 at 10:19