0

I have got the below method that will do export to excel, My aim is when I click the link on web page I need to export the data from kendo ui grid to excel along with that asp.net MVC4 for that purpose I have written below method.....

the below method is action method that will call when I click exporttoexcel action link on view 

    public ActionResult ExportToExcel()
    {

        byte[] file;
        string targetFilename = string.Format("{0}-{1}.xlsx", "Generated", "excel");

        DataTable dt = common.CreateExcelFile.ListToDataTable(GetSearchDraftPRResults());
        common.CreateExcelFile excelFileForExport = new CreateExcelFile();
        file = excelFileForExport.CreateExcelDocumentAsStream(dt, targetFilename);
        Response.Buffer = true;
        return File(file, "application/vnd.ms-excel", targetFilename);          
    }

and the below method is for creating excel document 

    public byte[] CreateExcelDocumentAsStream(DataTable dt, string filename)
    {

        DataSet ds = new DataSet();
        ds.Tables.Add(dt);
        System.Web.HttpResponse Response = null;

        System.IO.MemoryStream stream = new System.IO.MemoryStream();
        using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
        {
            WriteExcelFile(ds, document);
        }
        stream.Flush();
        stream.Position = 0;

        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";

        Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
        Response.AddHeader("content-disposition", "attachment; filename=" + filename);
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        byte[] data1 = new byte[stream.Length];
        stream.Read(data1, 0, data1.Length);
        return stream.ToArray();
    }

but when I click the action link I am getting error NullReferenceexpection at this line 

   Response.Clear();

I am not sure why I am getting this this exception and I am using open xml dll for export to excel

I am not sure about this procedure, Is this is right way for export to excel functionality .. would any one pls guide me in correct direction ...

Would any one please help on this that would be very grateful to me

many Thanks in advance....

Enigma State
  • 16,494
  • 25
  • 86
  • 179
  • possible duplicate of [What is a NullReferenceException and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – Daniel Kelley Jun 12 '14 at 09:38
  • 2
    You set `Response` to null in this line - `System.Web.HttpResponse Response = null;`. There doesn't appear to be any activity with `Response`, so it's not surprising you get an NRE on `Response.Clear();`. – Tim Jun 12 '14 at 09:40
  • What do you expect? In line 6 of `CreateExcelDocumentAsStream()` you're initializing `Response` with `null`. – haim770 Jun 12 '14 at 09:41
  • @Tim is there any other way to do this ..... – Enigma State Jun 12 '14 at 09:42
  • 1
    @pratapk - Don't set `Response` to null. – Tim Jun 12 '14 at 09:43
  • @haim770 I am expecting that this method CreateExcelDocumentAsStream() should return byte[] array .. – Enigma State Jun 12 '14 at 09:43
  • @pratapk - this has nothing to do with the stream. It has **everything** to do with `Response` being set to null. You can't call a method on an instance of an object that is null. – Tim Jun 12 '14 at 09:44

1 Answers1

2

You really don't need to mess with Response in the CreateExcelDocumentAsStream() method. As the name implies, the only responsibility of the method is to create the Excel file and return it as a byte[] array.

It's the (MVC) Action responsibility then to set the appropriate response headers and behavior to accommodate the client (Web Browser, in this case) needs.

Also, when you return a FileResult, it takes care of setting the Http response, no Response.Clear() or Response.Buffer needed in the Action as well.

haim770
  • 45,540
  • 6
  • 91
  • 121
  • what are the changes that I need to do in this method 'CreateExcelDocumentAsStream()' so that it will return byte array ..would you please suggest..... – Enigma State Jun 12 '14 at 09:52
  • Assuming the rest of the method is fine and the Stream is being read correctly, just get rid of all the lines starting with `Response`. – haim770 Jun 12 '14 at 09:54