1Spreadsheet::ParseExcelU:s:eSravCeoPnatrrsiebru(t3e)d PeSrplreDaodcsuhmeeentt:a:tPiaornseExcel::SaveParser(3)
2
6 Spreadsheet::ParseExcel::SaveParser - Rewrite an existing Excel file.
7
9 Say we start with an Excel file that looks like this:
10 -----------------------------------------------------
12 | | A | B | C |
13 -----------------------------------------------------
14 | 1 | Hello | ... | ... | ...
15 | 2 | World | ... | ... | ...
16 | 3 | *Bold text* | ... | ... | ...
17 | 4 | ... | ... | ... | ...
18 | 5 | ... | ... | ... | ...
19 Then we process it with the following program:
21 #!/usr/bin/perl
23 use strict;
25 use warnings;
26 use Spreadsheet::ParseExcel;
28 use Spreadsheet::ParseExcel::SaveParser;
29 # Open an existing file with SaveParser
32 my $parser = Spreadsheet::ParseExcel::SaveParser->new();
33 my $template = $parser->Parse('template.xls');
34 # Get the first worksheet.
37 my $worksheet = $template->worksheet(0);
38 my $row = 0;
39 my $col = 0;
40 # Overwrite the string in cell A1
43 $worksheet->AddCell( $row, $col, 'New string' );
44 # Add a new string in cell B1
47 $worksheet->AddCell( $row, $col + 1, 'Newer' );
48 # 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 $worksheet->AddCell( $row, $col + 2, 'Newest', $format_number );
55 # Write over the existing file or write a new file.
58 $template->SaveAs('newfile.xls');
59 We should now have an Excel file that looks like this:
61 -----------------------------------------------------
63 | | A | B | C |
64 -----------------------------------------------------
65 | 1 | New string | Newer | *Newest* | ...
66 | 2 | World | ... | ... | ...
67 | 3 | *Bold text* | ... | ... | ...
68 | 4 | ... | ... | ... | ...
69 | 5 | ... | ... | ... | ...
70
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
78 new()
79 $parse = new Spreadsheet::ParseExcel::SaveParser();
80 Constructor.
82 Parse()
84 $workbook = $parse->Parse($sFileName);
85 $workbook = $parse->Parse($sFileName , $formatter);
87 Returns a "Workbook" object. If an error occurs, returns undef.
89 The optional $formatter is a Formatter Class to format the value of
91 cells.
92
94 The "Parse()" method returns a
95 "Spreadsheet::ParseExcel::SaveParser::Workbook" object.
96 This is a subclass of the Spreadsheet::ParseExcel::Workbook and has the
98 following methods:
99 worksheets()
101 Returns an array of "Worksheet" objects. This was most commonly used to
102 iterate over the worksheets in a workbook:
103 for my $worksheet ( $workbook->worksheets() ) {
105 ...
106 }
107 worksheet()
109 The "worksheet()" method returns a single "Worksheet" object using
110 either its name or index:
111 $worksheet = $workbook->worksheet('Sheet1');
113 $worksheet = $workbook->worksheet(0);
114 Returns "undef" if the sheet name or index doesn't exist.
116 AddWorksheet()
118 $workbook = $workbook->AddWorksheet($name, %properties);
119 Create a new Worksheet object of type
121 "Spreadsheet::ParseExcel::Worksheet".
122 The %properties hash contains the properties of new Worksheet.
124 AddFont
126 $workbook = $workbook->AddFont(%properties);
127 Create new Font object of type "Spreadsheet::ParseExcel::Font".
129 The %properties hash contains the properties of new Font.
131 AddFormat
133 $workbook = $workbook->AddFormat(%properties);
134 The %properties hash contains the properties of new Font.
136
138 Spreadsheet::ParseExcel::SaveParser::Worksheet
139 Worksheet is a subclass of Spreadsheet::ParseExcel::Worksheet. And has
141 these methods :
142 The "Worksbook::worksheet()" method returns a
144 "Spreadsheet::ParseExcel::SaveParser::Worksheet" object.
145 This is a subclass of the Spreadsheet::ParseExcel::Worksheet and has
147 the following methods:
148
150 $workbook = $worksheet->AddCell($row, $col, $value, $format [$encoding]);
151 Create new Cell object of type "Spreadsheet::ParseExcel::Cell".
153 The $format parameter is the format number rather than a full format
155 object.
156 To specify just same as another cell, you can set it like below:
158 $row = 0;
160 $col = 0;
161 $worksheet = $template->worksheet(0);
162 $cell = $worksheet->get_cell( $row, $col );
163 $format_number = $cell->{FormatNo};
164 $worksheet->AddCell($row +1, $coll, 'New data', $format_number);
166
168 Please note that this module is currently (versions 0.50-0.60)
169 undergoing a major restructuring and rewriting.
170
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 Only last print area will remain. (Others will be removed)
178
180 Current maintainer 0.60+: Douglas Wilson dougw@cpan.org
181 Maintainer 0.40-0.59: John McNamara jmcnamara@cpan.org
183 Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org
185 Original author: Kawai Takanori kwitknr@cpan.org
187
189 Copyright (c) 2014 Douglas Wilson
190 Copyright (c) 2009-2013 John McNamara
192 Copyright (c) 2006-2008 Gabor Szabo
194 Copyright (c) 2000-2002 Kawai Takanori and Nippon-RAD Co. OP Division
196 All rights reserved.
198 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.
201perl v5.32.1 2021-01S-p2r7eadsheet::ParseExcel::SaveParser(3)