Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > Writing formulas to excel spreadsheet

Reply
Thread Tools

Writing formulas to excel spreadsheet

 
 
Will James
Guest
Posts: n/a
 
      05-19-2011
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!

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
 
 
 
Mike Stephens
Guest
Posts: n/a
 
      05-19-2011
Have a look at :rubyonwindows.blogspot.com/search/label/excel

and look for 'formula'

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
 
 
 
7stud --
Guest
Posts: n/a
 
      05-19-2011
Mike Stephens wrote in post #999754:
> Have a look at :rubyonwindows.blogspot.com/search/label/excel
>
> and look for 'formula'


...which shows this:

Adding Formulae

emptyRow = 15
worksheet.Range("t#{emptyRow}")['Formula'] =
"=(Q#{emptyRow}+L#{emptyRow}+I#{emptyRow}+S#{empty Row})"

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
Will James
Guest
Posts: n/a
 
      05-20-2011
7stud -- wrote in post #999761:
> Mike Stephens wrote in post #999754:
>> Have a look at :rubyonwindows.blogspot.com/search/label/excel
>>
>> and look for 'formula'

>
> ...which shows this:
>
> Adding Formulae
>
> emptyRow = 15
> worksheet.Range("t#{emptyRow}")['Formula'] =
> "=(Q#{emptyRow}+L#{emptyRow}+I#{emptyRow}+S#{empty Row})"


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)?

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
Chuck Remes
Guest
Posts: n/a
 
      05-20-2011

On May 19, 2011, at 11:42 PM, Will James wrote:

> 7stud -- wrote in post #999761:
>> Mike Stephens wrote in post #999754:
>>> Have a look at :rubyonwindows.blogspot.com/search/label/excel
>>>
>>> and look for 'formula'

>>
>> ...which shows this:
>>
>> Adding Formulae
>>
>> emptyRow = 15
>> worksheet.Range("t#{emptyRow}")['Formula'] =
>> "=(Q#{emptyRow}+L#{emptyRow}+I#{emptyRow}+S#{empty Row})"

>
> 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).

cr


 
Reply With Quote
 
Mike Stephens
Guest
Posts: n/a
 
      05-20-2011
Will James wrote in post #999789:
>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.

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
Will James
Guest
Posts: n/a
 
      05-20-2011
Mike Stephens wrote in post #999926:
> Will James wrote in post #999789:
>>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?

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
Mike Stephens
Guest
Posts: n/a
 
      05-20-2011
Will

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?

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
Daniel Berger
Guest
Posts: n/a
 
      05-20-2011


On May 20, 3:36=A0pm, Mike Stephens <(E-Mail Removed)> wrote:
> Will
>
> 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

 
Reply With Quote
 
Will James
Guest
Posts: n/a
 
      05-21-2011
Daniel Berger wrote in post #999984:
> On May 20, 3:36pm, Mike Stephens <(E-Mail Removed)> wrote:
>> Will
>>
>> 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 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.

--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Writing formulas to excel spreadsheet ampclj9@hotmail.com James Ruby 0 05-19-2011 04:16 PM
[ANN] LXL (Like Excel) 0.1.0 - A mini-language that mimics Microsoft Excel formulas. Kevin Howe Ruby 6 02-06-2005 10:05 PM
Save excel including embedded Formulas, Comments etc. qazmlp Perl Misc 2 11-17-2004 05:46 PM
Re: Writing mathematical formulas without using Word Equation Editor =?iso-8859-1?Q?Andr=E9_P=F6nitz?= Computer Support 0 06-27-2003 08:30 AM
Re: Writing mathematical formulas without using Word Equation Editor =?iso-8859-1?Q?Andr=E9_P=F6nitz?= Computer Support 0 06-27-2003 08:26 AM



Advertisments