A few months ago I needed to convert quite complex (kind of black box) computation from Excel spreadsheet to C code. At first I manually copied expressions from all cells but this was not simple as I had to check cells mentioned in expressions (formulas). It took long time and energy and the code didn’t work properly - results calculated by the spreadsheet differed from my program’s result.

Therefore I decided to save the Excel spreadsheet in ODF format (OpenOffice.org Calc) and write a little helper called SpreadsheetToCode. It’s a command-line utility that does the following steps:

1. Load OpenDocument Spreadsheet file (.ods) because it's basically XML file wrapped in ZIP and therefore super easy to handle
2. Pick all cells which contain numerical values (inputs, parameters) or expressions with them (formulas)
3. Print C-like source code with the spreadsheet's functionality expressed in lines of code

As this is just helper and not fully equipped Excel-to-EXE converter, so that your task is to rename variables to something meaningful (instead of G42), maybe merge some lines of code or remove redundant steps and incorporate the resulting code in your own program. Converter’s greatest help is that it checks for dependencies which means that code

double C2 = C10/3;

will be placed after

double C10 = C5+C1;

because C10 mustn’t be used before its declaration/definition.

Usage

To illustrate the usage I created simple spreadsheet (the original complex spreadsheet can not be disclosed) for calculating resistive voltage divider. It calculates V2 from V1, R1 and R2:

Of course this is a trivial task but I couldn’t think up anything better that wouldn’t take long minutes to create (Excel calculations aren’t my style, I use it just for painting tables). Many thanks to Wikipedia for the File:Resistive_divider.png.

For the conversion just download SpreadsheetToCode.jar and run it:

java -jar SpreadsheetToCode.jar resistive_divider.ods

The resulting code will be:

/* Constants: */
double C5 = 22.0;
double C6 = 20.0;
double C7 = 3.0;

/* Formulas: */
double C10 = C6+C7;
double C11 = C7/C10;
double E5 =  C5*C11;

The only thing you need is to wrap it into some function and look into spreadsheet what cells are inputs (C5, C6, C7: they are listed in Constants section) and which are results (E5 in our case). Problem solved!

Source code and limitations

Full Java source code is at dir.adamheinrich.com/spreadsheet_to_code/ (source.zip). As this was an one-hour project the code is not ideal (but I tried to keep it understandable) and there are some known limitations (or features :-) ) I didn’t want to solve:

• The resulting code is always C-like (with statements ended by a semicolon). You can change this in Cell.getGeneratedCode() method.
• Your task is to decide what cells are inputs and which are outputs. The easiest way is to wrap generated code in function and fill input cells with function arguments.
• Formulas containing IF or mathematical functions are not converted to valid C code (but from the generated code it's quite obvious is it supposed to do). Your task is to change function names to something meaningful and replace IF with ternary operator.
• Cells with column indexes higher than Z (AA1, AB1, ...) are not correctly represented. You can modify the cell name assignment in Cell.setName(int row, int column) method if you need this. It should be a few lines of code.

Result

This utility saved me hours of boring copy&paste in Excel and ordering lines of code because of dependencies. Instead I spend the time coding this utility which was more fun:-) It is placed online so that it can help someone else. Enjoy!