1Spreadsheet::ParseExcelU:s:eSravCeoPnatrrsiebru(t3e)d PeSrplreDaodcsuhmeeentt:a:tPiaornseExcel::SaveParser(3)
2
3
4

NAME

6       Spreadsheet::ParseExcel::SaveParser - Rewrite an existing Excel file.
7

SYNOPSIS

9       Say we start with an Excel file that looks like this:
10
11           -----------------------------------------------------
12          |   |      A      |      B      |      C      |
13           -----------------------------------------------------
14          | 1 | Hello       | ...         | ...         |  ...
15          | 2 | World       | ...         | ...         |  ...
16          | 3 | *Bold text* | ...         | ...         |  ...
17          | 4 | ...         | ...         | ...         |  ...
18          | 5 | ...         | ...         | ...         |  ...
19
20       Then we process it with the following program:
21
22           #!/usr/bin/perl
23
24           use strict;
25           use warnings;
26
27           use Spreadsheet::ParseExcel;
28           use Spreadsheet::ParseExcel::SaveParser;
29
30
31           # Open an existing file with SaveParser
32           my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
33           my $template = $parser->Parse('template.xls');
34
35
36           # Get the first worksheet.
37           my $worksheet = $template->worksheet(0);
38           my $row  = 0;
39           my $col  = 0;
40
41
42           # Overwrite the string in cell A1
43           $worksheet->AddCell( $row, $col, 'New string' );
44
45
46           # Add a new string in cell B1
47           $worksheet->AddCell( $row, $col + 1, 'Newer' );
48
49
50           # Add a new string in cell C1 with the format from cell A3.
51           my $cell = $worksheet->get_cell( $row + 2, $col );
52           my $format_number = $cell->{FormatNo};
53
54           $worksheet->AddCell( $row, $col + 2, 'Newest', $format_number );
55
56
57           # Write over the existing file or write a new file.
58           $template->SaveAs('newfile.xls');
59
60       We should now have an Excel file that looks like this:
61
62           -----------------------------------------------------
63          |   |      A      |      B      |      C      |
64           -----------------------------------------------------
65          | 1 | New string  | Newer       | *Newest*    |  ...
66          | 2 | World       | ...         | ...         |  ...
67          | 3 | *Bold text* | ...         | ...         |  ...
68          | 4 | ...         | ...         | ...         |  ...
69          | 5 | ...         | ...         | ...         |  ...
70

DESCRIPTION

72       The "Spreadsheet::ParseExcel::SaveParser" module rewrite an existing
73       Excel file by reading it with "Spreadsheet::ParseExcel" and rewriting
74       it with "Spreadsheet::WriteExcel".
75

METHODS

Parser

78   new()
79           $parse = new Spreadsheet::ParseExcel::SaveParser();
80
81       Constructor.
82
83   Parse()
84           $workbook = $parse->Parse($sFileName);
85
86           $workbook = $parse->Parse($sFileName , $formatter);
87
88       Returns a "Workbook" object. If an error occurs, returns undef.
89
90       The optional $formatter is a Formatter Class to format the value of
91       cells.
92

Workbook

94       The Parse() method returns a
95       "Spreadsheet::ParseExcel::SaveParser::Workbook" object.
96
97       This is a subclass of the Spreadsheet::ParseExcel::Workbook and has the
98       following methods:
99
100   worksheets()
101       Returns an array of "Worksheet" objects. This was most commonly used to
102       iterate over the worksheets in a workbook:
103
104           for my $worksheet ( $workbook->worksheets() ) {
105               ...
106           }
107
108   worksheet()
109       The worksheet() method returns a single "Worksheet" object using either
110       its name or index:
111
112           $worksheet = $workbook->worksheet('Sheet1');
113           $worksheet = $workbook->worksheet(0);
114
115       Returns "undef" if the sheet name or index doesn't exist.
116
117   AddWorksheet()
118           $workbook = $workbook->AddWorksheet($name, %properties);
119
120       Create a new Worksheet object of type
121       "Spreadsheet::ParseExcel::Worksheet".
122
123       The %properties hash contains the properties of new Worksheet.
124
125   AddFont
126           $workbook = $workbook->AddFont(%properties);
127
128       Create new Font object of type "Spreadsheet::ParseExcel::Font".
129
130       The %properties hash contains the properties of new Font.
131
132   AddFormat
133           $workbook = $workbook->AddFormat(%properties);
134
135       The %properties hash contains the properties of new Font.
136

Worksheet

138       Spreadsheet::ParseExcel::SaveParser::Worksheet
139
140       Worksheet is a subclass of Spreadsheet::ParseExcel::Worksheet.  And has
141       these methods :
142
143       The Worksbook::worksheet() method returns a
144       "Spreadsheet::ParseExcel::SaveParser::Worksheet" object.
145
146       This is a subclass of the Spreadsheet::ParseExcel::Worksheet and has
147       the following methods:
148

AddCell

150           $workbook = $worksheet->AddCell($row, $col, $value, $format [$encoding]);
151
152       Create new Cell object of type "Spreadsheet::ParseExcel::Cell".
153
154       The $format parameter is the format number rather than a full format
155       object.
156
157       To specify just same as another cell, you can set it like below:
158
159           $row            = 0;
160           $col            = 0;
161           $worksheet      = $template->worksheet(0);
162           $cell           = $worksheet->get_cell( $row, $col );
163           $format_number  = $cell->{FormatNo};
164
165           $worksheet->AddCell($row +1, $coll, 'New data', $format_number);
166

TODO

168       Please note that this module is currently (versions 0.50-0.60)
169       undergoing a major restructuring and rewriting.
170

Known Problems

172       You can only rewrite the features that Spreadsheet::WriteExcel supports
173       so macros, graphs and some other features in the original Excel file
174       will be lost.  Also, formulas aren't rewritten, only the result of a
175       formula is written.
176
177       Only last print area will remain. (Others will be removed)
178

AUTHOR

180       Current maintainer 0.60+: Douglas Wilson dougw@cpan.org
181
182       Maintainer 0.40-0.59: John McNamara jmcnamara@cpan.org
183
184       Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org
185
186       Original author: Kawai Takanori kwitknr@cpan.org
187
189       Copyright (c) 2014 Douglas Wilson
190
191       Copyright (c) 2009-2013 John McNamara
192
193       Copyright (c) 2006-2008 Gabor Szabo
194
195       Copyright (c) 2000-2002 Kawai Takanori and Nippon-RAD Co. OP Division
196
197       All rights reserved.
198
199       You may distribute under the terms of either the GNU General Public
200       License or the Artistic License, as specified in the Perl README file.
201
202
203
204perl v5.36.0                      2023-01S-p2r0eadsheet::ParseExcel::SaveParser(3)
Impressum