Solved

VB.Net DataSet to Excel

Posted on 2009-03-31
3
1,359 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now