Paulconsulting
asked on
VB.Net DataSet to Excel
I am using the following VB to export a DataSet to excel. It works great in IE, but in Firefox 3.07 I get the following Microsoft Office Excel dialog:
The file you are trying to open, 'ExcelReport.xls', is in a different format that specified by the extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?
When I select Yes, it opens fine.
Any ideas?
The file you are trying to open, 'ExcelReport.xls', is in a different format that specified by the extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?
When I select Yes, it opens fine.
Any ideas?
'Class to convert a dataset to an html stream which can be used to display the dataset
'in MS Excel
'The Convert method is overloaded three times as follows
' 1) Default to first table in dataset
' 2) Pass an index to tell us which table in the dataset to use
' 3) Pass a table name to tell us which table in the dataset to use
Imports System.Data
Imports System.Web.UI.WebControls
Imports Microsoft.VisualBasic
Public Class DataSetToExcel
Public Shared Sub Convert(ByVal ds As DataSet, ByVal response As HttpResponse)
'first let's clean up the response.object
response.Clear()
response.AddHeader("content-disposition", "attachment;filename=ExcelReport.xls")
response.Charset = ""
'set the response mime type for excel
response.ContentType = "application/vnd.xls"
'create a string writer
Dim stringWrite As New System.IO.StringWriter
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
'instantiate a datagrid
Dim dg As New System.Web.UI.WebControls.DataGrid
'set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables(0)
'bind the datagrid
dg.DataBind()
dg.HeaderStyle.Font.Bold = True
dg.HeaderStyle.Font.Size() = System.Web.UI.WebControls.FontUnit.Point(8)
dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Center
dg.ItemStyle.Font.Size() = System.Web.UI.WebControls.FontUnit.Point(8)
dg.ItemStyle.HorizontalAlign = HorizontalAlign.Center
'tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite)
'all that's left is to output the html
response.Write(stringWrite.ToString)
response.End()
End Sub
Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableIndex As Integer, ByVal response As HttpResponse)
'lets make sure a table actually exists at the passed in value
'if it is not call the base method
If TableIndex > ds.Tables.Count - 1 Then
Convert(ds, response)
End If
'we've got a good table so
'let's clean up the response.object
response.Clear()
response.AddHeader("content-disposition", "attachment;filename=ExcelReport.xls")
response.Charset = ""
'set the response mime type for excel
response.ContentType = "application/vnd.xls"
'create a string writer
Dim stringWrite As New System.IO.StringWriter
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
'instantiate a datagrid
Dim dg As New System.Web.UI.WebControls.DataGrid
'set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables(TableIndex)
'bind the datagrid
dg.DataBind()
'tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite)
'all that's left is to output the html
response.Write(stringWrite.ToString)
response.End()
End Sub
Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableName As String, ByVal response As HttpResponse)
'let's make sure the table name exists
'if it does not then call the default method
If ds.Tables(TableName) Is Nothing Then
Convert(ds, response)
End If
'we've got a good table so
'let's clean up the response.object
response.Clear()
response.AddHeader("content-disposition", "attachment;filename=ExcelReport.xls")
response.Charset = ""
'set the response mime type for excel
response.ContentType = "application/vnd.xls"
'create a string writer
Dim stringWrite As New System.IO.StringWriter
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
'instantiate a datagrid
Dim dg As New System.Web.UI.WebControls.DataGrid
'set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables(TableName)
'bind the datagrid
dg.DataBind()
'tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite)
'all that's left is to output the html
response.Write(stringWrite.ToString)
response.End()
End Sub
Public Shared Sub Convert(ByVal ds As DataSet, ByVal response As HttpResponse, ByVal strdestinationpath As String)
'first let's clean up the response.object
response.Clear()
response.AddHeader("content-disposition", "attachment;filename=ExcelReport.xls")
response.Charset = ""
'set the response mime type for excel
response.ContentType = "application/vnd.xls"
'create a string writer
Dim stringWrite As New System.IO.StringWriter
'create an htmltextwriter which uses the stringwriter
Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
'instantiate a datagrid
Dim dg As New System.Web.UI.WebControls.DataGrid
'set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables(0)
'bind the datagrid
dg.DataBind()
dg.HeaderStyle.Font.Bold = True
dg.HeaderStyle.Font.Size() = System.Web.UI.WebControls.FontUnit.Point(8)
dg.HeaderStyle.HorizontalAlign = HorizontalAlign.Center
dg.ItemStyle.Font.Size() = System.Web.UI.WebControls.FontUnit.Point(8)
dg.ItemStyle.HorizontalAlign = HorizontalAlign.Center
'tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite)
'all that's left is to output the html
Dim sw As System.IO.StreamWriter = System.IO.File.CreateText(strdestinationpath)
sw.Write(stringWrite.ToString)
sw.Close()
End Sub
End Class
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah jeez.....
Value: (DWORD)"ExtensionHardening
Default setting if value not present is 1 (enable and prompt)