Link to home
Start Free TrialLog in
Avatar of HangTenDesign
HangTenDesign

asked on

Formatting GridView for Export to Excel

Hello,

I have set up a "Download Excel" button on my Web Form containing a data bound GridView. I've managed to change the formatting to an extent before rendering the control for Excel. However, I have hit a wall. I still need to remove the URLs that are being passed to the spreadsheet. The header still maintains the PostBack scripting for the sorting of columns even though I set sorting and paging to "false" at runtime before rendering. And a column is still containing the URL "DataNavigateUrlFormatString" for a details page. I need the spreadsheet to just house the data and nothing else.

Below is the code I have so far: (BTW, i LOVE the fact I don't need a second page to do this like classic ASP!!)

    public override void VerifyRenderingInServerForm(Control control)
    {
    }

    protected void ExcelButton_Click(object sender, ImageClickEventArgs e)
    {
        GridView1.AllowPaging = false;
        GridView1.AllowSorting = false; // THIS IS NOT WORKING
        GridView1.AlternatingRowStyle.ForeColor = Color.FromName("#ffffff");
        GridView1.HeaderStyle.BackColor = Color.FromName("#ffffff");
        GridView1.HeaderStyle.ForeColor = Color.FromName("#000000"); // THIS IS NOT WORKING
        GridView1.HeaderStyle.Font.Bold = true; // THIS IS NOT WORKING
        GridView1.BorderColor = Color.FromName("none");

        Response.ClearHeaders();
        Response.ContentType = "application/vnd.ms-excel";
        Response.Charset = "";
        Page.EnableViewState = false;
        System.IO.StringWriter sWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sWriter);

        GridView1.RenderControl(hw);

        string sContent = sWriter.ToString();
        Response.Clear();
        string filename = "conversions.xls";
        Response.AddHeader("Content-Disposition", "FileName = " + filename);
        Response.Write(sContent);
        Response.End();

    }

Avatar of salan_alani
salan_alani
The below code works for me:

ASPX code:

<body>
    <form id="form1" runat="server">    
        <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
            GridLines="None" AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging" AllowSorting="True">
            <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <br />
        <asp:Button ID="btnExcel" runat="server" Text="Download Excel" OnClick="btnExcel_Click" />
    </form>
</body>


C# code behind:

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string DateI = "TO_DATE('05/12/2005','DD/MM/YYYY')";
            string sql = "SELECT BSC,SEG_ID,SEG,CI,NCC,BCC,LAC,DTX,MS_PMAX1,MS_PMAX2,DMAX,FRL,FRU,GENA,NSEI FROM SEG_PAR WHERE DATEI=" + DateI + " ORDER BY BSC,SEG";

            DataSet dsNetwork = new DataSet();
            OracleConnection MyConnection = new OracleConnection("Data Source=orcl;Persist Security Info=True;User ID=FCCI_BH;Password=fcciadmin;Unicode=True");
            OracleDataAdapter daNetwork = new OracleDataAdapter(sql, MyConnection);
            daNetwork.Fill(dsNetwork, "dtNetwork");

            GridView1.DataMember = "dtNetwork";
            GridView1.DataSource = dsNetwork;            
            GridView1.DataBind();

            daNetwork.Dispose();
            dsNetwork.Dispose();
            MyConnection.Dispose();
        }
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        if (GridView1.EditIndex != -1)
        {
            e.Cancel = true;
        }
        else
        {
            string DateI = "TO_DATE('05/12/2005','DD/MM/YYYY')";
            string sql = "SELECT BSC,SEG_ID,SEG,CI,NCC,BCC,LAC,DTX,MS_PMAX1,MS_PMAX2,DMAX,FRL,FRU,GENA,NSEI FROM SEG_PAR WHERE DATEI=" + DateI + " ORDER BY BSC,SEG_ID";

            DataSet dsNetwork = new DataSet();
            OracleConnection MyConnection = new OracleConnection("Data Source=orcl;Persist Security Info=True;User ID=FCCI_BH;Password=fcciadmin;Unicode=True");
            OracleDataAdapter daNetwork = new OracleDataAdapter(sql, MyConnection);
            daNetwork.Fill(dsNetwork, "dtNetwork");

            GridView1.DataMember = "dtNetwork";
            GridView1.DataSource = dsNetwork;
           
            GridView1.PageIndex = e.NewPageIndex;            
           
            GridView1.DataBind();

            daNetwork.Dispose();
            dsNetwork.Dispose();
            MyConnection.Dispose();            
        }
    }

    public override void VerifyRenderingInServerForm(Control GridView1)
    {
        // Do nothing
    }

    protected void btnExcel_Click(object sender, EventArgs e)
    {
        GridView1.AllowPaging = false;
        GridView1.AllowSorting = false;

        GridView1.RowStyle.ForeColor = Color.FromName("#000000");
        GridView1.RowStyle.BackColor = Color.FromName("none");
        GridView1.AlternatingRowStyle.ForeColor = Color.FromName("#000000");
        GridView1.AlternatingRowStyle.BackColor = Color.FromName("none");
        GridView1.HeaderStyle.Font.Bold = true;
        GridView1.HeaderStyle.ForeColor = Color.FromName("#000000");        
        GridView1.HeaderStyle.BackColor = Color.FromName("none");
        GridView1.BorderColor = Color.FromName("#000000");
        GridView1.GridLines = GridLines.Both;        
       
        string DateI = "TO_DATE('05/12/2005','DD/MM/YYYY')";
        string sql = "SELECT BSC,SEG_ID,SEG,CI,NCC,BCC,LAC,DTX,MS_PMAX1,MS_PMAX2,DMAX,FRL,FRU,GENA,NSEI FROM SEG_PAR WHERE DATEI=" + DateI + " ORDER BY BSC,SEG";

        DataSet dsNetwork = new DataSet();
        OracleConnection MyConnection = new OracleConnection("Data Source=orcl;Persist Security Info=True;User ID=FCCI_BH;Password=fcciadmin;Unicode=True");
        OracleDataAdapter daNetwork = new OracleDataAdapter(sql, MyConnection);
        daNetwork.Fill(dsNetwork, "dtNetwork");

        GridView1.DataMember = "dtNetwork";
        GridView1.DataSource = dsNetwork;
        GridView1.DataBind();

        daNetwork.Dispose();
        dsNetwork.Dispose();
        MyConnection.Dispose();


        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=SEG.xls");
        Response.Charset = "";
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        GridView1.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }
}


More details:
http://forums.asp.net/1/986762/ShowThread.aspx (not the same request, but the discussion will pass through it)
http://gridviewguy.com

Avatar of HangTenDesign
HangTenDesign

ASKER

Sorry, I'm not exactly sure what you are saying works for you in all that code. Can you elaborate on how your code differs from my example above and what I should do to eliminate the URLs from the GridView control??? Simply setting sorting to false is not doing it for me.
ASKER CERTIFIED SOLUTION
Avatar of salan_alani
salan_alani
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HangTenDesign
HangTenDesign

ASKER

THAT WAS EXACTLY IT!!!!!  Rebind the data. Thank you SO much.