Solved

export into excel based on stored procedure

Posted on 2008-10-24
5
1,376 Views
Last Modified: 2013-12-17
hi
i want to export data into excel by executing specific stored procedure, i dont want a grid view object on the page
0
Comment
Question by:mattibutt
[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
  • 3
  • 2
5 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22803251
And, how would you want to do that?  Do you want to use Excel automation?  Write an Open XML Excel file?  

What version of C# and Office are you using?
0
 
LVL 11

Author Comment

by:mattibutt
ID: 22813112
thing is i have been using an example where i can export recrods based on Gridview component the problem is when i make the gridview object invisible then it doesnt work to simplify this task if i can just export it without showing the gridview object on the asp.net web page then my problem is sorted

 protected void ExportButton_Click(object sender, EventArgs e)
    {
 
        //Export the GridView to Excel
 
        PrepareGridViewForExport(Gridview1);
 
        ExportGridView();
 
    }
 
 
 
 private void ExportGridView()
    {
 
        string attachment = "attachment; filename=Contacts.xls";
 
        Response.ClearContent();
 
        Response.AddHeader("content-disposition", attachment);
 
        Response.ContentType = "application/ms-excel";
 
        StringWriter sw = new StringWriter();
 
        HtmlTextWriter htw = new HtmlTextWriter(sw);
 
        Gridview1.RenderControl(htw);
 
        Response.Write(sw.ToString());
 
        Response.End();
 
    }
 
 
private void PrepareGridViewForExport(Control gv)
    {
 
        LinkButton lb = new LinkButton();
 
        Literal l = new Literal();
 
        string name = String.Empty;
 
        for (int i = 0; i < gv.Controls.Count; i++)
        {
 
            if (gv.Controls[i].GetType() == typeof(LinkButton))
            {
 
                l.Text = (gv.Controls[i] as LinkButton).Text;
 
                gv.Controls.Remove(gv.Controls[i]);
 
                gv.Controls.AddAt(i, l);
 
            }
 
            else if (gv.Controls[i].GetType() == typeof(DropDownList))
            {
 
                l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
 
                gv.Controls.Remove(gv.Controls[i]);
 
                gv.Controls.AddAt(i, l);
 
            }
 
            else if (gv.Controls[i].GetType() == typeof(CheckBox))
            {
 
                l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
 
                gv.Controls.Remove(gv.Controls[i]);
 
                gv.Controls.AddAt(i, l);
 
            }
 
            if (gv.Controls[i].HasControls())
            {
 
                PrepareGridViewForExport(gv.Controls[i]);
 
            }
        }
    }

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 22813290
That shows that you are rendering a GridView to an HTML string, and then streaming that to the browser.  Since you don't want that, I would like to find out what you do really want.  Working with Excel automation (single threaded) is not very friendly with ASP.NET (multi-threaded), so I would like to find out which direction you would like to take.
0
 
LVL 11

Author Comment

by:mattibutt
ID: 22814291
multi-threaded would be fine
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 22814329
My friend, that could mean so many things, so I would have to suggest that you use OLE DB, and SQL statements to insert into an Excel worksheet.

Proof-of concept:

Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory
http://davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

707 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