3
###############################################################################
5
# Example of how use Spreadsheet::WriteExcel to generate Excel outlines and
9
# reverse('�'), April 2003, John McNamara, jmcnamara@cpan.org
15
use Spreadsheet::WriteExcel;
17
# Create a new workbook and add some worksheets
18
my $workbook = Spreadsheet::WriteExcel->new('outline.xls');
19
my $worksheet1 = $workbook->add_worksheet('Outlined Rows');
20
my $worksheet2 = $workbook->add_worksheet('Collapsed Rows');
21
my $worksheet3 = $workbook->add_worksheet('Outline Columns');
22
my $worksheet4 = $workbook->add_worksheet('Outline levels');
24
# Add a general format
25
my $bold = $workbook->add_format(bold => 1);
29
###############################################################################
31
# Example 1: Create a worksheet with outlined rows. It also includes SUBTOTAL()
32
# functions so that it looks like the type of automatic outlines that are
33
# generated when you use the Excel Data->SubTotals menu item.
37
# For outlines the important parameters are $hidden and $level. Rows with the
38
# same $level are grouped together. The group will be collapsed if $hidden is
39
# non-zero. $height and $XF are assigned default values if they are undef.
41
# The syntax is: set_row($row, $height, $XF, $hidden, $level)
43
$worksheet1->set_row(1, undef, undef, 0, 2);
44
$worksheet1->set_row(2, undef, undef, 0, 2);
45
$worksheet1->set_row(3, undef, undef, 0, 2);
46
$worksheet1->set_row(4, undef, undef, 0, 2);
47
$worksheet1->set_row(5, undef, undef, 0, 1);
49
$worksheet1->set_row(6, undef, undef, 0, 2);
50
$worksheet1->set_row(7, undef, undef, 0, 2);
51
$worksheet1->set_row(8, undef, undef, 0, 2);
52
$worksheet1->set_row(9, undef, undef, 0, 2);
53
$worksheet1->set_row(10, undef, undef, 0, 1);
56
# Add a column format for clarity
57
$worksheet1->set_column('A:A', 20);
59
# Add the data, labels and formulas
60
$worksheet1->write('A1', 'Region', $bold);
61
$worksheet1->write('A2', 'North');
62
$worksheet1->write('A3', 'North');
63
$worksheet1->write('A4', 'North');
64
$worksheet1->write('A5', 'North');
65
$worksheet1->write('A6', 'North Total', $bold);
67
$worksheet1->write('B1', 'Sales', $bold);
68
$worksheet1->write('B2', 1000);
69
$worksheet1->write('B3', 1200);
70
$worksheet1->write('B4', 900);
71
$worksheet1->write('B5', 1200);
72
$worksheet1->write('B6', '=SUBTOTAL(9,B2:B5)', $bold);
74
$worksheet1->write('A7', 'South');
75
$worksheet1->write('A8', 'South');
76
$worksheet1->write('A9', 'South');
77
$worksheet1->write('A10', 'South');
78
$worksheet1->write('A11', 'South Total', $bold);
80
$worksheet1->write('B7', 400);
81
$worksheet1->write('B8', 600);
82
$worksheet1->write('B9', 500);
83
$worksheet1->write('B10', 600);
84
$worksheet1->write('B11', '=SUBTOTAL(9,B7:B10)', $bold);
86
$worksheet1->write('A12', 'Grand Total', $bold);
87
$worksheet1->write('B12', '=SUBTOTAL(9,B2:B10)', $bold);
90
###############################################################################
92
# Example 2: Create a worksheet with outlined rows. This is the same as the
93
# previous example except that the rows are collapsed.
97
# The group will be collapsed if $hidden is non-zero.
98
# The syntax is: set_row($row, $height, $XF, $hidden, $level)
100
$worksheet2->set_row(1, undef, undef, 1, 2);
101
$worksheet2->set_row(2, undef, undef, 1, 2);
102
$worksheet2->set_row(3, undef, undef, 1, 2);
103
$worksheet2->set_row(4, undef, undef, 1, 2);
104
$worksheet2->set_row(5, undef, undef, 1, 1);
106
$worksheet2->set_row(6, undef, undef, 1, 2);
107
$worksheet2->set_row(7, undef, undef, 1, 2);
108
$worksheet2->set_row(8, undef, undef, 1, 2);
109
$worksheet2->set_row(9, undef, undef, 1, 2);
110
$worksheet2->set_row(10, undef, undef, 1, 1);
113
# Add a column format for clarity
114
$worksheet2->set_column('A:A', 20);
116
# Add the data, labels and formulas
117
$worksheet2->write('A1', 'Region', $bold);
118
$worksheet2->write('A2', 'North');
119
$worksheet2->write('A3', 'North');
120
$worksheet2->write('A4', 'North');
121
$worksheet2->write('A5', 'North');
122
$worksheet2->write('A6', 'North Total', $bold);
124
$worksheet2->write('B1', 'Sales', $bold);
125
$worksheet2->write('B2', 1000);
126
$worksheet2->write('B3', 1200);
127
$worksheet2->write('B4', 900);
128
$worksheet2->write('B5', 1200);
129
$worksheet2->write('B6', '=SUBTOTAL(9,B2:B5)', $bold);
131
$worksheet2->write('A7', 'South');
132
$worksheet2->write('A8', 'South');
133
$worksheet2->write('A9', 'South');
134
$worksheet2->write('A10', 'South');
135
$worksheet2->write('A11', 'South Total', $bold);
137
$worksheet2->write('B7', 400);
138
$worksheet2->write('B8', 600);
139
$worksheet2->write('B9', 500);
140
$worksheet2->write('B10', 600);
141
$worksheet2->write('B11', '=SUBTOTAL(9,B7:B10)', $bold);
143
$worksheet2->write('A12', 'Grand Total', $bold);
144
$worksheet2->write('B12', '=SUBTOTAL(9,B2:B10)', $bold);
148
###############################################################################
150
# Example 3: Create a worksheet with outlined columns.
153
['Month', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',' Total'],
154
['North', 50, 20, 15, 25, 65, 80, ,'=SUM(B2:G2)'],
155
['South', 10, 20, 30, 50, 50, 50, ,'=SUM(B3:G3)'],
156
['East', 45, 75, 50, 15, 75, 100, ,'=SUM(B4:G4)'],
157
['West', 15, 15, 55, 35, 20, 50, ,'=SUM(B5:G6)'],
160
# Add bold format to the first row
161
$worksheet3->set_row(0, undef, $bold);
163
# The syntax is: set_column($first, $last, $height, $XF, $hidden, $level)
164
$worksheet3->set_column('A:A', 10, $bold );
165
$worksheet3->set_column('B:G', 5, undef, 0, 1);
166
$worksheet3->set_column('H:H', 10);
168
# Write the data and a formula
169
$worksheet3->write_col('A1', $data);
170
$worksheet3->write('H6', '=SUM(H2:H5)', $bold);
174
###############################################################################
176
# Example 4: Show all possible outline levels.
178
my $levels = ["Level 1", "Level 2", "Level 3", "Level 4",
179
"Level 5", "Level 6", "Level 7", "Level 6",
180
"Level 5", "Level 4", "Level 3", "Level 2", "Level 1"];
183
$worksheet4->write_col('A1', $levels);
185
$worksheet4->set_row(0, undef, undef, undef, 1);
186
$worksheet4->set_row(1, undef, undef, undef, 2);
187
$worksheet4->set_row(2, undef, undef, undef, 3);
188
$worksheet4->set_row(3, undef, undef, undef, 4);
189
$worksheet4->set_row(4, undef, undef, undef, 5);
190
$worksheet4->set_row(5, undef, undef, undef, 6);
191
$worksheet4->set_row(6, undef, undef, undef, 7);
192
$worksheet4->set_row(7, undef, undef, undef, 6);
193
$worksheet4->set_row(8, undef, undef, undef, 5);
194
$worksheet4->set_row(9, undef, undef, undef, 4);
195
$worksheet4->set_row(10, undef, undef, undef, 3);
196
$worksheet4->set_row(11, undef, undef, undef, 2);
197
$worksheet4->set_row(12, undef, undef, undef, 1);