Dadu Da

Dadu Da

  • NA
  • 43
  • 35.5k

Unable to export excel file from grid view or dataset

Jul 26 2012 12:29 AM
I try many thing with the code but the same code I before used not work in my current project. I can feth data from the data base if i change visible=true then  data also display in the grid view
following are the code

aspx page code

<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>



following are back page code

DataBaseConnection.dataBase dConnect = new dataBase();
    DataSet ds = new DataSet();
    DataTable dt = new DataTable();
    static string exceprionString = "", artType = "", searchCity = "", searchType = "", searchCont = "", searchSugType = "", searchSugCIty = "", fileName = "";
    static int i = 0, j = 0, artID = 0;
    static List<string> listArt = new List<string>();


/*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("content-disposition", 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-Disposition", "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("content-disposition", "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");

    }



Answers (4)