module errors

[shawn wilson]

I am trying to get "footnotes" - basically just text below that is
linked to and am getting tons of errors from Excel::Writer::XLSX. I
assume it's something to do with returning a pre-compiling a formatted
list but idk (and maybe there's a better way to do this?):

sub make_spreadsheet
{
my ($sOut, $phData, $phHostMap) = @_;
my $oWB = Excel::Writer::XLSX->new($sOut);

my $paIssues = sorted_issues($phData);
my $paHosts = sorted_hosts($phData);

# Define formats
my $phFormats;
$phFormats->{default} = $oWB->add_format();
$phFormats->{bold} = $oWB->add_format(bold => 1);
$phFormats->{center} = $oWB->add_format(align => 1, valign => 1);
$phFormats->{gray_bg} = $oWB->add_format(bg_color => 23);
$phFormats->{link} = $oWB->add_format(color => 'blue', underline => 1);
$phFormats->{footnote} =
$oWB->add_format(color => 'blue', underline => 1, font_script => 1);
$phFormats->{main} = $oWB->add_format()->set_align(vjustify => 1);

my @aParams = ($oWB, $phData, $paIssues, $paHosts, $phHostMap, $phFormats);
wb_add_main(@aParams);
wb_add_issue_matrix(@aParams);
wb_add_results(@aParams);
}

sub wb_add_main
{
my ($oWB, $phData, $paIssues, $paHosts, $phHostMap, $phFormats) = @_;

my $paDesc = compile_footnotes(@_);

my $oWS = $oWB->add_worksheet('Main');
my $sCount = 0;
$oWS->write($sCount++, 0, ["Name", "Description", "Total
Vulnerable"], $phFormats->{bold});
foreach my $paRow (@$paDesc)
{
my $sCol = 0;
if (scalar(@$paRow) > 1)
{
my $sIssue = $paRow->[0];
$oWS->write($sCount, $sCol++, internal_link($sIssue, 'A1'),
$phFormats->{link}, $sIssue);

$oWS->write_rich_string($sCount, $sCol++,
format_footnotes($phFormats, [@{$paRow}[1 .. $#$paDesc]], 'Main', 1,
'A'));

$oWS->write($sCount, $sCol++, (scalar(keys %{$phData->{$sIssue}}) -1));
}
else
{
$oWS->write($sCount, $sCol, '');
}
$sCount++;
}
$oWS->freeze_panes(1, 0);
}

# Return an array with:
# (Title, comment part, ....)
# A comment part is either a string or an array reference with:
# [Footnote location, text label]
# Note: Footnote location is the element of the array - offset accordingly
sub compile_footnotes
{
my ($oWB, $phData, $paIssues, $paHosts, $phHostMap, $phFormats) = @_;

my $oFormat = Text::Format->new();
$oFormat->config({firstIndent => 0, columns => 70});

my ($paRet, $paCommons, $phCommonCount);
foreach my $sIssue (@$paIssues)
{
next if (not exists($phData->{$sIssue}{_desc}));
push @$paRet, [$sIssue];
foreach my $sComment (@{$phData->{$sIssue}{_desc}})
{
# Grab common text calls to point to or insert later
if (grep {$_ eq $sComment} keys %$phCommonText)
{
$phCommonCount->{$sComment}++;
push @$paCommons, [$sComment, $#{$paRet}, $#{$paRet->[-1]}];
next;
}
$paRet->[-1][1] .= $oFormat->format($sComment);
}
}

# Fix common text
my $sFootNoteCount = 0;
foreach my $sCommon (keys %$phCommonCount)
{
my @aIdx = grep {$_->[0] eq $sCommon} @$paCommons;
if ($phCommonCount->{$sCommon} > 1)
{
$sFootNoteCount++;
if ($sFootNoteCount == 1)
{
push @$paRet, ([], ["Footnotes"]);
}
push @$paRet, [$sFootNoteCount,
$oFormat->format($phCommonText->{$sCommon})];
}
foreach my $paIdx (@aIdx)
{
my ($sFirst, $sSecond) = @{$paIdx}[1,2];
splice @{$paRet->[$sFirst]}, $sSecond, 0,
(($phCommonCount->{$sCommon} == 1) ?
$oFormat->format($phCommonText->{$sCommon}) :
[scalar(@$paRet), $sFootNoteCount]
);
}
}

return $paRet;
}

sub format_footnotes
{
my ($phFormats, $paRow, $sWSName, $sOffset, $sColLetter) = @_;

die "format_footnotes() requires at least 2 parameters\n"
if (scalar(@_) < 2);
die "format_footnotes() column must be a letter\n"
if (defined($sColLetter) and $sColLetter !~ /[A-Z]+/i);
warn "format_footnotes() worksheet name should be defined. Assuming Sheet1\n"
if (not defined($sWSName));

$sWSName //= "Sheet1";
$sOffset //= 0;
$sColLetter //= 'A';

my @aRet;
foreach my $sPart (@$paRow)
{
if (ref($sPart) eq 'ARRAY')
{
push @aRet, (
internal_link($sWSName, $sColLetter, ($sPart->[0] + $sOffset)),
$phFormats->{footnote},
$sPart->[1],
);
}
else
{
push @aRet, ($phFormats->{default}, $sPart);
}
}

return @aRet;
}

sub internal_link
{
my ($sWSName, $sCol, $sRow) = @_;
warn "Sheet name should be defined. Assuming Sheet1.\n"
if (not defined($sWSName));
$sWSName //= "Sheet1";

my $sPos = int_to_letter($sCol, $sRow);

return 'internal:\'' . $sWSName . '\'!' . $sPos;
}

sub int_to_letter
{
my ($sCol, $sRow) = @_;

$sCol = uc($sCol) // 'A';
$sRow //= '';
die "Invalid space $sCol\n" if ($sCol =~ /[0-9]+[A-Z]+/);
return $sCol if ($sCol =~ /^[A-Z]+[0-9]+$/);

my $sRet;
if ($sCol =~ /[0-9]+/)
{
my @aAlphabet = ('', "A" .. "Z");
$sRet = 'A' x int($sCol);
$sRet .= $aAlphabet[26 % $sCol];
}
else
{
$sRet = $sCol . $sRow;
}

return $sRet;
}


### OUTPUT
Use of uninitialized value in addition (+) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line[244/1911]
Use of uninitialized value in addition (+) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2331.
Use of uninitialized value in addition (+) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2331.
Use of uninitialized value in addition (+) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2331.
Use of uninitialized value in addition (+) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2331.
Use of uninitialized value in addition (+) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2331.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $str in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Package/XMLwrit
er.pm line 482.
Use of uninitialized value $data in concatenation (.) or string at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Packag
e/XMLwriter.pm line 235.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $str in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Package/XMLwrit
er.pm line 482.
Use of uninitialized value $data in concatenation (.) or string at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Packag
e/XMLwriter.pm line 235.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $str in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Package/XMLwrit
er.pm line 482.
Use of uninitialized value $data in concatenation (.) or string at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Packag
e/XMLwriter.pm line 235.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $str in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Package/XMLwrit
er.pm line 482.
Use of uninitialized value $data in concatenation (.) or string at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Packag
e/XMLwriter.pm line 235.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $str in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Package/XMLwrit
er.pm line 482.
Use of uninitialized value $data in concatenation (.) or string at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Packag
e/XMLwriter.pm line 235.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $token in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Worksheet.pm
line 2368.
Use of uninitialized value $str in pattern match (m//) at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Package/XMLwrit
er.pm line 482.
Use of uninitialized value $data in concatenation (.) or string at
/home/swilson/perl5/perlbrew/perls/perl-5.20.0/lib/site_perl/5.20.0/Excel/Writer/XLSX/Packag
e/XMLwriter.pm line 235.

[shawn wilson]

On Thu, Apr 9, 2015 at 2:10 PM, shawn wilson <ag4v...@gmail.com> wrote:
> I am trying to get "footnotes" - basically just text below that is
> linked to and am getting tons of errors from Excel::Writer::XLSX. I
> assume it's something to do with returning a pre-compiling a formatted
> list but idk (and maybe there's a better way to do this?):
>

And a full (not) working example (same error message so I didn't include it):
#!/usr/bin/env perl

use strict;
use warnings;
use utf8;

use 5.010;

use List::MoreUtils qw(uniq);
use Text::Format;
use Excel::Writer::XLSX;

my $phCommonText = {
'common' => "this should be a footnote",
};

make_spreadsheet(
't.xlsx',
{
'foo' => {
'_desc' => ["foo desc", "common"],
'1.2.3.4' => 1,
},
'bar' => {
'_desc' => ["bar desc", "common"],
'5.6.7.8' => 1,
},
'baz' => {
'_desc' => ["baz desc"],
'9.0.1.2' => 1,
},
},

''
);

sub make_spreadsheet
{
my ($sOut, $phData, $phHostMap) = @_;
my $oWB = Excel::Writer::XLSX->new($sOut);

my $paIssues = sorted_issues($phData);
my $paHosts = sorted_hosts($phData);

# Define formats
my $phFormats;
$phFormats->{default} = $oWB->add_format();
$phFormats->{bold} = $oWB->add_format(bold => 1);
$phFormats->{center} = $oWB->add_format(align => 1, valign => 1);
$phFormats->{gray_bg} = $oWB->add_format(bg_color => 23);
$phFormats->{link} = $oWB->add_format(color => 'blue', underline => 1);
$phFormats->{footnote} =
$oWB->add_format(color => 'blue', underline => 1, font_script => 1);
$phFormats->{main} = $oWB->add_format()->set_align(vjustify => 1);

my @aParams = ($oWB, $phData, $paIssues, $paHosts, $phHostMap, $phFormats);
wb_add_main(@aParams);
}

sub sorted_hosts
{
my ($phData) = @_;

# Get a sorted list of hosts
return [
map { $_->[0] }
sort { $a->[1] <=> $b->[1] }
map {
my ($x,$y)=(0,$_);
$x=$_ + $x * 256 for split(/\./, $y);
[$y,$x]
}
grep {not /^_desc$/}
uniq
map {keys %{$phData->{$_}}}
keys %$phData
];
}

sub sorted_issues
{
my ($phData) = @_;

return [sort {$a cmp $b} keys %$phData];
}

[jmcnamara]

Hi Shawn,

The issue is with the format_footnote() function. It isn't returning a list of Format/Text pairs as required by write_rich_string().

You should debug that function and ensure it returns the correct output.

John

[shawn wilson]

Ok, I guess you're pointing at this line?

push @aRet, (
internal_link($sWSName, $sColLetter, ($sPart->[0] + $sOffset)),
$phFormats->{footnote},
$sPart->[1],

So, what I want, is to be able to do this:
$worksheet->write_url( 1, 0, 'http://www.perl.com/', $format, 'Perl' );

In the middle of a row - how do I get that metadata ("Perl" in this
example) there with only two parameters?

[jmcnamara]

On Friday, 10 April 2015 10:53:21 UTC+1, shawn wilson wrote:

> The issue is with the format_footnote() function. It isn't returning a list
> of Format/Text pairs as required by write_rich_string().
>
> You should debug that function and ensure it returns the correct output.
>

Ok, I guess you're pointing at this line?

No. I didn't drill down that far. I saw that the error occurs here:

$oWS->write_rich_string($sCount, $sCol++,
    format_footnotes($phFormats, [@{$paRow}[1 .. $#$paDesc]], 'Main', 1, 'A'));

And that the reason is that format_footnotes() isn't returning valid formatting for write_rich_string().

So, what I want, is to be able to do this:
$worksheet->write_url( 1, 0, 'http://www.perl.com/', $format, 'Perl' );

In the middle of a row - how do I get that metadata ("Perl" in this
example) there with only two parameters?

So you want to write a rich string as a hyperlink, is that right?

The way to do that is to write a url to the cell first and then overwrite the string part with a rich string. Something like this.

use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new('rich_strings.xlsx');
my $bold     = $workbook->add_format( bold => 1 );
my $italic   = $workbook->add_format( italic => 1 );

$worksheet->write( 'A1', 'http://www.perl.com/' );

$worksheet->write_rich_string( 'A1',
                   'This is ', $bold, 'bold',
                   ' and this is ', $italic, 'italic' );

John

[shawn wilson]

Ok, so I can't have plain text and a link in the same cell?

> --
> You received this message because you are subscribed to the Google Groups
> "Spreadsheet::WriteExcel" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to spreadsheet-write...@googlegroups.com.
> To post to this group, send email to
> spreadsheet...@googlegroups.com.
> Visit this group at http://groups.google.com/group/spreadsheet-writeexcel.
> For more options, visit https://groups.google.com/d/optout.

[jmcnamara]

On Friday, 10 April 2015 12:35:20 UTC+1, shawn wilson wrote:

Ok, so I can't have plain text and a link in the same cell?

Correct. Excel doesn't allow a link in the middle of a string like this:

    "This is the url www.perl.com that the spider found"

You can simulate it with a rich string (similar to my example) by underlining the link part with a format but the entire cell will be a link not just the url part of the string. Also Excel only allows one link per cell.

John