Wrox Programmer Forums
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 6th, 2006, 02:32 AM
Authorized User
Join Date: Mar 2006
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default exporting datatable to excel


I want to export data from the data table to excel sheet. The code I am using requires excel excel to be installed on the server. I need the code which will export the data to excel sheet without requiring microsoft excel to be installed on the server. Currently i am using the following code.

        Dim oExcel As New Excel.Application()
        Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
        Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet
        Dim oCells As Excel.Range
        Dim sFile As String, sTemplate As String
        Dim dt As DataTable = CType(Application.Item("MyDataTable"), DataTable)

        sFile = Server.MapPath(Request.ApplicationPath) & "\timesheets\" & fname
        'sTemplate = Server.MapPath(Request.ApplicationPath) & "\MyTemplate.xls"
        oExcel.Visible = False : oExcel.DisplayAlerts = False

        'Start a new workbook

        oBooks = oExcel.Workbooks
        oBooks.Open(Server.MapPath(Request.ApplicationPath ) & "\MyTemplate.xls") 'Load colorful template with chart
        oBook = oBooks.Item(1)
        oSheets = oBook.Worksheets
        oSheet = CType(oSheets.Item(1), Excel.Worksheet)
        oSheet.Name = "First Sheet"
        oCells = oSheet.Cells
        DumpData(ds.Tables("export"), oCells) 'Fill in the data
        oSheet.SaveAs(sFile) 'Save in a temporary file
        'Quit Excel and thoroughly deallocate everything
        ReleaseComObject(oCells) : ReleaseComObject(oSheet)
        ReleaseComObject(oSheets) : ReleaseComObject(oBook)
        ReleaseComObject(oBooks) : ReleaseComObject(oExcel)
        oExcel = Nothing : oBooks = Nothing : oBook = Nothing
        oSheets = Nothing : oSheet = Nothing : oCells = Nothing
        'Response.Redirect(sFile) 'Send the user to the file
        Dim file As System.IO.FileInfo = New System.IO.FileInfo(sFile)

        If file.Exists Then 'set appropriate headers
            Response.AddHeader("Content-Disposition", "attachment; filename=" & file.Name)
            Response.AddHeader("Content-Length", file.Length.ToString())
            Response.ContentType = "application/octet-stream"
            Response.End() 'if file does not exist
            Response.Write("This file does not exist.")
        End If 'nothing in the URL as HTTP GET

Old July 10th, 2006, 08:34 AM
Wrox Author
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons

Well depending on what you want to do, this could work for you:

                Dim ds As New DataSet
                Dim xdd As New XmlDataDocument(ds)

                With Page.Response
                    .ContentType = "application/vnd.ms-excel"
                    .Charset = ""
                    myXSLDoc.Transform(xdd, Nothing, .OutputStream, xmlRez)
                End With

where xmlRez is an xmlResolver. This will send the data to the outputstream and force the browser to open excel on the clients machine. (I know this works with Excel 03 but have had spotty luck getting it to work with other versions of Excel)

"The one language all programmers understand is profanity."

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting to excel michaeltanfeliz Beginning VB 6 2 May 3rd, 2008 05:26 AM
.NET Tips-Simple way 2 Create Excel 4m DataTable ramuis78 ASP.NET 2.0 Basics 0 August 2nd, 2006 09:43 AM
Flushing datatable to an Excel file... livehed General .NET 0 January 13th, 2006 08:21 AM
Exporting to Excel swhite Access VBA 2 September 18th, 2003 09:42 AM
Exporting to Excel JJ Access 1 June 9th, 2003 11:30 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.