Problems displaying Excel on aspx page

[Guest]

Hi There

I have been having a play around with the following code to display a
datagrid in Excel (all from Steve Orr's site):

Private Sub btnTechServAccred_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnTechServAccred.Click

Dim AcredDT As DataTable = UserSession.Current.AcredTable
Dim aExcel As New Microsoft.Office.Interop.Excel.Application
Dim aBooks As Microsoft.Office.Interop.Excel.Workbooks
Dim aBook As Microsoft.Office.Interop.Excel.Workbook
Dim aSheets As Microsoft.Office.Interop.Excel.Sheets
Dim aSheet3 As Microsoft.Office.Interop.Excel.Worksheet
Dim aSheet1 As Microsoft.Office.Interop.Excel.Worksheet
Dim aCells As Microsoft.Office.Interop.Excel.Range
Dim tFile As String, tTemplate As String

tFile = "C:\Accreditations\Excel.xls"
tTemplate = "C:\Accreditations\Template.xls"

aExcel.Visible = False : aExcel.DisplayAlerts = False

aBooks = aExcel.Workbooks
aBooks.Open("C:\Accreditations\Template.xls")

aBook = aBooks.Item(1)
aSheets = aBook.Worksheets
aSheet3 = CType(aSheets.Item(3),
Microsoft.Office.Interop.Excel.Worksheet)
aSheet1 = CType(aSheets.Item(1),
Microsoft.Office.Interop.Excel.Worksheet)
aSheet3.Name = "Raw Data"
aCells = aSheet3.Cells
DumpData(AcredDT, aCells)
aSheet1.SaveAs(tFile)
aBook.Close()

aExcel.Quit()
ReleaseComObject(aCells) : ReleaseComObject(aSheet3)
ReleaseComObject(aSheets) : ReleaseComObject(aBook)
ReleaseComObject(aBooks) : ReleaseComObject(aExcel)
aExcel = Nothing : aBooks = Nothing : aBook = Nothing
aSheets = Nothing : aSheet3 = Nothing : aCells = Nothing
System.GC.Collect()
Response.Redirect(tFile)
End Sub

Private Function DumpData(ByVal _
dt As DataTable, ByVal oCells As Microsoft.Office.Interop.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


This all works beautifully if I run the page from within Visual Studio 2003
- but as soon as I browse to the page using http://localhost/blah-de-blah it
fails at the point you would expect to see the message from the browser
asking if you want to open or save the file. Browser simply comes back with:

"The Page Cannot Be Displayed"

I get the impression that it is failing when it tries to overwrite the
"MyExcel" temporary document - and I have no doubt I am doing something daft
or have forgotten something obvious... any help would be appreciated.

I have set impersonation "Yes" and the IUSR account has permission to write
to the relevant excel files on the C: drive

Thanks

 

[Mark Rae]

Dim aExcel As New Microsoft.Office.Interop.Excel.Application

Server-side automation of Office products is not recommended, to the extent
that Microsoft won't actually support any solution which uses it...
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2

This all works beautifully if I run the page from within Visual Studio
2003

That's right - because Visual Studio is running on your local machine - i.e.
it's not server-side automation...

I get the impression that it is failing when it tries to overwrite the
"MyExcel" temporary document - and I have no doubt I am doing something
daft
or have forgotten something obvious... any help would be appreciated.

See above...

 

[Guest]

Thanks Mark

I'm more interested in why the solution I have proposed is not working... I
appreciate and accept the constraints imposed on this - but it should still
work should it not...?

This is to support a report that may be run very occasionally on an Intranet
site and only 2 windows accounts have access to it in the first place

If anyone can tell me what I have missed in the code, rather than the
planning and concept, I would appreciate it still

Thanks again

 

[Mark Rae]

I'm more interested in why the solution I have proposed is not working...
I
appreciate and accept the constraints imposed on this - but it should
still
work should it not...?

??? Did you actually read the MSDN article ???

 

[Hans Kesting]

Hi There

I have been having a play around with the following code to display a
datagrid in Excel (all from Steve Orr's site):
[snip]
This all works beautifully if I run the page from within Visual Studio 2003
- but as soon as I browse to the page using http://localhost/blah-de-blah it
fails at the point you would expect to see the message from the browser
asking if you want to open or save the file. Browser simply comes back with:

"The Page Cannot Be Displayed"

I get the impression that it is failing when it tries to overwrite the
"MyExcel" temporary document - and I have no doubt I am doing something daft
or have forgotten something obvious... any help would be appreciated.

I have set impersonation "Yes" and the IUSR account has permission to write
to the relevant excel files on the C: drive

Thanks

When you run from within VS2003, the site probably uses *your* account,
which probably has admin rights locally.
When you install it on the server, it probably runs as "NETWORK
SERVICES" (for Win2003) or "ASPNET" (for WinXP), not the "IUSR.."
account.
(And note the remarks from Mark Rae)

Hans Kesting

 

[b.engelbrecht]

Few remarks that may help you:
- On a Windows 2003 server, you may create a second application pool
and set its identity to "Local System" instead of "Network services".
Assign that pool to the asp site that you want to write local files.
Note that for security you should not have asp scripts use this pool
unless they absolutely need to. One other right that "network user" is
missing is the ability to spawn executable processes, that may
interfere with starting the Excel engine.
- If it is possible that the target file exists, you need not only
DisplayAlerts = False but also AlertBeforeOverwriting = False, else the
process will stop at the moment that SaveAs is called and the target
exists. In Excel 2003, DisplayAlerts=false does not suppress the
overwrite message box.
- If Excel still can't be run from ASP, you could make an external
application that creates the excel object. Running an external
application will work if you set the application pool to "Local
system".

Best regards,

Berend


Stuart schreef:

Thanks

This turns out to be something daft as predicted... if you put the saveas
file path to the wwwroot/Project Folder of course all processes and relevant
users are able to manipulate the files in this location... this is not the
case in my test directory of C:\ (for obvious reasons)

I feel thoroughly and duly warned... - but as I was implying - Microsoft do
not recommend this for certain reasons... none of which are "It doesn't work
at all" - I probably will not use this process - but needed to know why it
did not work !

Thanks again

Hi There

I have been having a play around with the following code to display a
datagrid in Excel (all from Steve Orr's site):
[snip]
This all works beautifully if I run the page from within Visual Studio 2003
- but as soon as I browse to the page using http://localhost/blah-de-blah it
fails at the point you would expect to see the message from the browser
asking if you want to open or save the file. Browser simply comes back with:

"The Page Cannot Be Displayed"

I get the impression that it is failing when it tries to overwrite the
"MyExcel" temporary document - and I have no doubt I am doing something daft
or have forgotten something obvious... any help would be appreciated.

I have set impersonation "Yes" and the IUSR account has permission to write
to the relevant excel files on the C: drive

Thanks

When you run from within VS2003, the site probably uses *your* account,
which probably has admin rights locally.
When you install it on the server, it probably runs as "NETWORK
SERVICES" (for Win2003) or "ASPNET" (for WinXP), not the "IUSR.."
account.
(And note the remarks from Mark Rae)

Hans Kesting