how to reference a cell programmatically in a loop with axlsx

Question

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?

Answer 1

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