/ 
Spreadsheet-XLSX-Reader-LibXML-v0.24.2
River stage zero No dependents
/ Spreadsheet::XLSX::Reader::LibXML::Worksheet

NAME

Spreadsheet::XLSX::Reader::LibXML::Worksheet - Read xlsx worksheets with LibXML

SYNOPSIS

See the SYNOPSIS in the Workbook class

DESCRIPTION

If you want just learn the key elements of how to use the Worksheet instances returned from Spreadsheet::XLSX::Reader::LibXML this POD is for you. You will also want to skip the next paragraph. First, it is best to generate a worksheet instance with the main class. Once you have done that there are several ways to step through the data and return information from the identified location.

As you may have noticed this is only the pod explaining the Worksheet class not the file containing the actual class. In fact worksheets are built somwhat on the fly with an amalgam of classes, roles, and traits each with their own code and POD. Since this is a Moose package I tried to architect it as extensibly as possible. For protips to manipulate each element of the package look at the pod for that element and then review the tests for that element. Every .pm file has it's own test. I use MooseX::ShortCut::BuildInstance in order to test just the role or class functionality. The file t/Spreadsheet/XLSX/Reader/LibXML/10-get_cell.t represents a good 'under the hood' look at the way each is integrated into this larger package since it doesn't use the top level class for the test. The one exception to full extensibility is the worksheet class itself. Because worksheet reading is central to the functionality of this package the core worksheet build is hard coded.

The way to set what type of information is returned

There is a an attribute set in the primary instance called group_return_type. Setting this attribute will return either a full Spreadsheet::XLSX::Reader::LibXML::Cell instance, just the unformatted value, or the formatted value. For more details on the data available in the Cell instance read the documentation for the Cell instance.

Methods to access data from the sheet

These are the various functions that are available (independent of sheet parser type) to select which cells to read.

get_cell( $row, $column )

    Definition: Indicate both the requested row and requested column and the information for that position will be returned. Both $row and $column are required

    Accepts: the list ( $row, $column ) both required

    Returns: see returns for details on what is returned

get_next_value

    Definition: Reading left to right and top to bottom this will return the next cell with a value. This actually includes cells with no value but some unique formatting such as cells that have been merged with other cells.

    Accepts: nothing

    Returns: see returns for details on what is returned

fetchrow_arrayref( $row )

    Definition: In an homage to DBI I included this function to return an array ref of the cells or values in the requested $row. If no row is requested this returns the 'next' row. In the array ref any empty and non unique cell will show as 'undef'.

    Accepts: undef = next|$row = a row integer indicating the desired row

    Returns: an array ref of all possible column positions in that row with data filled in as appropriate.

fetchrow_array( $row )

    Definition: This function is just like fetchrow_arrayref except it returns an array instead of an array ref

    Accepts: undef = next|$row = a row integer indicating the desired row

    Returns: an array of all possible column positions in that row with data filled in as appropriate.

set_headers( @header_row_list )

    Definition: This function is used to set headers used in the function fetchrow_hashref. It accepts a list of row numbers that will be collated into a set of headers used to build the hashref for each row. The header rows are coallated in sequence with the first number taking precedence. The list is also used to set the lowest row of the headers in the table. All rows at that level and higher will be considered out of the table and will return undef while setting the error instance. If some of the columns do not have values then the instance will auto generate unique headers for each empty header column to fill out the header ref.

    Accepts: a list of row numbers

    Returns: an array ref of the built headers for review

fetchrow_hashref( $row )

    Definition: This function is used to return a hashref representing the data in the specified row. If no $row value is passed it will return the 'next' row of data. A call to this function without setting the headers first will return undef and set the error instance.

    Accepts: a target $row number for return values or undef meaning 'next'

    Returns: a hash ref of the values for that row

min_row

    Definition: This is the minimum row with data listed in the sheet. This value is affected by the workbook attributes from_the_edge, and count_from_zero

    Accepts: nothing

    Returns: an integer

has_min_row

max_row

    Definition: This is the maximum row with data listed in the sheet. This value is affected by the workbook attribute count_from_zero

    Accepts: nothing

    Returns: an integer

has_max_row

min_col

    Definition: This is the minimum column with data listed in the sheet. This value is affected by the workbook attributes from_the_edge, and count_from_zero

    Accepts: nothing

    Returns: an integer

has_min_col

max_col

    Definition: This is the maximum row with data listed in the sheet. This value is affected by the workbook attribute count_from_zero

    Accepts: nothing

    Returns: an integer

has_max_col

row_range

    Definition: This returns a list with the minimum row followed by the maximum row. This list is affected by the workbook attributes from_the_edge, and count_from_zero

    Accepts: nothing

    Returns: ( $minimum_row, $maximum_row ) - integers

col_range

    Definition: This returns a list with the minimum column followed by the maximum column. This list is affected by the workbook attributes from_the_edge, and count_from_zero

    Accepts: nothing

    Returns: ( $minimum_column, $maximum_column ) - integers

Attributes

These are attributes that affect the behaviour of the returned data in the worksheet instance. In general you would not set these on instance generation, Because the primary class will generate this instance for you. Rather you would use the attribue methods listed with each attribute to change the attribute after the worksheet instance has been generated. Additionally at the end of this list you will see a link to the workbook instance itself for access to all those settings as well.

min_header_col

    Definition: This attribute affects the hashref that is returned in the method fetchrow_hashref. If the table you are reading does not start in the first column of the sheet then you need to indicate where to start. Otherwize the fetchrow_hashref method will return auto generated headers and attach them to the cell data outside your table but inside the sheet data boundaries. This attribute tells fetchrow_hashref what column to use to start the hash ref build.

    Default: undef (which is equivalent to the minimum column of the sheet)

    Range: The minimum column of the sheet to or less than the max_header_col

    attribute methods Methods provided to adjust this attribute

      get_min_header_col

        Definition: returns the value stored in the attribute

      set_min_header_col

        Definition: Sets a new value for the attribute

      has_min_header_col

        Definition: Indicates if the attribute has a stored value

max_header_col

    Definition: This attribute affects the hashref that is returned in the method fetchrow_hashref. If the table you are reading ends before the max column of the sheet then you need indicate where to stop reading. Otherwize the fetchrow_hashref method will return auto generated headers and attach them to the cell values outside your table. This attribute tells fetchrow_hashref what column to use to end the hash ref build.

    Default: undef (equal to the maximum column of the sheet)

    Range: The maximum column of the sheet to or less than the min_header_col

    attribute methods Methods provided to adjust this attribute

      get_max_header_col

        Definition: returns the value stored in the attribute

      set_max_header_col

        Definition: Sets a new value for the attribute

      has_max_header_col

        Definition: Indicates if the attribute has a stored value

custom_formats

    Definition: This package will generate value conversions that generally match the numerical conversions set in the Excel spreadsheet. However, it may be that you want to convert the unformatted values for certain cells, rows, or columns in some user defined way. Build an object instance that has the two following methods; 'assert_coerce' and 'display_name'. Then place it here in this attribute as a value to a hash key that is keyed on the target Cell ID, or the row number, or the Column letter callout and this package will assign that conversion when calling 'value' on the cell rather than the conversion stored in the Excel spreadsheet.

    Default: {} = no custom conversions

    Range: keys representing cell ID's, row numbers, or column letter callouts

    Example:

    Building a converter on the fly (or use Type::Library or MooseX::Types)

    use DateTimeX::Format::Excel;
use DateTime::Format::Flexible;
use Type::Coercion;
use Type::Tiny;
my	@args_list	= ( system_type => 'apple_excel' );
my	$converter	= DateTimeX::Format::Excel->new( @args_list );
my	$string_via	= sub{ 
						my	$str = $_[0];
						return DateTime::Format::Flexible->parse_datetime( $str );#my	$dt	= 
						#~ return $dt->format_cldr( 'yyyy-M-d' );
					};
my	$num_via	= sub{
						my	$num = $_[0];
						return $converter->parse_datetime( $num );#my	$dt = 
						#~ return $dt->format_cldr( 'yyyy-M-d' );
					};

# Turn date strings or Excel date numbers to DateTime objects!
my	$date_time_from_value = Type::Coercion->new( 
	type_coercion_map => [ Num, $num_via, Str, $string_via, ],
);
$date_time_type = Type::Tiny->new(
		name		=> 'Custom_date_type',
		constraint	=> sub{ ref($_) eq 'DateTime' },
		coercion	=> $date_time_from_value,
	);

# Deep coercion! to handle first the $date_time_from_value coercion and then 
#    build a specific date string output
$string_type = Type::Tiny->new(
		name		=> 'YYYYMMDD',
		constraint	=> sub{
			!$_ or (
			$_ =~ /^\d{4}\-(\d{2})-(\d{2})$/ and
			$1 > 0 and $1 < 13 and $2 > 0 and $2 < 32 )
		},
		coercion	=> Type::Coercion->new(
			type_coercion_map =>[
				$date_time_type->coercibles, sub{ my $tmp = $date_time_type->coerce( $_ ); $tmp->format_cldr( 'yyyy-MM-dd' ) },
			],
		),
);

    Setting custom conversions to use for the worksheet

    my $worksheet = $workbook->worksheet( 'TargetWorksheetName' );
$worksheet->set_custom_formats( {
	E10	=> $date_time_type,
	10	=> $string_type,
	D14	=> $string_type,
} );

    attribute methods Methods provided to adjust this attribute

      set_custom_formats

        Definition: Sets a new (complete) hashref for the attribute

      has_custom_format( $key )

        Definition: checks if the specific custom format $key is set

      set_custom_format( $key = $coercion, ... )>

        Definition: sets the specific custom format $key(s) with $coercion(s)

      get_custom_format( $key )

        Definition: returns the specific custom format for that $key (see has_custom_format )

sheet_rel_id

    Definition: This is the relId of the sheet listed in the XML of the .xlsx file. You probably don't care and you should never set this value.

    attribute methods Methods provided to adjust this attribute

      rel_id

        Definition: returns the value stored in the attribute

sheet_id

    Definition: This is the Id of the sheet listed in the XML of the .xlsx file. You probably don't care and you should never set this value.

    attribute methods Methods provided to adjust this attribute

      sheet_id

        Definition: returns the value stored in the attribute

sheet_position

    Definition: This is the visual sheet position in the .xlsx file. You should never set this value.

    attribute methods Methods provided to adjust this attribute

      position

        Definition: returns the value stored in the attribute

sheet_name

    Definition: This is the visual sheet name in the .xlsx file. You should never set this value.

    attribute methods Methods provided to adjust this attribute

      get_name

        Definition: returns the value stored in the attribute

workbook_instance

    Definition: This attribute holds a reference back to the workbook instance so that the worksheet has access to the global settings managed there. As a consequence some of the same methods available to the workbook are available here in the worksheet. Beware that setting or adjusting the workbook level attributes with methods here will be universal and affect other worksheets. If that doesn't make sence then don't use these methods. (Nothing to see here! Move along.)

    Default: a Spreadsheet::XLSX::Reader::LibXML instance

    attribute methods Methods of the workbook exposed here by the delegation of the instance to this class through this attribute

      counting_from_zero

        Definition: returns the <count_from_zero|Spreadsheet::XLSX::Reader::LibXML/count_from_zero> state

      boundary_flag_setting

        Definition: returns the <file_boundary_flags|Spreadsheet::XLSX::Reader::LibXML/file_boundary_flags> state

      change_boundary_flag( $Bool )

        Definition: sets the <file_boundary_flags|Spreadsheet::XLSX::Reader::LibXML/file_boundary_flags> state (For the whole workbook!)

      get_shared_string_position( $int )

      get_format_position( $int, [$header] )

        Definition: returns the format data stored in the styles file at position $int. If the optional $header is passed only the data for that header is returned. Otherwise all styles for that position are returned. For more information review Spreadsheet::XLSX::Reader::LibXML::Styles. This is a delegation of a delegation!

      set_empty_is_end( $Bool )

        Definition: sets the <empty_is_end|Spreadsheet::XLSX::Reader::LibXML/empty_is_end> state (For the whole workbook!)

      is_empty_the_end

        Definition: returns the workbook state for the attribute <empty_is_end|Spreadsheet::XLSX::Reader::LibXML/empty_is_end>

      get_group_return_type

        Definition: returns the workbook state for the attribute <group_return_type|Spreadsheet::XLSX::Reader::LibXML/group_return_type>

      set_group_return_type( (instance|unformatted|value) )

        Definition: sets the <group_return_type|Spreadsheet::XLSX::Reader::LibXML/group_return_type> state (For the whole workbook!)

      get_epoch_year

      change_output_encoding

      get_date_behavior

      set_date_behavior

SUPPORT

TODO

    1. Add a way to set manual headers for fetchrow_hashref

    2. Add a pivot table reader (sometimes returns different values than just the sheet shows)

    3. Add more exposure to worksheet formatting values

    4. Add a DOM version of the parser

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 by Jed Lund

DEPENDENCIES

SEE ALSO