Solved

Formatting GridView for Export to Excel

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

822 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