view release on metacpan or  search on metacpan

examples/dynamic_arrays.pl  view on Meta::CPAN

$worksheet5->set_column_pixels('A:A', 100);
$worksheet5->set_column_pixels('D:D', 20);


#
# Example of using the XMATCH() function.
#
$worksheet6->write('D2', '=XMATCH(C2,A2:A6)');


# Write the data the function will work on.
$worksheet6->write('A1', 'Product', $header1);

$worksheet6->write('A2', 'Apple');
$worksheet6->write('A3', 'Grape');
$worksheet6->write('A4', 'Pear');
$worksheet6->write('A5', 'Banana');
$worksheet6->write('A6', 'Cherry');

$worksheet6->write('C1', 'Product' , $header2);
$worksheet6->write('D1', 'Position', $header2);
$worksheet6->write('C2', 'Grape');

$worksheet6->set_column_pixels('B:B', 20);


#
# Example of using the RANDARRAY() function.
#
$worksheet7->write('A1', '=RANDARRAY(5,3,1,100, TRUE)');


#
# Example of using the SEQUENCE() function.
#
$worksheet8->write('A1', '=SEQUENCE(4,5)');


#
# Example of using the Spill range operator.
#
$worksheet9->write('H2', '=ANCHORARRAY(F2)');

$worksheet9->write('J2', '=COUNTA(ANCHORARRAY(F2))');


# Write the data the to work on.
$worksheet9->write( 'F2', '=UNIQUE(B2:B17)');
$worksheet9->write('F1', 'Unique', $header2);
$worksheet9->write('H1', 'Spill', $header2);
$worksheet9->write('J1', 'Spill', $header2);

write_worksheet_data($worksheet9, $header1);
$worksheet9->set_column_pixels('E:E', 20);
$worksheet9->set_column_pixels('G:G', 20);
$worksheet9->set_column_pixels('I:I', 20);

#
# Example of using dynamic ranges with older Excel functions.
#
$worksheet10->write_dynamic_array_formula('B1:B3', '=LEN(A1:A3)');

# Write the data the to work on.
$worksheet10->write('A1', 'Foo');
$worksheet10->write('A2', 'Food');
$worksheet10->write('A3', 'Frood');


# Close the workbook.
$workbook->close();

# Utility function to write the data some of the functions work on.
sub write_worksheet_data {
    my $worksheet = shift;
    my $header    = shift;

    $worksheet->write('A1', 'Region',    $header);
    $worksheet->write('B1', 'Sales Rep', $header);
    $worksheet->write('C1', 'Product',   $header);
    $worksheet->write('D1', 'Units',     $header);

    my @data = (
        ['East',  'Tom',    'Apple',  6380],
        ['West',  'Fred',   'Grape',  5619],
        ['North', 'Amy',    'Pear',   4565],
        ['South', 'Sal',    'Banana', 5323],
        ['East',  'Fritz',  'Apple',  4394],
        ['West',  'Sravan', 'Grape',  7195],
        ['North', 'Xi',     'Pear',   5231],
        ['South', 'Hector', 'Banana', 2427],
        ['East',  'Tom',    'Banana', 4213],
        ['West',  'Fred',   'Pear',   3239],
        ['North', 'Amy',    'Grape',  6520],
        ['South', 'Sal',    'Apple',  1310],
        ['East',  'Fritz',  'Banana', 6274],
        ['West',  'Sravan', 'Pear',   4894],
        ['North', 'Xi',     'Grape',  7580],
        ['South', 'Hector', 'Apple',  9814],
   );

    my $row_num = 1;
    for my $row_data (@data) {
        $worksheet->write_row($row_num, 0, $row_data);
        $row_num++;
    }
}


__END__