NAME
Spreadsheet::Reader::Format::FmtDefault - Default number and string formats
SYNOPSIS
#!/usr/bin/env perl
use Spreadsheet::Reader::Format::FmtDefault;
my $formatter = Spreadsheet::Reader::Format::FmtDefault->new(
target_encoding => 'latin1',
epoch_year => 1904,
);
my $excel_format_string = $formatter->get_defined_excel_format( 0x0E );
print $excel_format_string . "\n";
$excel_format_string = $formatter->get_defined_excel_format( '0x0E' );
print $excel_format_string . "\n";
$excel_format_string = $formatter->get_defined_excel_format( 14 );
print $excel_format_string . "\n";
$formatter->set_defined_excel_formats( '0x17' => 'MySpecialFormat' );#Won't really translate!
$excel_format_string = $formatter->get_defined_excel_format( 23 );
print $excel_format_string . "\n";
###########################
# SYNOPSIS Screen Output
# 01: yyyy-mm-dd
# 02: yyyy-mm-dd
# 03: yyyy-mm-dd
# 04: MySpecialFormat
###########################DESCRIPTION
This is the default localization class used by Spreadsheet::Reader::Format. It is separate from the other parts of the formatter class to isolate the basic elements of localization to allow for the least work swapping it out. This class can be configured or adjused without replacing it or you can use it as a template for a new localization. To use the class as it stands just adjust the attributes to change the outcome of the methods. If you re-write this class it is used as a base class and must provide the same methods.
This class provides two basic functionalities. First, it stores and can retreive defined excel format strings. Excel uses these (common) formats to assign conversions to various cells in the sheet rather than storing a conversion string. Specifically these are the conversions provided to Excel end users in the pull down menu if they do not want to write their own custom conversion strings. This specific class represents the standard set of parsing strings localized for the United States found in Excel. There is one exception where position 14 (0x0E) is different than the Excel implementation since the Excel setting for that position breaks so many database data types. Where excel users have written their own custom conversion definition strings they are stored in the Styles file of the zipped archive. These strings are implemented by a parsing engine to convert raw values to formatted values. The rules for these conversions are layed out in the Excel documentation . The default implementation of these rules is done in Spreadsheet::Reader::Format::ParseExcelFormatStrings. The second functionality is string decoding. It is assumed that any file encoding is handled by the Excel file reader. However, once the file has been read into memory you may wish to decode it to some specific output format. The attribute target_encoding and the method change_output_encoding use Encode to do this.
For an explanation of functionality for a fully built Formatter class see the documentation for Spreadsheet::Reader::Format.
Attributes
Data passed to new when creating an instance containing this class. For modification of these attributes see the listed 'attribute methods' and Methods. For more information on attributes see Moose::Manual::Attributes.
excel_region
Definition: This records the target region of this localization role (Not the region of the Excel workbook being parsed). It's mostly a reference value.
Default: en = english
Attribute required: no
attribute methods Methods provided to adjust this attribute
get_excel_region
Definition: returns the value of the attribute (en)
set_excel_region( $region )
Definition: sets the value of the attribute.
target_encoding
Definition: This is the target output encoding. If it is not defined the string transformation step change_output_encoding becomes a passthrough. When the value is loaded it is used as a 'decode' target by Encode to transform the internally (unicode) stored perl string to some target 'output' formatting.
Attribute required: no
Default: none
Range: Any encoding recognized by Encode (No type certification is done)
attribute methods Methods provided to adjust this attribute
set_target_encoding( $encoding )
Definition: This should be recognized by "Listing available encodings" in Encode no testing of this compatability is done
get_target_encoding
Definition: Returns the currently set attribute value
has_target_encoding
Definition: Returns positive if the target_encoding has been set
defined_excel_translations
Definition: In Excel part of localization is the way numbers are displayed. Excel manages that with a default list of format strings that make the numbers appear in a familiar way. These are the choices provided in the pull down menu for formats if you did not want to write your own custom format string. This is where you store that list for this package. In this case the numbers are stored as hash key => value pairs where the keys are array positions (written in hex) and the values are the Excel readable format strings (definitions). Beware that if you change the list your parser may break if you don't supply replacements for all the values in the default list. If you just want to replace some of the values use the method set_defined_excel_formats.
Attribute required: yes
Default:
{
0x00 => 'General',
0x01 => '0',
0x02 => '0.00',
0x03 => '#,##0',
0x04 => '#,##0.00',
0x05 => '$#,##0_);($#,##0)',
0x06 => '$#,##0_);[Red]($#,##0)',
0x07 => '$#,##0.00_);($#,##0.00)',
0x08 => '$#,##0.00_);[Red]($#,##0.00)',
0x09 => '0%',
0x0A => '0.00%',
0x0B => '0.00E+00',
0x0C => '# ?/?',
0x0D => '# ??/??',
0x0E => 'yyyy-mm-dd', # Was 'm-d-yy', which is bad as system default
0x0F => 'd-mmm-yy',
0x10 => 'd-mmm',
0x11 => 'mmm-yy',
0x12 => 'h:mm AM/PM',
0x13 => 'h:mm:ss AM/PM',
0x14 => 'h:mm',
0x15 => 'h:mm:ss',
0x16 => 'm-d-yy h:mm',
0x1F => '#,##0_);(#,##0)',
0x20 => '#,##0_);[Red](#,##0)',
0x21 => '#,##0.00_);(#,##0.00)',
0x22 => '#,##0.00_);[Red](#,##0.00)',
0x23 => '_(*#,##0_);_(*(#,##0);_(*"-"_);_(@_)',
0x24 => '_($*#,##0_);_($*(#,##0);_($*"-"_);_(@_)',
0x25 => '_(*#,##0.00_);_(*(#,##0.00);_(*"-"??_);_(@_)',
0x26 => '_($*#,##0.00_);_($*(#,##0.00);_($*"-"??_);_(@_)',
0x27 => 'mm:ss',
0x28 => '[h]:mm:ss',
0x29 => 'mm:ss.0',
0x2A => '##0.0E+0',
0x2B => '@',
0x31 => '@',
}Range: Any hashref of formats recognized by Spreadsheet::Reader::Format::ParseExcelFormatStrings
attribute methods Methods provided to by the attribute to adjust it.
total_defined_excel_formats
Definition: get the count of the current key => value pairs
Methods
These are methods to use this class. For additional FmtDefault options see the Attributes section.
get_defined_excel_format( $position )
Definition: This will return the preset excel format string for the stored position from the attribute defined_excel_translations. The positions are actually stored in a hash where the keys are integers representing a position in an order list.
Accepts: an integer or an octal number or octal string for the format string $position
Returns: an excel format string (not a built coercion)
set_defined_excel_formats( %args )
Definition: This will set the excel format strings for the indicated positions in the attribute defined_excel_translations.
Accepts: a Hash list, a hash ref (both with keys representing positions), or an arrayref of strings with the update strings in the target position. All passed argument lists greater than one will be assumed to be hash arguments and must come in pairs. If a single argument is passed then that value is checked to see if it is a hashref or an arrayref. For passed arrayrefs all empty positions are ignored meaning that any preexisting value in that positions is left in force. To erase the default value send '@' (passthrough) as the format string for that position. This function does not do any string validation. The validation is done when the coercion is generated.
Returns: 1 for success
change_output_encoding( $string )
Definition: This should be called on the output string prior to performing any coercion.
Accepts: a perl unicode coded string
Returns: the converted $string decoded to the defined format
SUPPORT
TODO
Nothing yet.
AUTHOR
Jed Lund
jandrew@cpan.org
COPYRIGHT
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
The full text of the license can be found in the LICENSE file included with this module.
This software is copyrighted (c) 2016 by Jed Lund
DEPENDENCIES
SEE ALSO
Spreadsheet::ParseExcel - Excel 2003 and earlier
Spreadsheet::XLSX - 2007+
Spreadsheet::ParseXLSX - 2007+
All lines in this package that use Log::Shiras are commented out