how to export a dataset to excel and raise a file download dialog from asp.net webmethod?

Question

Hi all,

I am using the following code to export a datset to excel sheet.

[WebMethod]
    public static void ExporttoExcel()
    {
        DataSet ds;
       productfactory pf=new productfactory();
        ds = pf.getproducts();
        HttpResponse response = HttpContext.Current.Response;

        // first let's clean up the response.object
        response.Clear();
        response.Charset = "";
        response.ContentEncoding = System.Text.Encoding.Default;

        // set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", "attachment;filename=\"products.xls\"");

        // create a string writer
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                // instantiate a datagrid
                DataGrid dg = new DataGrid();                    
                dg.DataSource = ds.Tables[0];
                dg.DataBind();
                dg.RenderControl(htw);
                string filepath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\products.xls";                    
                response.Write(sw.ToString());
               // response.End();

            }
        }       
    }

Problem is that its not raising file download and hence no export is taking place.The same code works fine in a normal method.But with the webmethod its not working.

Please someone help.

Thanks.

Answer 1

I suggest to make an HttpHandler ending in ashx, and place inside him your code that create the excel file.

then call it from your javascript code like that.

document.location.href = "ExporttoExcel.ashx";

Answer 2

The problem is that WebMethods are not designed to allow you to interact with the Response object (evident in that it wasn't available and you had to use HttpContext.Current.Response to get to it). WebMethods are designed to be blackbox to the user. They will perform and action and/or return a value.

Perhaps you can give us a better idea of what you are trying to accomplish and we can suggest an alternate solution.