Export SQL Query Data to Excel file and Prompt User to Save (Avoiding the user of controls like Gridview)

Hi All,

I'm hoping someone maybe able to help with carrying out a select query which then is exported to excel where it prompts the end user to save avoiding:

  • using any control such as gridview etc.
  • not saving directly to the server.

I found the following which grabs the query results and save to an .xls file directly onto the server but can't get it to prompt the user to download.

Any help would be greatly appreciated!


Code: (taken from http://support.microsoft.com/default.aspx?scid=kb;en-us;311194)

//You use these variables throughout the application.
        string fileExcel, filePath, fileName, strLine, sql;
        FileStream objFileStream;
        StreamWriter objStreamWriter;
        Random nRandom = new Random(DateTime.Now.Millisecond);
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString);

        //Create a random file name.
        fileExcel = "t" + nRandom.Next().ToString() + ".xls";

        //Set a virtual folder to save the file.
        //Make sure to change the application name to match your folder.
        filePath = Server.MapPath("");
        fileName = filePath + "\\" + fileExcel;

        //Use FileSystem objects to create the .xls file.
        objFileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);
        objStreamWriter = new StreamWriter(objFileStream);

        //Use a DataReader object to connect to the Pubs database.
        sql = "SELECT * FROM table WHERE blah blah blah'";
        SqlCommand cmd = new SqlCommand(sql, conn);
        SqlDataReader dr;
        dr = cmd.ExecuteReader();

        //Initialize the string that is used to build the file.
        strLine = "";

        //Enumerate the field names and the records that are used to build
        //the file.
        for (int i = 0; i <= dr.FieldCount - 1; i++)
            strLine = strLine + dr.GetName(i).ToString() + Convert.ToChar(9);
        //Write the field name information to the file.

        //Reinitialize the string for data.
        strLine = "";

        //Enumerate the database that is used to populate the file.
        while (dr.Read())
            for (int i = 0; i <= dr.FieldCount - 1; i++)
                strLine = strLine + dr.GetValue(i).ToString() + Convert.ToChar(9);
            strLine = "";

        //Clean up.


12/12/2007 12:31:33 PM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 59

I found the following which grabs the query results and save to an .xls file directly onto the server but can't get it to prompt the user to download.


  After the file created you can simply try Response.Redirect("relative path of the newly created file")


Allen Chen
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
12/14/2007 5:10:46 AM

Similar Artilces:

UpdatePanel within a web user control resets data of controls outside the user control
Hello, I have a usercontrol on my page which displays a running marquee text. It makes use of an UpdatePanel (within the user control) to refresh the marquee text (within the updatepanel) at periodic intervals. For this I added a timer control and its tick event as an async postback trigger for the UpdatePanel. I have a lot of textboxes and dropdown list boxes (all with runat=server) on the page outside the above user control. These are also contained in different UpdatePanels. Problem is, when the user control does a postback to refresh the marquee text, it causes some of...

User Control in a User Control
I have a first user control that contain a second user control. If I cahe the first user control can I still dynamically control the second one or it is cached with the first one therefore can not control it anymore? Thanks When your first control is cached, its rendered out and its output is stored in the cache. On every request to the page containing your control that can satisfy it from cache, the output will just simply be inserted - your first control will never be instantiated or run in that case, so the second one wont be either. Hope this helps,Mike VolodarskyProgram Manager...

User Controls in User Controls?
I have the follow set up and it is throwing an error "Unhandled error loading module" ... is there some trick to this that I don't know?I have a project A that uses controls from project B (project B is just a project with all my user controls used through out other modules).  project A uses user control 1 from project B with out any issues at all - great.  But then I wanted to add user control 2 to user control 1 and that is when I get my error.  In fact I don't even have the user control 2 in user control 1, just when I put the register part into user control 1 is when it al...

User control in a user control
Hello all, I have created a user control with some checkboxes, and public properties to get the values of these checkboxes. I have also created a second user control using the first user control; this second control also access the public properties of the first one. My problem is when I implement the second control in an aspx page, if I click at runtime the checkboxes in the first control, I am able to access its public properties from the methods of the second control but the values of these properties are wrong. while at design time, I do not have this problem. Than...

user control to user control
I have an aspx page that have a "Register" directive to two usercontrol called rptMaster.ascx and rptDetail.ascx. I'm trying to set up a Master-Detail relationship and wish for the rptMaster.ascx to trigger and event from a dropdownlist and for the rptDetail to be listening for DropDownList event. I believe I have gotten the events part okay with help from this articel http://codeproject.com/aspnet/Page_UserControl.asp but my declaration for a private statement to my rptMaster class is producing an error. I'm using beta 2 of asp.net and I 'm having trouble getting a reference TO ...

User Control Passing Data To Another User Control?
I got two user controls in a .aspx page. one user control contains a list of names along with the user id as that listbox .value property. now when the user selects a user a clicks on a command button it needs to send the value of the selected user name to another user control in the same form name shippingInfo; Anyone know how to do this; I keep getting a "Object reference not set to an instance of an object" error message here is the code for the MyContacts control (the one with the user name and user id value) Imports PackageDeliveryVB.ShippingRequestInfo Public Class...

Retrieving Data from a Child User Control While in a Parent User Control
I have a User Control that has several Nested (Child) User Controls in it. When I do the final button click (in the Parent User Control) I need to be able to access the Child Controls info (TextBox, DropDownList’s, etc) from the Parent Control. I have tried using the FindControl(“”) function and many others with no success (generally I get a null reference exception when trying to read the data). I have tried referencing the Child Control then using that reference to access the Child element (TextBox, etc) with no success. Has anyone else done this? If so, could you share some...

User Controls not working if I put Validation controls in user control
Hi           I have validation controls(RequiredField Validator,RegularExpressionValidator)  in user controls.User controls are not posting back like the events are not wiring up.If I remove Validation controls in User controls page,user controls are working fine.Can anyone please help me out in getting it fixed. In the Validation controls, make sure you are setting the ControlToValidate property to the control that it links to.  If you're in .NET 2.0, it's also a good idea to put all the validation controls in the same Valid...

User Control controls not created when dynamically creating user control
  Have created a user control for displaying and editing a dynamically number of values. The user control contains two labels and an edit box. When dynamically loading the user control I get an error when the control itself tries to change setting on the fields within the user control.   Why doesnt this work? It works okay if I dont load it dynamically but create a user-control-instance in the aspx-page but thats no solution.   Thankyou!     Here is my code:   ucDataValue.ascx   <%@ Control Language="C#" AutoEventWireup="true&...

How to access a user control from another user control in a Wizard control
My page has a wizard control and each step is a user control.  I could find the value of a control in step1 user control in the wizard page but not in step2 user control.From wizard.aspx.vb – it worksDim m_title As DropDownList = CType(Me.PersonalBox1.FindControl("ddlTitle1"), DropDownList) Question:How can I find the value of a control(textbox or dropdownlist)in step1 user control from step2 user control? I want to find “PersonalBox1.FindControl("ddlTitle1")” from Step2 AddressBox1. I have tried several ways to access it but no luck. From AddressBox.ascx.vb –...

User control inside another user control data binding problem
Hi allmy problem that drives me crazy for already 2 days is the following...I have a page default.aspx, user control UserProfilesList.ascx, and another user control UserPic.ascx inside the first one like this (UserProfilesList.ascx) <asp:DataList ID="UserPicsDataList" RepeatColumns="5" runat="server"> <ItemTemplate> <uc1:UserPic ID="UserPicViewer" UserId='<%# Eval("ID") %>' runat="server" /> </ItemTemplate></asp:DataList>----------------------------------------------------...

Using a user control within a user control, which is in a page (aspx file)
I am attempting to have an aspx page that has a user control and within that user control load another user control programatically. I was thinking that I would expose some properties in the first user control and have the user control within that first user control access those properties. Would I do this by inheriting the main user control in the sub user control's class? Also, if I want a method to fire when the user control is loaded, what do I have to do? I was using the Page_Load method, but I don't think that is correct, or it's not exactly what I want. Let me know if I am wrong. :-) ...

Unable to access user control's user control's function\property from another user control
Hi, I used to call an user control's user control function as stated below from my user control in ASP.Net 1.1 wucCompany.wucEmployee.GetEmployeeSomething() After migrating to ASP.Net 2.0, I am unable to use any properties/functions(even the public ones) of the user control's User control's from another user control.  The way I have to do is create property\function in wucCompany which calls the wucEmployee's property\function and call the wucDepartment.GetEmployeeSomething().Since I need to do this change in too many places, I can...

Raiseevent user control in user control
 Hi there, I am working on a project in which a user control (BedrijfMasterDatagrid.ascx) is creating an other user control (BedrijfDatagrid.ascx).The user control BedrijfDatagrid.ascx is correctly created, but the clicked event of the created image control (BedrijfDatagrid.ibImageButtonClicked ) is not fired. (see BedrijfDetail.aspx)When I place BedrijfDatagrid.ascx directly on the page the event is correctly fired. Summary:BedrijfMasterDatagrid.ascx:       Datagrid with 1 row in which BedrijfDatagrid is dynamically createdBedrijfDatagrid.ascx  ...

Web resources about - Export SQL Query Data to Excel file and Prompt User to Save (Avoiding the user of controls like Gridview) - asp.net.presentation-controls

Avoiding dangerous climate change - Wikipedia, the free encyclopedia
"ARTICLE 2. OBJECTIVE. The ultimate objective of this Convention and any related legal instruments that the Conference of the Parties may adopt ...

Facebook Not Concerned With Reports Of Young Users Avoiding Site
It seems like on a monthly basis, there’s been a report or study that says teens and young adults are avoiding Facebook in favor of other sites ...

Avoiding the Unintended Consequences of Casual Feedback
... shared the following anecdote: One of the company's product teams had been working around the clock for weeks in order to hit a critical Avoiding ...

App Store - Bunny Run - Cross the street avoiding cars & tracks!
Get Bunny Run - Cross the street avoiding cars & tracks! on the App Store. See screenshots and ratings, and read customer reviews.

DATING FOR GAMERS – Avoiding the Friend Zone? - YouTube

Quit sports? Avoiding baguettes? French guide on how to spot radicalism
France released a poster listing telltale signs to help people identify friends or family members who could be radicalised.

Virtual Instruments: Avoiding "The Abyss", trouncing Brocade and not hiding behind SDN
Virtual Instruments, the nearly 5-year-old infrastructure performance management software and hardware maker, is one of those companies that ...

Improve your resolution by avoiding the obvious
Forget about drinking eight glasses of water a day. You can get all the water you need without drinking any.

Big firms avoiding paying tax, says union
A new report shows Australia is missing out on up to $47 billion due to companies avoiding or evading tax.

Avoiding 'Telstra Mark II' with the NBN
NBN Co's role in backhaul markets is a critical element in getting the NBN right and turning NBN Co into another Telstra would be the worst possible ...

Resources last updated: 12/7/2015 5:50:22 AM