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
