CODEWITHSUNDEEP
rubyaxlsx
Paul Brannan
Paul Brannan

Reputation: 1703

how to reference a cell programmatically in a loop with axlsx

I want to programmatically add rows to my worksheet that reference other cells within the same row. I've done this:

require 'axlsx'

Axlsx::Package.new do |p|
  p.workbook.add_worksheet(name: 'Foo') do |sheet|
    sheet.add_row(['i', 'i*2'])
    for i in 1..10 do
      sheet.add_row([i, "=A#{i+1} * 2"])
    end
  end
  p.serialize('test.xlsx')
end

Is there a better way to do this, that doesn't involve keeping track of which row I'm on?

Upvotes: 3

Views: 1616

Answers (1)

Nerve
Nerve

Reputation: 6871

You don't need to track every individual row for these tasks. There are excel functions for many of them that can be easily found over the Internet.

In your specific case, You can use this:

sheet.add_row([i,"=A:A * 2"])  # No need to iterate over the rows.

Here, A:A means that "Use Cells A1, A2, A3, A4 ..... "

Upvotes: 1

Related Questions