Data_validation in Spreadsheet::WriteExcel

sushi2k has asked for the wisdom of the Perl Monks concerning the following question:

Hy Perlmonks,

as usual I've got a little problem with Spreadsheet::WriteExcel ;-) More specific with data_validation.

I have three columns with data_validation fields, like this one:

        #column: rating 
            $worksheet[$counterWorkSheet]->data_validation('F'.$counte
+rRow,
            {
                validate => 'list',
                value    => ['good', 'sufficient', 'unsatisfied', 'cri
+tical'],
            });
[download]

On creation time of the xls the perl-script is parsing also an xml-file that specifies some formatting (like color, font and so on). So that a user hasn't to look up in the code if he want's to change such peanuts. But I want also to specify in this xml-file the values of a data_validation field.

My problem is now, that if I create a variable with the content that belongs to the data_validation field and put it as argument in value, it will appear as one value. It doesn't recognise the quotes (') I'm inserting in the $columnEvidence.

        my $columnEvidence;

        foreach my $e (@{$xmlData->{columnEvidence}})
        {
            $columnEvidence += "'$e',";         
        }

        #column: evidence    
    $worksheet[$counterWorkSheet]->data_validation('E'.$counterRow,
            {
                validate => 'list',
                value    => [ $columnEvidence ]
            });
[download]

Has someone an idea how I can fill the data_validation field dynamically with content from an array?

Comment on Data_validation in Spreadsheet::WriteExcel Select or Download Code

Replies are listed 'Best First'.
Re: Data_validation in Spreadsheet::WriteExcel by jmcnamara (Monsignor) on Aug 10, 2009 at 15:42 UTC
This isn't a Spreadsheet::WriteExcel problem as such. The `data_validation()` value parameter requires an array ref of values but you are passing an array ref with a single string value: Something like the following should work: `my @columnEvidence; foreach my $e (@{$xmlData->{columnEvidence}}) { push @columnEvidence, $e; } ... $worksheet[$counterWorkSheet]->data_validation('E'.$counterRow, { validate => 'list', value => [ @columnEvidence ], }); # Or: value => \@columnEvidence,` [download] Or skip the accumulation and just use the array ref directly: `value => $xmlData->{columnEvidence},` [download] -- John.	[reply] [d/l] [select]
Re: Data_validation in Spreadsheet::WriteExcel by biohisham (Priest) on Aug 06, 2009 at 14:18 UTC
I have had a great bit with Spreadsheet::WriteExcel, it has been fun to learn it and all most of the times,, but it got some drawbacks and rigidity, I won't speculate on your problem but some will advice you to learn Win32::OLE module, I think it holds much water. for Spreadsheet::WriteExcel I can ask you to check http://www.koders.com/info.aspx?c=ProjectInfo&pid=WZKBNF1X3ERMTDSA4BGQNFCQSF for examples that might conform to your specific question and the method in focus. Excellence is an Endeavor of Persistence. Chance Favors a Prepared Mind	[reply] [d/l]