0

I have a user control with a Grid. Now I have added an export to excel button on the Same user grid just above the grid to enable user to export the corresponding Grid's data to Excel. I wrote the following function on button click even t in User control.

protected void Home_ExportExcel_Click(object sender, EventArgs args)
    {
        DataTable resultTbl = new DataTable();
        if (this.HomeGridDataSource != null)
            resultTbl = this.HomeGridDataSource as DataTable;
        Download(resultTbl, this.MasterPage.CurrentLibrary);
    }

the Download Function is 

private void Download(DataTable tb)
    {
        string attachment = "attachment; filename=HomeGridData" + DateTime.Now.ToString() + ".xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/vnd.ms-excel";
        string tab = "";
        foreach (DataColumn dc in tb.Columns)
        {
            Response.Write(tab + dc.ColumnName);
            tab = "\t";
        }
        Response.Write("\n");
        int i;
        foreach (DataRow dr in tb.Rows)
        {
            tab = "";
            for (i = 0; i < tb.Columns.Count; i++)
            {
                Response.Write(tab + dr[i].ToString());
                tab = "\t";
            }
            Response.Write("\n");
        }
        Response.End();
    }

Now I get the response for the request as a text format but I need this to be downloaded to the user machine with the Grid data.

I also tried generating .xml file with the Excel styles etc.. but I get the Same result as a text format in response object but I am expecting it to be downloaded.

What am I missing here?

Programmerzzz
  • 1,237
  • 21
  • 48
  • You could try building up a Response in HTML instead of CSV/TSV, http://stackoverflow.com/a/39314154/495455 by the way **XLS** file extension have a MimeType of *application/vnd.ms-excel* perhaps it didn't work with the XML attempt because you didn't use the **XLSX** extension with MimeType *application/vnd.openxmlformats-officedocument.spreadsheetml.sheet* – Jeremy Thompson Dec 19 '16 at 04:33
  • An interesting observation is , if we usethe Same code from codebehind of .aspx file. it returns the Excel file as expected. But for some reason it don't work when we put it in .ascx file – Programmerzzz Dec 19 '16 at 04:50
  • That is wierd, that shouldn't be a problem, maybe you need to set `Buffer` &/or `EnableViewState` see here https://forums.asp.net/post/4317346.aspx – Jeremy Thompson Dec 19 '16 at 04:54
  • Hi, The given thread talks about rendering excel file in ASP.NET user control but not on how to download it. Am I missing something? more over I tried setting the `Buffer =true` and `ViewState=false` but no luck. Any ideas of what am I overlooking? – Programmerzzz Dec 19 '16 at 20:47

1 Answers1

0

Setting up content type as Excel file is not enough for client to consider response as a Excel file. Your server responds by textual information that is treated by client as a common response. Even though user saves your response to Excel file, the file will not be compliant with the format because it is text. The better way to work this out is to generate an Excel file using some library. For example., EPPlus. Using that library you can generate an Excel file in memory (without creating a physical file on disk) and then return it as a stream using Response.OutputStream.Write. A good example of how to respond by stream\file is in the following topic.

Community
  • 1
  • 1
Yuriy Tseretyan
  • 1,676
  • 16
  • 17
  • An interesting observation is , if we usethe Same code from codebehind of .aspx file. it returns the Excel file as expected. But for some reason it don't work when we put it in .ascx file. – Programmerzzz Dec 19 '16 at 04:49
  • Kindly report on your troubleshooting and results with the suggested link: http://stackoverflow.com/questions/27974374/returning-a-downloadable-file-using-a-stream-in-asp-net-web-forms – Jeremy Thompson Dec 27 '16 at 08:58