1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
#!/usr/bin/perl
######################################################################
#
# This is an example of how to extend Spreadsheet::WriteExcel to allow
# you to write a large amount of data with a single method call.
#
# The new method is called writemany()
#
# Sam Kington, sam@illuminated.co.uk, November 2000
#
use strict;
use Spreadsheet::WriteExcel;
my $workbook=Spreadsheet::WriteExcel->new("writemany.xls");
my $worksheet=$workbook->addworksheet("Test");
my @contents;
my $bold=$workbook->addformat;
$bold->set_bold(1);
$bold->set_color("red");
$bold->set_font("Arial");
$bold->set_border(6);
$worksheet->set_column('A:P', 15);
foreach my $row (0..5) {
foreach my $col (0..5) {
$contents[$row][$col]="Row ".($row+1)." : Col ".("A".."Z")[$col];
$worksheet->write($row, $col, $contents[$row][$col], $bold);
}
}
$worksheet->writemany(7, 0, \@contents, {direction => "col"});
$worksheet->writemany(14,0, \@contents, {direction => "row", format => $bold});
$worksheet->writemany(21,0, [5..20], {direction => "col", format => $bold});
$workbook->close;
package Spreadsheet::WriteExcel::Worksheet;
######################################################################
#
# writemany ($row, $col, $ref, $options)
#
# Starting at row $row and column $col, calls either write_number() or
# write_string() on the contents of the arraryref $ref.
# $ref may be a simple arrayref (one-dimensional) or an arrayref of
# arrayrefs (two-dimensional)
# $row and $column are zero indexed.
# $options is a hashref with the following keys:
# direction: either "row", "col" depending on the type of $ref. This
# governs the direction values are inserted; it defaults
# to "row"
# If scalar @$ref==5 and direction eq "row", values will be
# inserted from ($row, $col) to ($row+4, $col)
# If scalar @$ref==10, scalar @{$ref->[0]}==4 and
# $direction eq "col", values will be inserted from
# ($row, $col) to ($row+3, $col+9)
# format: a format object (optional)
#
# Returns: array of return values of called subroutines, in the same
# order as $ref
sub writemany {
my ($self, $row, $col, $ref, $options)=@_;
# If this is an arrayref, go through it
if (ref($ref) eq "ARRAY") {
# Work out the direction we're going
my $direction=$options->{direction} || "row";
# Work out the converse direction
my $otherdirection={row=>"col",
col=>"row"}->{$direction};
# Cycle through
for (@$ref) {
$self->writemany($row, $col, $_,
{direction => $otherdirection,
format => $options->{format} || undef});
$direction eq "row" ? $row++ : $col++;
}
} else {
# It's a simple scalar value (or something that we don't
# handle), so pass it through to write
$self->write($row, $col, $ref, $options->{format});
}
}
|
