button to export to excel

Discussion in 'ASP .Net' started by DC Gringo, Jan 31, 2005.

  1. DC Gringo

    DC Gringo Guest

    I have a simple button that should open another window and export a datagrid
    to an Excel file. I'm getting: "Name 'window' is not declared."

    What do I need to declare or import?


    <INPUT ID="Button5" ONCLICK="Button5_Click" NAME="Button5" TYPE="button"
    VALUE="Export to Excel">

    Sub Button5_Click()
    Dim sHTML
    sHTML = window.Form1.children("dgCommunities").outerhtml()
    Dim oXL, oBook
    oXL = CreateObject("Excel.Application")
    oBook = oXL.Workbooks.Add
    oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
    oBook.HTMLProject.RefreshDocument()
    oXL.Visible = True
    oXL.UserControl = True
    End Sub
     
    DC Gringo, Jan 31, 2005
    #1
    1. Advertisements

  2. Steve C. Orr [MVP, MCSD], Feb 1, 2005
    #2
    1. Advertisements

  3. Ken Cox [Microsoft MVP], Feb 1, 2005
    #3
  4. DC Gringo

    DC Gringo Guest

    Steve,

    This worked great with Excel installed. May I ask what needs to change when
    using Microsoft Office Web Components?

    _____
    DC G
     
    DC Gringo, Feb 14, 2005
    #4
  5. Here are some examples using Office Web Components for Office 2003:

    -http://msdn.microsoft.com/office/understanding/owc/default.aspx

    And here are some examples using Office Web Components for earlier versions:

    -http://msdn.microsoft.com/office/previous/components/default.aspx
     
    Steve C. Orr [MVP, MCSD], Feb 14, 2005
    #5
  6. DC Gringo

    DC Gringo Guest

    Steve,

    Yes, I've seen that, but I'm looking for a bit more guidance than such a
    huge reference.

    Plain and simple, I've got Excel installed on my workstation and my app
    works just fine. I upload it to our hosting provider and it doesn't.
    They've installed OWC latest edition, but to no avail. Here's my code:

    Public Sub btnCommunitiesExcel_OnClick(ByVal sender As System.Object, ByVal
    e As System.EventArgs)

    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 ds As New DataSet
    Dim da As New SqlDataAdapter(Session("savedCommunitiesSql"),
    connection1.conString)
    da.Fill(ds, "CommunitiesExcel")
    Dim dt As DataTable = ds.Tables("CommunitiesExcel")

    'Insert your code that runs under the security context of the authenticating
    user here.
    sFile = Server.MapPath(Request.ApplicationPath) & _
    "\advanced\ExcelExports\Communities.xls"
    sTemplate = Server.MapPath(Request.ApplicationPath) & _
    "\advanced\ExcelExports\CommunitiesTemplate.xls"
    oExcel.Visible = False : oExcel.DisplayAlerts = False

    'Start a new workbook
    oBooks = oExcel.Workbooks
    oBooks.Open(Server.MapPath(Request.ApplicationPath) & _
    "\advanced\ExcelExports\CommunitiesTemplate.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(dt, 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

    End Sub



    Private Function DumpData(ByVal _
    dt As DataTable, ByVal oCells As Excel.Range) As String
    Dim dr As DataRow, ary() As Object
    Dim iRow As Integer, iCol As Integer

    'Output Column Headers
    For iCol = 0 To dt.Columns.Count - 1
    oCells(2, iCol + 1) = dt.Columns(iCol).ToString
    Next

    'Output Data
    For iRow = 0 To dt.Rows.Count - 1
    dr = dt.Rows.Item(iRow)
    ary = dr.ItemArray
    For iCol = 0 To UBound(ary)
    oCells(iRow + 3, iCol + 1) = ary(iCol).ToString
    Response.Write(ary(iCol).ToString & vbTab)
    Next
    Next
    End Function
     
    DC Gringo, Feb 14, 2005
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.