8

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 enter image description here. 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 enter image description here.

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!

Bob Gilmore
  • 12,608
  • 13
  • 46
  • 53
Nicolas Belley
  • 803
  • 2
  • 12
  • 29
  • 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. – Nicolas Belley Oct 31 '13 at 13:08

1 Answers1

7

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.

Christopher Oezbek
  • 23,994
  • 6
  • 61
  • 85
Elliot Nelson
  • 11,371
  • 3
  • 30
  • 44