Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.ruby > #4768 > unrolled thread

Writing formulas to excel spreadsheet

Back to article view | Back to comp.lang.ruby

Contents

  Writing formulas to excel spreadsheet Will James <ampclj9@hotmail.com> - 2011-05-19 11:22 -0500
    Re: Writing formulas to excel spreadsheet Mike Stephens <rubfor@recitel.net> - 2011-05-19 16:58 -0500
      Re: Writing formulas to excel spreadsheet 7stud -- <bbxx789_05ss@yahoo.com> - 2011-05-19 17:37 -0500
        Re: Writing formulas to excel spreadsheet Will James <ampclj9@hotmail.com> - 2011-05-19 23:42 -0500
          Re: Writing formulas to excel spreadsheet Chuck Remes <cremes.devlist@mac.com> - 2011-05-20 09:28 -0500
          Saving Electricty - Using a Blackboard? Mike Stephens <rubfor@recitel.net> - 2011-05-20 12:41 -0500
            Re: Saving Electricty - Using a Blackboard? Will James <ampclj9@hotmail.com> - 2011-05-20 13:19 -0500
    Matz never said Microsoft was the Devil Incarnate. (or did he?) Mike Stephens <rubfor@recitel.net> - 2011-05-20 16:36 -0500
      Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Daniel Berger <djberg96@gmail.com> - 2011-05-20 17:02 -0500
        Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Will James <ampclj9@hotmail.com> - 2011-05-20 21:28 -0500
          Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Mike Stephens <rubfor@recitel.net> - 2011-05-21 16:14 -0500
            Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Damjan Rems <d_rems@yahoo.com> - 2011-05-23 15:18 -0500
              Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Will James <ampclj9@hotmail.com> - 2011-05-24 11:45 -0500
            Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Mike Stephens <rubfor@recitel.net> - 2011-05-23 16:38 -0500
              Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Michal Suchanek <hramrach@centrum.cz> - 2011-05-23 18:00 -0500
                Keep your options open Mike Stephens <rubfor@recitel.net> - 2011-05-24 12:45 -0500
                  Re: Keep your options open Johnny Morrice <spoon@killersmurf.com> - 2011-05-24 13:22 -0500
                  Re: Keep your options open Stu <stu@rubyprogrammer.net> - 2011-05-24 16:55 -0500
              Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Christopher Dicely <cmdicely@gmail.com> - 2011-05-23 20:53 -0500
          Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Colin Bartlett <colinb2r@googlemail.com> - 2011-05-25 10:27 -0500
            Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Mike Stephens <rubfor@recitel.net> - 2011-05-25 11:54 -0500
            Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Will James <ampclj9@hotmail.com> - 2011-05-25 13:53 -0500
              Re: Matz never said Microsoft was the Devil Incarnate. (or did he?) Will James <ampclj9@hotmail.com> - 2011-05-26 12:46 -0500

Page 2 of 2 — ← Prev page 1 [2]

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

Colin Bartlett wrote in post #1000942:

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

You are using formulae. Will wanted to change the formulae. (Apologies,
Will, if I came across as criticising you - that wasn't intended. I
fully
understand your problem constraints, which were not evident to start
with).

I'm curious to know why Will would change formulae. If parameters are
changing then he could factor those out as values passed in ( a bit like
your text file approach). If the data structures are changing
(eg variable range sizes) he could let Excel sort that out at run time.
Maybe you could tell us more, Will?

As regards to VBA, personally - for purity - I would aim to avoid it as
it shouldn't add any functionality you couldn't achieve by using a
combination of Ruby and Excel. To avoid being flamed yet again, I do
appreciate that this assumes you are running Ruby in the presence of
Windows and Excel. If you want to create an Excel sheet on a non-Windows
platform, your options are reduced.

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

[toc] | [prev] | [next] | [standalone]

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

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

Thanks for the good tips and suggestions, Colin. I've never really
looked at VB, and like yourself, would rather stick with ruby, but will
take a look at the method to which you pointed in the link. I guess that
if the VB script forces an after-the-fact evaluation, one dirty solution
would be to have the ruby script (after it has done its part) call the
VB script just to force calculations, but not knowing anything about VB,
I'm not sure about what sort of new issues this would introduce, aside
from having to have some sort of vb interpreter.

I've compared the excel xml for evaluated vs. non-evaluated cells, and
it appears that the spreadsheet gem writes whatever strings you tell it
to write (and the cell is tagged accordingly), whereas excel formulas
have a particular format, such as:
    <Cell ss:Index="14"
     ss:Formula="=(RC[-11]+R[57]C[-11]+R[114]C[-11]+R[171]C[-11]+R[228]C[-11])/5"><Data
      ss:Type="Number">1.5812633999999999E-2</Data></Cell>
    <Cell
which may take a while to implement if one were to, for example, dig
into the underlying module and try to add formula functionality. I don't
think I can fool around with this particular approach, because it risks
dedicating too much time to the tools used to solve the primary problem,
and cuts into the time allocated to the problem.

I'm glad to hear you're working on a ruby solution to this issue, and
hope you let us know when you've got it ready. It's kind of funny - as I
said earlier, I've never used ruby prior to this task, but I'm starting
to get into it to the point that I may end up using it quite a bit. But 
I guess at this point, this particular issue has become one of 
intellectual curiousity more than necessity.

>I'm curious to know why Will would change formulae. If parameters are
>changing then he could factor those out as values passed in ( a bit like
>your text file approach). If the data structures are changing
>(eg variable range sizes) he could let Excel sort that out at run time.
>Maybe you could tell us more, Will?

No prob - the task at hand doesn't involve changing formulas, but 
dynamically generating them. Specifically, I'm reading in large, 
cumbersome text files of output from another program, putting the data 
into excel (in a more human-readable format), and dynamically generating 
formulas in accordance with the nature of the input.

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

[toc] | [prev] | [next] | [standalone]

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

Chad Perrin wrote in post #1001016:
>
> Note that the reference was to VBA, which is not *strictly* the same as
> VB.  Knowing the difference between Visual Basic (or Visual Basic .NET)
> and Visual Basic for Applications might help you navigate through search
> results to find relevant information.
>
> Both of them are different from VBScript, in case it matters.

Thanks, Chad!

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

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | comp.lang.ruby

csiph-web