Last post Jul 09, 2015 12:40 PM by PatriceSc
Jul 05, 2015 10:20 PM|gc12345|LINK
I am building a page that opens a copy of an Excel file, insert user inputs onto a Worksheet, some calculation is done by Excel, and then ASP exports another Worksheet with calculation results to the web user.
However instead of opening the Workbook and closing it immediately, I want the Excel Workbook to remain open for the duration of the user's session, so the user can update 5~10 different parameters, and see result calculated by Excel.
How can this be achieved? Right now I am connecting to Excel thru Microsoft.Office.Interop.Excel.Application
I understand that this is not the ideal solution, but it's a given requirement, and the project is not large scale, there will just be 3~4 users.
Jul 06, 2015 02:42 AM|Mikesdotnetting|LINK
You can save the object that represents the workbook in a session variable.
Jul 06, 2015 04:35 AM|gc12345|LINK
Thanks for that idea, I'll look into how to save the object in Session variable (as I'm a noob in ASP)
So my ASP code will leave Excel Workbooks (one copy for each user Session) lying around on the server. But then how do I trigger a code to Close the Workbook if the user went MIA and Session expired?
If the user completed the action (e.g they did some action to close the case in the front-end) I can trigger closing Excel manually.
Jul 06, 2015 04:50 AM|Mikesdotnetting|LINK
But then how do I trigger a code to Close the Workbook if the user went MIA and Session expired?
I'll look into how to save the object in Session variable
// saving to session variable
Session["workbook"] = workBook;
//retrieving from Session variable
var workBook = (Excel.WorkBook)Session["workbook"];
Jul 06, 2015 06:11 AM|gc12345|LINK
Thanks, that helped a lot
However I can't seem to find the Session_End event, it's not in my global.asax
And where would I define the Excel object so that it is also accessible by Session_End event ?
Jul 06, 2015 10:34 AM|Mikesdotnetting|LINK
You can add the event handler to global.asax:
Sub Session_End(sender As Object, e As EventArgs)
You add a workbook to a session variable wherever you need to (usually in your code behind).
Jul 09, 2015 12:40 PM|PatriceSc|LINK
Or let them to update all parameters and generate the worksheet once for all? I'm not sure to get why you need to keep the Excel workbook opened.
- using server side automation is not recommended. Excel was not done to be automated server side.
http://www.codeproject.com/Articles/670141/Read-and-Write-Microsoft-Excel-with-Open-XML-SDK or a 3rd party library could help
- make sure to understand what happens client side and server side. Sometimes new devs are caugth when realizing that what they have done on their dev box can't and won't work with a real web server (it seemed ok just because the same machine was used as both
the browser client and the web server).
How does your page opens the Workbook? You CAN'T do that from server side code (on a real web server at best it would open Excel ON THE SERVER). I'm afraid you are currently doing something like that.