Excel:...tility(3pm)
✨ NAME ✨
Utility — helper functions for Excel::Writer::XLSX, bestie 💖
So, real talk: this is Excel:...tility(3pm), a User Contributed Perl Documentation page. It's giving handy little sidekick module. Let's get into it 🫶
🌸 SYNOPSIS 🌸
These are functions to help you out with some very common tasks when you're using Excel::Writer::XLSX.
They're mostly about two things: rows and columns in A1 notation, and dates and times. That's it. That's the whole vibe 💅
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' ); # 37389Obsessed with how clean that is ngl ✨ Let's break it down.
🦋 DESCRIPTION 🦋
Okay so this module is basically your little toolbox 🧰 — a set of functions to help with some common tasks you'll run into when you're working with the Excel::Writer::XLSX module. And there are two main categories of function, periodt:
🎀 Row and column functions
These ones are for dealing with Excel's A1 representation of cells. The girls 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 ones convert dates and times into the numeric format that Excel actually uses. The girls 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_USAll of these functions are exported by default. No cap. But — and this is the cute part — you can use import lists if you wanna limit which functions get imported 🎀 like:
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 functionsLook at that — granular control. Iconic behaviour, fr fr 👑
🌟 That's part 1, bestie — more is coming, so don't go anywhere 💖
💖 ROW AND COLUMN FUNCTIONS 💖
okay so Excel::Writer::XLSX gives you two whole ways to point at a cell, and bestie, you're gonna use both:
- Row-column notation — zero based index for both row and column. very "programmer brain" of it.
- A1 notation — the classic Excel alpha-numeric thing: column letter + 1-based row. very "I grew up in the spreadsheet" of it.
Columns go from A to XFD, which is 0 to 16,383. Rows go from 0 to 1,048,575 in Excel 2007+. For example:
(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 the move when you're referring to cells programmatically:
for my $i ( 0 .. 9 ) {
$worksheet->write( $i, 0, 'Hello' ); # Cells A1 to A10
}A1 notation is the move when you're setting up a worksheet by hand or when you're doing formula things:
$worksheet->write( 'H1', 200 );
$worksheet->write( 'H2', '=H7+1' );The functions in the following sections are the ones you want for dealing with A1 notation, like:
( $row, $col ) = xl_cell_to_rowcol('C2'); # (1, 2)
$str = xl_rowcol_to_cell( 1, 2 ); # C2Cell references in Excel can be either relative or absolute. Absolute refs get a dollar symbol in front, like this:
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 absoluteAn absolute reference only actually changes anything if the cell gets copied — go read the Excel docs for the full tea on that. But good news: all of the functions below support absolute references. 🫶
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 one takes a zero based row and column cell reference and hands you back an 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 ); # A2The optional $row_absolute and $col_absolute params let you say whether 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$1Scroll up for the absolute-cell-reference explainer, it's all there. ⬆️
xl_cell_to_rowcol($string)
Parameters:
$string: String in A1 format
Returns: List — ($row, $col)
Takes an Excel cell reference in A1 notation and converts it to a zero based row and column. It also handles Excel's absolute $ cell notation, no cap:
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)xl_col_to_name($col, $col_absolute)
Parameters:
$col: Integer$col_absolute: Boolean (1/0) [optional, default is 0]
Returns: A column string name.
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); # AAAThe optional $col_absolute param lets you flag whether 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 ); # $Bxl_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.
Converts zero based row and column cell references into 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 ); # A1xl_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.
Converts zero based row and column cell references into 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$9This is so 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',
);xl_inc_row($string)
Parameters:
$string, a string in A1 format
Returns: Incremented string in A1 format
Takes a cell reference string in A1 notation and bumps the row up by one. Also handles Excel's absolute $ 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$5xl_dec_row($string)
Parameters:
$string, a string in A1 format
Returns: Decremented string in A1 format
Takes a cell reference string in A1 notation and drops the row down by one. Absolute $ notation? Handled. 💅
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$4xl_inc_col($string)
Parameters:
$string, a string in A1 format
Returns: Incremented string in A1 format
Takes a cell reference string in A1 notation and moves the column forward one. Yep, $ notation works too:
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$5xl_dec_col($string)
Parameters:
$string, a string in A1 format
Returns: Decremented string in A1 format
Takes a cell reference string in A1 notation and moves the column back one. Absolute $ notation included, no extra charge:
my $str = xl_dec_col( 'B1' ); # A1
my $str = xl_dec_col( 'AA1' ); # Z1
my $str = xl_dec_col( '$C1' ); # $B1
my $str = xl_dec_col( '$E$5' ); # $D$5xl_cell_autofit_width($string)
Parameters:
$string, The string to calculate the cell width for.
Returns: The string autofit width in pixels. Returns 0 if the string is empty.
This one figures out the width you'd need to auto-fit a string in a cell:
my $width = xl_cell_autofit_width($string);The Worksheet autofit() method can auto-fit cell data to the optimal column width. But sometimes you wanna handle the auto-fitting yourself and layer your own logic on top to cap the max and min ranges.
That's where xl_cell_autofit_width() comes in — it does the calculation for you. It works by estimating the pixel width of a string based on the width of each character. It also adds a 7 pixel padding for the cell boundary, exactly the way Excel does. 🎀
You can take that calculated width and use it with the Worksheet set_column_pixels() method. ✨
🌸 xl_date_list($years, $months, $days, $hours, $minutes, $seconds) 🌸
Parameters:
$years— Integer$months— Integer [optional, default is 1]$days— Integer [optional, default is 1]$hours— Integer [optional, default is 0]$minutes— Integer [optional, default is 0]$seconds— Float [optional, default is 0]
Returns: a number that represents an Excel date, or undef for an invalid date.
Requires: Date::Calc
This one takes your lil pile of numbers and turns it into a number that represents an Excel date. Every single parameter is optional except $years — that one's the main character, she's required. 👑
$date1 = xl_date_list( 2002, 1, 2 ); # 2 Jan 2002
$date2 = xl_date_list( 2002, 1, 2, 12 ); # 2 Jan 2002 12:00 pm
$date3 = xl_date_list( 2002, 1, 2, 12, 30 ); # 2 Jan 2002 12:30 pm
$date4 = xl_date_list( 2002, 1, 2, 12, 30, 45 ); # 2 Jan 2002 12:30:45 pmYou can team her up with functions that parse date and time strings, bestie — in fact she's lowkey the backbone of most of the functions right below. 💪
🦋 xl_parse_time($string) 🦋
Parameters: $string, a textual representation of a time
Returns: a number that represents an Excel time, or undef for an invalid time.
She takes a time string and gives you back a number that represents an Excel time. These time formats are the valid ones:
hh:mm [AM|PM]
hh:mm [AM|PM]
hh:mm:ss [AM|PM]
hh:mm:ss.ss [AM|PM](not me noticing hh:mm is in that list twice... i'm not touching the source, that's literally what it says 💀)
The meridian, AM or PM, is optional and case insensitive. No meridian means a 24 hour time is assumed, periodt.
$time1 = xl_parse_time( '12:18' );
$time2 = xl_parse_time( '12:18:14' );
$time3 = xl_parse_time( '12:18:14 AM' );
$time4 = xl_parse_time( '1:18:14 AM' );Time in Excel is expressed as a fraction of the day in seconds, so you can also just calculate an Excel time yourself like this:
$time = ( $hours * 3600 + $minutes * 60 + $seconds ) / ( 24 * 60 * 60 );✨ xl_parse_date($string) ✨
Parameters: $string, a textual representation of a date and time
Returns: a number that represents an Excel date, or undef for an invalid date.
Requires: Date::Manip and Date::Calc
Date + time string in, number that represents an Excel date out. 🎀
The parsing is done by the ParseDate() function from Date::Manip. Peep the Date::Manip documentation for the full tea on which date and time formats can be parsed. Heads up tho — to use this function you'll probably have to initialise some Date::Manip variables through the xl_parse_date_init() function first, see below. 👇
xl_parse_date_init( "DateFormat=non-US" );
$date1 = xl_parse_date( "11/7/97" );
$date2 = xl_parse_date( "Friday 11 July 1997" );
$date3 = xl_parse_date( "10:30 AM Friday 11 July 1997" );
$date4 = xl_parse_date( "Today" );
$date5 = xl_parse_date( "Yesterday" );Note: if you parse a string that represents a time but not a date, this function will add the current date. Want the time without the date? Do something like this:
$time = xl_parse_date( "10:30 AM" );
$time -= int( $time );💜 xl_parse_date_init("variable=value", ...) 💜
Parameters: a list of Date::Manip variable strings
Returns: a list of all the Date::Manip strings
Requires: Date::Manip
This is the setup girlie — she initialises the variables that the Date::Manip module needs. Call her before you call xl_parse_date(). She only needs to be called once, bestie, don't spam her. 🙈
She's a thin wrapper for the Date::Manip::Date_Init() function. You can use Date_Init() directly if you want, no gatekeeping here. Check the Date::Manip documentation for more.
xl_parse_date_init( "DateFormat=non-US" );
$date1 = xl_parse_date( "11/7/97" ); # July 11th 1997🌈 xl_decode_date_EU($string) 🌈
Parameters: $string, a textual representation of a date and time
Returns: a number that represents an Excel date, or undef for an invalid date.
Requires: Date::Calc
Another date + time string → number-that-represents-an-Excel-date situation. 💅
The date parsing is performed using the Decode_Date_EU() function of the Date::Calc module. Go read the Date::Calc documentation for the details on which date formats can be parsed. Also, note this lil bit from the Date::Calc docs:
"If the year is given as one or two digits only (i.e., if the year is less than 100), it is mapped to the window 1970 -2069 as follows:"
0 <= $year < 70 ==> $year += 2000;
70 <= $year < 100 ==> $year += 1900;The time portion of the string gets parsed by the xl_parse_time() function described above. 🕐
Note: the EU in the function name means a European date format is assumed if it isn't clear from the string. See the first example below. 👀
$date1 = xl_decode_date_EU( "11/7/97" ); #11 July 1997
$date2 = xl_decode_date_EU( "Sat 12 Sept 1998" );
$date3 = xl_decode_date_EU( "4:30 AM Sat 12 Sept 1998" );🧚 xl_decode_date_US($string) 🧚
Parameters: $string, a textual representation of a date and time
Returns: a number that represents an Excel date, or undef for an invalid date.
Requires: Date::Calc
Same exact energy as her EU sister, just American flavored. 🇺🇸
The date parsing is performed using the Decode_Date_US() function of the Date::Calc module. Refer to the Date::Calc documentation for more info on the date formats that can be parsed. And also note this from the Date::Calc docs:
"If the year is given as one or two digits only (i.e., if the year is less than 100), it is mapped to the window 1970 -2069 as follows:"
0 <= $year < 70 ==> $year += 2000;
70 <= $year < 100 ==> $year += 1900;The time portion of the string is parsed using the xl_parse_time() function described above. 🕐
Note: the US in the function name means that an American date format is assumed if it is not clear from the string. See the first example below. 👀
$date1 = xl_decode_date_US( "11/7/97" ); # 7 November 1997
$date2 = xl_decode_date_US( "Sept 12 Saturday 1998" );
$date3 = xl_decode_date_US( "4:30 AM Sept 12 Sat 1998" );🎀 xl_date_1904($date) 🎀
Parameters: $date, an Excel date with a 1900 epoch
Returns: an Excel date with a 1904 epoch, or zero if the $date is before 1904
Girlie converts an Excel date that's living on the 1900 epoch into one on the 1904 epoch. Time zone of calendars, iconic. ✨
$date1 = xl_date_list( 2002, 1, 13 ); # 13 Jan 2002, 1900 epoch
$date2 = xl_date_1904( $date1 ); # 13 Jan 2002, 1904 epochSee also the set_1904() workbook method in the Excel::Writer::XLSX documentation. 📚
👑 REQUIREMENTS 👑
The date and time functions need functions from the Date::Manip and Date::Calc modules. Those required functions are "autoused" from those modules, so you do not have to install them unless you actually wanna use the date and time routines. Meaning: yes bestie, you can absolutely use the row and column functions without having Date::Manip and Date::Calc installed. Slay. 💖
Want the full lore on "autousing"? Go read the documentation on the autouse pragma. 🫶
💀 BUGS 💀
When you're using the autoused functions from Date::Manip and Date::Calc on Perl 5.6.0 with -w, you will get a warning like this:
"Subroutine xxx redefined ..."The current workaround for this is to put use warnings; near the beginning of your program. 👍
🌸 AUTHOR 🌸
John McNamara jmcnamara@cpan.org
💖 COPYRIGHT 💖
Copyright MM-MMXXV, John McNamara.
All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.
and that's a wrap on the whole page, bestie!! 🎉 this was perl v5.40.1 dated 2025-12-22, and yes she ate, no crumbs left. go write some spreadsheets, you're the main character now 👑💅✨
📦 where this page comes from
- ubuntu resolute: libexcel-writer-xlsx-perl 1.15-1
- ubuntu stonking: libexcel-writer-xlsx-perl 1.15-1
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms