---
name: "Spreadsheet::XLSX::Utility2007"
section: "3pm"
platform: "linux"
package: "libspreadsheet-xlsx-perl"
version: "0.18-1"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/Spreadsheet::XLSX::Utility2007"
---

# Spreadsheet::XLSX::Utility2007(3pm)

Utility function for Spreadsheet::XLSX

## NAME

Spreadsheet::XLSX::Utility2007 - Utility function for Spreadsheet::XLSX

## SYNOPSIS

```
    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.

ExcelFmt is used by Spreadsheet::XLSX::Fmt2007.pm which is used by Spreadsheet::XLSX.

## Functions

This module can export 3 functions: ExcelFmt, ExcelLocaltime and LocaltimeExcel.

### 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.

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

ex.

### 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)

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)

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.