Solved

Formatting GridView for Export to Excel

Posted on 2006-06-09
4
950 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

734 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