Solved

VB.Net DataSet to Excel

Posted on 2009-03-31
3
1,372 Views
Last Modified: 2012-05-06
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?


'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

Open in new window

0
Comment
Question by:Paulconsulting
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
vavjeeva earned 500 total points
ID: 24031669
you would have to change the settings client's registry in order to skip this warning.. check this link for more details..

http://blogs.msdn.com/vsofficedeveloper/pages/Excel-2007-Extension-Warning.aspx
0
 
LVL 9

Expert Comment

by:vavjeeva
ID: 24031682
Key: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security
Value: (DWORD)"ExtensionHardening" = [0 = Disable check; 1 = Enable check and prompt; 2 = Enable check, no prompt deny open]
Default setting if value not present is 1 (enable and prompt)
0
 

Author Comment

by:Paulconsulting
ID: 24031782
Ah jeez.....
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question