CODEWITHSUNDEEP
asp.net.netmultithreadingexcelopenxml
activebiz
activebiz

Reputation: 6238

Using Excel as template for .NET code

I am currently in design phase of an application which at present running in Excel. Its a scientific application which contains complex mathematical (trigo. algebra etc.) formulas. Initially I tried to do everything in code and soon found out that its going to be very tedious (with high probability of bugs) to translate these scientific formulas into .NET.

Another option is to use the Excel spreadsheet and its cell formula's kind of "template" in which a user interface in .NET allows user to enters data , the .NET (web app) passes this input data to excel spreadsheet , the excel spreadsheet do the work and gives the results and the .NET app reads this and shows to the user. (This is a simplified explanation of the proposed design).

Its going to be Web Application (Not public facing site).

One of the problem (that I though of) would be when multiple user using the application, the excel file will have issue with multiple thread read/write. To Overcome this I have though about storing copy of the file in SQL Database, Copy it to temp folder with a Guid name (to make it unique) per session and once its done delete the file etc.

My question is, does anyone sees any flaws or drawback in this plan , for example performance, read/write excel etc or anything else that I need to consider? I am planning to Use OpenXML and ClosedXML library. Do I need to install Office on the deployment server for this to work? (I would have thought not).

Thanks,

Upvotes: 0

Views: 1016

Answers (3)

Ross
Ross

Reputation: 300

If you are automating Excel on then server, which can be full of issues, then you can just start a new instance of the app and open a copy of the workbook in that app instance, each time you webpage sends a request. Should be ok for a reasonably number of users - depending on hardware and the size of the sheet.

Ross

Upvotes: 0

Rex
Rex

Reputation: 2140

Personally I don't really think it's a bad idea - I did an application which generates a few hundred of spreadsheets (based on a few templates for different users) and I used ActiveMQ and Spring.Net service bus to make it faster enough - generally 3 mins to generate more than 200 spreadsheets:

I had about 5 dedicated servers running excel as service and the front-end application only sends requests and does not do the spreadsheet creation/write/read process.

The only problem I had is one excel service could not handle more than 10 sheets at a time, otherwise it will crash somehow (even now cannot figure out why...).

talking about security and auditing, it's also a good idea to store the request (in your case client input/spreadsheet instance maybe) into a database.

that's just my experience. forgot to mention, i used Office Interop instead of OpenXML and now I am thinking about adapting OpenXML.

---- EDIT ------------- Another option to avoid using multiple instances is to use a single threaded single-instance, a quick example (in vb.Net): 

Public Class MyCalculator

    Public Class ParamSet
        Property Param1 As Double
        Property Param2 As Long
        ' etc. & etc.
    End Class

    Public Class ResultSet
        Property Output1 As Double
        Property Output2 As Long
        ' etc. & etc.
    End Class

    Private Shared _instance As MyCalculator

    Public Shared Function Calculate(params As ParamSet) As ResultSet
        SyncLock _instance
            Return _instance.DoWork(params)
        End SyncLock
    End Function

    Shared Sub New()
        _instance = New MyCalculator()
    End Sub

    Private Sub New()
        ' Start up excel instance
        ' Set UserControl = true - to make sure it won't be killed by the system automatically
        ' Hold the excel instance in a local variable
    End Sub

    Private Function DoWork(params As ParamSet) As ResultSet
        ' write the params to the excel sheet
        ' then read output you need from corresponding cell
        ' return it out as a result set
    End Function

End Class

the singleton plus SyncLock will ensure you have one single instance of excel and believe it would be fast enough for your case - it only read/write from excel sheet and the excel instance will be kept live on server.

and it has the benefit that you can easily replace excel calculation with any other means, for example, your own implementation.

of course, drawbacks are obvious: all requests will be processed one by one as it's single threaded. but I believe it will be fast enough - the slow part (excel start up) has been taken out and now only read-write from sheet. about fast read/write from excel, there are plenty of resources online

Upvotes: 2

Derek
Derek

Reputation: 8630

Although sticking to your original plan may be tedious, it is still a better solution than reading / writing to excel spreadsheets.

I'm sure that if you look hard enough you will find solutions to replicating the formulae from your excel spreadsheet into a .Net application, besides you could also treat it as a learning curve.

Have you ever came across http://linqtocsv.codeplex.com/

This could be of benefit to you if you intend on sticking with a spreadsheet, but using a SQL Database with a .Net application is a more robust system and solves any issues where multiple users trying to access your data. You can use Entity Framework or NHibernate too.

Excel isn't designed for multiple users accessing it the same time, I think you would end up with a lot of problems down the line.

Upvotes: 0

Related Questions