Excel-Template-XLSX

 view release on metacpan or  search on metacpan

Changes  view on Meta::CPAN

v1.1.1  10-Dec-2019
        added command to upload to CPAN
        
v1.1.0  10-Dec-2019
        Changed build.pl to require Excel::Writer::XLSX version 1.01
        
v1.0.9  07-Oct-2019
        Removed references to Mojo::Util which were used for development.
        
v1.0.8  24-Sep-2019
        Fixed bug in handling of array formulas
        
v1.0.7  02-Jun-2016
        Changed build script to require Excel::Writer::XLSX 0.90 
        
v1.0.6  02-Jun-2016
        Ticket #114543: Changed XLSX.pm and test scripts to use EWX public api $worksheet->get_name()
        issue arose from release of Excel::Writer::XLSX 0.90 

v1.0.5  01-Mar-2016
         Ticket #105683: Loosing format information in merged cells (rich strings)

MANIFEST  view on Meta::CPAN

t/01-object.t
t/02-workbook.t
t/03-worksheet.t
t/04-styles.t
t/05-shared_strings.t
t/06-cell.t
t/06-cell.xlsx
t/07-two_templates.t
t/08-open_output.t
t/09-template_callback.t
t/10-array_formulas.t
t/11-formulas.t
t/12-merged_ranges.t
t/99-todo.t
t/boilerplate.t
t/manifest.t
t/pod-coverage.t
t/pod.t

lib/Excel/Template/XLSX.pm  view on Meta::CPAN

      }

       }    # return hashref
}
###############################################################################
sub _parse_sheet_pass2 {

=head2 _parse_sheet_pass2

Parses cell contents (first by row, then by column).  Cells can contain
inline strings, string references, direct string values, formulas,
and hyperlinks.  Each cell may also contain formatting information.  
The format is in an index to formatting for borders, shading, alignment,
font, and number formats.

=cut

   my $self = shift;
   my ($sheet) = @_;

   return {

lib/Excel/Template/XLSX.pm  view on Meta::CPAN

            # In-line string (not seen in practice)
            elsif ( $t eq 'str' ) {
               $val = '=' . $cell->first_child('f')->text();
            }

            # Formulas
            elsif ( $t eq 'n' ) {
               if ( my $form_child = $cell->first_child('f') ) {
                  my $is_array = $form_child->att('t');
                  my $ref      = $form_child->att('ref');
                  my $formula  = $form_child->text() // q[="No Formula Found"];
                  if ($is_array and $ref) {
                     $sheet->write_array_formula( $ref, "=${formula}", $format, $val );
                  }
                  else {
                     if ( my $ref = $self->{MERGED_RANGES}{$sheet_idx}{$a1} ) {
                       $sheet->merge_range_type('formula', $ref, "=${formula}", $format, $val);
                       $self->{MERGED_RANGES}{$sheet_idx}{$a1} = 0; # Flag it as used.  Handle unused ranges at end
                     } else {
                        $sheet->write_formula( $a1, "=${formula}", $format, $val );
                     }
                  }
                  next;
               }

            }
            elsif ( $t eq 'b' ) {
               $val = $val ? "TRUE" : "FALSE";
            }
            elsif ( $t eq 'e' ) {

t/04-styles.t  view on Meta::CPAN

$sheet->write( 3, 0, 4050.1,     $num_format1 );

# Zip codes:
# string tested in shared strings
my $zip_format = $wbk->add_format( num_format => '00000' );
$sheet->write( 4, 0, '01111', $zip_format );
$sheet->keep_leading_zeros();    # Just converts number to a string
$sheet->write( 5, 0, '01111' );

my $prot_format = $wbk->add_format( 'hidden' => 1, 'locked' => 1 );
$sheet->write_formula( 6, 0, '="Locked" & " Hidden"', $prot_format );

$wbk->close();

# Get workbook content as a template
my ( $gfh, $gfilename ) = tempfile( SUFFIX => '.xlsx' );
my ( $self, $twbk ) = Excel::Template::XLSX->new( $gfilename, $efilename );
$self->parse_template();

# Get the format object referenced by cell 0,0
my $got_sheet = $twbk->get_worksheet_by_name('Sheet1');

t/05-shared_strings.t  view on Meta::CPAN

$center->set_align( 'vcenter' );
my $right = $wbk->add_format();
$right->set_align( 'right' );

my $red = $wbk->add_format();
$red->set_color('red');
my $wksheet = $wbk->add_worksheet();
$wksheet->write( 'A1', 'A1A1' );
$wksheet->write_rich_string( 'A2', 'Some ', $red, 'red ', 'text' );
$wksheet->merge_range_type('rich_string', 'A3:B4', 'Some ', $red, 'red ', 'text', $center);
$wksheet->merge_range_type('formula', 'A5:B7', q[=A1 & "B1B1"], $right);
$wbk->close();

# Get workbook content as a template
my ( $gfh, $gfilename ) = tempfile( SUFFIX => '.xlsx' );
my ( $self, $twbk ) = Excel::Template::XLSX->new( $gfilename, $efilename );
$self->parse_template();

is_deeply($twbk->{_sheetnames}->{"Sheet1"}->{_merge}, $wbk->{_sheetnames}->{"Sheet1"}->{_merge}, "Sheet Data Structure");

TODO: {

t/06-cell.t  view on Meta::CPAN

use Excel::Writer::XLSX;
use Excel::Template::XLSX;
use Test::More;
use File::Temp qw(tempfile);

# Can be set to 1 to see the created template and output file during debugging
$File::Temp::KEEP_ALL = 0;

###############################################################################

# Issue with using EWX to create array formulas
# Expected spreadsheet developed by hand.
my $efilename = q[./t/06-cell.xlsx];
TODO: {
  local $TODO = "Restore programmitically generated expected Excel worksheet";
  ok(0, 'Add ability to programmatically generate Excel Worksheet');
}
=for nothing
# Create expected workbook content
my ( $efh, $efilename ) = tempfile( SUFFIX => '.xlsx' );
my $wbk     = Excel::Writer::XLSX->new($efilename);

t/10-array_formulas.t  view on Meta::CPAN

my ( $self, $twbk ) = Excel::Template::XLSX->new( $gfilename, $efilename );
$self->parse_template();

# Get string table lookup by ID, not value
my $hstring = $twbk->{_str_table};
# warn "hstring is ", dumper( $hstring );


TODO: {
  local $TODO = <<"EOF";
Find a way to read output file, and see if array formula survives.  
Consider is_deeply on sheet XML.
For now, set KEEP_ALL to 1, and examine output file by hand.
EOF
  is( $hstring->{A1}, '0', "Sheet 1 A1 in row 0" );
  is( $hstring->{A2}, '1', "Sheet 1 A2 in row 1" );
}
# is( get_cell( $twbk, $hstring->{0}, 0, 0 ), 'A1', "Sheet 1 A1" );
# is( get_cell( $twbk, $hstring->{1}, 0, 1 ), 'A2', "Sheet 1 A2" );

my $sheet = $twbk->sheets(0);