autofit excel column width using spreadsheet gem

Question

I am using the Ruby Spreadsheet gem to export data from a rails app to excel, is there a way to get the size of a columns adjust to the size of the content automatically?

Answer 1

Not that I know of. I've had to do it manually by keeping track of the lengths of the strings in each column and then taking the max length and adding a couple more units to it, then setting the width of the column to that calculated value.

account_name_lengths = []
# generate each row
accounts.each do |account|
  account_name_lengths << account.name.length

  # add to sheet here
end

sheet.column(0).width = account_name_lengths.max + 5

Answer 2

For auti-fit all columns

def auto_fit(sheet)
  (0...sheet.column_count).each do |col_idx|
    column = sheet.column(col_idx)
    column.width = column.each_with_index.map do |cell, row|
      chars = cell.present? ? cell.to_s.strip.split('').count + 4 : 1
      ratio = sheet.row(row).format(col_idx).font.size / 10
      (chars * ratio).round
    end.max
  end
end