Solved

Formatting GridView for Export to Excel

Posted on 2006-06-09
4
953 Views
Last Modified: 2008-01-09
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();

    }

0
Comment
Question by:HangTenDesign
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 2

Expert Comment

by:salan_alani
ID: 16876143
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

0
 

Author Comment

by:HangTenDesign
ID: 16883087
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.
0
 
LVL 2

Accepted Solution

by:
salan_alani earned 500 total points
ID: 16883524
I gave you one of my examples. Actually, I don't know how your GridView is built, for me I bind the GridView control within the code-behind (not within the ASPX code). So whenever I want to change the formatting (any formatting changes) I need to re-DataBind the GridView againg. My code is not big, but sometimes the formatting will not take action unless you re-DataBind the GridView again.
0
 

Author Comment

by:HangTenDesign
ID: 16889071
THAT WAS EXACTLY IT!!!!!  Rebind the data. Thank you SO much.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question