Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Formatting GridView for Export to Excel

Posted on 2006-06-09
4
Medium Priority
?
958 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 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Integration Management Part 2
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses
Course of the Month15 days, 6 hours left to enroll

577 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