gridview properties for excel export

[Sergio E.]

Hi, I'm looking for information about gridview's cell properties...

I've the following case: one gridview with alternatirg row style and
normalrowstyle, 3 o 4 custom styles that are applied to cells in the
row_databound event, and now I nedd to do a excel export of the gridview
exactly as is showed in the browser, but al the examples and thigs I've
tried don't send the cell color to excel and now I think if I can read the
gridview properties in the prerender event to get them for each cell.

I will apreciate any help,

 

[Guest]

A few years back in one of the contracts we did something similar to this -
needed to export to excel a grid with formatting.
We had xsl stylesheet defined and then Transformed the incoming Dataset xml
into html. Then you change content type to "application/vnd.xls" and let
Excel open it.
I think starting from 97 version (I am not sure about prior ones) Excel will
correctly process html.
The html will have to be self contained - means no external references (css
files, for example) - you will have to define everything inside your
transformation.

 

[Sergio E.]

thank's for answering me, i was trying and the big trouble is that there is
a css stylesheet that contains al de colors for the rows, alternate rows,
and special cells, and some cells are re-styled in the gridview_rowdatabound
event so i must use this styles and i must convert the gridview, not the
dataset... mi big trouble now is the fact that the color are in css
classes...and i don't know hot to access this information...

if i can do some like
me.getcssclass(me.gridview1.rows(0).cells(0).cssclass).backgroundcolor then
i have my problem solved!, but i can't find information about how to get the
specification of some class included in an attached file to an aspx with the
<link href="Styles/Normal.css" rel="stylesheet" type="text/css" />
directive.

there is the code i'm using... and i don't understand why the colors are not
rendered ... i supose that is because is the browser itself who paints the
colors in te last step:

public sub Excel()
Dim sb As StringBuilder = New StringBuilder()
Dim sw As StringWriter = New StringWriter(sb)
Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
Dim pag As Page = New Page()
Dim frm As HtmlForm = New HtmlForm()
Dim gv As GridView = Me.GVResultado

pag.EnableEventValidation = False
gv.AllowPaging = False
gv.EnableViewState = False
gv.DataBind()
If (Not (gv.HeaderRow) Is Nothing) Then
PrepareControlForExport(gv.HeaderRow)
End If
For Each row As GridViewRow In gv.Rows
PrepareControlForExport(row)
Next
If (Not (gv.FooterRow) Is Nothing) Then
PrepareControlForExport(gv.FooterRow)
End If

pag.DesignerInitialize()
pag.Controls.Add(frm)
frm.Controls.Add(gv)
pag.RenderControl(hw)
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment;filename=data.xls")
Response.Charset = "UTF-8"
Response.ContentEncoding = Encoding.Default
Response.Write(sb.ToString())
gv.AllowPaging = True
gv.EnableViewState = True
gv.DataBind()
Response.End()
end sub

 

[Guest]

Sergio,

As far as I know you cannot do anything like
me.getcssclass(me.gridview1.rows(0).cells(0).cssclass).backgroundcolor
this is usually quite complex process performed (often differently) by
different browsers.
What I would suggest you to try would be to try reading the content of your
css file (provided it does not have @includes from other css files) and try
to stick it into the header of your output within <style> tags - this way the
result html will become self-contained, and hopefully contain enough
information for Excel to correctly display your information.

Let me know how you go.
If you have any more questions, I would be more than happy to assist

 

[Sergio E.]

hi, thanks for this solution, i can read the css file, but i can'f find the
sintax to do the next part (the output to the header with the <style> tags)
i have the full css file loaded into a stringbuilder exactly as appears in
the physical file...

Can you post an example of the output to the header?

Thank's again.

 

[Sergio E.]

hi, i finally solve the problem using this code:

Response.Write("<HEAD><STYLE type=""text/css"">" +
ReadCss("~/Styles/Normal.css") + "<STYLE></HEAD>")

and then write the gridview...

where readcss is a private function wich reads a given css file and returns
it as simple string.

 

[Guest]

glad that you have it all worked out