Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Professional
|
ASP.NET 2.0 Professional If you are an experienced ASP.NET programmer, this is the forum for your 2.0 questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 18th, 2007, 06:04 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default Adding to Export Of GridView to Excel

I am very much a novice--not an expert, but figured this was a professional topic so thought I would try here.

I have a GridView that captures members that are enrolled in events. I can pass the Gridview to Excel using the code below. My question is, How can I manipulate the code to add my own content. For example, let's say that I want to capture the name of the event in a string (or read the .text of a control) and add that at the top of the excel sheet, skip a row, then put the Gridview contents.

How would I do that? ( I am not familiar nor know how I would change .tostring or how htmlwriter/stringwriter work).

Secondly, my Gridview control doesn't use gridlines, but I'd like the Excel doc to show them---how would I change that?

Any help would be MUCH appreciated.

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();

Kind Regards,
Rob

 
Old January 19th, 2007, 02:56 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi Rob,

As I suggested in an earlier e-mail, you can create in-memory container controls and the GridView. You could, for example, create a Panel and add controls to it. You can style these controls as well, similar to what you would do declaratively:
Code:
    Dim myPanel As Panel = New Panel()
    Dim myLabel As New Label
    Dim myLogin As New Login()

    myLabel.Text = "Joop en Piet"

    myLogin.BorderColor = Drawing.Color.Black
    myLogin.BorderWidth = New Unit(2)

    myPanel.Controls.Add(myLabel)
    myPanel.Controls.Add(myLogin)

    Dim attachment As String = "attachment; filename=Contacts.xls"
    Response.ClearContent()
    Response.AddHeader("content-disposition", attachment)
    Response.ContentType = "application/ms-excel"

    Dim sw As IO.StringWriter = New IO.StringWriter()
    Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)

    myPanel.RenderControl(htw)

    Response.Write(sw.ToString())
    Response.End()
    This adds a Label and a Login to a Panel and then renders the panel to the Response stream.

Notice that you're pretty limited with what you can do in terms of styling. Excel isn't the greatest HTML renderer, so you'll loose a lot of formatting. You'll just have to try to see what works for you.

In your case, don't use GridView1, but create a new one in-memory, bind it and add it to the panel:
Code:
Dim myGridView As New GridView()
myGridView.DataSource = Whatever your data source is
myGridView.DataBind()

myPanel.Controls.Add(myGridView)
Hope this helps,

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
 
Old January 19th, 2007, 04:24 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

Imar,

Thanks.

Just a little confused on a couple things.

a) In the top part, you reference myLogin as new Login(). I assume that is just to illustrate that you can use different controls other than just a GridView--in my case I would use a Gridview?
b) Not sure what you mean (please forgive) about creating it "in memory"? On my page, I have a gridview, set it's data source to an objectDatasource ("KnightsDataSource") that references a Select procedure. Couldn't I just, in the page_load of that page, reference the Gridview I have already created? You are suggesting to create a new one without putting on the page..why?
c) I saw an article whree there is an issue with rendering the gridview if there are other controls on it. (I'm not sure what the work around was--as I was just tryign to add the Gridview with other content)---but I would assume you would have the same issue with a Panel if there are other controls in it (such as the Gridview)?

Thanks so much,
Rob

 
Old January 19th, 2007, 04:39 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

a), Yes, exactly.

b) You could that too. However, with an export page there really isn't a page; a page usually outputs HTML to the browser, while in this case the output is sent to Excel. By building a control in-memory (with New GridView() instead of adding it to the page), you can move the code to a helper method so you can reuse it in multiple applications.

c) Yes. If you do, you get an error like this: "Control 'GridView1_ctl04_Button1' of type 'Button' must be placed inside a form tag with runat=server." There might be a work around, but I never tried it....

Have you tried out various options with this code? It's interesting to see how it works...

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
 
Old January 19th, 2007, 07:25 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

I'll try it this weekend and post.

To re-iterate point C above...what I am getting at is that I already have the GridView on the page...the export is just if the user want's to export to Excel to print or manipulate. So..I was wondering why I'd want to create another, if I just couldn't use the one on the page that already exists?

Lastly, I guess the entire reason for the post was to just get different views on this..as I had heard that you can't use the code I gave if the control you are rendering has controls itself...

In doing some research I found an article that showed a unique way of doing it...rather, creating a connection to your DB and simply using the DataSet/Reader to speak directly to Excel..I haven't messed with this..but thought perhaps I could use this and add content to the strline...but not sure how I'd do that.

Dim i As Integer
Dim strLine As String, filePath, fileName, fileExcel, link
Dim objFileStream As FileStream
Dim objStreamWriter As StreamWriter
Dim nRandom As Random = New Random(DateTime.Now.Millisecond)
Dim fs As Object, myFile As Object
Dim cnn As SqlConnection = New SqlConnection("server=(local);database=pubs;" & _
                                             "Integrated Security=SSPI")

'Create a pseudo-random file name.
fileExcel = "t" & nRandom.Next().ToString() & ".xls"

'Set a virtual folder to save the file.
'Make sure that you change the application name to match your folder.
filePath = Server.MapPath("\ExcelVBTest")
fileName = filePath & "\" & fileExcel

'Use FileStream to create the .xls file.
objFileStream = New FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write)
objStreamWriter = New StreamWriter(objFileStream)

'Use a DataReader to connect to the Pubs database.
cnn.Open()
Dim sql As String = "select au_id,au_lName,au_fname,phone," & _
                    "address,city,state,zip,contract from authors"
Dim cmd As SqlCommand = New SqlCommand(sql, cnn)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader()

'Enumerate the field names and records that are used to build the file.
For i = 0 To dr.FieldCount - 1
   strLine = strLine & dr.GetName(i).ToString & Chr(9)
Next

'Write the field name information to file.
objStreamWriter.WriteLine(strLine)

'Reinitialize the string for data.
strLine = ""

'Enumerate the database that is used to populate the file.
While dr.Read()
   For i = 0 To dr.FieldCount - 1
      strLine = strLine & dr.GetValue(i) & Chr(9)
   Next
   objStreamWriter.WriteLine(strLine)
   strLine = ""
End While

'Clean up.
dr.Close()
cnn.Close()
objStreamWriter.Close()
objFileStream.Close()

'Show a link to the Excel file.
HyperLink1.Text = "Open Excel"
HyperLink1.NavigateUrl = fileExcel

Trust me--not trying to be difficult--just looking for a way to solve this.

 
Old January 19th, 2007, 07:58 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Oh, I don't think you're making things difficult. I was just wondering why you didn't try things out. You can "assume" things don't work with embedded controls, or you can just slap a page together with a GridView that has controls in it. Then set a break point, see where things break and fall apart, and look in the controls / page to see what happens and what error you get. Much better than assuming....

Regarding your point c and my answer: Like I said, yes you could do that as well. But I'll think you run into some issues with post backs, which you require if the data is already on the page anyway.

With the data reader code, you have more or less the same issue as you had before: it's only outputting data, not formatting. However, since this "Excel" file is actually nothing more than HTML that Excel can interpret, you could write other markup to it as well by embedding HTML tags in the output.

Does this help?

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
 
Old January 22nd, 2007, 11:24 PM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

Imar,

Ok, this actually worked. A couple questions, however.

a) I get the following in the "immediate window"?
A first chance exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll
An exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll but was not handled in user code

b) How can I maniuplate the Excel a little bit..for example, when I do this, the Gridview appears just below the label...I know this is minor, but I'd like to add a blank row between the two. Do you know how I could manipulate the code to do that?

c) Ok, so the GridView shows the grid..but the rest of the Excel doc is blank..is there any way to leave all the gridlines on the Excel doc?

SOOO CLOSE!!

Thanks,
Rob

 
Old January 23rd, 2007, 02:37 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

a) I've seen this before with Response.Redirect calls. I wouldn't worry about it too much as long as the correct file is generated. But, maybe Google knows more about it.

b) You can create a <asp:Literal> on the fly, add "<br />" to it and insert it in the panel between the label and the GridView. Remember, the "Excel" you're generating isn't Excel stuff; it's just plain HTML.

c) As far as I know, you can't. This seems to be Excel's way of dealing with HTML.

If you want true Excel export, you'll need to look at third party components that can do this....

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.
 
Old January 23rd, 2007, 09:35 AM
Friend of Wrox
 
Join Date: Jul 2006
Posts: 238
Thanks: 0
Thanked 2 Times in 2 Posts
Send a message via MSN to rsearing
Default

Thank you Imar!

 
Old January 23rd, 2007, 03:56 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

You're welcome....

Imar
---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Export Gridview to Excel and Hide column PDALUX ASP.NET 2.0 Basics 2 March 1st, 2012 02:16 AM
Gridview data export to excel sheet abhishekkashyap27 C# 2005 1 August 1st, 2008 10:10 AM
Export ALL records from GridView into Excel or CSV kevorkian ASP.NET 2.0 Basics 7 June 17th, 2008 01:28 PM
Export GridView data to Excel file rao965 ASP.NET 2.0 Professional 0 June 14th, 2007 10:18 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.