hi,

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
        oBook.Close()
        'Quit Excel and thoroughly deallocate everything
        oExcel.Quit()
        ReleaseComObject(oCells) : ReleaseComObject(oSheet)
        ReleaseComObject(oSheets) : ReleaseComObject(oBook)
        ReleaseComObject(oBooks) : ReleaseComObject(oExcel)
        oExcel = Nothing : oBooks = Nothing : oBook = Nothing
        oSheets = Nothing : oSheet = Nothing : oCells = Nothing
        System.GC.Collect()
        '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.Clear()
            Response.AddHeader("Content-Disposition", "attachment; filename=" & file.Name)
            Response.AddHeader("Content-Length", file.Length.ToString())
            Response.ContentType = "application/octet-stream"
            Response.WriteFile(file.FullName)
            Response.End() 'if file does not exist
        Else
            Response.Write("This file does not exist.")
        End If 'nothing in the URL as HTTP GET

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

                Dim ds As New DataSet
                ds.ReadXml(strXMLFile)
                Dim xdd As New XmlDataDocument(ds)

                With Page.Response
                    .Clear()
                    .ContentType = "application/vnd.ms-excel"
                    .Charset = ""
                    myXSLDoc.Transform(xdd, Nothing, .OutputStream, xmlRez)
                    .End()
                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."