unable to export excel file from girdview or datase from database in asp.net with c#

Question

0.00/5 (No votes)

See more:

I try all thing with the code but the same code I before used not work in my current project. I can retrieve data from the data base if i change visible=true then data also display in the grid view. I try all the function as mentioned bellow but still I am unable to get answer

ASPX Code

code behind c# code

ASP.NET

<asp:GridView ID="gvExport" runat="server" Visible="false" 
                                AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" 
                                GridLines="None">
                                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                                <Columns>
                                    <asp:BoundField DataField="artID" HeaderText="ID" />
                                    <asp:BoundField DataField="artName" HeaderText="Name" />
                                    <asp:BoundField DataField="artType" HeaderText="Category" />
                                    <asp:BoundField DataField="artWork" HeaderText="Work Type" />
                                    <asp:BoundField DataField="artCont1" HeaderText="Contact Person (I)" />
                                    <asp:BoundField DataField="artMob1" HeaderText="Mobile" />
                                    <asp:BoundField DataField="artCont2" HeaderText="Contact person (II)" />
                                    <asp:BoundField DataField="artMob2" HeaderText="Mobile" />
                                    <asp:BoundField DataField="artPhone" HeaderText="Office Phone" />
                                    <asp:BoundField DataField="artEmail" HeaderText="Email ID" />
                                    <asp:BoundField DataField="artStreet" HeaderText="Street" />
                                    <asp:BoundField DataField="artCity" HeaderText="City" />
                                    <asp:BoundField DataField="artState" HeaderText="State" />
                                    <asp:BoundField DataField="artCountry" HeaderText="Country" />
                                </Columns>
                                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                                <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                                <EditRowStyle BackColor="#999999" />
                                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                            </asp:GridView>

and back page code as followed

C#

/*Export User Artist File As MS Excel Format*/
    protected void btExport_Click(object sender, EventArgs e)
    {
        if (((string)Session["userRole"]).Equals("ADMIN") || ((string)Session["userRole"]).Equals("MANAGER"))
        {
            //try
            //{
                exportFile("artist");
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
        }
        else
            SMS("User Doed Not Rights To Export File");
    }
    /*Export Suggested Artist File As MS Excel Format*/
    protected void btSugExport_Click(object sender, EventArgs e)
    {
        if (((string)Session["userRole"]).Equals("ADMIN") || ((string)Session["userRole"]).Equals("MANAGER"))
        {
            try
            {
                exportFile("sugArtist");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        else
            SMS("User Doed Not Rights To Export File");
    }
    /*********File Export From Grid View*/
    private void exportFile(string expportType)
    {
        if (((string)Session["userRole"]).Equals("ADMIN") || ((string)Session["userRole"]).Equals("MANAGER"))
        {
            //try
            //{
                ds.Clear();
                if (expportType.Equals("artist"))
                {
                    if (((string)Session["userID"]).Equals("admin"))
                        ds = dConnect.artistInfo(0, ((string)Session["userID"]), "", "", "", "", "", "", "", "", "", "", "", "", "", "admin");
                    else if (((string)Session["userRole"]).Equals("MANAGER"))
                        ds = dConnect.artistInfo(0, ((string)Session["userID"]), "", "", "", "", "", "", "", "", "", "", "", "", "", "manager");
                    else if (((string)Session["userRole"]).Equals("MANAGER"))
                        ds = dConnect.artistInfo(0, ((string)Session["userID"]), "", "", "", "", "", "", "", "", "", "", "", "", "", "select");
                    fileName = (string)Session["userID"];
                }
                else if (expportType.Equals("sugArtist"))
                {
                    ds = dConnect.artistInfo(0, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "sugExport");           
                    fileName = "Sugested Artist";
                }
                exceprionString = "";
                exceprionString = dConnect.exceptionMessage();

            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
            //try
            //{
                if (ds.Tables[0].Rows.Count > 0 && exceprionString.Equals(""))
                {
                    if (expportType.Equals("artist"))
                    {
                        gvExport.DataSource = ds.Tables[0];
                        gvExport.DataBind();

                        //PrepareGridViewForExport(gvExport);
                        //ExportInExcel(gvExport);

                        ExcelFileExpor(gvExport);

                        //ExportExcelFile(gvExport);

                        //ExcelExportByDataSet(ds);
                    }
                    else if (expportType.Equals("sugArtist"))
                    {
                        gvSugArtistExport.DataSource = ds.Tables[0];
                        gvSugArtistExport.DataBind();

                        //PrepareGridViewForExport(gvSugArtistExport);
                        //ExportInExcel(gvSugArtistExport);

                        ExcelFileExpor(gvSugArtistExport);

                        //ExportExcelFile(gvSugArtistExport);

                        //ExcelExportByDataSet(ds);
                    }
                }
                else
                    SMS("File Not Export Due To Data Not Found");
            //}
            //catch (Exception ex)
            //{
            //    //throw ex;
            //    //SMS("File Unable To Export");
            //    SMS(ex.ToString());
            //}
        }
        else
            SMS("user Does Not Rights To Export File");
    }
    /*Export Excel Sheet*/
    /***********Clear Contro Of Grid View Befor Export To Excel*/
    public override void VerifyRenderingInServerForm(Control control)
    {
    }
    /*Remove Control Before Export The File*/
    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].GetType() == typeof(LinkButton))
            {
                l.Text = (gv.Controls[i] as LinkButton).Text;
                gv.Controls.Remove(gv.Controls[i]);
                gv.Controls.AddAt(i, l);
            }
            else if (gv.Controls[i].GetType() == typeof(DropDownList))
            {
                l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
                gv.Controls.Remove(gv.Controls[i]);
                gv.Controls.AddAt(i, l);
            }
            else if (gv.Controls[i].GetType() == typeof(CheckBox))
            {
                l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
                gv.Controls.Remove(gv.Controls[i]);
                gv.Controls.AddAt(i, l);
            }
            else if (gv.Controls[i].GetType() == typeof(HiddenField))
            {
                l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
                gv.Controls.Remove(gv.Controls[i]);
                gv.Controls.AddAt(i, l);
            }
            if (gv.Controls[i].HasControls())
            {
                PrepareGridViewForExport(gv.Controls[i]);
            }
        }
    }

    /*Export Data From Grid View To Excel **********/
    private void ExportInExcel(GridView grid)
    {
        HtmlForm form = new HtmlForm();
        string attachment = "attachment; filename=" + fileName + ".xls";
        Response.ClearContent();
        Response.AddHeader("removed-disremoved", attachment);
        Response.ContentType = "application/ms-excel";
        StringWriter stw = new StringWriter();
        HtmlTextWriter htextw = new HtmlTextWriter(stw);
        form.Controls.Add(grid);
        this.Controls.Add(form);
        form.RenderControl(htextw);
        Response.Write(stw.ToString());
        //Response.Output.Write(stw.ToString());//
        //Response.Flush();//
        //Response.End();
    
        HttpContext.Current.ApplicationInstance.CompleteRequest();
    }
    /**Export Excel FIle **/
    private void ExcelFileExpor(GridView gv)
    {
        StringWriter tw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        gv.RenderControl(hw);

        Response.ContentType = "application/vnd.ms-excel"; 
        Response.AppendHeader("Content-Disremoved", "attachment; filename="+ fileName +".xls");
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();
    }
    /*Export With Drow Command Like paint The Header text Etc */
    private void ExportExcelFile(GridView GridView1)
    {
        try
        {
            Response.Clear();
            Response.Buffer = true;

            Response.AddHeader("removed-disremoved", "attachment;filename=" + fileName + ".xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);

            GridView1.AllowPaging = false;
            GridView1.DataBind();

            //Change the Header Row back to white color
            GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

            //Apply style to Individual Cells
            GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
            GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
            GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
            GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");

            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridViewRow row = GridView1.Rows[i];

                //Change Color back to white
                row.BackColor = System.Drawing.Color.White;

                //Apply text style to each Row
                row.Attributes.Add("class", "textmode");

                //Apply style to Individual Cells of Alternating Row
                if (i % 2 != 0)
                {
                    row.Cells[0].Style.Add("background-color", "#C2D69B");
                    row.Cells[1].Style.Add("background-color", "#C2D69B");
                    row.Cells[2].Style.Add("background-color", "#C2D69B");
                    row.Cells[3].Style.Add("background-color", "#C2D69B");
                }
            }
            GridView1.RenderControl(hw);

            //style to format numbers to string
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            //Response.End();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
        catch (Exception ex)
        {
            SMS(ex.ToString());
        }
        //Response.End();
    }
    /*Export Excel By DataTable*/
    private void ExcelExportByDataSet(DataSet ddData)
    {
        if (ddData.Tables[0].Rows.Count > 0)
        {
            DataTable dt = new DataTable();
            dt = (DataTable)ddData.Tables[0];

            string attachment = "attachment; filename=" + fileName + ".xls";
            //string attachment = "attachment; filename="+ fileName +".xls";
            //string attachment = "attachment; filename=" + fileName + ".csv";

            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            //Response.ContentType = "application/excel";
            Response.ContentType = "application/vnd.ms-excel";
            //Response.ContentType = "text/csv";

            string tab = "";
            foreach (DataColumn dc in dt.Columns)
            {
                Response.Write(tab + dc.ColumnName);
                tab = "\t";
            }
            Response.Write("\n");

            int i;
            foreach (DataRow dr in dt.Rows)
            {
                tab = "";
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    Response.Write(tab + dr[i].ToString());
                    tab = "\t";
                }
                Response.Write("\n");
            }
            dt.Clear();

            Response.End();
            SMS("File Successfully Export");
        }
        else
            SMS("Unalbe Export File");

    }

Posted 25-Jul-12 18:45pm

Dadu Da

Add a Solution

Comments

Prasad_Kulkarni 26-Jul-12 0:49am

How could one understand with such code what exact the problem is?

Simply post if you're getting any error or exact problem where you think (of course by debugging) that problem must be.

Christian Graus 26-Jul-12 0:52am

This is a huge code dump. Do you know how to use a debugger ? Have you tried to fix your own issue ? Do you know where it doesn't work, and what it does wrong ?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dadu Da · Answer 1 · 2012-07-25T19:27:00

I checked all the code with the other project all the function work good on the other project

I want to display all try to export excel sheet from grid or data set.
That's why I put all the tried code with success in previous project and not in current project.
I know it is a use code
but try to understand my problem I used may thing to get success but still facing the problem If I put the code in try catch the following exception acre

System.Threading.ThreadAbortException: Thread was being aborted.
at task.ExportInExcel(GridView grid) in d:\Event Management Project\eventManagement\task.aspx.cs:line 779
at task.btExport_Click(Object sender, EventArgs e) in d:\Event Management Project\eventManagement\task.aspx.cs:line 705

and always required the following ajax file during debugging, but i am using Ajax framework 3.0 with ASP.NET Framework 3.5

E:\SetUP\AjaxControlToolkit-Framework3.5\AjaxControlToolkit\ExtenderBase\ExtenderControlBase.cs

Raghunatha_Reddy_S · Answer 2 · 2012-07-25T20:01:00

Solution 2

How you filled the gridview, did you have filled the gridview in

C#

!isPostback

in page load

Posted 25-Jul-12 20:01pm

Raghunatha_Reddy_S

Updated 25-Jul-12 20:02pm

v2

Comments

Dadu Da 4-Aug-12 3:05am

I fill it on button click event