locked
Export Grid View Data to Excel sheet RRS feed

  • Question

  • I have a GridView and its retrieving all data from the SQL Server data base. and when i click a cmd button "Export to Excel" it just downloads an excel file. but i am getting an error  while opening the file, while other excel files are opening as usual. My code is as below.

    private void PrepareGridViewForExport(Control gv)

            {
                LinkButton lb = new LinkButton();
                Literal l = new Literal();
                string name = String.Empty;
                for (int i = 0; i < gv.Controls.Count; i++)
                {
                    if (gv.Controls[i].HasControls())
                    {
                        PrepareGridViewForExport(gv.Controls[i]);
                    }
                }
            }


     private void Export_GridView()
            {
                string attachment = "attachment; filename=ProductInfo.xls";
                Response.ClearContent();
                Response.AddHeader("content-disposition", attachment);
                Response.ContentType = "application/ms-excel";
                StringWriter sw1 = new StringWriter();
                HtmlTextWriter htw1 = new HtmlTextWriter(sw1);
                grd_ProdInfo.RenderControl(htw1);
                Response.Write(sw1.ToString());
                Response.End();
            }

    public override void VerifyRenderingInServerForm(Control control)
            {

            }

    protected void cmdExport_Click(object sender, EventArgs e)
            {
                PrepareGridViewForExport(grd_ProdInfo);
                Export_GridView();
            }


    Wednesday, May 9, 2012 5:50 AM

Answers

All replies

  • Have you tried opening the file manually?  Sometimes excel will have errors if a file is empty.  Have you also tried running the Microsoft Office Application Recovery Application?

    jdweng

    Wednesday, May 9, 2012 8:47 AM
  • Yes I tried. but i was unable to open that excel file since the visible property of the grid view was false. so, wen i was trying to pass it by a htmlwriter object, it cant identify. Since i need not have to show the  grid view to the user i just made the visible true in start of the click event and visible false at the end of the click event. so now it works.

    So, i have a doubt now further.

    i have grid view having 3 bound fields and 4 template fields. i would like to export all the fields in the excel sheet. can u please help me out in this regarding ?


    Rupesh

    Wednesday, May 9, 2012 11:27 AM
  • I've been having problems myself opening up excel files when they are still attached to processes in Visual Studio.  ?We first need to determine if the excel file is good to determine if the problem is with the creating of the excel file or the reading of the excel file.  Your problem may just be that the ecxcel file is no getting closed properly. So first try stopping visual Studio.  Then check the following

    1) Check the task manager to make sure there is no excel.exe process running.  If there are any excel.exe running kill the process. 

    2) Try opening up the excel file and see if the excel file contains any data and check if the data is in the correct locations.

    3) Check the excel worksheet to make sure it is in a table format

           a) All the rows contain data in the primary key column (usually the first column)

           b) There are no blank rows of data before tthe last row of data

           c) There is a header row in row number 1 with text in some of the columns

           d) Check to see the number of worksheets in the workbook.


    jdweng

    Wednesday, May 9, 2012 11:40 AM
  • Run fiddler to see what is downloaded HTML. Post to http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads and ask how to fix the HTML so excel can open it even when you declare the content type as an excel file, then post to http://forums.asp.net/18.aspx/1?Web+Forms and ask how to generate the fixed HTML in asp.net.


    The following is signature, not part of post
    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Visual C++ MVP


    Thursday, May 10, 2012 4:24 PM
  • Hi Rupesh ,

    you should first check that whether the excel sheet is in correct format or no.


    With Thanks

    Sidhanta Tripathy


    • Edited by Sidhanta Wednesday, May 30, 2012 8:57 AM
    Wednesday, May 30, 2012 8:57 AM
  • This is truly a helpful question with good answers to me on solving the gridview data export to excel problem. I am recently also dealing with some works on the datum, involving problems towards a UI gridview control. I want to export the grid data to excel but can't find a practical guide, this do resolve much of my doubts.
    Wednesday, April 2, 2014 2:32 AM