Spreads...ity2007(3)

🎀 NAME 🎀

okay so bestie, you've pulled up Spreadsheet::XLSX::Utility2007, and this is its User Contributed Perl Documentation page, section 3. The one-line summary is literally “Utility function for Spreadsheet::XLSX,” and the whole vibe is let me help you handle Excel formats without spiraling. 💅

---

🌸 SYNOPSIS 🌸

Here's the exact little demo from the page, no cap:

use strict;
#Declare
use Spreadsheet::XLSX::Utility2007 qw(ExcelFmt ExcelLocaltime LocaltimeExcel);

#Convert localtime ->Excel Time
my $iBirth = LocaltimeExcel(11, 10, 12, 23, 2, 64);
                           # = 1964-3-23 12:10:11
print $iBirth, "\n";       # 23459.5070717593

#Convert Excel Time -> localtime
my @aBirth = ExcelLocaltime($iBirth, undef);
print join(":", @aBirth), "\n";   # 11:10:12:23:2:64:1:0

#Formatting
print ExcelFmt('yyyy-mm-dd', $iBirth), "\n"; #1964-3-23
print ExcelFmt('m-d-yy', $iBirth), "\n";     # 3-23-64
print ExcelFmt('#,##0', $iBirth), "\n";      # 23,460
print ExcelFmt('#,##0.00', $iBirth), "\n";   # 23,459.51
print ExcelFmt('"My Birthday is (m/d):" m/d', $iBirth), "\n";
                                             # My Birthday is (m/d): 3/23

---

✨ DESCRIPTION ✨

Spreadsheet::XLSX::Utility2007 exports utility functions concerned with Excel format setting. That's the whole summary, and honestly it ate.

Also: ExcelFmt is used by Spreadsheet::XLSX::Fmt2007.pm which is used by Spreadsheet::XLSX. So it's a supporting character with main-character energy. 🔥

---

🧚 FUNCTIONS 🧚

The page says this module can export 3 functions: ExcelFmt, ExcelLocaltime and LocaltimeExcel. (Then it keeps going and documents more functions below. We love a plot twist. 💀)

ExcelFmt

$sTxt = ExcelFmt($sFmt, $iData [, $i1904]);

• $sFmt is a format string for Excel.
• $iData is the target value.
• If $flg1904 is true, this functions assumes that epoch is 1904.
• $sTxt is the result.

Yes, the signature says $i1904 and the prose says $flg1904. They are both here, living their best lives. 🙈

For more detail and examples, please refer sample/chkFmt.pl in this distribution.

Then there's a lonely little ex. sitting there with no example under it. Iconic, honestly. 🎀

ExcelLocaltime

($iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay, $iMSec) =
            ExcelLocaltime($iExTime [, $flg1904]);

ExcelLocaltime converts time information in Excel format into Perl localtime format. $iExTime is a time of Excel. If $flg1904 is true, this functions assumes that epoch is 1904. $iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay are same as localtime. $iMSec means 1/1,000,000 seconds(ms).

LocaltimeExcel

$iExTime = LocaltimeExcel($iSec, $iMin, $iHour, $iDay, $iMon, $iYear
[,$iMSec] [,$flg1904])

LocaltimeExcel converts time information in Perl localtime format into Excel format . $iSec, $iMin, $iHour, $iDay, $iMon, $iYear are same as localtime.

If $flg1904 is true, this functions assumes that epoch is 1904. $iExTime is a time of Excel.

col2int

$iInt = col2int($sCol);

converts a excel row letter into an int for use in an array

This function was contributed by Kevin Mulholland.

int2col

$sCol = int2col($iRow);

convert a column number into column letters NOET: This is quite a brute force coarse method does not manage values over 701 (ZZ)

caveat, bestie: it really is a brute force coarse method, and it does not manage values over 701 (ZZ).

This function was contributed by Kevin Mulholland.

sheetRef

($iRow, $iCol) = sheetRef($sStr);

convert an excel letter-number address into a useful array address NOTE: That also Excel uses X-Y notation, we normally use Y-X in arrays $sStr, excel coord (eg. A2).

This function was contributed by Kevin Mulholland.

xls2csv

$sCsvTxt = xls2csv($sFileName, $sRegion, $iRotate);

convert a chunk of an excel file into csv text chunk $sRegions = "sheet-colrow:colrow" (ex. '1-A1:B2' means 'A1:B2' for sheet 1) $iRotate = 0 or 1 (output should be rotated or not)

tiny detail: the signature says $sRegion, while the prose says $sRegions. Both are invited to the party. 🫶

This function was contributed by Kevin Mulholland.

---

🌈 AUTHOR 🌈

Rob Polocz rob.polocz@trackvia.com based on work by for Spreadsheet::ParseExcel by Kawai Takanori (Hippo2000) used with permission

---

🦋 SEE ALSO 🦋

• Spreadsheet::ParseExcel
• Spreadsheet::WriteExcel

---

👑 COPYRIGHT 👑

This module is part of the Spreadsheet::XLSX distribution.

---

okay bye bestie 💖 this page came from perl v5.42.0, dated 2024-03-22, and the footer is out here giving Spreads...ity2007(3). slay. ✨