Excel vs. Office Web Components

Discussion in 'ASP .Net' started by DC Gringo, Feb 17, 2005.

  1. DC Gringo

    DC Gringo Guest

    Allison (or others), thank you for the advice...a few more questions:

    - I have tested on my workstation on Excel XP and my application references
    the Excel 10.0 Object Library. I was told the server has the "Office 2003"
    components which I'm assuming is OWC11. How do my imports, declarations or
    other code change to account for the components versus having Excel
    installed?


    Imports Microsoft.VisualBasic
    Imports System.Data
    Imports System.Runtime.InteropServices.Marshal


    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

    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")

    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

    'impersonationContext.Undo()

    End Sub
    DC Gringo, Feb 17, 2005
    #1
    1. Advertising

  2. The code you supplied below should work with both Office XP and Office 2003.
    I should know since I wrote it and I've tested it with both versions of
    Office.
    ;-)

    --
    I hope this helps,
    Steve C. Orr, MCSD, MVP
    http://SteveOrr.net


    "DC Gringo" <> wrote in message
    news:O5iqE$...
    > Allison (or others), thank you for the advice...a few more questions:
    >
    > - I have tested on my workstation on Excel XP and my application
    > references
    > the Excel 10.0 Object Library. I was told the server has the "Office
    > 2003"
    > components which I'm assuming is OWC11. How do my imports, declarations
    > or
    > other code change to account for the components versus having Excel
    > installed?
    >
    >
    > Imports Microsoft.VisualBasic
    > Imports System.Data
    > Imports System.Runtime.InteropServices.Marshal
    >
    >
    > 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
    >
    > 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")
    >
    > 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
    >
    > 'impersonationContext.Undo()
    >
    > End Sub
    >
    >
    >
    Steve C. Orr [MVP, MCSD], Feb 17, 2005
    #2
    1. Advertising

  3. DC Gringo

    DC Gringo Guest

    Steve,

    Yes, it works perfectly with Office XP. But I need it to work with Office
    Web Components that are installed on the server. What do I need to do to
    this to make it work?

    _____
    DC G


    "Steve C. Orr [MVP, MCSD]" <> wrote in message
    news:...
    > The code you supplied below should work with both Office XP and Office

    2003.
    > I should know since I wrote it and I've tested it with both versions of
    > Office.
    > ;-)
    >
    > --
    > I hope this helps,
    > Steve C. Orr, MCSD, MVP
    > http://SteveOrr.net
    >
    >
    > "DC Gringo" <> wrote in message
    > news:O5iqE$...
    > > Allison (or others), thank you for the advice...a few more questions:
    > >
    > > - I have tested on my workstation on Excel XP and my application
    > > references
    > > the Excel 10.0 Object Library. I was told the server has the "Office
    > > 2003"
    > > components which I'm assuming is OWC11. How do my imports, declarations
    > > or
    > > other code change to account for the components versus having Excel
    > > installed?
    > >
    > >
    > > Imports Microsoft.VisualBasic
    > > Imports System.Data
    > > Imports System.Runtime.InteropServices.Marshal
    > >
    > >
    > > 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
    > >
    > > 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")
    > >
    > > 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
    > >
    > > 'impersonationContext.Undo()
    > >
    > > End Sub
    > >
    > >
    > >

    >
    >
    DC Gringo, Feb 18, 2005
    #3
  4. You mean without Excel being installed on the server?
    I'd be surprised if the code would work at all.
    There are many ways you can export to Excel without Excel having to be
    installed on the server, but I don't think this is one of them.
    For some other ideas look here:
    http://SteveOrr.net/Articles/ExcelExport.aspx
    http://SteveOrr.net/Articles/ExportPanel.aspx
    http://SteveOrr.net/export.aspx

    --
    I hope this helps,
    Steve C. Orr, MCSD, MVP
    http://SteveOrr.net



    "DC Gringo" <> wrote in message
    news:%...
    > Steve,
    >
    > Yes, it works perfectly with Office XP. But I need it to work with Office
    > Web Components that are installed on the server. What do I need to do to
    > this to make it work?
    >
    > _____
    > DC G
    >
    >
    > "Steve C. Orr [MVP, MCSD]" <> wrote in message
    > news:...
    >> The code you supplied below should work with both Office XP and Office

    > 2003.
    >> I should know since I wrote it and I've tested it with both versions of
    >> Office.
    >> ;-)
    >>
    >> --
    >> I hope this helps,
    >> Steve C. Orr, MCSD, MVP
    >> http://SteveOrr.net
    >>
    >>
    >> "DC Gringo" <> wrote in message
    >> news:O5iqE$...
    >> > Allison (or others), thank you for the advice...a few more questions:
    >> >
    >> > - I have tested on my workstation on Excel XP and my application
    >> > references
    >> > the Excel 10.0 Object Library. I was told the server has the "Office
    >> > 2003"
    >> > components which I'm assuming is OWC11. How do my imports,
    >> > declarations
    >> > or
    >> > other code change to account for the components versus having Excel
    >> > installed?
    >> >
    >> >
    >> > Imports Microsoft.VisualBasic
    >> > Imports System.Data
    >> > Imports System.Runtime.InteropServices.Marshal
    >> >
    >> >
    >> > 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
    >> >
    >> > 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")
    >> >
    >> > 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
    >> >
    >> > 'impersonationContext.Undo()
    >> >
    >> > End Sub
    >> >
    >> >
    >> >

    >>
    >>

    >
    >
    Steve C. Orr [MVP, MCSD], Feb 18, 2005
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Julian Sanz

    Office Web Components

    Julian Sanz, Oct 30, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    455
    Julian Sanz
    Oct 30, 2003
  2. =?Utf-8?B?U2Vu?=

    Excel with Office Web Components

    =?Utf-8?B?U2Vu?=, Nov 3, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    396
    =?Utf-8?B?U2Vu?=
    Nov 3, 2005
  3. =?Utf-8?B?U2Vu?=

    Excel with Office Web Components

    =?Utf-8?B?U2Vu?=, Nov 3, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    594
    Alvin Bruney - ASP.NET MVP
    Nov 3, 2005
  4. Richard Crowley
    Replies:
    0
    Views:
    164
    Richard Crowley
    Nov 22, 2005
  5. Michael Hartshorn
    Replies:
    0
    Views:
    119
    Michael Hartshorn
    Jun 8, 2007
Loading...

Share This Page