Excel-Writer-XLSX
view release on metacpan or search on metacpan
lib/Excel/Writer/XLSX/Utility.pm view on Meta::CPAN
package Excel::Writer::XLSX::Utility;
###############################################################################
#
# Utility - Helper functions for Excel::Writer::XLSX.
#
#
# Used in conjunction with Excel::Writer::XLSX
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
# Documentation after __END__
#
# perltidy with the following options: -mbl=2 -pt=0 -nola
use 5.008002;
use strict;
use Exporter;
use warnings;
use autouse 'Date::Calc' => qw(Delta_DHMS Decode_Date_EU Decode_Date_US);
use autouse 'Date::Manip' => qw(ParseDate Date_Init);
use Carp;
use Digest::MD5 qw(md5_hex);
use File::Basename 'fileparse';
our $VERSION = '1.15';
# Row and column functions
my @rowcol = qw(
xl_rowcol_to_cell
xl_cell_to_rowcol
xl_col_to_name
xl_range
xl_range_formula
xl_inc_row
xl_dec_row
xl_inc_col
xl_dec_col
xl_cell_autofit_width
);
# Date and Time functions
my @dates = qw(
xl_date_list
xl_date_1904
xl_parse_time
xl_parse_date
xl_parse_date_init
xl_decode_date_EU
xl_decode_date_US
);
our @ISA = qw(Exporter);
our @EXPORT_OK = ();
our @EXPORT = ( @rowcol, @dates, 'quote_sheetname', 'get_image_properties' );
our %EXPORT_TAGS = (
rowcol => \@rowcol,
dates => \@dates
);
our %CHAR_WIDTHS = (
' ' => 3, '!' => 5, '"' => 6, '#' => 7, '$' => 7, '%' => 11,
'&' => 10, "'" => 3, '(' => 5, ')' => 5, '*' => 7, '+' => 7,
',' => 4, '-' => 5, '.' => 4, '/' => 6, '0' => 7, '1' => 7,
'2' => 7, '3' => 7, '4' => 7, '5' => 7, '6' => 7, '7' => 7,
'8' => 7, '9' => 7, ':' => 4, ';' => 4, '<' => 7, '=' => 7,
'>' => 7, '?' => 7, '@' => 13, 'A' => 9, 'B' => 8, 'C' => 8,
'D' => 9, 'E' => 7, 'F' => 7, 'G' => 9, 'H' => 9, 'I' => 4,
'J' => 5, 'K' => 8, 'L' => 6, 'M' => 12, 'N' => 10, 'O' => 10,
'P' => 8, 'Q' => 10, 'R' => 8, 'S' => 7, 'T' => 7, 'U' => 9,
'V' => 9, 'W' => 13, 'X' => 8, 'Y' => 7, 'Z' => 7, '[' => 5,
'\\' => 6, ']' => 5, '^' => 7, '_' => 7, '`' => 4, 'a' => 7,
'b' => 8, 'c' => 6, 'd' => 8, 'e' => 8, 'f' => 5, 'g' => 7,
'h' => 8, 'i' => 4, 'j' => 4, 'k' => 7, 'l' => 4, 'm' => 12,
'n' => 8, 'o' => 8, 'p' => 8, 'q' => 8, 'r' => 5, 's' => 6,
't' => 5, 'u' => 8, 'v' => 7, 'w' => 11, 'x' => 7, 'y' => 7,
'z' => 6, '{' => 5, '|' => 7, '}' => 5, '~' => 7,
);
###############################################################################
#
# xl_rowcol_to_cell($row, $col, $row_absolute, $col_absolute)
#
sub xl_rowcol_to_cell {
my $row = $_[0] + 1; # Change from 0-indexed to 1 indexed.
my $col = $_[1];
my $row_abs = $_[2] ? '$' : '';
my $col_abs = $_[3] ? '$' : '';
my $col_str = xl_col_to_name( $col, $col_abs );
lib/Excel/Writer/XLSX/Utility.pm view on Meta::CPAN
return $row, $col, $row_abs, $col_abs;
}
###############################################################################
#
# xl_col_to_name($col, $col_absolute)
#
sub xl_col_to_name {
my $col = $_[0];
my $col_abs = $_[1] ? '$' : '';
my $col_str = '';
# Change from 0-indexed to 1 indexed.
$col++;
while ( $col ) {
# Set remainder from 1 .. 26
my $remainder = $col % 26 || 26;
# Convert the $remainder to a character. C-ishly.
my $col_letter = chr( ord( 'A' ) + $remainder - 1 );
# Accumulate the column letters, right to left.
$col_str = $col_letter . $col_str;
# Get the next order of magnitude.
$col = int( ( $col - 1 ) / 26 );
}
return $col_abs . $col_str;
}
###############################################################################
#
# xl_range($row_1, $row_2, $col_1, $col_2, $row_abs_1, $row_abs_2, $col_abs_1, $col_abs_2)
#
sub xl_range {
my ( $row_1, $row_2, $col_1, $col_2 ) = @_[ 0 .. 3 ];
my ( $row_abs_1, $row_abs_2, $col_abs_1, $col_abs_2 ) = @_[ 4 .. 7 ];
my $range1 = xl_rowcol_to_cell( $row_1, $col_1, $row_abs_1, $col_abs_1 );
my $range2 = xl_rowcol_to_cell( $row_2, $col_2, $row_abs_2, $col_abs_2 );
if ($range1 eq $range2) {
return $range1;
}
else {
return $range1 . ':' . $range2;
}
}
###############################################################################
#
# xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
#
sub xl_range_formula {
my ( $sheetname, $row_1, $row_2, $col_1, $col_2 ) = @_;
$sheetname = quote_sheetname( $sheetname );
my $range = xl_range( $row_1, $row_2, $col_1, $col_2, 1, 1, 1, 1 );
return '=' . $sheetname . '!' . $range
}
###############################################################################
#
# quote_sheetname()
#
# Sheetnames used in references should be quoted if they contain any spaces,
# special characters or if they look like something that isn't a sheet name.
# The rules are shown inline below.
#
sub quote_sheetname {
my $sheetname = shift;
my $uc_sheetname = uc( $sheetname );
my $needs_quoting = 0;
my $row_max = 1_048_576;
my $col_max = 16_384;
# Don't quote sheetname if it is already quoted by the user.
if ( $sheetname !~ /^'/ ) {
# Rule 1. Sheet names that contain anything other than \w and "."
# characters must be quoted.
if ( $sheetname =~ /[^\w\.\p{Emoticons}]/ ) {
$needs_quoting = 1;
}
# Rule 2. Sheet names that start with a digit or "." must be quoted.
elsif ( $sheetname =~ /^[\d\.\p{Emoticons}]/ ) {
$needs_quoting = 1;
}
# Rule 3. Sheet names must not be a valid A1 style cell reference. Valid
# means that the row and column range values must also be within Excel
# row and column limits.
elsif ( $uc_sheetname =~ /^([A-Z]{1,3}\d+)$/ ) {
my ( $row, $col ) = xl_cell_to_rowcol( $1 );
if ( $row >= 0 && $row < $row_max && $col >= 0 && $col < $col_max )
{
$needs_quoting = 1;
}
}
# Rule 4. Sheet names must not *start* with a valid RC style cell
# reference. Other characters after the valid RC reference are ignored
# by Excel. Valid means that the row and column range values must also
# be within Excel row and column limits.
#
# Note: references without trailing characters like R12345 or C12345 are
lib/Excel/Writer/XLSX/Utility.pm view on Meta::CPAN
#
sub _process_gif {
my $data = $_[0];
my $filename = $_[1];
my $type = 'gif';
my $x_dpi = 96;
my $y_dpi = 96;
my $width = unpack "v", substr $data, 6, 2;
my $height = unpack "v", substr $data, 8, 2;
print join ", ", ( $type, $width, $height, $x_dpi, $y_dpi, "\n" );
if ( not defined $height ) {
croak "$filename: no size data found in gif image.\n";
}
return ( $type, $width, $height, $x_dpi, $y_dpi );
}
1;
__END__
=head1 NAME
Utility - Helper functions for L<Excel::Writer::XLSX>.
=head1 SYNOPSIS
Functions to help with some common tasks when using L<Excel::Writer::XLSX>.
These functions mainly relate to dealing with rows and columns in A1 notation and to handling dates and times.
use Excel::Writer::XLSX::Utility; # Import everything
($row, $col) = xl_cell_to_rowcol( 'C2' ); # (1, 2)
$str = xl_rowcol_to_cell( 1, 2 ); # C2
$str = xl_col_to_name( 702 ); # AAA
$str = xl_inc_col( 'Z1' ); # AA1
$str = xl_dec_col( 'AA1' ); # Z1
$date = xl_date_list(2002, 1, 1); # 37257
$date = xl_parse_date( '11 July 1997' ); # 35622
$time = xl_parse_time( '3:21:36 PM' ); # 0.64
$date = xl_decode_date_EU( '13 May 2002' ); # 37389
=head1 DESCRIPTION
This module provides a set of functions to help with some common tasks encountered when using the L<Excel::Writer::XLSX> module. The two main categories of function are:
Row and column functions: these are used to deal with Excel's A1 representation of cells. The functions in this category are:
xl_rowcol_to_cell
xl_cell_to_rowcol
xl_col_to_name
xl_range
xl_range_formula
xl_inc_row
xl_dec_row
xl_inc_col
xl_dec_col
Date and Time functions: these are used to convert dates and times to the numeric format used by Excel. The functions in this category are:
xl_date_list
xl_date_1904
xl_parse_time
xl_parse_date
xl_parse_date_init
xl_decode_date_EU
xl_decode_date_US
All of these functions are exported by default. However, you can use import lists if you wish to limit the functions that are imported:
use Excel::Writer::XLSX::Utility; # Import everything
use Excel::Writer::XLSX::Utility qw(xl_date_list); # xl_date_list only
use Excel::Writer::XLSX::Utility qw(:rowcol); # Row/col functions
use Excel::Writer::XLSX::Utility qw(:dates); # Date functions
=head1 ROW AND COLUMN FUNCTIONS
L<Excel::Writer::XLSX> supports two forms of notation to designate the position of cells: Row-column notation and A1 notation.
Row-column notation uses a zero based index for both row and column while A1 notation uses the standard Excel alphanumeric sequence of column letter and 1-based row. Columns range from A to XFD, i.e. 0 to 16,383, rows range from 0 to 1,048,575 in Exc...
(0, 0) # The top left cell in row-column notation.
('A1') # The top left cell in A1 notation.
(1999, 29) # Row-column notation.
('AD2000') # The same cell in A1 notation.
Row-column notation is useful if you are referring to cells programmatically:
for my $i ( 0 .. 9 ) {
$worksheet->write( $i, 0, 'Hello' ); # Cells A1 to A10
}
A1 notation is useful for setting up a worksheet manually and for working with formulas:
$worksheet->write( 'H1', 200 );
$worksheet->write( 'H2', '=H7+1' );
The functions in the following sections can be used for dealing with A1 notation, for example:
( $row, $col ) = xl_cell_to_rowcol('C2'); # (1, 2)
$str = xl_rowcol_to_cell( 1, 2 ); # C2
Cell references in Excel can be either relative or absolute. Absolute references are prefixed by the dollar symbol as shown below:
A1 # Column and row are relative
$A1 # Column is absolute and row is relative
A$1 # Column is relative and row is absolute
$A$1 # Column and row are absolute
An absolute reference only makes a difference if the cell is copied. Refer to the Excel documentation for further details. All of the following functions support absolute references.
=head2 xl_rowcol_to_cell($row, $col, $row_absolute, $col_absolute)
Parameters: $row: Integer
$col: Integer
$row_absolute: Boolean (1/0) [optional, default is 0]
$col_absolute: Boolean (1/0) [optional, default is 0]
Returns: A string in A1 cell notation
This function converts a zero based row and column cell reference to a A1 style string:
$str = xl_rowcol_to_cell( 0, 0 ); # A1
$str = xl_rowcol_to_cell( 0, 1 ); # B1
$str = xl_rowcol_to_cell( 1, 0 ); # A2
The optional parameters C<$row_absolute> and C<$col_absolute> can be used to indicate if the row or column is absolute:
$str = xl_rowcol_to_cell( 0, 0, 0, 1 ); # $A1
$str = xl_rowcol_to_cell( 0, 0, 1, 0 ); # A$1
$str = xl_rowcol_to_cell( 0, 0, 1, 1 ); # $A$1
See above for an explanation of absolute cell references.
=head2 xl_cell_to_rowcol($string)
Parameters: $string String in A1 format
Returns: List ($row, $col)
This function converts an Excel cell reference in A1 notation to a zero based row and column. The function will also handle Excel's absolute, C<$>, cell notation.
my ( $row, $col ) = xl_cell_to_rowcol('A1'); # (0, 0)
my ( $row, $col ) = xl_cell_to_rowcol('B1'); # (0, 1)
my ( $row, $col ) = xl_cell_to_rowcol('C2'); # (1, 2)
my ( $row, $col ) = xl_cell_to_rowcol('$C2'); # (1, 2)
my ( $row, $col ) = xl_cell_to_rowcol('C$2'); # (1, 2)
my ( $row, $col ) = xl_cell_to_rowcol('$C$2'); # (1, 2)
=head2 xl_col_to_name($col, $col_absolute)
Parameters: $col: Integer
$col_absolute: Boolean (1/0) [optional, default is 0]
Returns: A column string name.
This function converts a zero based column reference to a string:
$str = xl_col_to_name(0); # A
$str = xl_col_to_name(1); # B
$str = xl_col_to_name(702); # AAA
The optional parameter C<$col_absolute> can be used to indicate if the column is absolute:
$str = xl_col_to_name( 0, 0 ); # A
$str = xl_col_to_name( 0, 1 ); # $A
$str = xl_col_to_name( 1, 1 ); # $B
=head2 xl_range($row_1, $row_2, $col_1, $col_2, $row_abs_1, $row_abs_2, $col_abs_1, $col_abs_2)
Parameters: $sheetname String
$row_1: Integer
$row_2: Integer
$col_1: Integer
$col_2: Integer
$row_abs_1: Boolean (1/0) [optional, default is 0]
$row_abs_2: Boolean (1/0) [optional, default is 0]
$col_abs_1: Boolean (1/0) [optional, default is 0]
$col_abs_2: Boolean (1/0) [optional, default is 0]
Returns: A worksheet range formula as a string.
This function converts zero based row and column cell references to an A1 style range string:
my $str = xl_range( 0, 9, 0, 0 ); # A1:A10
my $str = xl_range( 1, 8, 2, 2 ); # C2:C9
my $str = xl_range( 0, 3, 0, 4 ); # A1:E4
my $str = xl_range( 0, 3, 0, 4, 1 ); # A$1:E4
my $str = xl_range( 0, 3, 0, 4, 1, 1 ); # A$1:E$
my $str = xl_range( 0, 0, 0, 0 ); # A1
=head2 xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
Parameters: $sheetname String
$row_1: Integer
$row_2: Integer
$col_1: Integer
$col_2: Integer
Returns: A worksheet range formula as a string.
This function converts zero based row and column cell references to an A1 style formula string:
my $str = xl_range_formula( 'Sheet1', 0, 9, 0, 0 ); # =Sheet1!$A$1:$A$10
my $str = xl_range_formula( 'Sheet2', 6, 65, 1, 1 ); # =Sheet2!$B$7:$B$66
my $str = xl_range_formula( 'New data', 1, 8, 2, 2 );# ='New data'!$C$2:$C$9
This is useful for setting ranges in Chart objects:
$chart->add_series(
categories => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ),
values => xl_range_formula( 'Sheet1', 1, 9, 1, 1 ),
);
# Which is the same as:
$chart->add_series(
categories => '=Sheet1!$A$2:$A$10',
values => '=Sheet1!$B$2:$B$10',
);
=head2 xl_inc_row($string)
Parameters: $string, a string in A1 format
Returns: Incremented string in A1 format
This functions takes a cell reference string in A1 notation and increments the row. The function will also handle Excel's absolute, C<$>, cell notation:
my $str = xl_inc_row( 'A1' ); # A2
my $str = xl_inc_row( 'B$2' ); # B$3
my $str = xl_inc_row( '$C3' ); # $C4
my $str = xl_inc_row( '$D$4' ); # $D$5
=head2 xl_dec_row($string)
Parameters: $string, a string in A1 format
Returns: Decremented string in A1 format
This functions takes a cell reference string in A1 notation and decrements the row. The function will also handle Excel's absolute, C<$>, cell notation:
my $str = xl_dec_row( 'A2' ); # A1
my $str = xl_dec_row( 'B$3' ); # B$2
my $str = xl_dec_row( '$C4' ); # $C3
my $str = xl_dec_row( '$D$5' ); # $D$4
=head2 xl_inc_col($string)
Parameters: $string, a string in A1 format
Returns: Incremented string in A1 format
This functions takes a cell reference string in A1 notation and increments the column. The function will also handle Excel's absolute, C<$>, cell notation:
my $str = xl_inc_col( 'A1' ); # B1
my $str = xl_inc_col( 'Z1' ); # AA1
my $str = xl_inc_col( '$B1' ); # $C1
my $str = xl_inc_col( '$D$5' ); # $E$5
=head2 xl_dec_col($string)
Parameters: $string, a string in A1 format
Returns: Decremented string in A1 format
This functions takes a cell reference string in A1 notation and decrements the column. The function will also handle Excel's absolute, C<$>, cell notation:
my $str = xl_dec_col( 'B1' ); # A1
( run in 0.496 second using v1.01-cache-2.11-cpan-39bf76dae61 )