rjw | 1f88458 | 2022-01-06 17:20:42 +0800 | [diff] [blame^] | 1 | package 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 | |
| 18 | use strict; |
| 19 | use warnings; |
| 20 | |
| 21 | our $VERSION = '0.59'; |
| 22 | |
| 23 | ############################################################################### |
| 24 | # |
| 25 | # new() |
| 26 | # |
| 27 | # Constructor. |
| 28 | # |
| 29 | sub 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 | # |
| 42 | sub 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 | # |
| 55 | sub 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 | # |
| 68 | sub 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 | # |
| 81 | sub 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 | # |
| 94 | sub 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 | # |
| 120 | sub 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 | # |
| 134 | sub 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 | |
| 150 | 1; |
| 151 | |
| 152 | __END__ |
| 153 | |
| 154 | =pod |
| 155 | |
| 156 | =head1 NAME |
| 157 | |
| 158 | Spreadsheet::ParseExcel::Cell - A class for Cell data and formatting. |
| 159 | |
| 160 | =head1 SYNOPSIS |
| 161 | |
| 162 | See the documentation for Spreadsheet::ParseExcel. |
| 163 | |
| 164 | =head1 DESCRIPTION |
| 165 | |
| 166 | This module is used in conjunction with Spreadsheet::ParseExcel. See the documentation for Spreadsheet::ParseExcel. |
| 167 | |
| 168 | =head1 Methods |
| 169 | |
| 170 | The 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 | |
| 183 | The C<value()> method returns the formatted value of the cell. |
| 184 | |
| 185 | my $value = $cell->value(); |
| 186 | |
| 187 | Formatted 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 | |
| 189 | If 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 | |
| 191 | For a defined C<$cell> the C<value()> method will always return a value. |
| 192 | |
| 193 | In 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 | |
| 198 | The C<unformatted()> method returns the unformatted value of the cell. |
| 199 | |
| 200 | my $unformatted = $cell->unformatted(); |
| 201 | |
| 202 | Returns the cell value without a numeric format. See the C<value()> method above. |
| 203 | |
| 204 | |
| 205 | =head2 get_format() |
| 206 | |
| 207 | The C<get_format()> method returns the L<Spreadsheet::ParseExcel::Format> object for the cell. |
| 208 | |
| 209 | my $format = $cell->get_format(); |
| 210 | |
| 211 | If a user defined format hasn't been applied to the cell then the default cell format is returned. |
| 212 | |
| 213 | |
| 214 | =head2 type() |
| 215 | |
| 216 | The 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 | |
| 220 | See also L<Dates and Time in Excel>. |
| 221 | |
| 222 | |
| 223 | =head2 encoding() |
| 224 | |
| 225 | The C<encoding()> method returns the character encoding of the cell. |
| 226 | |
| 227 | my $encoding = $cell->encoding(); |
| 228 | |
| 229 | This 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 | |
| 231 | The 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 | |
| 248 | The C<is_merged()> method returns true if the cell is merged. |
| 249 | |
| 250 | my $is_merged = $cell->is_merged(); |
| 251 | |
| 252 | Returns C<undef> if the property isn't set. |
| 253 | |
| 254 | |
| 255 | =head2 get_rich_text() |
| 256 | |
| 257 | The 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 | |
| 261 | The return value is an arrayref of arrayrefs in the form: |
| 262 | |
| 263 | [ |
| 264 | [ $start_position, $font_object ], |
| 265 | ..., |
| 266 | ] |
| 267 | |
| 268 | Returns undef if the property isn't set. |
| 269 | |
| 270 | |
| 271 | =head1 Dates and Time in Excel |
| 272 | |
| 273 | Dates 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 | |
| 275 | The integer part of the number stores the number of days since the epoch and the fractional part stores the percentage of the day. |
| 276 | |
| 277 | A 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 | |
| 289 | The 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 | |
| 291 | For 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 | |
| 296 | Maintainer 0.40+: John McNamara jmcnamara@cpan.org |
| 297 | |
| 298 | Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org |
| 299 | |
| 300 | Original author: Kawai Takanori kwitknr@cpan.org |
| 301 | |
| 302 | =head1 COPYRIGHT |
| 303 | |
| 304 | Copyright (c) 2009-2010 John McNamara |
| 305 | |
| 306 | Copyright (c) 2006-2008 Gabor Szabo |
| 307 | |
| 308 | Copyright (c) 2000-2006 Kawai Takanori |
| 309 | |
| 310 | All rights reserved. |
| 311 | |
| 312 | You 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 |