1package Spreadsheet::ParseExcel::FmtDefault; 2 3############################################################################### 4# 5# Spreadsheet::ParseExcel::FmtDefault - A class for Cell formats. 6# 7# Used in conjunction with Spreadsheet::ParseExcel. 8# 9# Copyright (c) 2014 Douglas Wilson 10# Copyright (c) 2009-2013 John McNamara 11# Copyright (c) 2006-2008 Gabor Szabo 12# Copyright (c) 2000-2006 Kawai Takanori 13# 14# perltidy with standard settings. 15# 16# Documentation after __END__ 17# 18 19use strict; 20use warnings; 21 22use Spreadsheet::ParseExcel::Utility qw(ExcelFmt); 23our $VERSION = '0.65'; 24 25my %hFmtDefault = ( 26 0x00 => 'General', 27 0x01 => '0', 28 0x02 => '0.00', 29 0x03 => '#,##0', 30 0x04 => '#,##0.00', 31 0x05 => '($#,##0_);($#,##0)', 32 0x06 => '($#,##0_);[Red]($#,##0)', 33 0x07 => '($#,##0.00_);($#,##0.00_)', 34 0x08 => '($#,##0.00_);[Red]($#,##0.00_)', 35 0x09 => '0%', 36 0x0A => '0.00%', 37 0x0B => '0.00E+00', 38 0x0C => '# ?/?', 39 0x0D => '# ??/??', 40 0x0E => 'yyyy-mm-dd', # Was 'm-d-yy', which is bad as system default 41 0x0F => 'd-mmm-yy', 42 0x10 => 'd-mmm', 43 0x11 => 'mmm-yy', 44 0x12 => 'h:mm AM/PM', 45 0x13 => 'h:mm:ss AM/PM', 46 0x14 => 'h:mm', 47 0x15 => 'h:mm:ss', 48 0x16 => 'm-d-yy h:mm', 49 50 #0x17-0x24 -- Differs in Natinal 51 0x25 => '(#,##0_);(#,##0)', 52 0x26 => '(#,##0_);[Red](#,##0)', 53 0x27 => '(#,##0.00);(#,##0.00)', 54 0x28 => '(#,##0.00);[Red](#,##0.00)', 55 0x29 => '_(*#,##0_);_(*(#,##0);_(*"-"_);_(@_)', 56 0x2A => '_($*#,##0_);_($*(#,##0);_(*"-"_);_(@_)', 57 0x2B => '_(*#,##0.00_);_(*(#,##0.00);_(*"-"??_);_(@_)', 58 0x2C => '_($*#,##0.00_);_($*(#,##0.00);_(*"-"??_);_(@_)', 59 0x2D => 'mm:ss', 60 0x2E => '[h]:mm:ss', 61 0x2F => 'mm:ss.0', 62 0x30 => '##0.0E+0', 63 0x31 => '@', 64); 65 66#------------------------------------------------------------------------------ 67# new (for Spreadsheet::ParseExcel::FmtDefault) 68#------------------------------------------------------------------------------ 69sub new { 70 my ( $sPkg, %hKey ) = @_; 71 my $oThis = {}; 72 bless $oThis; 73 return $oThis; 74} 75 76#------------------------------------------------------------------------------ 77# TextFmt (for Spreadsheet::ParseExcel::FmtDefault) 78#------------------------------------------------------------------------------ 79sub TextFmt { 80 my ( $oThis, $sTxt, $sCode ) = @_; 81 return $sTxt if ( ( !defined($sCode) ) || ( $sCode eq '_native_' ) ); 82 return pack( 'U*', unpack( 'n*', $sTxt ) ); 83} 84 85#------------------------------------------------------------------------------ 86# FmtStringDef (for Spreadsheet::ParseExcel::FmtDefault) 87#------------------------------------------------------------------------------ 88sub FmtStringDef { 89 my ( $oThis, $iFmtIdx, $oBook, $rhFmt ) = @_; 90 my $sFmtStr = $oBook->{FormatStr}->{$iFmtIdx}; 91 92 if ( !( defined($sFmtStr) ) && defined($rhFmt) ) { 93 $sFmtStr = $rhFmt->{$iFmtIdx}; 94 } 95 $sFmtStr = $hFmtDefault{$iFmtIdx} unless ($sFmtStr); 96 return $sFmtStr; 97} 98 99#------------------------------------------------------------------------------ 100# FmtString (for Spreadsheet::ParseExcel::FmtDefault) 101#------------------------------------------------------------------------------ 102sub FmtString { 103 my ( $oThis, $oCell, $oBook ) = @_; 104 105 my $sFmtStr = 106 $oThis->FmtStringDef( $oBook->{Format}[ $oCell->{FormatNo} ]->{FmtIdx}, 107 $oBook ); 108 109 # Special case for cells that use Lotus123 style leading 110 # apostrophe to designate text formatting. 111 if ( $oBook->{Format}[ $oCell->{FormatNo} ]->{Key123} ) { 112 $sFmtStr = '@'; 113 } 114 115 unless ( defined($sFmtStr) ) { 116 if ( $oCell->{Type} eq 'Numeric' ) { 117 if ( int( $oCell->{Val} ) != $oCell->{Val} ) { 118 $sFmtStr = '0.00'; 119 } 120 else { 121 $sFmtStr = '0'; 122 } 123 } 124 elsif ( $oCell->{Type} eq 'Date' ) { 125 if ( int( $oCell->{Val} ) <= 0 ) { 126 $sFmtStr = 'h:mm:ss'; 127 } 128 else { 129 $sFmtStr = 'yyyy-mm-dd'; 130 } 131 } 132 else { 133 $sFmtStr = '@'; 134 } 135 } 136 return $sFmtStr; 137} 138 139#------------------------------------------------------------------------------ 140# ValFmt (for Spreadsheet::ParseExcel::FmtDefault) 141#------------------------------------------------------------------------------ 142sub ValFmt { 143 my ( $oThis, $oCell, $oBook ) = @_; 144 145 my ( $Dt, $iFmtIdx, $iNumeric, $Flg1904 ); 146 147 if ( $oCell->{Type} eq 'Text' ) { 148 $Dt = 149 ( ( defined $oCell->{Val} ) && ( $oCell->{Val} ne '' ) ) 150 ? $oThis->TextFmt( $oCell->{Val}, $oCell->{Code} ) 151 : ''; 152 153 return $Dt; 154 } 155 else { 156 $Dt = $oCell->{Val}; 157 $Flg1904 = $oBook->{Flg1904}; 158 my $sFmtStr = $oThis->FmtString( $oCell, $oBook ); 159 160 return ExcelFmt( $sFmtStr, $Dt, $Flg1904, $oCell->{Type} ); 161 } 162} 163 164#------------------------------------------------------------------------------ 165# ChkType (for Spreadsheet::ParseExcel::FmtDefault) 166#------------------------------------------------------------------------------ 167sub ChkType { 168 my ( $oPkg, $iNumeric, $iFmtIdx ) = @_; 169 if ($iNumeric) { 170 if ( ( ( $iFmtIdx >= 0x0E ) && ( $iFmtIdx <= 0x16 ) ) 171 || ( ( $iFmtIdx >= 0x2D ) && ( $iFmtIdx <= 0x2F ) ) ) 172 { 173 return "Date"; 174 } 175 else { 176 return "Numeric"; 177 } 178 } 179 else { 180 return "Text"; 181 } 182} 183 1841; 185 186__END__ 187 188=pod 189 190=head1 NAME 191 192Spreadsheet::ParseExcel::FmtDefault - A class for Cell formats. 193 194=head1 SYNOPSIS 195 196See the documentation for Spreadsheet::ParseExcel. 197 198=head1 DESCRIPTION 199 200This module is used in conjunction with Spreadsheet::ParseExcel. See the documentation for Spreadsheet::ParseExcel. 201 202=head1 AUTHOR 203 204Current maintainer 0.60+: Douglas Wilson dougw@cpan.org 205 206Maintainer 0.40-0.59: John McNamara jmcnamara@cpan.org 207 208Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org 209 210Original author: Kawai Takanori kwitknr@cpan.org 211 212=head1 COPYRIGHT 213 214Copyright (c) 2014 Douglas Wilson 215 216Copyright (c) 2009-2013 John McNamara 217 218Copyright (c) 2006-2008 Gabor Szabo 219 220Copyright (c) 2000-2006 Kawai Takanori 221 222All rights reserved. 223 224You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file. 225 226=cut 227