[LRUG] Creating spreadsheets with programmatically formulas baked into them - already done in Ruby?

[Daniel Barlow]

On Thu, Feb 16, 2012 at 4:12 PM, Chris Adams <mail at chrisadams.me.uk> wrote:

> Hi all,
>
> I'm curious about how one might go about generating a downloadable
> spreadsheet for Excel or Openoffice, where formulae inside it are
> generated server side, depending on what a user has requested
> beforehand in a Rails 3 app.
>
>
I don't know about Excel, but if you're willing to educate the recipient a
little, Openoffice (and Libreoffice) will accept formulae in CSV files if
you untick "Quoted fields as text" in the import dialog.  I realise this
doesn't answer the question as posed, but for a limited/internal audience
it might do the trick

If you have a table with formulae on each row that depend on other columns
in that row, you'll either have to track the row numbers yourself or use
relative references - e.g.

require 'csv'
# rows is some data
CSV.open(ARGV[0],"w") do |csv|
  csv << rows[0].keys
  rows.each do |r|
    r[:owing]='=INDIRECT("R[0]C[-2]",0)-INDIRECT("R[0]C[-1]",0)'

r[:value]='=(INDIRECT("R[0]C[-5]",0)+INDIRECT("R[0]C[-4]",0))*INDIRECT("R[0]C[-1]",0)'
    csv << r.values
  end
end


-dan

-- 
dan at telent.net
http://ww.telent.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20120216/22322420/attachment-0003.html>