NAME
Spreadsheet::XLSX::Reader::LibXML::FmtDefault - Default number and string formats
SYNOPSIS
#!/usr/bin/env perl
use Spreadsheet::XLSX::Reader::LibXML::FmtDefault;
my $formatter = Spreadsheet::XLSX::Reader::LibXML::FmtDefault->new;
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";
my $conversion = $formatter->parse_excel_format_string( '[$-409]dddd, mmmm dd, yyyy;@' );
print 'For conversion named: ' . $conversion->name . "\n";
for my $unformatted_value ( '7/4/1776 11:00.234 AM', 0.112311 ){
print "Unformatted value: $unformatted_value\n";
print "..coerces to: " . $conversion->assert_coerce( $unformatted_value ) . "\n";
}
###########################
# SYNOPSIS Screen Output
# 01: yyyy-mm-dd
# 02: yyyy-mm-dd
# 03: yyyy-mm-dd
# 04: MySpecialFormat
# 05: For conversion named: DATESTRING_0
# 06: Unformatted value: 7/4/1776 11:00.234 AM
# 07: ..coerces to: Thursday, July 04, 1776
# 08: Unformatted value: 0.112311
# 09: ..coerces to: Friday, January 01, 1900
###########################
DESCRIPTION
This is the default class used by Spreadsheet::XLSX::Reader::LibXML. It is separate from the other parts of the formatter class to isolate the elements of localization in this package. It can be configured or adjused in two ways. First use the class as it stand and adjust the attributes to change the outcome of the methods. Second re-write the class to have the same method names but produce different results and then integrate it with the general formatter interface.
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. Additionally 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 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 sheet. 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::XLSX::Reader::LibXML::ParseExcelFormatStrings. The second functionality is string decoding. It is assumed that any file encoding is handled by "ENCODINGS SUPPORT IN XML::LIBXML" in XML::LibXML. 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::XLSX::Reader::LibXML::FormatInterface. This will include the role Spreadsheet::XLSX::Reader::LibXML::ParseExcelFormatStrings.
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. See the documentation for "format_inst" in Spreadsheet::XLSX::Reader::LibXML to see which attribute methods are delegated all the way to the workbook level.
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
get_target_encoding
Definition: Returns the currently set attribute value
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::XLSX::Reader::::LibXML::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. See the documentation for "format_inst" in Spreadsheet::XLSX::Reader::LibXML to see which attribute methods are delegated all the way to the workbook level.
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 for the format string $position
Returns: an excel format string
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 is always called by the Worksheet when a cell value is retreived in order to allow for encoding adjustments on the way out. See "ENCODINGS SUPPORT IN XML::LIBXML" in XML::LibXML for an explanation of how the input encoding is handled. This conversion out is done prior to any number formatting. If you are replacing this class you need to have this function and you can use it to mangle your output string any way you want.
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) 2014, 2015 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