ASP .NET 2.5 - VB - Excel 2003 - Spreadsheet population

[Richard]

Hello,

This is my setup:

Application's front-end tools:
Visual Studio 2005, ASP .Net 2.5, VB, Excel 2003
Web server running Win Server 2003

I have a pre-formatted spreadsheet (that's why this cannot be done with a
..csv file) in Excel 2003. It needs to be populated with data retrieved from
SQL server with a stored procedure. After populated the resulting spreadsheet
file needs to be accessible to the web application's user in their local PC,
so (s)he can open it and add other data by directly editing the spreadsheet
outside the application.

How it works now is: User clicks button on a page. On the web server,
there's code to make a copy of the original spreadsheet we're using as a
template, the copied file is opened, and the cells to be populated are
referenced using Excel's named ranges. The file is saved on the web server,
and delivered to the client browser, so the user gets a dialog box asking to
either open or save the spreadsheet. To process the spreadsheet on the server
the application uses the following references:

office.dll
Microsoft.Vbe.Interop.dll
Microsoft.Office.Interop.Excel.dll

The code that opens the spreadsheet is:

Dim oExcelApplication As Microsoft.Office.Interop.Excel.ApplicationClass =
New Microsoft.Office.Interop.Excel.ApplicationClass

and then calling the oExcelApplication's
..WorkBooks.Open(sSpreadsheetFileName) method.

When WorkBooks.Open() is executed, an instance of EXCEL is ran on the web
server (I can see it using Windows Process Viewer). Here is the problem:

By company policy Excel cannot be installed on the server!

I have a few questions:

Q#1) Arent the office automation DLLs the project is using supposed to
manipulate Excel files w/o running Excel?

Q#2) What are my options?

I've been thinking about the following alternative implementations:

I#1) Populate the sheet in the client by running Excel from Javascript code
(problem is each user must have the templates in their local machines, and
Javascript must have access to local folders).

I#2) Populate the sheet in the server using OfficeOpenXml.

Q#3) Can I reference the OfficeOpenXml package from Net 2.5 ? (I don't think
so).

Your help is greatly appreciated,

Sincerely,

Richard

 

[Mark Stevens]

Richard,

Firstly, the method you have started to suggest (running Excel on the
web server) is not supported by MS as it can cause some issues. In
fact there is an article on MSDN which warns against doing this.

Does this have to be Excel 2003 format? I have used an Open XML
library from Codeplex (SimpleOOXML) to generate Excel 2007 files and
this works well. Not tried named ranges but I do paste whole chunks
of data from a DataSet into a worksheet and that works fine. Reason
for the question about Excel 2003 is that I have only worked with 2007
format with this library.

Regards,
Mark

Hello,

This is my setup:

Application's front-end tools:
Visual Studio 2005, ASP .Net 2.5, VB, Excel 2003
Web server running Win Server 2003

I have a pre-formatted spreadsheet (that's why this cannot be done with a
.csv file) in Excel 2003. It needs to be populated with data retrieved from
SQL server with a stored procedure. After populated the resulting spreadsheet
file needs to be accessible to the web application's user in their local PC,
so (s)he can open it and add other data by directly editing the spreadsheet
outside the application.

How it works now is: User clicks button on a page. On the web server,
there's code to make a copy of the original spreadsheet we're using as a
template, the copied file is opened, and the cells to be populated are
referenced using Excel's named ranges. The file is saved on the web server,
and delivered to the client browser, so the user gets a dialog box asking to
either open or save the spreadsheet. To process the spreadsheet on the server
the application uses the following references:

office.dll
Microsoft.Vbe.Interop.dll
Microsoft.Office.Interop.Excel.dll

The code that opens the spreadsheet is:

Dim oExcelApplication As Microsoft.Office.Interop.Excel.ApplicationClass =
New Microsoft.Office.Interop.Excel.ApplicationClass

and then calling the oExcelApplication's
.WorkBooks.Open(sSpreadsheetFileName) method.

When WorkBooks.Open() is executed, an instance of EXCEL is ran on the web
server (I can see it using Windows Process Viewer). Here is the problem:

By company policy Excel cannot be installed on the server!

I have a few questions:

Q#1) Arent the office automation DLLs the project is using supposed to
manipulate Excel files w/o running Excel?

Q#2) What are my options?

I've been thinking about the following alternative implementations:

I#1) Populate the sheet in the client by running Excel from Javascript code
(problem is each user must have the templates in their local machines, and
Javascript must have access to local folders).

I#2) Populate the sheet in the server using OfficeOpenXml.

Q#3) Can I reference the OfficeOpenXml package from Net 2.5 ? (I don't think
so).

Your help is greatly appreciated,

Sincerely,

Richard

--
|\ _,,,---,,_ A picture used to be worth a
ZZZzzz /,`.-'`' -. ;-;;, thousand words - then along
|,4- ) )-,_. ,\ ( `'-' came television!
'---''(_/--' `-'\_)

Mark Stevens (mark at thepcsite fullstop co fullstop uk)

This message is provided "as is".