Rails AXSLX sort.cells

Question

I'm using gem 'axlsx_rails' in a Rails app.

I would like to sort cells (row 2 through 23 on column A) as the last step in creating a tab.

Here is the code I have:

wb.add_worksheet(:name => "Cost") do |sheet|
  sheet.page_setup.set :orientation => :portrait
  sheet.add_row ['Seq', 'Category', 'Remarks', 'Amount', 'Notes'], :style => [header_cell, header_cell, header_cell, header_cell, header_cell]
    @costproject.costestimates.each do |costestimate|
      sheet.add_row [costestimate.costcat.position, costestimate.costcat.category_name, costestimate.costcat.categorydesc, number_with_precision(costestimate.amount, :precision => 2), costestimate.notes], :style=> [intgr,nil,nil,money]
    end
  sheet.add_row [nil, 'TOTAL', nil, "=SUM(D2:D23)"]
  sheet.column_widths 5, 35, 25, 25
  cells.sort ?????
end

I assume this can be done. Is that right? If yes, how? What do I replace cells.sort ????? with?

Thanks for your help!

Update 1:

Thanks to emcanes, I sorted the records during the add_row:

       sheet.add_row ['Seq', 'Category', 'Remarks', 'Amount', 'Notes'], :style => [header_cell, header_cell, header_cell, header_cell, header_cell]
    @costproject.costestimates.includes(:costcat).order("costcats.position").each do |ce|
      sheet.add_row [ce.costcat.position, ce.costcat.category_name,  ce.costcat.categorydesc, number_with_precision(ce.amount, :precision => 2), ce.notes], :style=> [intgr,border,border,money,border]
    end

I would still like to know if AXSLX can use cells.sort??

Answer 1

The rows SimpleTypedList is available through the worksheet, and though there is no sheet.rows= method, you can use sort_by! to modify it in place:

wb.add_worksheet(:name => "Cost") do |sheet|
  # your other code
  first_row = sheet.rows.first
  last_row  = sheet.rows.last
  # get a total position higher than all others
  tpos = sheet.rows.map {|row| row.cells[0].value.to_i}.max + 1
  sheet.rows.sort_by! do |row|
    (row == first_row ? -1 : (row == last_row ? tpos : row.cells[0].value.to_i))
  end
  # now do styling
end

There is no internal index for a row, so you can't ask what index it is. It simply finds out where it is located in the rows list if you ask it. So you have to save the title/total rows ahead of time.

I've not tested the styling, but I'm not at all sure it would follow the sort, since this is messing with Axlsx internals. It may need to happen after, unless it is generic.

And, incidentally, use the Axlsx::cell_r function to get your total range:

"=SUM(D2:#{Axlsx::cell_r(3,@costproject.costestimates.length)}"

Since it expects zero based indices, the actual length will count the title row. 22 estimates will give you "D23".