CODEWITHSUNDEEP
rubyrubygemsaxlsx
Tom Lord
Tom Lord

Reputation: 28305

Generate axlsx spreadsheets which are not easily formed by simple use of `add_row`

Background:

I have previously been using the gem spreadsheet to generate .xls spreadsheets as part of a ruby project.

However, this has caused various formatting and compatibility issues - the most recent being that old versions of excel only support up to 256 columns per sheet (column IV). So, rather than hack around with VB or reformatting the data into multiple sheets, it makes more sense to refactor the code to use a .xlsx generator instead.

Enter axlsx.

Problem:

My generated spreadsheets look something like this:

Header1 | Header2 | Header3 |
-----------------------------
aaa     | bbb     | ccc
-----------------------------
        | ddd     | eee
-----------------------------
        | fff     |

(There are actually more like 300 columns and 500 rows, but you get the idea.)

My old approach to generate this, using the spreadsheet gem, goes along the lines of:

def initialize
  @book = Spreadsheet::Workbook.new
  @sheet = book.create_worksheet
  @columns = 0
end

def write_column(header, rows)
  @sheet[0, @columns] = header
  rows.each_with_index do |data, i|
    @sheet[i+1, @columns] = data
  end
  @columns += 1
end

However, this approach is invalid for axlsx. As the documentation states:

Note: The recommended way to generate cells is via Worksheet#add_row

Question:

What is the "right" way to generate spreadsheets such as the above, which clearly "look like" they should be generated by an add_column method rather than add_row?

The documented approach is to do something like:

sheet.add_row ['Header1', 'Header2', 'Header3']
sheet.add_row ['aaa', 'bbb', 'ccc']
sheet.add_row [nil, 'ddd', 'eee']
sheet.add_row [nil, 'fff', nil]

...However, this is not great since I'm now unable to directly loop through each column of data.

Another approach could be to initialise a big grid of cells, like:

sheet.add_row ['Header1', 'Header2', 'Header3']
3.times { sheet.add_row [nil, nil, nil] }

And then add my data in a separate step, like:

sheet["A2"] = "aaa"

However, this feels wrong - as it's abusing the way in which sheets get generated.

How would the community advise sheets like this be generated?

Upvotes: 0

Views: 400

Answers (1)

noel
noel

Reputation: 2145

If you are receiving columns of data, have you tried placing each column as a row in an intermediate array, transposing it, and loading its rows into the Axlsx spreadsheet?

intermediate = [column1, column2, column3]
sheet.add_row ['Header1', 'Header2', 'Header3']
intermediate.transpose.each do |row|
  sheet.add_row row
end

I don't know what the "proper" way would be, but transpose is very useful for these kinds of problems.

That may be memory intensive with large amounts of data btw.

Upvotes: 1

Related Questions