8 ms·
Excel_to_code – Converts some Excel spreadsheets to C
- donatj 11y agoI've got a project in a similar veign here: https://github.com/donatj/XLS-XML-Formula-Expander https://github.com/donatj/XLS-XML-Formula-Expander It's ancient and flawed but it converts a SpreadsheetML ( https://en.wikipedia.org/wiki/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 11y agoReally 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 https://i.imgur.com/q2KZRR1.png
- maguirre 11y agounrelated but you should see the generated code that matlab/simulink outputs from their Realtime workshop. This reminds of that
- lorddoig 11y ago> For small spreadsheets this will take a minute or so. For large spreadsheets it is best to run it overnight. Ah, Ruby.
- jakobegger 11y agoAny insights why the conversion is so slow? Intuitively, converting a spreadsheet doesn't sound like it should be a high complexity task.
- JupiterMoon 11y agoWhere do you start? No seriously where would your program start and where would it finish? Most spreadsheets are a tangled mess of interacting cells.
- laumars 11y agoExcel manages to process it in real time.
- JupiterMoon 11y agoYep for all its faults it's an impressive program.
- laumars 11y agoIn fairness to Excel, I think most of it's faults are more down to user misuse (eg people creating "databases" in Excel) rather than problems with the package itself. Though Excel's handling of CSV can sometimes be a little troublesome - particularly with date / time fields. And I wish it support Open Document Spreadsheet (.ODS). But the former is a fringe case and the latter is obviously just a business decision. So, as you say, it's a very impressive program.
- sqlacid 11y agoSurely 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 11y 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 11y agovlookup pseudo-joins ahoy !
- personalcompute 11y agoI'll point out that if you find yourself with a RDBMS-like situation in Excel I highly recommend using Excel 2013+ 'tables' feature which adds foreign keys and better pseudo-joins (in pivot tables) inside of Excel. Better than anything hacked together with VLOOKUP. (heck, I've even seen some people hack it together with only SUMIF)
- function_seven 11y agoI use tables all the time in Excel 2010, haven't had much experience with 2013. Is this something that is truly new in 2013?
- personalcompute 11y agoI don't know, I have no experience with 2010 so I went with the lower bound I can guarantee as opposed to potentially giving harmful wrong information. Can't edit my comment now.
- amyjess 11y agoAt 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.
- arthurcolle 11y agoNot really understanding why one would want this?
- mirceal 11y agomany 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 11y agoBecause 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.
- arthurcolle 11y agoRight, 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.
- m_mueller 11y agoI think this might be geared at companies who want to convert Excel workflows to a POSIX based cloud solution. If you want to combine moving to the cloud (e.g. for multi-user) with reducing vendor lock-in, this makes some sense I think.
- ossreality 11y agoC is "a unix language"? wtf?
- teraflop 11y ago
- jscottmiller 11y ago> The generated code uses floating point, rather than fully precise arithmetic, so results can differ slightly Use with caution.
- teraflop 11y agoExcel already uses floating point internally, so if you're worried about compatibility, this is less risky than switching to arbitrary precision math.
- jscottmiller 11y agoWow, 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.
- AceJohnny2 11y agoI expect all financial math is fixed point.
- chillingeffect 11y agoI'm not sure why ppl downvoted you. But from what I've heard - never implemented myself - what you're saying is common... At least as far as using separate fixed point values to represent the left and right sides of the decimal place. Now I'm sure for things like computing interest by raising things to a power or even computing percentages, etc, floating point is used (and eventually reduced to fixed point values), so maybe someone found it too absolute to say "all" financial math is fixed point? Anyway, if someone with experience can confirm or deny, it would be neat...
- JupiterMoon 11y agoBecause he is wrong? Excel uses 64 bit floats.
- 11y ago
- cm2187 11y agoIn 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 11y agoNice 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 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 11y agoI'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 11y agoThis 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 :)
- danjc 11y agoAs 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 https://developers.flowgear.net/kb/Node:Excel_Workbook).
- morgante 11y agoHow did you go about converting the Excel workbooks into a usable program which you can feed data into and get back computed results?
- cnvogel 11y agoI 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.
- danjc 11y agoThat's about how we do it. Excel (and its COM API) was never intended for multi-tenancy or consumption from a service. That said, if you run the service under a named user account (as opposed to SYSTEM or NETWORK) and make sure you won't have users accessing the file and locking it, you're good to go. If you need to invoke the same sheet repeatedly, you can build a cache of range output values keyed on a hash of range input values. This enables you to bypass Excel for a state that you've previously seen. It's also pretty easy to inject and execute script (as opposed to invoking existing scripts) - that deals with the problem of users being able to see or edit VBA.
- dnesteruk 11y agoAlso check out http://activemesa.com/x2c http://activemesa.com/x2c
- tmaly 11y agoI 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.