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

Well, I'm not a newbie in Perl, but sometimes I like to get things complicated ... Context : So I've been requested to produce configs based on a template from Excel files. Challenge accepted, I could read the template in a given Excel file, retrieve cell format , read the content and call a function which would search and replace given that % var % would match data in a data structure received by the program. Then, write a new Excel file with the result. Everybody is happy with that. Problem : Now they want to send me arrays of data, to create config lines such as ip address 10.10.10.10 255.255.255.255 So I thought I could use the looping system in TT , such as 
[% FOREACH item IN secondaries %]
ip address [% item.ip %] [% item.mask %] secondary
[% END %]

[download]
I got it to work, but only if the 3 lines for the loop are in the same cell. Then it gets funky, and I think the problem is with encoding or something like that, where I get totally lost. I get : " blank ip address bla bla blank ip address bla bla blank " I'm totally clueless. In fact I adjusted encoding and binmode while creating the Template->new object and calling the process sub 
sub template_s_r {
 
 my ($lineref, $dataref) = @_;
 my $val;
 my $tt = Template->new({
        ENCODING        =>      'utf8'
 });
 $tt->process(\$lineref, $dataref, \$val, {binmode => ':utf8'}) or die
+ $$tt->error, "\n";
 return $val || 0;

}

[download]
and I found out I could add the "text wrap" format to the cell. Which I did. But the result is ... crappy ? And when I try to copy and paste the content of this cell it litterally outputs "ip address bla bla\nip address bla bla\n" (including the ") Can someone help me on this one please ? Cheers, Luc

Replies are listed 'Best First'.
Re: Using Template::Toolkit with Excel files
by iguanodon (Priest) on Nov 29, 2018 at 14:18 UTC
[reply]

      Well, it's funny 'cause after writing this post, I realized the benefits of writing down a problem

      a) My post is so unclear which means I don't understand totally what's going on
      b) It forced me to look again what's available out there. Maybe my nose was stuck in my problem.

      So I found Excel::Template and Excel::Template::Plus which allows to change the engine (TT for instance). I know why I overlooked Excel::Template, since at the beginning I thought it would be nice to allow the network guys to consult their template at any time (in the original Excel format) but now that I've played a lot with the Excel modules, and learned a bit more about it's behavior ... I dunno maybe I could create a nice display in a webpage from the xml structure used for the template.

[reply]
        So I found Excel::Template

        I thought you might head down this path and got to work trying to work up an example, but I have failed. The example I was shooting for was the one right on the cpan site: https://metacpan.org/pod/Excel::Template.

        I worked it up two different ways and get the same error, so I'm hoping that my failure is diagnostic for the many contributors to the monastery whose experience in this is greater than mine. So far, I only have 2 failures.

        At first, I thought my input file wasn't correct on the path, but I took the extra measure of stringifying the Path::Tiny values, with the attending certainty and liternalness of a string, and there is a file there with the xml example in it:

        Read more... (4 kB)

        Then I read further on the cpan site that I can get the data from __DATA__ with FILE => \*DATA . I give that a whirl:

        Read more... (6 kB)

        Same error:

        Not a GLOB reference at /usr/local/share/perl/5.26.1/Spreadsheet/Write
+Excel/OLEwriter.pm line 250, <INFILE> line 0.000000.
    (in cleanup) Not a GLOB reference at /usr/local/share/perl/5.26.1/
+Spreadsheet/WriteExcel/OLEwriter.pm line 418, <INFILE> line 0.000000.

        [download]

        I hope we can figure this out....

[reply]
[d/l]
[select]
Re: Using Template::Toolkit with Excel files
by 1nickt (Canon) on Nov 29, 2018 at 10:32 UTC

    Sorry, it's not clear what your actual question is. Please create and post an SSCCE demonstrating your problem.


    The way forward always starts with a minimal test.
[reply]
      So my actual question is : how should I deal with FOR loops in TT in an Excel file ? Even with other modules, then problem remains.
[reply]
      I know ... I'm sorry I realized afterwards how bad my writeup was.
[reply]

Back to Seekers of Perl Wisdom