Hi all
I have got this VB code working BUT I get Html code in the end of the output that makes Excel not wanting to open it properly..... when manually deleting the trailing html code in the output the Excel accepts the file and its format very well ... so the question is ... how do I get rid of the trailing characters when making a response.write output from a datagrid to Excel? Correct datagrid output colored in blue, the trailing html code is colored in red, and my actual code doing the job is in grey.........
<tr>
<td>9083</td><td>6427</td><td> </td><td>2009-04-15 00:00:00</td><td>New</td><td>Lumturije Nici</td><td>6711050564</td><td>Domarev. 9</td><td>43344</td><td>Partille</td><td> </td><td> </td><td> </td><td>031440466</td><td>0762914683</td><td> </td><td>1</td><td>lumturije_nici@hotmail.com</td><td> </td><td>448</td><td>Ahmad Farhat</td><td> </td><td><SYSnotes>
<MAIL Date="2009-04-16 15:55:00"> 20090416PPM2RåPension3.se</MAIL>
<MAIL Date="2009-04-16 15:53:00"> 20090416PPM2WePension3.se</MAIL>
</SYSnotes>
Orderdate:2009-04-15
SellerID:448 SellerName:Ahmad Farhat
</td><td>6711050564__20090415_448_143429.mp3</td>
</tr><tr>
<td>9085</td><td>6429</td><td> </td><td>2009-04-15 00:00:00</td><td>New</td><td>Peiponen Eija</td><td>7211264820</td><td>Länkharvsg. 100</td><td>42466</td><td>Angered</td><td> </td><td> </td><td> </td><td>0313310521</td><td>0735681849</td><td> </td><td>1</td><td>eija_p@hotmail.com</td><td> </td><td>438</td><td>Micaela Henriksson</td><td> </td><td><SYSnotes>
<MAIL Date="2009-04-16 15:55:00"> 20090416PPM2RåPension3.se</MAIL>
<MAIL Date="2009-04-16 15:53:00"> 20090416PPM2WePension3.se</MAIL>
</SYSnotes>
Orderdate:2009-04-15
SellerID:438 SellerName:Micaela Henriksson
</td><td>7211264820__20090415_438_144842.mp3</td>
</tr>
</table>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html lang="sv-SE">
<head id="Head">
<!--**********************************************************************************-->
<!-- DotNetNuke� - http://www.dotnetnuke.com -->
<!-- Copyright (c) 2002-2008 -->
<!-- by DotNetNuke Corporation -->
<!--**********************************************************************************-->
<meta id="MetaDescription" name="DESCRIPTION" content="InfoAcc Rapporter" /><meta id="MetaKeywords" name="KEYWORDS" content="InfoAcc Rapporter,DotNetNuke,DNN" /><meta id="MetaCopyright" name="COPYRIGHT" content="Copyright 2009 by www.pensionsradgivarna.se" /><meta id="MetaGenerator" name="GENERATOR" content="DotNetNuke " /><meta id="MetaAuthor" name="AUTHOR" content="www.pensionsradgivarna.se" /><meta name="RESOURCE-TYPE" content="DOCUMENT" /><meta name="DISTRIBUTION" content="GLOBAL" /><meta name="ROBOTS" content="INDEX, FOLLOW" /><meta name="REVISIT-AFTER" content="1 DAYS" /><meta name="RATING" content="GENERAL" /><meta http-equiv="PAGE-ENTER" content="RevealTrans(Duration=0,Transition=1)" /><style id="StylePlaceholder" type="text/css"></style><link id="borderTypeClassTag" rel="stylesheet" type="text/css" href=""></link><link id="borderStyleClassTag" rel="stylesheet" type="text/css" href=""></link><link id="backgroundClassTag" rel="stylesheet" type="text/css" href=""></link><link id="_Portals__default_" rel="stylesheet" type="text/css" href="/Portals/_default/default.css" /><link id="_Portals_3_Skins_Minimalist_Custom_Gradient_" rel="stylesheet" type="text/css" href="/Portals/3/Skins/Minimalist-Custom-Gradient/skin.css" /><link id="_Portals_3_Skins_Minimalist_Custom_Gradient_Horizontal_Tabbed_css" rel="stylesheet" type="text/css" href="/Portals/3/Skins/Minimalist-Custom-Gradient/Horizontal-Tabbed.css" /><link id="_Portals_3_Containers_Minimalist_Custom_Glass_Container03_css" rel="stylesheet" type="text/css" href="/Portals/3/Containers/Minimalist-Custom-Glass/Container03.css" /><link id="_Portals_3_" rel="stylesheet" type="text/css" href="/Portals/3/portal.css" /><title>
InfoAcc Rapporter
</title></head>
<body id="Body">
<noscript></noscript>
--------------------------------------------------------------------------------------------------------------------
' code in a .ascx module file:
GridView1.DataSource = objDataSet.Tables(0)
GridView1.DataBind()
GridViewExportUtil.Export("OutputFilename.xls", GridView1)
----------------------------------------------------------------------------------------------------
Imports System
Imports System.Data
Imports System.Configuration
Imports System.IO
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Namespace ITansvar.Modules.PRStatistik
Public Class GridViewExportUtil
Public Shared Sub Export(ByVal fileName As String, ByVal gv As GridView)
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.AddHeader("content-disposition", String.format("attachment; filename={0}", fileName))
HttpContext.Current.Response.ContentType = "application/ms-excel"
Dim sw As StringWriter = New StringWriter
Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
' Create a form to contain the grid
Dim table As Table = New Table
table.GridLines = gv.GridLines
' add the header row to the table
If (Not (gv.HeaderRow) Is Nothing) Then
GridViewExportUtil.PrepareControlForExport(gv.HeaderRow)
table.Rows.Add(gv.HeaderRow)
End If
' add each of the data rows to the table
For Each row As GridViewRow In gv.Rows
GridViewExportUtil.PrepareControlForExport(row)
table.Rows.Add(row)
Next
' add the footer row to the table
If (Not (gv.FooterRow) Is Nothing) Then
GridViewExportUtil.PrepareControlForExport(gv.FooterRow)
table.Rows.Add(gv.FooterRow)
End If
' render the table into the htmlwriter
table.RenderControl(htw)
' render the htmlwriter into the response
HttpContext.Current.Response.Write(sw.ToString)
HttpContext.Current.Response.Flush()
HttpContext.Current.Response.End()
End Sub
' Replace any of the contained controls with literals
Private Shared Sub PrepareControlForExport(ByVal control As Control)
Dim i As Integer = 0
Do While (i < control.Controls.Count)
Dim current As Control = control.Controls(i)
If (TypeOf current Is LinkButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text))
ElseIf (TypeOf current Is ImageButton) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText))
ElseIf (TypeOf current Is HyperLink) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text))
ElseIf (TypeOf current Is DropDownList) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text))
ElseIf (TypeOf current Is CheckBox) Then
control.Controls.Remove(current)
control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))
'TODO: Warning!!!, inline IF is not supported ?
End If
If current.HasControls Then
GridViewExportUtil.PrepareControlForExport(current)
End If
i = (i + 1)
Loop
End Sub
End Class
End Namespace
/Johan Hyra vattenskoter