Rails Axlsx Column Width And Wrap Text

Question

Using Axlsx some of my cells are set to wrap text which works great. The problem I'm running into is how the auto-width of the cells seems to be calculated. It seems like what's happening is initially the data comes in like this:

cell line1 cell line2 cell line3 cell line4 cell line5

Which Axlsx using to set the width to be that big. But then wrap text comes in and correctly sets the cell to this:

cell line1
cell line2
cell line3
cell line4
cell line5

It's height is then set correctly to display all five values but the row width is still huge. Does anyone know if there's a way to get Axlsx to auto-size the cell as if you were to double click the cell within Excel to have it auto size the width? Setting Excel column widths correctly when adding to spreadsheet via Axlsx seems to elude to it but I couldn't get it to have any effect - not sure the :widths property exists anymore or works?

I also have been playing around with column_widths as well e.g. sheet.column_widths *30. Does anyone know if there's a way to force all columns to have the same widths with this property? It doesn't seem to accept an array - only integers/floats/fixnums separated by commas and doing *30 only sets the first column to a width of 30. I don't actually know beforehand how many columns I'm going to be using (dynamic data) so I'd love to be able to set that to just auto-size all of the columns to a certain width.

Gladine

Answer 1

Check colum_widths function, It iterates through arguments passed and sets that many(number of arguments passed) columns width to passed values.

so, If you want to set X number of columns width to Y, then you should pass [Y]*x array as argument to column_widths, and use spalt(*) operator to convert this array to arguments list.

In your case Number of columns are going to de dynamic, so you can use sheet.column_info.count to get number of columns in sheet and If you want to set all that columns' width to say 30, then all you need to do is, AFTER adding data set width using - sheet.column_widths *([30]*sheet.column_info.count)

Sensualism

This has an example of column widths that can be set : rubydoc.info/github/randym/axlsx/Axlsx/Worksheet:add_row it uses :ignore, :auto or a fixed numerical width in the example. Word of warning that the auto calculation is just that and will not always size the column to be the correct width. There is a project clone that is trying to address the problem here: rscz.ru/clones/axlsx/tree/… – Creolized

Answer 2

Not a real solution, but I use the following code to set the column width of "wrap columns" when Axlsx makes them too wide:

sheet.column_widths *(sheet.column_info.map { |info| [info.width, 50].min })

Caveator