|
Subject:
|
Adding to Export Of GridView to Excel
|
|
Posted By:
|
rsearing
|
Post Date:
|
1/18/2007 5:04:42 PM
|
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
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/19/2007 1:56:04 PM
|
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: 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: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.
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/19/2007 3:24:01 PM
|
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
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/19/2007 3:39:14 PM
|
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.
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/19/2007 6:25:09 PM
|
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.
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/19/2007 6:58:30 PM
|
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.
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/22/2007 10:24:24 PM
|
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
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/23/2007 1:37:07 AM
|
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.
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/23/2007 8:35:28 AM
|
Thank you Imar!
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/23/2007 2:56:22 PM
|
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.
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/23/2007 6:25:39 PM
|
Imar,
Sorry if this is trivial, but trying to figure out how to do this. I have tried the following code and it doesn't work. I tried looking at properties of LiteralControl and Literal--not sure how to add this.
Dim myLiteral As New LiteralControl()
myLiteral.Text = "<br/>"
Dim myPanel As Panel = New Panel()
Dim myLabel As New Label
Dim MyGridView As New GridView()
MyGridView = GridView2
myLabel.Text = "Table Header"
MyGridView.BorderColor = Drawing.Color.Black
MyGridView.BorderWidth = New Unit(1)
myPanel.Controls.Add(myLabel)
myPanel.Controls.Add(myLiteral)
myPanel.Controls.Add(MyGridView)
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()
Regards,
Rob
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/23/2007 6:37:07 PM
|
Can you define "it doesn't work"? It's hard to see the solution without a problem description.
One thing I notice is the way you set up the GridView. Instead of assigning it GridView2, you probably want to bind it you your original data source:
myGridView.DataSource = SomeCallInOneOfYourClassesThatReturnsData()
myGridView.DataBind()
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.
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/23/2007 7:50:17 PM
|
Sorry...the GridView works fine. I don't get an error--I get the actual Excel doc---there's just not the extra line between the label and the gridview.
(Again, sorry).
Rob
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/24/2007 2:17:25 PM
|
If Excel doesn't understand breaks, try giving it a <table> instead.
Create a new Table, a table Row and a table Cell. Add the Cell to the Row, the Row to the Table and the Table to the Panel.
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
While typing this post, I was listening to: The Ship Song by Nick Cave & the Bad Seeds (Track 5 from the album: The Good Son) What's This?
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/24/2007 2:38:29 PM
|
Imar,
Could you provide sample code or an example of how I'd do that? I'm lost. I would assume that I add a table..row..then the gridview to the panel?
Sorry---
Rob
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/24/2007 2:46:13 PM
|
No need to say sorry....
Untested, but something like this should work: Dim myPanel As Panel = New Panel()
Dim myLabel As New Label
Dim myGridView As New GridView()
Dim myTable As New Table()
Dim myRow As New TableRow()
Dim myCell As New TableCell()
myCell.Text = "<br />"
' Add the cell to the row
myRow.Cells.Add(myCell)
' Add the row to the table
myTable.Rows.Add(myRow)
myGridView = GridView2
myLabel.Text = "Table Header"
MyGridView.BorderColor = Drawing.Color.Black
MyGridView.BorderWidth = New Unit(1)
myPanel.Controls.Add(myLabel)
' Add the table to the Panel
myPanel.Controls.Add(myTable)
myPanel.Controls.Add(myGridView)
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()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
While typing this post, I was listening to: Play In The Sunshine by Prince (Track 2 from the album: Sign Of The Times (Disk 1)) What's This?
|
|
Reply By:
|
rsearing
|
Reply Date:
|
1/24/2007 10:34:19 PM
|
That worked. Just wish there was a way to mess with gridlines. You can't add gridlines to the label/table nicely....
Thanks Imar!!
:)
Rob
|
|
Reply By:
|
Imar
|
Reply Date:
|
1/25/2007 1:41:54 AM
|
There are ways to do that, but you need to pay for them.
Look at commercial Excel writers like http://officewriter.softartisans.com/
They write true Excel data, not just some HTML that Excel interprets.
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.
|
