Writing formulas to excel spreadsheet

Discussion in 'Ruby' started by Will James, May 19, 2011.

  1. Will James

    Will James Guest

    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/.
     
    Will James, May 19, 2011
    #1
    1. Advertising

  2. Have a look at :rubyonwindows.blogspot.com/search/label/excel

    and look for 'formula'

    --
    Posted via http://www.ruby-forum.com/.
     
    Mike Stephens, May 19, 2011
    #2
    1. Advertising

  3. Will James

    7stud -- Guest

    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#{emptyRow})"

    --
    Posted via http://www.ruby-forum.com/.
     
    7stud --, May 19, 2011
    #3
  4. Will James

    Will James Guest

    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#{emptyRow})"


    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/.
     
    Will James, May 20, 2011
    #4
  5. Will James

    Chuck Remes Guest

    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#{emptyRow})"

    >
    > 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
     
    Chuck Remes, May 20, 2011
    #5
  6. Saving Electricty - Using a Blackboard?

    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/.
     
    Mike Stephens, May 20, 2011
    #6
  7. Will James

    Will James Guest

    Re: Saving Electricty - Using a Blackboard?

    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/.
     
    Will James, May 20, 2011
    #7
  8. Matz never said Microsoft was the Devil Incarnate. (or did he?)

    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/.
     
    Mike Stephens, May 20, 2011
    #8
  9. Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

    On May 20, 3:36=A0pm, Mike Stephens <> 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
     
    Daniel Berger, May 20, 2011
    #9
  10. Will James

    Will James Guest

    Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

    Daniel Berger wrote in post #999984:
    > On May 20, 3:36pm, Mike Stephens <> 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/.
     
    Will James, May 21, 2011
    #10
  11. Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

    Chad Perrin 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.

    --
    Posted via http://www.ruby-forum.com/.
     
    Mike Stephens, May 21, 2011
    #11
  12. Will James

    Damjan Rems Guest

    Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

    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

    --
    Posted via http://www.ruby-forum.com/.
     
    Damjan Rems, May 23, 2011
    #12
  13. Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

    Chad Perrin wrote in post #1000266:
    > 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.

    --
    Posted via http://www.ruby-forum.com/.
     
    Mike Stephens, May 23, 2011
    #13
  14. Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

    On 23 May 2011 23:38, Mike Stephens <> wrote:
    > Chad Perrin wrote in post #1000266:
    >> 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 =C2=A0- 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 problema=
    tic.

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

    Regards

    Michal
     
    Michal Suchanek, May 24, 2011
    #14
  15. Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

    On Mon, May 23, 2011 at 2:38 PM, Mike Stephens <> wrote:

    >
    > 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.
     
    Christopher Dicely, May 24, 2011
    #15
  16. Will James

    Will James Guest

    Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

    Damjan Rems wrote in post #1000470:
    > 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.

    --
    Posted via http://www.ruby-forum.com/.
     
    Will James, May 24, 2011
    #16
  17. Keep your options open

    Michal Suchanek wrote in post #1000501:
    > 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
    http://marketshare.hitslink.com/operating-system-market-share.aspx?qprid=10

    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.

    --
    Posted via http://www.ruby-forum.com/.
     
    Mike Stephens, May 24, 2011
    #17
  18. Re: Keep your options open

    > 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!

    Do you do parties?
     
    Johnny Morrice, May 24, 2011
    #18
  19. Will James

    Stu Guest

    Re: Keep your options open

    x11 is not an operating system. FreeBSD is not even on that list. If
    your gonna link analysis and statistic web at least link one that has
    been around since before the bubble.

    http://news.netcraft.com/archives/2...e-hosting-company-sites-in-december-2010.html

    As for referring to your peers as childish within your remark about M$
    hate. Seriously if you don't know what the problem is at this point
    you really should reevaluate some of this cognitive dissonance so you
    wont be spewing bullshit like over 90% computer in the whole world run
    x,y,z OS.

    On Tue, May 24, 2011 at 12:45 PM, Mike Stephens <> wrote:
    > Michal Suchanek wrote in post #1000501:
    >> 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
    > http://marketshare.hitslink.com/operating-system-market-share.aspx?qprid=10
    >
    > 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.
    >
    > --
    > Posted via http://www.ruby-forum.com/.
    >
    >
     
    Stu, May 24, 2011
    #19
  20. Re: Matz never said Microsoft was the Devil Incarnate. (or did he?)

    On Sat, May 21, 2011 at 3:28 AM, Will James <> wrote:
    > Daniel Berger wrote in post #999984:
    >> On May 20, 3:36pm, Mike Stephens <> 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.
     
    Colin Bartlett, May 25, 2011
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. FSD
    Replies:
    0
    Views:
    1,191
  2. Kevin Howe
    Replies:
    6
    Views:
    227
    Kevin Howe
    Feb 6, 2005
  3.  James
    Replies:
    0
    Views:
    170
    James
    May 19, 2011
  4. qazmlp
    Replies:
    2
    Views:
    145
    Ben Morrow
    Nov 17, 2004
  5. Yousaf
    Replies:
    1
    Views:
    105
    Bart Van der Donck
    Apr 10, 2007
Loading...

Share This Page