blob: 25270754d4f32453bb0fb861f9c620b8c38da3f8 [file] [log] [blame]
rjw1f884582022-01-06 17:20:42 +08001package Spreadsheet::ParseExcel::Cell;
2
3###############################################################################
4#
5# Spreadsheet::ParseExcel::Cell - A class for Cell data and formatting.
6#
7# Used in conjunction with Spreadsheet::ParseExcel.
8#
9# Copyright (c) 2009 John McNamara
10# Copyright (c) 2006-2008 Gabor Szabo
11# Copyright (c) 2000-2006 Kawai Takanori
12#
13# perltidy with standard settings.
14#
15# Documentation after __END__
16#
17
18use strict;
19use warnings;
20
21our $VERSION = '0.59';
22
23###############################################################################
24#
25# new()
26#
27# Constructor.
28#
29sub new {
30 my ( $package, %properties ) = @_;
31 my $self = \%properties;
32
33 bless $self, $package;
34}
35
36###############################################################################
37#
38# value()
39#
40# Returns the formatted value of the cell.
41#
42sub value {
43
44 my $self = shift;
45
46 return $self->{_Value};
47}
48
49###############################################################################
50#
51# unformatted()
52#
53# Returns the unformatted value of the cell.
54#
55sub unformatted {
56
57 my $self = shift;
58
59 return $self->{Val};
60}
61
62###############################################################################
63#
64# get_format()
65#
66# Returns the Format object for the cell.
67#
68sub get_format {
69
70 my $self = shift;
71
72 return $self->{Format};
73}
74
75###############################################################################
76#
77# type()
78#
79# Returns the type of cell such as Text, Numeric or Date.
80#
81sub type {
82
83 my $self = shift;
84
85 return $self->{Type};
86}
87
88###############################################################################
89#
90# encoding()
91#
92# Returns the character encoding of the cell.
93#
94sub encoding {
95
96 my $self = shift;
97
98 if ( !defined $self->{Code} ) {
99 return 1;
100 }
101 elsif ( $self->{Code} eq 'ucs2' ) {
102 return 2;
103 }
104 elsif ( $self->{Code} eq '_native_' ) {
105 return 3;
106 }
107 else {
108 return 0;
109 }
110
111 return $self->{Code};
112}
113
114###############################################################################
115#
116# is_merged()
117#
118# Returns true if the cell is merged.
119#
120sub is_merged {
121
122 my $self = shift;
123
124 return $self->{Merged};
125}
126
127###############################################################################
128#
129# get_rich_text()
130#
131# Returns an array ref of font information about each string block in a "rich",
132# i.e. multi-format, string.
133#
134sub get_rich_text {
135
136 my $self = shift;
137
138 return $self->{Rich};
139}
140
141###############################################################################
142#
143# Mapping between legacy method names and new names.
144#
145{
146 no warnings; # Ignore warnings about variables used only once.
147 *Value = *value;
148}
149
1501;
151
152__END__
153
154=pod
155
156=head1 NAME
157
158Spreadsheet::ParseExcel::Cell - A class for Cell data and formatting.
159
160=head1 SYNOPSIS
161
162See the documentation for Spreadsheet::ParseExcel.
163
164=head1 DESCRIPTION
165
166This module is used in conjunction with Spreadsheet::ParseExcel. See the documentation for Spreadsheet::ParseExcel.
167
168=head1 Methods
169
170The following Cell methods are available:
171
172 $cell->value()
173 $cell->unformatted()
174 $cell->get_format()
175 $cell->type()
176 $cell->encoding()
177 $cell->is_merged()
178 $cell->get_rich_text()
179
180
181=head2 value()
182
183The C<value()> method returns the formatted value of the cell.
184
185 my $value = $cell->value();
186
187Formatted in this sense refers to the numeric format of the cell value. For example a number such as 40177 might be formatted as 40,117, 40117.000 or even as the date 2009/12/30.
188
189If the cell doesn't contain a numeric format then the formatted and unformatted cell values are the same, see the C<unformatted()> method below.
190
191For a defined C<$cell> the C<value()> method will always return a value.
192
193In the case of a cell with formatting but no numeric or string contents the method will return the empty string C<''>.
194
195
196=head2 unformatted()
197
198The C<unformatted()> method returns the unformatted value of the cell.
199
200 my $unformatted = $cell->unformatted();
201
202Returns the cell value without a numeric format. See the C<value()> method above.
203
204
205=head2 get_format()
206
207The C<get_format()> method returns the L<Spreadsheet::ParseExcel::Format> object for the cell.
208
209 my $format = $cell->get_format();
210
211If a user defined format hasn't been applied to the cell then the default cell format is returned.
212
213
214=head2 type()
215
216The C<type()> method returns the type of cell such as Text, Numeric or Date. If the type was detected as Numeric, and the Cell Format matches C<m{^[dmy][-\\/dmy]*$}i>, it will be treated as a Date type.
217
218 my $type = $cell->type();
219
220See also L<Dates and Time in Excel>.
221
222
223=head2 encoding()
224
225The C<encoding()> method returns the character encoding of the cell.
226
227 my $encoding = $cell->encoding();
228
229This method is only of interest to developers. In general Spreadsheet::ParseExcel will return all character strings in UTF-8 regardless of the encoding used by Excel.
230
231The C<encoding()> method returns one of the following values:
232
233=over
234
235=item * 0: Unknown format. This shouldn't happen. In the default case the format should be 1.
236
237=item * 1: 8bit ASCII or single byte UTF-16. This indicates that the characters are encoded in a single byte. In Excel 95 and earlier This usually meant ASCII or an international variant. In Excel 97 it refers to a compressed UTF-16 character string where all of the high order bytes are 0 and are omitted to save space.
238
239=item * 2: UTF-16BE.
240
241=item * 3: Native encoding. In Excel 95 and earlier this encoding was used to represent multi-byte character encodings such as SJIS.
242
243=back
244
245
246=head2 is_merged()
247
248The C<is_merged()> method returns true if the cell is merged.
249
250 my $is_merged = $cell->is_merged();
251
252Returns C<undef> if the property isn't set.
253
254
255=head2 get_rich_text()
256
257The C<get_rich_text()> method returns an array ref of font information about each string block in a "rich", i.e. multi-format, string.
258
259 my $rich_text = $cell->get_rich_text();
260
261The return value is an arrayref of arrayrefs in the form:
262
263 [
264 [ $start_position, $font_object ],
265 ...,
266 ]
267
268Returns undef if the property isn't set.
269
270
271=head1 Dates and Time in Excel
272
273Dates and times in Excel are represented by real numbers, for example "Jan 1 2001 12:30 PM" is represented by the number 36892.521.
274
275The integer part of the number stores the number of days since the epoch and the fractional part stores the percentage of the day.
276
277A date or time in Excel is just like any other number. The way in which it is displayed is controlled by the number format:
278
279 Number format $cell->value() $cell->unformatted()
280 ============= ============== ==============
281 'dd/mm/yy' '28/02/08' 39506.5
282 'mm/dd/yy' '02/28/08' 39506.5
283 'd-m-yyyy' '28-2-2008' 39506.5
284 'dd/mm/yy hh:mm' '28/02/08 12:00' 39506.5
285 'd mmm yyyy' '28 Feb 2008' 39506.5
286 'mmm d yyyy hh:mm AM/PM' 'Feb 28 2008 12:00 PM' 39506.5
287
288
289The L<Spreadsheet::ParseExcel::Utility> module contains a function called C<ExcelLocaltime> which will convert between an unformatted Excel date/time number and a C<localtime()> like array.
290
291For date conversions using the CPAN C<DateTime> framework see L<DateTime::Format::Excel> http://search.cpan.org/search?dist=DateTime-Format-Excel
292
293
294=head1 AUTHOR
295
296Maintainer 0.40+: John McNamara jmcnamara@cpan.org
297
298Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org
299
300Original author: Kawai Takanori kwitknr@cpan.org
301
302=head1 COPYRIGHT
303
304Copyright (c) 2009-2010 John McNamara
305
306Copyright (c) 2006-2008 Gabor Szabo
307
308Copyright (c) 2000-2006 Kawai Takanori
309
310All rights reserved.
311
312You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
313
314=cut