add_write_handler() stackable to add multiple write() handlers?

[marcschwartz]

Hi,

I have been using the code at https://github.com/jmcnamara/spreadsheet-writeexcel/blob/master/examples/autofit.pl code to implement the autofit approach for column widths for some time and it works well. Thanks!

I have come across some recent situations where, despite using the keep_leading_zeros() worksheet function, there are circumstances where this fails to retain the zeros (e.g. 0123.4 becomes 123.4) presumably because keep_leading_zeros() only works for integers and not floats.

I have had some requests to keep leading zeros in that circumstance, as well as retaining trailing zeros for similar reasoning, as they are part of formatted strings and not numbers, per se.

As a result, I have been explicitly forcing the use of write_string() for all data in these circumstances, which is called via a user definable argument that would cover all data formats.

However, this approach would appear to obviate the autofit functionality, presumably since it bypasses where the autofit write handler would otherwise execute, by calling write_string() directly instead of write(). Thus, the combination of autofit and write_string() appears to fail.

I was wondering if add_write_handler() is, in effect, "stackable", so that when both approaches are needed, I could use the autofit write handler, followed by a write_string() handler, such that they would work in concert with each other.

I should note that I have commented out the part of the autofit.pl script where it ignores numbers, which seems to work as expected so that these otherwise numeric values are treated as string content and the column widths are adjusted accordingly.

Thus, could I use something like the following:

if ($AdjWidth eq "TRUE") { $WorkSheet->add_write_handler(qr[\w], \&store_string_widths); }

if ($AllText eq "TRUE") { $WorkSheet->add_write_handler(qr[\w], \&use_write_string); }

where use_write_string() would explicitly call write_string() for all cell content and then not return to write(), as is the case with store_string_widths().

I did not want to try this blindly for fear of some unseen and unintended consequences, and I did not find any examples via Google search.

If this would not work, is there an alternative approach that I should be considering, where I could effectively force all content in the worksheet to be written as text, while preserving autofit?

I would need this done in such a manner, that one or both of the autofit and write_string() approaches can work.

Thanks!

[marcschwartz]

A follow up.

Based upon testing the past few days, it would seem that, unless I am missing something, the use of add_write_handler() is "stackable", that is, multiple sub-routines can be added/inserted sequentially.

I have streamlined the two handlers, and have replaced the use of keep_leading_zeros() with the following, to handle both integer-like and float-like formatted content, with the exception of a float with a leading zero, followed immediately by a decimal point, to leave that as numeric:

sub use_write_string {

  my $worksheet = shift;
  my $token     = $_[2];

  # use this all the time
  if ($AllText eq "TRUE") {
    return $worksheet->write_string( @_ );
  # only for leading/trailing zeroes  
  } elsif ($token =~ /^0[^\.]|0$/) {
    return $worksheet->write_string( @_ );
  } else {
    # Return control to write();
    return undef;
  }
}

This allows for a default use of write_string() to deal with leading and trailing zeroes when present, the conditional use of write_string() in all cases based upon a user definable argument ($AllText), or to simply return to write() in other cases.

I have the following code to add the handlers:

if ($AdjWidth eq "TRUE") {
$WorkSheet->add_write_handler(qr[\w], &store_string_widths);
}

$WorkSheet->add_write_handler(qr[\w], &use_write_string);

so that the column width adjustment is followed by the write_string() handler.

Unless somebody notes something that I am missing, or there are "unseen" consequences, I will move forward with this modification, and this issue can be marked as closed.

Perhaps this might be helpful to others in some manner.

Thanks!

[jmcnamara]

Unless somebody notes something that I am missing, or there are "unseen" consequences, I will move forward with this modification, and this issue can be marked as closed.

Ok. Thanks. Closing.