Hi, everyone. I’ve just started using ruby a couple of days ago, and
I’ve been using it to read data from text files and write to excel
spreadsheets. I also need to be able to write formulas to spreadsheets,
but when I open the excel file, the formula is in there without having
been evaluated - for example, the cell will appear as “=A1+A58+A114”
instead of whatever the value of that sum happens to be. If I click on
the cell and hit “enter,” the formula will evaluate, but it does not do
so automatically.
Is there any way to get ruby to force excel to evaluate? Thanks!
Thanks, guys. However, the above seems to require win32ole, for which,
if I’m not mistaken, you need office to be installed on the system.
Is there a way to do this with just the spreadsheet gem (i.e. just
require spreadsheet)?
I looked through the code in the spreadsheet gem a few months ago (2?)
looking for this functionality. As far as I could tell, it was not yet
possible to write formulas to a spreadsheet cell. It looks like that
functionality is on the roadmap. Look at the “roadmap” section on the
homepage: http://spreadsheet.rubyforge.org/
Looks like formula support isn’t slated until version 0.8.0 (and we’re
on 0.6.x right now).
the above seems to require win32ole, for which,
if I’m not mistaken, you need office to be installed on the system.
I’m intrigued - how are you running Excel to get it to calculate
formulae if it’s not on your computer?
As it happens, I don’t think win32ole has got anything to do with Office
anyway. It’s to do with OLE, which is a Windows feature. Excel happens
to present an OLE object model.
There are alternatives to office (i.e. openoffice) which allow you to
work with excel spreadsheets but don’t provide the necessary COM objects
or whatever it is that’s needed to use some features of certain
libraries or modules in certain languages. I think to do:
class ExcelConst
end
WIN32OLE.const_load(excel, ExcelConst)
or
excel = WIN32OLE::new(‘excel.Application’)
you do need to have excel installed. I remember in perl, to do stuff
like:
use Win32::OLE qw(in with);
use Win32::OLE::Const ‘Microsoft Excel’;
you need to have excel on the system. I’m not a professional programmer
(these days, I mostly program to automate a lot of painful data
crunching tasks that would take ages to do by hand), so some of this is
a bit beyond me…
Chuck - thanks. It’s too bad that the formula stuff isn’t implemented
yet, but could there be some clever workarounds to force or trick excel
into evaluating?
the above seems to require win32ole, for which,
if I’m not mistaken, you need office to be installed on the system.
I’m intrigued - how are you running Excel to get it to calculate
formulae if it’s not on your computer?
As it happens, I don’t think win32ole has got anything to do with Office
anyway. It’s to do with OLE, which is a Windows feature. Excel happens
to present an OLE object model.
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.
Why fanny around with some Micky Mouse spreadsheet gem when you can have
the Full Monty?
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. In addition, the spreadsheet gem works on any platform
(last I checked).
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.
Why fanny around with some Micky Mouse spreadsheet gem when you can have
the Full Monty?
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. In addition, the spreadsheet gem works on any platform
(last I checked).
Regards,
Dan
Yep, that’s correct - I want the scripts (which are to be used for tasks
that would be too cumbersome to do manually) 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.
The spreadsheet gem meets the above requirements, and it was working
brilliantly until I got to writing formulas to spreadsheets. It’s
alright though, as it still saves me lots of time and effort.
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.
Why fanny around with some Micky Mouse spreadsheet gem when you can have
the Full Monty?
On Sat, May 21, 2011 at 11:28:26AM +0900, Will J. wrote:
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.
. . . and frankly, you shouldn’t have to explain why you’re writing code
for something like this, unless it’s actually relevant to the problem
you
want help solving.
and frankly, you shouldn’t have to explain why you’re writing code
for something like this, unless it’s actually relevant to the problem
you want help solving.
Chad, if you ever want to learn anything,and my experience of you -like
others before me - is you don’t, learn this: it is always vital to
understand why you are doing something.
On Sun, May 22, 2011 at 06:14:10AM +0900, Mike S. wrote:
Chad P. wrote in post #1000098:
and frankly, you shouldn’t have to explain why you’re writing code
for something like this, unless it’s actually relevant to the problem
you want help solving.
Chad, if you ever want to learn anything,and my experience of you -like
others before me - is you don’t, learn this: it is always vital to
understand why you are doing something.
I do want to learn things, and I agree that it is vital to understand
why
you are doing something, but it is not vital to explain to some
Microsoft obsessed shitbird why you are using Ruby to interact with an
XLS file rather than doing it “by hand” in Excel.
but it is not vital to explain to some
Microsoft obsessed shitbird why you are using Ruby to interact with an
XLS file rather than doing it “by hand” in Excel.
The trouble with you Chad is you always manage to miss the point.
You too.
No-one on this thread has mentioned doing it by hand. The issue was
whether or not to use Windows OLE rather than a Linux environment, given
that the end-product is for Windows users, and that it is does what Will
wants - unlike his Linux solution.
Not necessarily, the spreadsheets can be used on a variety of systems
all of which can read them.
Now I would choose a different file format but if part of the target
users want to open the files in Excel or another spreadsheet this
might be the most suitable format for the purpose.
CSV or HTML works too but importing into a spreadsheet is somewhat
problematic.
I’m not Microsoft obsessed. The simple fact is it is the Linga Franca of
the computing world. Linux is only on a tiny minority of desktops. So
Maybe where you work. I would guesstimate Windows being on about half
of computers in the world.
That’s something that should be taken into consideration but not taken
for granted.
it’s natural to question why someone would choose to go down a such a
route when Windows is what most other people would be using.
Will has said his colleagues just happen to be avid Linux fans so that’s
fair enough.
That answers the question.
I did not see anything about them being avid Linux fans. If you did
not notice there are things like Windows without MS Office installed,
OS X, even PDAs and tablets that can run Ruby but not MS Office OLE.
I know on this channel I will get the anti-Microsoft lobby. Fortunately
I don’t get it at work. Professional programmers grudgingly accept that
.NET has bugs and the usual MS baggage but probably is a better bet than
say Java for mainstream e-business. Sadly people rarely have an opinion
on Ruby.
I don’t know what you mean by e-business. If you mean serving web
pages then I would rather rely on Java then .NET for the purpose.
I know on this channel I will get the anti-Microsoft lobby. Fortunately
I don’t get it at work. Professional programmers grudgingly accept that
.NET has bugs and the usual MS baggage but probably is a better bet than
say Java for mainstream e-business.
Well, except that plenty of professional programmers come to the exact
opposite conclusion wrt the relative desirability of .NET and Java for
mainstream e-business.
Just my 5 cents. What do you need formulas for. Is your data going to be
changed by hand in the future?
If not, why not calculate it by your program.
by
TheR
I thought about doing it this way, but the spreadsheet will have
calculations based on previous calculations, based on previous
calculations, and so on, so 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), and so that the
viewer can verify that nothing went wrong anywhere in the process of
deriving the final values. Basically, the formulas are good for
transparency in this case.
but it is not vital to explain to some
Microsoft obsessed shitbird why you are using Ruby to interact with an
XLS file rather than doing it “by hand” in Excel.
The trouble with you Chad is you always manage to miss the point.
No-one on this thread has mentioned doing it by hand. The issue was
whether or not to use Windows OLE rather than a Linux environment, given
that the end-product is for Windows users, and that it is does what Will
wants - unlike his Linux solution.
I’m not Microsoft obsessed. The simple fact is it is the Linga Franca of
the computing world. Linux is only on a tiny minority of desktops. So
it’s natural to question why someone would choose to go down a such a
route when Windows is what most other people would be using.
Will has said his colleagues just happen to be avid Linux fans so that’s
fair enough.
That answers the question.
I know on this channel I will get the anti-Microsoft lobby. Fortunately
I don’t get it at work. Professional programmers grudgingly accept that
.NET has bugs and the usual MS baggage but probably is a better bet than
say Java for mainstream e-business. Sadly people rarely have an opinion
on Ruby.
I would guesstimate Windows being on about half
of computers in the world.
even PDAs and tablets that can run Ruby but not MS Office OLE.
According to this
90% of computers run Windows, so normally you’d stand a fair chance of
your program working if you used OLE.
I don’t know what you mean by e-business. If you mean serving web
pages then I would rather rely on Java then .NET for the purpose.
I’m not saying I’m right or that MS is better. I’m just saying there are
a lot of people in my world (eg web apps for customers to service
120,000 loans; websites to sell international health insurance etc) and
they just don’t have this slightly puerile anti-microsoft attitude, so
I’m confused why a few people on here cannot help themselves from making
snide remarks whenever MS is mentioned.
I’m not saying I’m right or that MS is better. I’m just saying there
are a lot of people in my world (eg web apps for customers to service
120,000 loans; websites to sell international health insurance etc)
and they just don’t have this slightly puerile anti-microsoft
attitude, so I’m confused why a few people on here cannot help
themselves from making snide remarks whenever MS is mentioned.
Before my unbelieving eyes, you have transformed a discussion on using
ruby into an operating system flame war. Now that’s magic!