Excel_to_code – Converts some Excel spreadsheets to C

by gmac • 11 years ago

92 59 11 years ago

donatj• 11 years ago

I've got a project in a similar veign here: https://github.com/donatj/XLS-XML-Formula-Expander

It's ancient and flawed but it converts a SpreadsheetML ( https://en.wikipedia.org/wiki/SpreadsheetML ) aka "Excel 2003 XML" spreadsheet to PHP in real time.

There is a VERY complicated configuration tool for a major steel company that runs on this still, last I checked anyway. I left that job ~5 years ago. I've done a few small updates since then for them, but not much.

It was nice though because it allowed the company to just submit a new Spreadsheet and their tool would automatically update. I was very proud of it at the time. Haven't had reason to maintain it though, but it works much faster than other tools that do similar for PHP.

barsonme• 11 years ago

Really interesting project. The source code is a bit scary[1] but I suppose that's to be expected (machine) translating Excel to C. Good work.

[1] https://i.imgur.com/q2KZRR1.png

maguirre• 11 years ago

unrelated but you should see the generated code that matlab/simulink outputs from their Realtime workshop. This reminds of that

lorddoig• 11 years ago

For small spreadsheets this will take a minute or so. For large spreadsheets it is best to run it overnight.

Ah, Ruby.

jakobegger• 11 years ago

Any insights why the conversion is so slow? Intuitively, converting a spreadsheet doesn't sound like it should be a high complexity task.

JupiterMoon3• 11 years ago

Where do you start? No seriously where would your program start and where would it finish? Most spreadsheets are a tangled mess of interacting cells.

sqlacid2• 11 years ago

Surely I'm not the only one that remembers someone doing this with Lotus 1-2-3 in the 80's...how far we've come. What's next, a database in a spreadsheet? Word processor? Modem application?

Bjartr• 11 years ago

What's next, a database in a spreadsheet?

You seem to imply that's not already the case (though many wish it wasn't)

liotier• 11 years ago

vlookup pseudo-joins ahoy !

amyjess• 11 years ago

At my last company, someone wrote a format to encode RDF data in spreadsheets.

Google Sheets was our primary tool for configuration and content authoring for way too long. Eventually, he added support for OOXML sheets, too. At some point, a long time later, we started migrating to TTL, and thank goodness for that.

The code was all open-sourced, by the way, but I'd rather not link it here because I don't really want this username associated with that company.

morgante2• 11 years ago

The code was all open-sourced, by the way, but I'd rather not link it here because I don't really want this username associated with that company.

Any search clues? It sounds really interesting and I've been exploring the possibility of using spreadsheets as a widely understood authoring tool.

realusername• 11 years ago

What's next, a database in a spreadsheet?

You have no idea, poor innocent soul... :(. I hope you won't touch excel-based databases, it's really awful...

sqlacid2• 11 years ago

I've got the scars, 123, Symphony, Quattro, Paradox, Excel, Access, dBase II, III, IV, Lotus Notes.....OLE!

semicolondev• 11 years ago

You might not believe this but my boss builds web app mock-ups using Excel. I was surprised to find that out but it works.

Intermernet3• 11 years ago

Excel is either brilliant or horrible (depending on your role in dealing with the output) at being re-purposed to do "the wrong thing".

I've had form templates sent to me in Excel because of the easy-to-use table layout controls. I've had games sent to me in Excel because VBA was so damn easy to program simple ideas in. I've had PoCs of algorithms sent to me in Excel because the graphical chaining of each step is so illustrative.

I've also seen the nightmare that haunts businesses who define their business logic through a bunch of interconnected Excel files. As an aside, this seems like an area ripe for "disruption" (remember when "disruption" was a bad word?), which is where tools like excel_to_code could probably make a large impact. If you could validate and test the entire business logic, as expressed in Excel files, you could reduce or eliminate the "Excel defined business rules" nightmare. If you can do this, there are thousands of SMEs that will be knocking at your door.

I personally don't use Excel for much, and I used to deride people who used it for "the wrong task", but I've recently had to swallow my pride, and accept that it's a serious power tool for most non-programmer people. I'd even go so far to say that it's the most successful "visual programming" tool out there. And by a long way.

Intermernet3• 11 years ago

Flight simulator? http://www.eeggs.com/items/29841.html

arthurcolle2• 11 years ago

Not really understanding why one would want this?

mirceal• 11 years ago

many business "workflows" still run on excel.

there might be some uses for non-technical users, but yeah... it's sort of madness to do this for a production service.

untog• 11 years ago

Because a lot (a lot) of companies have a staggering array of business logic sewn into spreadsheets. Eventually that might end up having performance issues, so transforming some of that logic to C is, at the very least, an interesting solution to a problem.

Everyone looks down on them, but spreadsheets really are incredibly functional, and a lot more accessible that most programming languages.

arthurcolle2• 11 years ago

Right, I get that... but why C? Presumably if you're using Excel you have access to the whole MSFT stack, so it just seems weird to use a Unix language when it seems likely that some VS/C# tool has already been built for this.

I know C/C++ works just fine on Windows, but the command-line tools on cmd.exe leave a lot to be desired.

_delirium• 11 years ago

It's used in parts of engineering too. Matlab is more typical for complex models, but a reasonable number of models end up being implemented in Excel.

cm21872• 11 years ago

And let's be honest. If you ask an internal IT department to do in code the equivalent of what you do with a spreadsheet in a day, they will prioritize it, discuss specs for months, go away for another 6 months, come back with something that has nothing to do with what we are trying to achieve, then go away again. By the time it is up and running you probably don't need that stuff anymore. And of course you will never be able to update it. And if it stops working, IT will hide behind their ticket systems, which they seem to spend more time tweaking and upgrading than the applications they are supposed to care for.

Ahhh. Corporate IT. I may miss it one day. But not today.

727374• 11 years ago

Also, in addition to the other responses, excel is great for quickly modeling problems/systems with lots of inputs and outputs and calculations in between e.g. discounted cash flow.

jscottmiller2• 11 years ago

The generated code uses floating point, rather than fully precise arithmetic, so results can differ slightly

Use with caution.

teraflop3• 11 years ago

Excel already uses floating point internally, so if you're worried about compatibility, this is less risky than switching to arbitrary precision math.

jscottmiller2• 11 years ago

Wow, didn't realize that about excel (though I have observed it in google sheets). I wonder how many hedge funds rely on sheets with catastrophic cancellation issues.

Someone• 11 years ago

Floating point tweaked to make it look like it uses decimal arithmetic. See http://www.eecs.berkeley.edu/~wkahan/Mindless.pdf, starting at page 3.

cm21872• 11 years ago

In fact I always thought it should be a feature of Excel. Take a worksheet, define inputs and outputs, and make the worksheet a function which can then be reused to apply for instance to a table of data.

There are many things that could be improved in Excel. But Microsoft seems to have unstaffed all developpers from the office team and that product seems to be in maintenance mode. The only innovation in 10y is a button that enables to share a spreadsheet on facebook. Will be very popular in a corporate environment.

kranzky• 11 years ago

Nice work! I built something similar that keeps everything in Ruby. It was made to allow models built in Excel by a non-programmer to be executed on the back-end of a Rails app, but it's pretty generic: https://github.com/agworld/soroban

I had big plans for the project; the next step was to convert the models to JavsScript so they could be executed directly on the client. But, like most of my pet projects, this has remained a pipe dream.

Good luck with it!

robsco• 11 years ago

I've got a system that has been working quite nicely. Basically it's a python server that interfaces with a headless instance of LibreOffice Calc. Spreadsheets are uploaded to a directory that is polled for changes. The python server opens/closes the spreadsheets in LibreOffice. The python client connects to the python server and can modify and read cells in any spreadsheets. Each spreadsheet is 'locked' when it is being accessed so to avoid two simultaneous modifications/requests giving incorrect results.

LibreOffice nowadays works pretty well with excel spreadsheets. It also means that as LibreOffice improves so does my excel sever. I'm also working in the GNU/Linux world so this is a great solution for me.

batou• 11 years ago

This sounds similar to a reporting system I wrote in about 1999 using Word automation API. It sat on a windows NT box and watched for changes on a netware share. That would load up a text file, read it, parse it and pick documents and fill in bookmarks in the documents then print it to a printer or PDF file.

Worked wonderfully apart from the memory leaks :)

danjc2• 11 years ago

As a developer (or someone with some scripting or DBA skills), it's pretty easy to scoff at Excel being used as a way to internalise business rules but the thing about it is that it's this ubiquitous language that a lot of people understand.

We've saved ourselves a lot of hassle implementing application integration solutions by outsourcing domain-specific number crunching/mapping to a spreadsheet in our integration platform. It enables us to treat the customers' business rules as a black box - feed inputs against named ranges and read out named ranges (https://developers.flowgear.net/kb/Node:Excel_Workbook).

morgante2• 11 years ago

How did you go about converting the Excel workbooks into a usable program which you can feed data into and get back computed results?

cnvogel3• 11 years ago

I think Joel Spolsky (?) is often quoted by saying that the easiest way to parse ms office file formats is to just install it on a server and use the documented API to Word, Excel,...

I used this approach regularly about 4 years ago in my previous job to interact, query,... with data stored in "excel databases".

So, while I'm not the parent commenter, my suggestion would be to have a windows server that runs both my interface software, and excel. You load the workbook, populate input fields, let excel do the processing and query output fields/workbooks/...

Of course the excel sheet could also contain VBA code actively pulling out data from a DB, heck it could even call code in custom shared libraries. But I doubt that passing around workbooks full of code amongst it illiterate users will do any good. The less magic they contain outside of stock cell formulas the less chance of users messing up the computation.

If you want to play with it, look up the win32 modules shipping directly with ActiveState's python distribution.

dnesteruk• 11 years ago

Also check out http://activemesa.com/x2c

tmaly• 11 years ago

I did something like this at work to convert these huge excel sheets to a compressed format. I normalize the excel xml files with an xslt then I extract of the meta data I store in named ranges.

Excel_to_code – Converts some Excel spreadsheets to C

Related Stories