0

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,

activebiz
  • 6,000
  • 9
  • 41
  • 64
  • 1
    Do not use excel in a web environment, the probabillity of bugs is 110%... – Christian Sauer Oct 25 '13 at 07:38
  • Using OpenXML will avoid having to install Excel on the server. However, OpenXML will only allow you to modify an Excel file. To perform the computations you will have to open the file in Excel and doing that in a web application will probably involve hacks and frustration. – Martin Liversage Oct 25 '13 at 09:02
  • @MartinLiversage I think for calculation part I can do something like this... http://stackoverflow.com/questions/2668643/openxml-sdk-make-excel-recalculate-formula/4263285#4263285 – activebiz Oct 25 '13 at 09:45

3 Answers3

2

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

Rex
  • 2,130
  • 11
  • 12
  • Thanks @Rex, Are you saying it will not be possible to process more then 5 copies of excel on a single web server? – activebiz Oct 25 '13 at 08:24
  • i happened with my service servers - winXP pro. But I don't think you need that many instances, do you? – Rex Oct 25 '13 at 08:30
  • Another option is to use singleton and single thread and keep one excel instance live for ever on your server. i did a quick example in vb.net, see the editted part of my post. if you need, i can make it in C# version and just give me your email address. – Rex Oct 25 '13 at 08:43
  • What I was thinking along the line of .... open one instance (copy) of excel per web session, do the work, close down and delete the excel file. – activebiz Oct 25 '13 at 09:42
  • 1
    I guess you would then encounter a lot frustrations... you cannot control how many concurrent sessions are there, though it might work with your DEV environment as you probably have one or two session at one time – Rex Oct 25 '13 at 10:05
0

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.

Derek
  • 8,300
  • 12
  • 56
  • 88
  • Thanks @Derek, Does anyone knows any libraries which can either convert Math Formula (e.g. from word) or Excel formula to .NET/XML ? – activebiz Oct 25 '13 at 07:58
0

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

Ross
  • 300
  • 1
  • 8