[LRUG] Creating spreadsheets with programmatically formulas baked into them - already done in Ruby?
Daniel Barlow
dan at telent.net
Thu Feb 16 08:59:56 PST 2012
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.html>
More information about the Chat
mailing list