auto width of columns needs to ignore merged cells about axlsx HOT 9 CLOSED

It will solve the issue. And it could cause another issue.

Some merged cells can contain long enough text which width need to be adjusted. But you can ignore this case as it is rare.

from axlsx.

The width parsing should be delayed until the sheet is serialized. It should be trivial to determine if the collective widths of columns that contain merged cells are less than the predictable width of a merged cell and disperse the increase equally among merged collums. I will leave this open until I have time to re-write it.

from axlsx.

I'm not sure that ignoring the contents of the merged cells is the only solution. It is more difficult than that. Imagine a spreadsheet with five rows of headings. The first three rows are report headings. So these are going to be long winded, blah blah blah types of information. These are either centered on top of each other in the middle of the spreadsheet, or they are in the leftmost column.

             Dummy Client                                                 Carrier Route Discount   $0.082   
     Dummy Project Description                                            Additional Book Cost      $0.55   
MLK503.#633446.PS99 2012/01/13 14:02                                        
      ____________Eligible Pieces____________     Postage     Accum   Addtnl      Net     Accum       Net
Level .....Input ...Before .....Adds ...After   ..Savings ..Savings ....Book ....Cost ..Savings ..Savings

The next two rows contain column headings. These are typically one word, or small phrase, stacked on top of each other. Regardless, the existence of long winded report headings are going to screw up the column widths. What one needs is the ability to manually set the column widths as he or she chooses.

I could not figure out the mechanism for forcing a column width. So what did was backhand worksheet.rb and altered the :auto_fit_data attribute. I changed it from attr_reader to attr_accessor. I then forced into it an array of elements that accomplished what I wanted. The spreadsheet came out perfectly!

    :longtxt => "123,456,789"
    :longtxt2 => "$123,456,789"
    pp ws.auto_fit_data = [
      {:longest=>"123456", :sz=>11},
      {:longest=>:longtxt, :sz=>11},
      {:longest=>:longtxt, :sz=>11},

from axlsx.

Don, that is a great workaround for now.
I think we need a solution that both allows the developer to specify the width manually, as well as properly determine widths as I mentioned above.

The width parsing should be delayed until the sheet is serialized. It should be trivial to determine if the collective widths
of columns that contain merged cells are less than the predictable width of a merged cell and disperse the increase
equally among merged columns. I will leave this open until I have time to re-write it.

Logically, I am thinking something like:

calculate column widths ignoring merges
calculate width of merged cells
for each merge
sum the width of columns in merge range
disperse the difference to each column in the merge range if the sum exceeds the merge width

from axlsx.

Hey Randy,

I really need this workaround too.
Any chance you can change the auto_fit_data attribute from attr_reader to attr_accessor in the gem?
I am using the gem and when I start trying to run the code (with modifications) from lib instead of from the gem I get an error about 'zip/zip' not sure what to do.

Thanks a lot
Elisha

from axlsx.

Hi Elisha

Nice to hear from you again.

Could you send me a copy of the sheet you are working with?
It would be nice to have a realistic example to work against when I actually get into making auto_width "merge aware".

In the mean time, here is some info that might help you hack out what you need.

The auto_fit_data is an array of hashes like this:

1.8.7 :008 > ws.auto_fit_data
 => [{:sz=>11, :longest=>"this is a really long string man"}, {:sz=>11, :longest=>"asdf"}, {:sz=>11, :longest=>"longer"}] 

Each hash in the array represents the longest string in the column where index:0 = A, index:1 = B
The accessor for the array is read_only, but the hashes and content in the array can be edited.

e.g.

worksheet.auto_fit_data[0][:longest] = "nn" 

would alter the string analyzed to determine the width of the first column.

Best regards,

Randy

from axlsx.

Hi Elisha,

Very glad to hear the hack works for you, and thank you very much for your sample.

It really brings to light Don's point. We cannot accurately predict everything - you even have widths set up and looking great for empty columns, multiple indentation levels and a healthy set of linked images.

Which leads me to think that the "Hack" needs to be built into a supported solution.

I am going to add a third key=>pair to that array of data, :fixed=>nil, so that when we - the developer want a fixed width on a column - we can make it so, and a method on worksheet to set whatever column widths we want to with relative ease.

Just remember that the calculation of width is not pixels, not ems, but well - something pretty nasty. One of those dark corners of excel that I often think of as a great example of how to NOT do something.

A bit of trial and error until you find what you like will be important - but we get full control when we need it.
I should be able to release .17 with this feature by tomorrow evening.

from axlsx.

A formalized way to manipulate column widths has been released.

While we still have access to worksheet.auto_fit_data[index][:longest] to fake the content in auto_width calculations the recommended way to specify the widths for the columns in your sheet is now:

 worksheet.column_widths(*args)

each argument passed specifies the fixed width value for the column at the index in your list of arguments. (that makes no sense!) lets have an example.

Assuming a sheet that has five columns, and we want to set a fixed width for the second and third column:

worksheet.column_widths nil, 8.17, 2.9

and you are done.

nil values tell axlsx to use the longest text in the column. Unspecified values are always nil.

However if you just want to set the width of the 25th column (assuming you have that many columns in your sheet) this is easier to work with:

worksheet.auto_fit_data[24][:fixed] = 35

As axlsx is sparse, you cannot set the width for columns that do not exist yet in your sheet. To that extent, I recommend setting up any fixed widths after you populate the data.

Thanks to all for the great feedback and pressure to do something about this.

from axlsx.

Thanks to all for the great feedback

Seriously, thank you. I like working with people that have good ideas like
AXLSX. It nice working with people that adapt their products is timely
manner.

This product is awesome! I wish I had the smarts to develop stuff like
this.

dvn

On Mon, Feb 13, 2012 at 8:53 PM, Randy Morgan <
[email protected]

wrote:

A formalized way to manipulate column widths has been released.

While we still have access to worksheet.auto_fit_data[index][:longest] to
fake the content in auto_width calculations the recommended way to specify
the widths for the columns in your sheet is now:

worksheet.column_widths(*args)

each argument passed specifies the fixed width value for the column at the
index in your list of arguments. (that makes no sense!) lets have an
example.

Assuming a sheet that has five columns, and we want to set a fixed width
for the second and third column:

worksheet.column_widths nil, 8.17, 2.9

and you are done.

nil values tell axlsx to use the longest text in the column. Unspecified
values are always nil.

However if you just want to set the width of the 25th column (assuming you
have that many columns in your sheet) this is easier to work with:

worksheet.auto_fit_data[24][:fixed] = 35

As axlsx is sparse, you cannot set the width for columns that do not exist
yet in your sheet. To that extent, I recommend setting up any fixed widths
after you populate the data.

Thanks to all for the great feedback and pressure to do something about
this.

---

Reply to this email directly or view it on GitHub:
#5 (comment)

from axlsx.