Using Ruby Spreadsheet gem, is there a way to get cell to adjust to size of content

Question

If 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 cell (width and height) to adjust to the size of the content automatically?

Answer 1

def autofit(worksheet)
    (0...worksheet.column_count).each do |col|
        @high = 1
        row = 0
        worksheet.column(col).each do |cell|
            w = cell==nil || cell=='' ? 1 : cell.to_s.strip.split('').count+3
            ratio = worksheet.row(row).format(col).font.size/10
            w = (w*ratio).round
            if w > @high
                @high = w
            end
            row=row+1
        end
        worksheet.column(col).width = @high
    end
    (0...worksheet.row_count).each do |row|
        @high = 1
        col = 0
        worksheet.row(row).each do |cell|
            w = worksheet.row(row).format(col).font.size+4
            if w > @high
                @high = w
            end
            col=col+1
        end
        worksheet.row(row).height = @high
    end
end

This method will loop through each column in the worksheet, then loop through each cell in the column, finding the cell with the most characters (+3 for a little extra space) and adjusting according to that cell. It then does the same thing for the rows. This value works great for font size 10, but to make sure it gets a little bigger for larger fonts, it adjust to the font size with this bit of code:

ratio = worksheet.row(row).format(col).font.size/10
w = (w*ratio).round

..It's not perfect, but gets the job done.

Updated to include KevinM's method. Both work for me, but Kevin's looks better too. The original line 6 was:

cell==nil || cell=='' ? w = 1 : w == cell.to_s.strip.split('').count+3

Answer 2

For future readers, here is a slightly more "ruby-esque" version of CCinkosky's code above. (Note: I didn't bother resizing the column heights, but this could be added easily.)

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