Setting Excel column widths correctly when adding to spreadsheet via Axlsx

Question

I'm trying to format my report and making sure the columns have a correct width and I can't seem to make it happen with auto_width.

With this code, this is the kind of report I get . Notice the space that is way to long for an auto_width, since if I double click on each column border in Excel, it correctly resizes, see this picture .

Maybe it's the order I'm doing things?

This is the code I'm using:

workbook = xlsx_package.workbook
worksheet_name = 'My Worksheet'
xlsx_package.use_autowidth = true

# Styles init
header_style = workbook.styles.add_style               :bg_color => "D8D8D8",
                                                   :b => true,
                                                   :border => { :style => :thin, :color => "00" },
                                                   :alignment => { :horizontal => :center,
                                                                   :vertical => :center ,
                                                                   :wrap_text => false}
totals_style = workbook.styles.add_style               :bg_color => "D8D8D8",
                                                   :b => true,
                                                   :border => { :style => :thin, :color => "00" },
                                                   :alignment => { :horizontal => :center,
                                                                   :vertical => :center ,
                                                                   :wrap_text => false}
odd_row_style = workbook.styles.add_style              :bg_color => "A9E2F3",
                                                   :border => { :style => :thin, :color => "00" },
                                                   :alignment => { :horizontal => :center,
                                                                   :vertical => :center ,
                                                                   :wrap_text => false}
even_row_style = workbook.styles.add_style             :bg_color => "CEECF5",
                                                   :border => { :style => :thin, :color => "00" },
                                                   :alignment => { :horizontal => :center,
                                                                   :vertical => :center ,
                                                                   :wrap_text => false}
merged_title_cell_style = workbook.styles.add_style    :bg_color => "D8D8D8",
                                                   :b => true,
                                                   :sz => 16,
                                                   :border => { :style => :thin, :color => "00" },
                                                   :alignment => { :horizontal => :center,
                                                                   :vertical => :center ,
                                                                   :wrap_text => true}

workbook.add_worksheet(:name => worksheet_name) do |sheet|
  # Add empty row for aesthetics
  sheet.add_row([''], :height => 8)

  # We add the meta header row
  meta_header_row = ['', "Meta header 1", '', '', '', '', '', '', '', '', '', "Meta header 2", '', '', '', '']
  sheet.add_row(meta_header_row, :style => merged_title_cell_style, :height => 30)
  sheet.merge_cells('B2:K2')
  sheet.merge_cells('L2:P2')

  @data = Array.new
  @data << ['John', 1, 2, 3, 4, 5, 6, 7, 8, 9 ,10, 11, 12, 13, 14, 15]
  @data << ['Jack', 1, 2, 3, 4, 5, 6, 7, 8, 9 ,10, 11, 12, 13, 14, 15]
  @data << ['Bob', 1, 2, 3, 4, 5, 6, 7, 8, 9 ,10, 11, 12, 13, 14, 15]
  @data << ['Franck', 1, 2, 3, 4, 5, 6, 7, 8, 9 ,10, 11, 12, 13, 14, 15]
  @data << ['A total', 4, 8, 16, 20, 24, 28, 32, 36, 40, 44, 48, 52, 56, 60, 64]
  @data << ['Another total', 4, 8, 16, 20, 24, 28, 32, 36, 40, 44, 48, 52, 56, 60, 64]

  @data.each_with_index do |data_row, index|
    if(index == 0)
      sheet.add_row(data_row, :style => header_style)
    elsif(index >= @data.count - 2)
      sheet.add_row(data_row, :style => totals_style)
    elsif(index.even?)
      sheet.add_row(data_row, :style => even_row_style)
    else
      sheet.add_row(data_row, :style => odd_row_style)
    end
  end

  # Styling
  sheet.col_style(0, header_style)

  # We keep the first 2 cells white
  sheet.rows[0..1].each{|row| row.cells[0].style = 0}

  sheet.auto_filter = "A3:A#{sheet.rows.count}"
end

Thanks for you help!

Hearken

I just realized that the fact that the first column in each group is that much bigger is because I add the text of the meta header there before merging the rows. I don't see how to do it differently. It still doesn't explain why all the columns are so large even with autowidth. – Hearken

Answer 1

I too have experienced this problem. I'm assuming you're searching for that solution that mimics as close as possible that shrink-wrapped "I just double-click every column header" look. Unfortunately, I have found this very difficult to do.

Something that might help you is an (undocumented?) width type:

sheet.add_row(meta_header_row, 
  :style => merged_title_cell_style, 
  :height => 30,
  :widths => [:ignore] * meta_header_row.count # caution: Use *:widths* not :width
)

This tells Axlsx that you still want to use autowidth to calculate column widths, but to ignore the content of any of the cells in this row while doing so.

Ithunn

Thanks, I'm going to try that and see how it ends. And yes, this is what I'm trying to find as an effect, that the column has the right width if you changed the font. – Hearken

I'm sorry I changed contract in between this time and now, so I haven't tried it! – Hearken

For posterity, this solution is fine, and documented here: rubydoc.info/github/randym/axlsx/Axlsx/… – Ellette

Worked great for me. – Interject