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!
Change the column width to automatically fit the contents (AutoFit) Select the column or columns that you want to change. On the Home tab, in the Cells group, click Format. Under Cell Size, click AutoFit Column Width.
On the first column click A to select the column, and then right click and select Column Width. Type the width that you want for your column. Click OK.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With