• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1381
  • Last Modified:

export into excel based on stored procedure

hi
i want to export data into excel by executing specific stored procedure, i dont want a grid view object on the page
0
mattibutt
Asked:
mattibutt
  • 3
  • 2
1 Solution
 
Bob LearnedCommented:
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
 
mattibuttAuthor Commented:
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
 
Bob LearnedCommented:
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
 
mattibuttAuthor Commented:
multi-threaded would be fine
0
 
Bob LearnedCommented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now