Data Access and ObjectDataSource Control

Re: connect sql datasource to excel in .net

Trini_NAtwaroo — Mon, 06 Jul 2009 15:12:55 GMT

hey thanks for all your help, i used the code below to add the text before exporting to excel.

protected void Page_Load(object sender, EventArgs e)
        {
            VerifyRenderingInServerForm(form1);
            gvToExport.GridLines = GridLines.Both;

        }
        protected void btnExport_Click(object sender, EventArgs e)
        {
            Panel p = new Panel();
            Panel p2 = new Panel();
            Panel p3 = new Panel();
            Panel p4 = new Panel();
            Label lb = new Label();
            Label lb2 = new Label();
            Label lb3 = new Label();
            Label lb4 = new Label();
            lb.Text = "Company Name
            lb2.Text = "Title of report:";
            lb3.Text = "1st March to 31st March 2008"; // test
            lb4.Text = "------";
            p.Controls.Add(lb);
            p2.Controls.Add(lb2);
            p3.Controls.Add(lb3);
            p4.Controls.Add(lb4);
                
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.xls";
            //StringWriter StringWriter = new System.IO.StringWriter();
           // HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
           // gvToExport.RenderControl(HtmlTextWriter);
           // Response.Write(StringWriter.ToString());
           // Response.End();
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite =
            new HtmlTextWriter(stringWrite);
            // turn off paging 
            gvToExport.AllowPaging = false;
            gvToExport.DataBind();
            p.RenderControl(htmlWrite);
            p2.RenderControl(htmlWrite);
            p3.RenderControl(htmlWrite);
            p4.RenderControl(htmlWrite);
            gvToExport.RenderControl(htmlWrite);          
            
            Response.Write(stringWrite.ToString());
            Response.End();
            // turn the paging on again 
            gvToExport.AllowPaging = true;
            gvToExport.DataBind();

        }

        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Verifies that a Form control was rendered */
        }

        protected void gvToExport_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gvToExport.PageIndex = e.NewPageIndex;
        }

Thanks

Re: connect sql datasource to excel in .net

hans_v — Mon, 06 Jul 2009 13:37:27 GMT

I didn't have a change to try it, but a couple of days ago I found a very promising solution on CodePlex:

ExcelPackage: Office Open XML Format file creation

Re: connect sql datasource to excel in .net

Trini_NAtwaroo — Mon, 06 Jul 2009 13:15:56 GMT

hey thanks I actually used something similar as it and worked, however I am wondering if I can format the excel spreadsheet before it exports eg I want a title and subject of the excel before the gridview appears, do you have any idea by chance how to do this?

Thanks

Re: connect sql datasource to excel in .net

vinz — Mon, 06 Jul 2009 03:30:39 GMT

Exporting Multiple GridViews To Excel SpreadSheet in ASP.Net

Export Data to Excel

connect sql datasource to excel in .net

Trini_NAtwaroo — Sun, 05 Jul 2009 01:35:17 GMT

hey guys , I need some assistance and advice.

The final product is an excel sheet. I have a query which returns result ideally I would be doing this using an sql datasource in .net. However what i would accomplish is to "export" the results returned from the query to an excel sheet fill with the returns; as well as additionally add in a title etc. So I was thinking if its possible to connect the datasource to a excel sheet and if this is possible how exactly will I be doing this.

Please help any ideas or advice is welcomed

Thanks