Is there a cross-compiler for Excel?

[BNPMike]

Have you seen a cross-compiler that turns Excel into something that can run on a server?

Googling, all I can see is things that turn Excel into a package. The spreadsheet seems to still be/need Excel.

I can see somethings would be tricky to translate (eg Pivot tables) but I guess the main challenge of translating is to retain the dependency graph - you can't just translate each function; you have to know what order to execute them in.

I'm kind-of thinking F# might be a good target as it is a functional platform like Excel.

 

[firefytr]

Hello,

Can you be more specific? What are you trying to do here exactly? Are you wanting to view Excel, but not using Excel?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[BNPMike]

Yes - not very clear, Zack.

I'm treating Excel as a model. You put some figures in, press enter and it calculates some other figures. Once you're satisfied your model is what you want, you could then change it from Excel into something Microsoft like you to run in a server environment. Their position is Excel is only suitable for single user workstation situations.

So no user interaction apart from this one shot - fill in the numbers on Sheet 1 and read the numbers calculated on Sheet N.

 

[firefytr]

Well, it's hard to say with any certainty, since we really don't know what it is exactly you're wanting to do, other than some form of a calculation. If you're using another programming language, then I'm not sure if you'd need Excel at all, but that depends on what you're dealing with and the calculation(s). Unfortunately we won't be able to help you much further unless you are able to provide more details. Some things that would help are what application you're running, what calculation(s) you are trying to run, and the expected results.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[BNPMike]

OK

Let's take an international health insurance example: Sheet1 has fields where you can enter date of birth, country of origin, country of cover, cover level, additional features (travel etc). Sheets 2 to 10 do various look ups on pricing, elligibility etc tables, error checking and message generation. Sheet 11 displays the price (plus messages). Assume there is no VBA, database access etc.

Forget the two display pages (1 and 11) - is there any utility that will take Sheets 2 to 10 and cross compile them to a different platform, so you have a program that takes the variables on Sheet 2 and returns the price in Sheet10?

As far as I can see, what you need to do is take each cell that is not a formula, and treat is as a variable. Take each formula cell and pick a corresponding target language function to map it to, or create some appropriate functions. Then you need to check for ranges that have special behaviours (eg a function is not passed the range limits). Then you have to build a dependency graph to work out which order to 'write' the functions.

It doesn't seem that complicated so I'm surpised it's not already been done.

 

[firefytr]

Seems rather over-complicated to me. This still all depends on where you're trying to do this from. You've given more about your data structure in Excel, which is good, but it still depends on what you're needing it for.

If you're using another programming language, and I'm still not sure if you are, and if so what you're using, I would think about setting a reference to the Excel Object Library that way you can access the file. If you can access the OL then you have the ability to do whatever you want in Excel and return results as desired, so you can perform any calculation you normally would (in Excel).

Excel does run in a server environment btw.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[BNPMike]

Zack

I was reading this. MS are saying you shouldn't run Excel on a server, and it is likely to infringe your licence. That might be academic, unless you want to use the Cloud - maybe it's more dificult to get hosting for something contravenes licence conditions.

Perhaps if I put it another way: If you don't like JavaScript you can elect to use CoffeeScript (very trendy at the moment). You then cross compile to JS for implementation.

Excel is the most efficient language in terms of lines-of-code per function point (although some analyses put other platforms close, but not typical ones). It is also ten times as widely known as all other languages put together. There is thus merit in using it to develop solutions ('prototypes') that can be 'productionized' once the logic is developed. I would imagine Financial Services companies would be interested. I think they regularly have to translate Excel models into normal languages.

Microsoft provide Excel Services - a version of Excel that runs on Sharepoint - but so far its functionality is some way behind the desktop application.

If we go back to using automation (let's say ASP.NET calling Excel) there seem to be several options available. Any recommendations about what people find best?

 

[mintjulep]

So you want user input, output for the user to read and some math in-between the two?

Seems like that can be done using any programming language you want.

 

[firefytr]

I'm not sure I agree with all of the points you make. Any coding in Excel is VBA. Sure you could get fancy and call upon other languages from the VBIDE (in Excel, or any Office application), I'm not sure you'd want to though. I'm with mintjulep on that one, there are many languages you can use for this. If, however, you are working with data stored in an Excel workbook, it would make sense to reference that, so you would have the ability to manipulate it as desired - of course you would need to know what you needed in the end for that.

You're definitely right about the issues concerning running Office from a server, my only point was you 'can', not necessarily that you 'should'.

Excel is the most efficient language in terms of lines-of-code per function point

Not sure if I would call it efficient in relation to other programming languages, especially the higher level languages such as C#. If, however, you're talking at a local level and only in the workbook/application environment [of Excel], then yes, it can be efficient. But that is highly subjective.

It is also ten times as widely known as all other languages put together.

I don't think I'd go that far. I'd be willing to bet you a dollar that more people have heard of C#, heck even HTML, than they have VBA.

Again, all of this really depends on the entire scope of what you're trying to achieve, which I'm still not very clear on.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[BNPMike]

Thanks guys. The question was did you know where I should look for a cross compiler. I think what you are saying is you've never heard of one, which is probably why I couldn't find one, googling.

The original idea for this train of thought actually came from the international health insurance application I mentioned. The real thing was C# in ASP MVC, calling special web services. However it transpired that the web services all generated prices that originally derived from a fairly simple spreadsheet (well about three). To get an app that would work when the core system was down for maintenance, I started fiddling with Excel automation using Ruby. Then it occurred to me that instead of just picking up the data from Excel, why didn't I get Excel to do all the work. That's how it started. So personally my interest is web applications but the issues apply equally to other deployment models. Note, I'm not using Excel to interact with the end user via its display capabilities (except during development). Nor am I using VBA. The beauty of basic Excel is it is a functional programming environment with all the data displayed instantaneously. Once you dabble in VBA you venture into the traditional world of mutable variables that need to run through debuggers to observe their behaviour. Also functional platforms can be more easily spread across multiple cores - as indeed Excel now does. Excel needs to know which functions depend on others and which don't, so it knows which calculations can be spun off into separate threads, and which can't.

 

[firefytr]

That makes a lot more sense.

In the core sense of the question, it depends on the platform you're using. Think of Visual Studio - you have multiple languages, all run through the CLR and become compiled. I think your question really should have been something along the lines of, "Where is a common run-time/compiler I can use to process Excel data from a server-side web application, possibly using multiple languages?" And of course I would point you to the .NET framework (CLR:

http://en.wikipedia.org/wiki/Common_Language_Runtime).

I do still wonder why the data is being held in Excel though. If it's on a server, I'm assuming it has some for of SQL on there, why couldn't you hold it in a SQL table and perform calcs on it during run-time?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci

 

[BNPMike]

Well, I don't want to 'use' multiple languages. That's really the whole point of the exercise.

If I cross compile Excel, that's behind-the-scenes just like programming in Coffeescript uses JavaScript behind-the-scenes.

In the scenario I mentioned, only the core system is down for maintenenance. It generates the price and registers the policy. If you want to avoid missing the opportunity to quote during downtime, the web and database servers are up all the time, so - yes - you could use C# reading database tables. However that's a question for the ASP Forum, not this one.

My current thinking is I use ASP with VB to handle the web call, get authentication/request logon and collect previous state. This would all be a feature of the framework, and wouldn't require any developer input. All this gets loaded into Excel which performs the logic and formats the output page.

ASP then reads the output, stores the state and turns the output sheet into HTML, returning it to the calling IP browser. Again this would be an automatic feature of the Framework unless the developer wants to add CSS.

So what I'm trying to do is use Excel as a non-interactive, general-purpose programming language, at least for the purposes of straight-forward web sites. Excel is not entirely capable for this but it's not too far off; nothing that a few custom functions wouldn't sort out .

 

[macropod]

You cannot cross-compile Excel, per se. Aside from anything else, it would be a breach of your user licence - expect to be seriously prosecuted by MS if you get found out (and you probably would).

Regardless, the problem really isn't one of cross-compiling Excel, but of having a spreadsheet application you can use on the platform concerned. There are numerous spreadsheet applications you might consider, including from Open Office.

Cheers
Paul Edstein
[MS MVP - Word]

 

[BNPMike]

Paul - I didn't mean Excel the program. I meant a given spreadsheet.

To take the most simplistic case, if my spreadsheet took 10 numbers and contained the function A11 = SUM(A1:A10) then I would want a program that took 10 numbers as parameters, and returned the sum.

Actually you make a fair point - cross compiling to Open Office has the advantage that you don't have to do anything whatsoever, which is very attractive amount of effort. It overcomes the licensing issue although I don't know if it is happy on a server.

Worth considering.

 

[macropod]

In that case, all the talk about cross-compiling is completely irrelevant. You really should take time to learn what a given technical expression means before you use it. In this case, see:

http://en.wikipedia.org/wiki/Cross_compiler

For your purposes, all you need to do is:
(a) confirm which spreadsheet applications support the functions you require on the required platforms. The fact that you might use a different spreadsheet application on each platform is then of little consequence; and
(b) decide which workbook format to store the file in. Just as Excel can read and write numerous workbook formats, so can any other decent spreadsheet application. Simply choose a suitable format that all the spreadsheet applications you want to work with can read & write.
None of the above has anything to do with cross-compiling.

Cheers
Paul Edstein
[MS MVP - Word]

 

[BNPMike]

My original question was categorically about compiling a program in Excel format (ie a spreadsheet) to make it run on another platform eg F#. You perhaps haven't read the full thread. I floated F# as it is also a functional language and therefore might find it more natural to deal with Excel's dependency tree.

It just so happens that to make an Excel spreadsheet run on OpenOffice you don't actually have to do anything apart from save the progam. Of course that's not quite true. OpenOffice cannot run all Excel programs without tweaking. Also, to make Excel usable in the context I've described, you would have to add custom functions, so you would need some process to substitute relevant code (eg VBA to Star Basic) for OpenOffice, but that would not be difficult.

 

[firefytr]

Depending on the language you're using, and I'm not fluent with F#, you would really only need to reference the Excel OL to have access to do whatever you want with the file. This really isn't an Excel question at all, or even an Office question. From what I gather you're trying to read an Excel file without Excel. Unless you use an interop in your code, I don't know how to do it.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci