From: Colin Bartlett <colinb2r@...>
Date: 2011-05-26T00:27:56+09:00
Subject: Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

On Sat, May 21, 2011 at 3:28 AM, Will James <ampclj9@hotmail.com> wrote:
> Daniel Berger wrote in post #999984:
>> On May 20, 3:36pm, Mike Stephens <rub...@recitel.net> wrote:
>>> I'm still fascinated why you are doing sophisticated things with Excel
>>> (not Open Office) but steadfastly refuse to load it on your computer.
>>> Windows and Excel can be purchased for the price of a monitor. You gain
>>> ownership of software that costs hundreds and hundreds of millions to
>>> develop.
>>
>> Even if he had it installed locally, I'm guessing that he would want
>> to generate the document in code since generating it by hand would be
>> cumbersome.
>
> Yep, that's correct - I want the scripts to work across a variety of
> platforms, with as little dependence on outside applications and
> libraries as possible. This is partly because the scripts I'm writing
> may be used by a few others; I don't know about what software they will
> or won't have on their systems, and they will likely not be too willing
> to resolve too many dependency issues. I do have office installed on one
> of my systems, but don't have access to that one right now, and will not
> buy another copy just for this task.

There are other use cases for what you want to do, which is why I'm
interested in any problems you come across and any ways you solve
them. For example: I distrust spreadsheets for making important
calculations (too easy to make errors in obscure cells without
noticing), but they are very useful for displaying data. So I make
some calculations using Ruby (or whatever), and then display the
results on a worksheet page, using formulas to generate some of the
displayed results for the same reasons you give in a later post "it's
good to have the formulas in there, in part so that whoever is viewing
the formulas can follow the process of
how something is derived (without too much effort)". An important part
of what I'm doing is that the worksheets will be usable in even quite
old versions of Excel, so I only want to use elementary features of
Excel.

Actually, the original version of this used Microsoft Excel
VisualBasic for Applications, with all the calculations being done in
VBA, and using VBA to generate the worksheet pages. But I'd rather use
Ruby for the calculations, so I'm currently rewriting it, hence my
interest in what you're doing.

I'm assuming you've looked at things like this
  http://www.cpearson.com/excel/optimize.htm
which has a section on using VBA to force calculations: I haven't
tried adapting the VBA code to run from Ruby accessing Excel, but it
should be possible?

As a very orthogonal suggestion: one thing that was worrying me about
my approach was what if I couldn't manage to get Ruby to write
anything directly into Excel. (You haven't got that problem.) But a
possible solution occurred to me: use Ruby (or whatever) to generate a
text file which has a list of cells and values or formulas (and
formatting) to be entered into (or used by) each cell. Then write a
VBA function to read such text files and generate the worksheet(s): a
little messy, but fairly easy to do, and I was much happier once I had
a backup plan if directly accessing Excel through Ruby didn't work.