3

I just started my internship as a computer science student. The project I'm working on is web based price calculation. Previously, the price calculation have been done in a quite huge excel spreadsheet, taking a ton of factors into account when calculating. My first thought when tasked with this, was to study the spreadsheet and make some code that did it all. But, then I was presented with a problem: I'll only be here for 3 months and there are nobody in the office that can do any coding, so how are they going to maintain the calculations when new factors have to be implemented? Therefore, they would far prefer to keep the spreadsheet and have some code that interact with the spreadsheet to calculate the price. But in a server environment, Excel won't be running and as far as I've been able to research, interactions via oledb, only access the data of the spreadsheet and none of the formula will update when new data is being input.

So, my question is: are there any ways to solve this issue with having the spreadsheet formula update without having excel running on a server, or are there any other easy maintainable way for people without coding knowledge to use, to implement the business logic?

Hiromi
  • 51
  • 1
  • 6
    There is a good software-engineering question hidden in here; it is an architectural and organizational one, unfortunately, the community here seems to mistake your post as a request for "3rd party resources". I hope those "lets all close the things we don't understand" fraction does not win again here. – Doc Brown Aug 20 '19 at 08:44
  • 2
    Kudos to you for thinking about long term maintainability and appropriate solutions for the company you are working in. But I would add that asking your boss about who will have responsibility for your creation after you are gone is also a very appropriate question and may influence your solution. – Peter M Aug 20 '19 at 13:04
  • [Force Recalculation of Workbook / Worksheet using SpreadsheetML](http://www.ericwhite.com/blog/force-recalculation-of-workbook-worksheet-using-spreadsheetml/)... Not a complete solution to your problem, because it still stands up the Excel engine to do the recalculation. It's too bad that the OpenXML SDK can't do this for you. – Robert Harvey Aug 20 '19 at 15:23
  • @DocBrown I've done my best to help without damning the question but there are obvious problems. If it is a straight request about how to hook in Excel, it is a *resource request*. If it is about how to support a system with no technical staff, it is *too broad*. If it is about migrating the existing Excel solution then without a steer it is *unclear what they're asking*. There are bits we can progress but taken as a whole it just seems far too woolly. – Robbie Dee Aug 20 '19 at 15:45
  • @RobertHarvey: maybe you misunderstood the question? There are well-known recommendations from Microsoft not run Excel on a Server, because this leads to various issues. This is not a problem of getting Excel to do automatically some recalculations. This is a question for a system architecture which can *replace* Excel as a calculation tool without creating a situation where a developer is required every day for doing the work of the business departments. – Doc Brown Aug 20 '19 at 17:13
  • @DocBrown: Yeah, I get it. But sometimes you gotta do what you gotta do. We recently rewrote an interop module that produced generated PowerPoints, replacing it with the OpenXML SDK, and got a 60x speed pop and tremendous stability improvements. But we weren't recalculating a spreadsheet, nor were we running it on a server. It's just a lead, that's all. If I thought it was a real answer, I would have posted it as such. – Robert Harvey Aug 20 '19 at 17:16
  • @RobbieDee: IMHO this is not a question on "how to hook in Excel", see my comment above. And honestly, I think you put the bar very high - going that route you can probably find reasons to close 80% of the questions even Robert does not put on hold as "too broad" or "unclear. ;-) It seems you found the question "answerable enough", and so did I. And both of us came up with a pretty concise answer. – Doc Brown Aug 20 '19 at 17:20
  • @DocBrown You make a good point. I guess it isn't always about finding the perfect solution. If we can provide at least some pearls of wisdom it helps the OP and others who may stumble across this post later. – Robbie Dee Aug 21 '19 at 10:22
  • Computations, without too much code, on the web? I would suggest a BI solution. Could be MS Power BI, Qlik, Tableau, SAS VA...There are many alternatives. –  Aug 31 '19 at 20:12

3 Answers3

4

You wrote

The project I'm working on is web based price calculation

and

I'll only be here for 3 months and there are nobody in the office that can do any coding

Note that these two statements are already showing the main issue here: if the company for which you are working really wants to offer a web based price calculation in a permanent fashion - they have to invest into some permanent software maintenance.

Up to this point this has not much to do with the fact how they currently run their price calculations, or if those are done in Excel. If you run a custom Web site with own business logic, you need a specialist for fixing the bugs, adding new features, keep an eye on security issues, and so on. There is no way around this!

Let me sketch an approach which may require only little maintenance. It may be possible for you to build a solution where the business people maintain their price formulas in Excel, and whenever they are happy with this, automate the process of taking out the numbers and formulas from the sheet and generate some equivalent C# code for running it on the web server. Such code generators can be simple or hard to build, depending on how complex the current formulas are, and if the business is willing to accept some restrictions for that spreadsheet.

A variant of this is building an interpreter instead of a generator, in which case the web application reads the Excel sheet directly using a library suitable for running on a web server. Of course, the application will still have to contain the code to evaluate the formulas and cannot rely on Excel to do this.

So yes, you should aim for a solution where not every change of one VAT factor in one simple formula requires a developer to build a new release of the web site. But no, your company should not expect to run the Web site with no-one at hand being qualified to make changes to the code.

See also this older SO post: How do you use Excel server-side? There is also a commercial component mentioned which may be able to run Excel-compatible formula evaluations on a server, but since I have no experience with it, I cannot tell you anything about it.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
1

The fact that they've asked you to do this suggests that they're at least willing to look at other ways of doing this - perhaps as a proof of concept/trial balloon.

If you design it in a modular way, the fact that nobody codes isn't an absolute barrier in itself (although it is a substantial risk given the paucity of expertise). As long as they can edit the calculations in an isolated way and then build and deploy your solution, they should be able to make changes.

Ideally, they should also be able to run unit tests to prove that their changes work as required.

As a first step, I'd clarify whether the spreadsheet absolutely must be part of the solution as this will have a huge impact on your design.

Robbie Dee
  • 9,717
  • 2
  • 23
  • 53
1

Yes...sort off.

You could migrate the excel sheet to google sheets, which can do things automatically and is online. Since it's quite similar to excel the people at the company should not have problem maintaining it, and it can be done in less than 3 months.

However you'd have to revise the server environment and issues regarding access or even adding scripts.

deags
  • 111
  • 2
  • Sounds like an excellent idea. The company has to trust Google, of course. – Doc Brown Aug 20 '19 at 20:53
  • I've find no problem with google docs overall and then getting business google mini-environment is very cheap, which should ensure the security part, not to mention they can be used in collaboration and there's a clear log of activities. BUT... the scripts. Much can be achieved with formulas in the sheets but scripts do require a special type of 'access' even if they are done just for that (at least from the lego user perspective). So it could be that scripts wont be allowed for security reasons. Even then, It's viable for OPs case. – deags Aug 22 '19 at 15:52