Excel vs. Office Web Components

[DC Gringo]

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.Visua lBasic
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.Coun t - 1
oCells(2, iCol + 1) = dt.Columns(iCol ).ToString
Next

'Output Data
For iRow = 0 To dt.Rows.Count - 1
dr = dt.Rows.Item(iR ow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
oCells(iRow + 3, iCol + 1) = ary(iCol).ToStr ing
Response.Write( ary(iCol).ToStr ing & vbTab)
Next
Next
End Function

Public Sub btnCommunitiesE xcel_OnClick(By Val sender As System.Object,
ByVal e As System.EventArg s)
Dim oExcel As New Excel.Applicati on
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("savedC ommunitiesSql") ,
connection1.con String)
da.Fill(ds, "CommunitiesExc el")
Dim dt As DataTable = ds.Tables("Comm unitiesExcel")

sFile = Server.MapPath( Request.Applica tionPath) & _
"\advanced\Exce lExports\Commun ities.xls"

sTemplate = Server.MapPath( Request.Applica tionPath) & _
"\advanced\Exce lExports\Commun itiesTemplate.x ls"

oExcel.Visible = False : oExcel.DisplayA lerts = False

'Start a new workbook
oBooks = oExcel.Workbook s
oBooks.Open(Ser ver.MapPath(Req uest.Applicatio nPath) & _
"\advanced\Exce lExports\Commun itiesTemplate.x ls") 'Load colorful
template with chart
oBook = oBooks.Item(1)
oSheets = oBook.Worksheet s
oSheet = CType(oSheets.I tem(1), Excel.Worksheet )
oSheet.Name = "First Sheet"
oCells = oSheet.Cells

DumpData(dt, oCells) 'Fill in the data

oSheet.SaveAs(s File) 'Save in a temporary file
oBook.Close()

'Quit Excel and thoroughly deallocate everything
oExcel.Quit()
ReleaseComObjec t(oCells) : ReleaseComObjec t(oSheet)
ReleaseComObjec t(oSheets) : ReleaseComObjec t(oBook)
ReleaseComObjec t(oBooks) : ReleaseComObjec t(oExcel)
oExcel = Nothing : oBooks = Nothing : oBook = Nothing
oSheets = Nothing : oSheet = Nothing : oCells = Nothing
System.GC.Colle ct()
Response.Redire ct(sFile) 'Send the user to the file

'impersonationC ontext.Undo()

End Sub

[Steve C. Orr [MVP, MCSD]]

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" <dc******@visio ntechnology.net > wrote in message
news:O5******** *****@TK2MSFTNG P12.phx.gbl...

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.Visua lBasic
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.Coun t - 1
oCells(2, iCol + 1) = dt.Columns(iCol ).ToString
Next

'Output Data
For iRow = 0 To dt.Rows.Count - 1
dr = dt.Rows.Item(iR ow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
oCells(iRow + 3, iCol + 1) = ary(iCol).ToStr ing
Response.Write( ary(iCol).ToStr ing & vbTab)
Next
Next
End Function

Public Sub btnCommunitiesE xcel_OnClick(By Val sender As System.Object,
ByVal e As System.EventArg s)
Dim oExcel As New Excel.Applicati on
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("savedC ommunitiesSql") ,
connection1.con String)
da.Fill(ds, "CommunitiesExc el")
Dim dt As DataTable = ds.Tables("Comm unitiesExcel")

sFile = Server.MapPath( Request.Applica tionPath) & _
"\advanced\Exce lExports\Commun ities.xls"

sTemplate = Server.MapPath( Request.Applica tionPath) & _
"\advanced\Exce lExports\Commun itiesTemplate.x ls"

oExcel.Visible = False : oExcel.DisplayA lerts = False

'Start a new workbook
oBooks = oExcel.Workbook s
oBooks.Open(Ser ver.MapPath(Req uest.Applicatio nPath) & _
"\advanced\Exce lExports\Commun itiesTemplate.x ls") 'Load colorful
template with chart
oBook = oBooks.Item(1)
oSheets = oBook.Worksheet s
oSheet = CType(oSheets.I tem(1), Excel.Worksheet )
oSheet.Name = "First Sheet"
oCells = oSheet.Cells

DumpData(dt, oCells) 'Fill in the data

oSheet.SaveAs(s File) 'Save in a temporary file
oBook.Close()

'Quit Excel and thoroughly deallocate everything
oExcel.Quit()
ReleaseComObjec t(oCells) : ReleaseComObjec t(oSheet)
ReleaseComObjec t(oSheets) : ReleaseComObjec t(oBook)
ReleaseComObjec t(oBooks) : ReleaseComObjec t(oExcel)
oExcel = Nothing : oBooks = Nothing : oBook = Nothing
oSheets = Nothing : oSheet = Nothing : oCells = Nothing
System.GC.Colle ct()
Response.Redire ct(sFile) 'Send the user to the file

'impersonationC ontext.Undo()

End Sub

[DC Gringo]

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]" <St***@Orr.ne t> wrote in message
news:eq******** *****@TK2MSFTNG P12.phx.gbl...

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" <dc******@visio ntechnology.net > wrote in message
news:O5******** *****@TK2MSFTNG P12.phx.gbl...

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.Visua lBasic
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.Coun t - 1
oCells(2, iCol + 1) = dt.Columns(iCol ).ToString
Next

'Output Data
For iRow = 0 To dt.Rows.Count - 1
dr = dt.Rows.Item(iR ow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
oCells(iRow + 3, iCol + 1) = ary(iCol).ToStr ing
Response.Write( ary(iCol).ToStr ing & vbTab)
Next
Next
End Function

Public Sub btnCommunitiesE xcel_OnClick(By Val sender As System.Object,
ByVal e As System.EventArg s)
Dim oExcel As New Excel.Applicati on
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("savedC ommunitiesSql") ,
connection1.con String)
da.Fill(ds, "CommunitiesExc el")
Dim dt As DataTable = ds.Tables("Comm unitiesExcel")

sFile = Server.MapPath( Request.Applica tionPath) & _
"\advanced\Exce lExports\Commun ities.xls"

sTemplate = Server.MapPath( Request.Applica tionPath) & _
"\advanced\Exce lExports\Commun itiesTemplate.x ls"

oExcel.Visible = False : oExcel.DisplayA lerts = False

'Start a new workbook
oBooks = oExcel.Workbook s
oBooks.Open(Ser ver.MapPath(Req uest.Applicatio nPath) & _
"\advanced\Exce lExports\Commun itiesTemplate.x ls") 'Load colorful
template with chart
oBook = oBooks.Item(1)
oSheets = oBook.Worksheet s
oSheet = CType(oSheets.I tem(1), Excel.Worksheet )
oSheet.Name = "First Sheet"
oCells = oSheet.Cells

DumpData(dt, oCells) 'Fill in the data

oSheet.SaveAs(s File) 'Save in a temporary file
oBook.Close()

'Quit Excel and thoroughly deallocate everything
oExcel.Quit()
ReleaseComObjec t(oCells) : ReleaseComObjec t(oSheet)
ReleaseComObjec t(oSheets) : ReleaseComObjec t(oBook)
ReleaseComObjec t(oBooks) : ReleaseComObjec t(oExcel)
oExcel = Nothing : oBooks = Nothing : oBook = Nothing
oSheets = Nothing : oSheet = Nothing : oCells = Nothing
System.GC.Colle ct()
Response.Redire ct(sFile) 'Send the user to the file

'impersonationC ontext.Undo()

End Sub

[Steve C. Orr [MVP, MCSD]]

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" <dc******@visio ntechnology.net > wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..

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]" <St***@Orr.ne t> wrote in message
news:eq******** *****@TK2MSFTNG P12.phx.gbl...

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" <dc******@visio ntechnology.net > wrote in message
news:O5******** *****@TK2MSFTNG P12.phx.gbl...

> 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.Visua lBasic
> 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.Coun t - 1
> oCells(2, iCol + 1) = dt.Columns(iCol ).ToString
> Next
>
> 'Output Data
> For iRow = 0 To dt.Rows.Count - 1
> dr = dt.Rows.Item(iR ow)
> ary = dr.ItemArray
> For iCol = 0 To UBound(ary)
> oCells(iRow + 3, iCol + 1) = ary(iCol).ToStr ing
> Response.Write( ary(iCol).ToStr ing & vbTab)
> Next
> Next
> End Function
>
> Public Sub btnCommunitiesE xcel_OnClick(By Val sender As
> System.Object,
> ByVal e As System.EventArg s)
> Dim oExcel As New Excel.Applicati on
> 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("savedC ommunitiesSql") ,
> connection1.con String)
> da.Fill(ds, "CommunitiesExc el")
> Dim dt As DataTable = ds.Tables("Comm unitiesExcel")
>
> sFile = Server.MapPath( Request.Applica tionPath) & _
> "\advanced\Exce lExports\Commun ities.xls"
>
> sTemplate = Server.MapPath( Request.Applica tionPath) & _
> "\advanced\Exce lExports\Commun itiesTemplate.x ls"
>
> oExcel.Visible = False : oExcel.DisplayA lerts = False
>
> 'Start a new workbook
> oBooks = oExcel.Workbook s
> oBooks.Open(Ser ver.MapPath(Req uest.Applicatio nPath) & _
> "\advanced\Exce lExports\Commun itiesTemplate.x ls") 'Load colorful
> template with chart
> oBook = oBooks.Item(1)
> oSheets = oBook.Worksheet s
> oSheet = CType(oSheets.I tem(1), Excel.Worksheet )
> oSheet.Name = "First Sheet"
> oCells = oSheet.Cells
>
> DumpData(dt, oCells) 'Fill in the data
>
> oSheet.SaveAs(s File) 'Save in a temporary file
> oBook.Close()
>
> 'Quit Excel and thoroughly deallocate everything
> oExcel.Quit()
> ReleaseComObjec t(oCells) : ReleaseComObjec t(oSheet)
> ReleaseComObjec t(oSheets) : ReleaseComObjec t(oBook)
> ReleaseComObjec t(oBooks) : ReleaseComObjec t(oExcel)
> oExcel = Nothing : oBooks = Nothing : oBook = Nothing
> oSheets = Nothing : oSheet = Nothing : oCells = Nothing
> System.GC.Colle ct()
> Response.Redire ct(sFile) 'Send the user to the file
>
> 'impersonationC ontext.Undo()
>
> End Sub
>
>
>