i figured it out. i was passing write_col an array instead of a ref. so this works: $worksheet->write_col(0, 0, $comp);. i think the error handling could be improved though.

as for the code dump, here's what i have right now i had some issues in my logic i'm trying to solve so it's changed more than just resolving this issue:

#!/usr/bin/perl

use strict;
use warnings;

use Data::Dumper;
use List::UtilsBy qw/max_by/;

use Getopt::Long;
use Pod::Usage;

use Spreadsheet::ParseExcel;
use Excel::Writer::XLSX;

my $opts;
$opts->{comp} = shift @ARGV;
$opts->{with} = shift @ARGV;

GetOptions( 'output|o=s'   => \$opts->{out},
            'colmap|c=s'   => \$opts->{col},
            'format|f=s'   => \$opts->{format},
            'help|h'       => \$opts->{help},
            'man|m'        => \$opts->{man}
         ) or pod2usage({ -verbose => 0, -output => \*STDERR,
            -msg => "$0 no parameter found.\n
            Use --help for more options.\n"
         } );

if ($opts->{man}) {
   pod2usage( -verbose => 2 );
} elsif ($opts->{help} or !defined($opts->{comp}) or !defined($opts->{
+with})) {
   pod2usage( -verbose => 0, -output => \*STDERR,
               -msg => "$0 [options]" );
}

my $workbook = Excel::Writer::XLSX->new($opts->{'out'} // 'out.xlsx');
my $worksheet = $workbook->add_worksheet();
my $fmt = $workbook->add_format(color => $opts->{format} // 'blue');

my @cols = map {/(\d+):(\d+)/ ? [$1, $2] : die "Malformed pair: $_"} s
+plit(' ', $opts->{col}) if $opts->{col};

my ($comp, $comp_parm) = xltree($opts->{comp});
my ($with, $with_parm) = xltree($opts->{with});

$worksheet->write_col(0, 0, $comp);

if (@cols) {
   foreach my $col (@cols) {
      colcmp($worksheet, $comp, $with, $comp_parm, $with_parm, $col);
   }
} else {
   foreach my $col ($comp_parm->[2] .. $comp_parm->[3]) {
      colcmp($worksheet, $comp, $with, $comp_parm, $with_parm, $col);
   }
}

$workbook->close();

sub xltree { # from excel file, return array $data structure and an ar
+ray of $row_min, $row_max, $col_min, $col_max;
   my ($file) = @_;

   my $parser     = Spreadsheet::ParseExcel->new();
   my $workbook   = $parser->parse($file);

   if (!defined($workbook)) {
      die "Problem with $file: " . $parser->error() . ".\n";
   }

   my $worksheet = $workbook->worksheet(0);

   my ($row_min, $row_max) = $worksheet->row_range();
   my ($col_min, $col_max) = $worksheet->col_range();

   my $data;

   for my $row ($row_min .. $row_max) {
      for my $col ($col_min .. $col_max) {
         my $cell = $worksheet->get_cell($row, $col);
         $data->[$row][$col] = defined($cell) ? $cell->unformatted : "
+ ";
      }
   }

   return $data, [$row_min, $row_max, $col_min, $col_max];
}

sub colcmp {
   my ($worksheet, $comp, $with, $comp_parm, $with_parm, $col) = @_;

   for my $comp_row ($comp_parm->[0] .. $comp_parm->[1]) {
      my @row_i;
      for my $with_row ($with_parm->[0] .. $with_parm->[1]) {
         $row_i[$with_row] = grep { $_ ~~ [split(" ", $with->[$with_ro
+w][$col->[1]])] }
               split(" ", $comp->[$comp_row][$col->[0]]);
      }
      my $max_match = max_by { $row_i[$_] } 0 .. $#row_i;
      my @wmap = map { $with->[$max_match][$col->[1]] =~ /$_/ ? ($fmt,
+ "$_ ") : "$_ " }
                  split(" ", $comp->[$comp_row][$col->[0]]);
      $worksheet->write_rich_string($comp_row, $col->[0], @wmap);
   }
}

[download]

In reply to Re^2: get_xf_index error by ag4ve
in thread get_xf_index error by ag4ve

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.