Five Ways of Improving Excel to Code Conversion
Converting Excel to compileready C++ or C# code isn’t as easy as you might think: there are plenty of cases where generating code straightahead is either inefficient or would result in incorrect code.
Here is an overview of five different cases where X2C performs adjustments.
1. Sequence Folding
Say you decide to map a few cells containing values {0, 1, 2, 3, 4}
to a vector:
If you were to perform initialization of this vector, it might look as follows:
double Foo[5]; void InitializeFoo() { Foo[0] = 0; Foo[1] = 1; Foo[2] = 2; Foo[3] = 3; Foo[4] = 4; }
The above is correct, but tedious. Wouldn’t it be great if we could detect repetition like this and put it in a loop? Well, X2C does exactly this, so your generated code will look as follows:
double Foo[5]; void InitializeFoo() { for (int i = 0; i < 5; ++i) Foo[i] = i; }
X2C does its best at detecting row/columndependent values and, if it can see that even a part of the elements can be initialized in a for
loop, it rewrites the code accordingly.
2. Range Function Substitutions
Let’s say you decided to find smallest of the elements above:
What sort of code would you expect? Maybe something like
double Min() const { return std::min(Foo[0], std::min(Foo[1], std::min(Foo[2], // you get the idea }
Well, X2C could just give you that, but it’s a lot smarter. Depending on the language, X2C knows about ways of aggregating values so, for the above case, the generated code would be as follows:
double Min() const { return *std::min_element(std::begin(Foo), std::end(Foo)); }
3. Complex Aggregate Calculations
Sometimes, though, aggregations get complicated – not just a simple sum, average or calculation of smallest or largest element, but rather something like the following:
In this case, what X2C does is perform a walk over each of the elements and outputs the final result based on elementwise reads, i.e.:
double FooBar() const { return (Foo[0]*Bar[0] + Foo[1]*Bar[1] + Foo[2]*Bar[2] + Foo[3]*Bar[3] + Foo[4]*Bar[4]); }
In future, we aim to teach X2C to fold even these types of expressions into loops, as the above could be calculated using a simple for
loop and just one temporary variable.
4. Synthetic Evaluation for Simple Functions
Some functions which appear deceptively easy in Excel require additional evaluation steps when translated to code. Here’s a simple example:
What would you expect X2C to do with the TRUNC()
function in the above example? Well, for functions which are difficult to immediately express using existing APIs, X2C simply rewrites them the best way it knows. For the above, you’d get the following code:
double Foo::Pi() const { return M_PI; } double Foo::PiTruncated() const { return (boost::math::round(Pi() * 100) / 100); }
The value 100 in the above comes from the fact we need to truncate to 2 decimal places (10^{2} = 100).
5. CompileTime Evaluation
Sometimes, the parameters to an Excel function actually determine which function to call. In this case, X2C simply checks the parameter at ‘compiletime’ and writes the appropriate code. Here’s an example:
In the above, it’s the 4^{th} parameter to the normal distribution calculation that’s the problem. If set to FALSE
, this is a calculation of the probability density function φ(x), whereas if it is TRUE
, we need to compute the cumulative distribution function Φ(x) instead.
To get around this, X2C simply looks at the parameter value and infers the invocation for that. So for the cell above, you would get the following function:
double Foo() const { return boost::math::cdf<>( boost::math::normal_distribution<>(0, 1), 0.5); }
Conclusion
These are just some of the ways in which X2C improves the conversion process. If you want to see these, and many other transformations in action, download X2C and give it a go! ■

Marek

http://devtalk.net Dmitri
