This is the complete code to export to excel, without links and the page
is exported in landscape view.
Protected Sub BtnExportExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
response.Clear() response.Buffer = True
response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.AddHeader("content-disposition", "attachment;filename=Projects.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Me.EnableViewState = False Dim sw As New IO.StringWriter()
Dim hw As New HtmlTextWriter(sw) GridView1.AllowPaging = False
GridView1.DataBind()
'adding spaces to the left and right of the title
GridView1.Caption = "T16 DI Area Pending Claims Listings"
GridView1.HeaderRow.Attributes.Add("style", "font-size:10px")
'GridView1.HeaderRow.Attributes.Add("ss:WrapText", "1")
'GridView1.AlignmentElement.Attributes.Add("ss:WrapText", "1")
'GridView1.a()
For i As Integer = 0 To GridView1.HeaderRow.Cells.Count - 1
GridView1.HeaderRow.Cells(i).Style.Add("background-color", "#507CD1")
Next For i As Integer = 0 To GridView1.Rows.Count - 1
Dim row As GridViewRow = GridView1.Rows(i)
'Change Color back to white
row.BackColor = System.Drawing.Color.White
'Apply text style to each Row
row.Attributes.Add("class", "textmode") Next
Me.RemoveControls(GridView1) GridView1.RenderControl(hw)
'Dim style As String = "<style> .textmode { mso-number-format:\@; text-wrap; } </style>"
'Response.Write(style)
' set content type and character set to cope with european chars like the umlaut.
Response.Write("<meta http-equiv=Content-Type content=""text/html; charset=utf-8"">" & vbLf)
' add the style props to get the page orientation
response.Write(AddExcelStyling()) Response.Write(sw.ToString())
Response.Flush() Response.Write("</body>")
Response.Write("</html>") response.[End]() End Sub
Private Function AddExcelStyling() As String
Dim sb As New StringBuilder()
sb.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office'" & vbLf & "xmlns:x='urn:schemas-microsoft-com:office:excel'" & vbLf & "xmlns='http://www.w3.org/TR/REC-html40'>" & vbLf & "<head>" & vbLf)
sb.Append("<style>" & vbLf) sb.Append("@page")
sb.Append("{margin:.0in .0in .0in .0in;" & vbLf)
sb.Append("mso-header-margin:.5in;" & vbLf)
sb.Append("mso-footer-margin:.5in;" & vbLf)
sb.Append("mso-page-orientation:landscape;}" & vbLf)
sb.Append("</style>" & vbLf)
sb.Append("<!--[if gte mso 9]><xml>" & vbLf)
sb.Append("<x:ExcelWorkbook>" & vbLf)
sb.Append("<x:ExcelWorksheets>" & vbLf)
sb.Append("<x:ExcelWorksheet>" & vbLf)
sb.Append("<x:Name>Projects 3 </x:Name>" & vbLf)
sb.Append("<x:WorksheetOptions>" & vbLf)
sb.Append("<x:Print>" & vbLf)
sb.Append("<x:ValidPrinterInfo/>" & vbLf)
sb.Append("<x:PaperSizeIndex>9</x:PaperSizeIndex>" & vbLf)
sb.Append("<x:HorizontalResolution>600</x:HorizontalResolution" & vbLf)
sb.Append("<x:VerticalResolution>600</x:VerticalResolution" & vbLf)
sb.Append("</x:Print>" & vbLf)
sb.Append("<x:Selected/>" & vbLf)
sb.Append("<x:DoNotDisplayGridlines/>" & vbLf)
sb.Append("<x:ProtectContents>False</x:ProtectContents>" & vbLf)
sb.Append("<x:ProtectObjects>False</x:ProtectObjects>" & vbLf)
sb.Append("<x:ProtectScenarios>False</x:ProtectScenarios>" & vbLf)
sb.Append("</x:WorksheetOptions>" & vbLf)
sb.Append("</x:ExcelWorksheet>" & vbLf)
sb.Append("</x:ExcelWorksheets>" & vbLf)
sb.Append("<x:WindowHeight>12780</x:WindowHeight>" & vbLf)
sb.Append("<x:WindowWidth>19035</x:WindowWidth>" & vbLf)
sb.Append("<x:WindowTopX>0</x:WindowTopX>" & vbLf)
sb.Append("<x:WindowTopY>15</x:WindowTopY>" & vbLf)
sb.Append("<x:ProtectStructure>False</x:ProtectStructure>" & vbLf)
sb.Append("<x:ProtectWindows>False</x:ProtectWindows>" & vbLf)
sb.Append("</x:ExcelWorkbook>" & vbLf)
sb.Append("</xml><![endif]-->" & vbLf)
sb.Append("</head>" & vbLf)
sb.Append("<body>" & vbLf) Return sb.ToString()
End Function Private Sub RemoveControls(ByVal grid As Control)
Dim literal As New Literal()
For i As Integer = 0 To grid.Controls.Count - 1
If TypeOf grid.Controls(i) Is LinkButton Then
literal.Text = TryCast(grid.Controls(i), LinkButton).Text
grid.Controls.Remove(grid.Controls(i))
grid.Controls.AddAt(i, literal)
ElseIf TypeOf grid.Controls(i) Is DropDownList Then
literal.Text = TryCast(grid.Controls(i), DropDownList).SelectedItem.Text
grid.Controls.Remove(grid.Controls(i))
grid.Controls.AddAt(i, literal)
ElseIf TypeOf grid.Controls(i) Is CheckBox Then
literal.Text = If(TryCast(grid.Controls(i), CheckBox).Checked, "True", "False")
grid.Controls.Remove(grid.Controls(i))
grid.Controls.AddAt(i, literal)
ElseIf TypeOf grid.Controls(i) Is HyperLink Then
literal.Text = TryCast(grid.Controls(i), HyperLink).Text
grid.Controls.Remove(grid.Controls(i))
grid.Controls.AddAt(i, literal)
ElseIf TypeOf grid.Controls(i) Is Button Then
literal.Text = TryCast(grid.Controls(i), Button).Text
grid.Controls.Remove(grid.Controls(i))
grid.Controls.AddAt(i, literal) End If
If grid.Controls(i).HasControls() Then
RemoveControls(grid.Controls(i)) End If Next
End SubEnd Class
