{
    "mode": "perldoc",
    "parameter": "Spreadsheet::ParseExcel::Utility",
    "section": "",
    "url": "https://www.chedong.com/phpMan.php/perldoc/Spreadsheet%3A%3AParseExcel%3A%3AUtility/json",
    "generated": "2026-06-14T00:19:44Z",
    "synopsis": "use Spreadsheet::ParseExcel::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);\n# Convert localtime to Excel time\nmy $datetime = LocaltimeExcel(11, 10, 12, 23, 2, 64); # 1964-3-23 12:10:11\nprint $datetime, \"\\n\"; # 23459.5070717593 (Excel date/time format)\n# Convert Excel Time to localtime\nmy @time = ExcelLocaltime($datetime);\nprint join(\":\", @time), \"\\n\";   # 11:10:12:23:2:64:1:0\n# Formatting\nprint ExcelFmt('yyyy-mm-dd', $datetime), \"\\n\"; # 1964-3-23\nprint ExcelFmt('m-d-yy',     $datetime), \"\\n\"; # 3-23-64\nprint ExcelFmt('#,##0',      $datetime), \"\\n\"; # 23,460\nprint ExcelFmt('#,##0.00',   $datetime), \"\\n\"; # 23,459.51",
    "sections": {
        "NAME": {
            "content": "Spreadsheet::ParseExcel::Utility - Utility functions for Spreadsheet::ParseExcel.\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "use Spreadsheet::ParseExcel::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);\n\n# Convert localtime to Excel time\nmy $datetime = LocaltimeExcel(11, 10, 12, 23, 2, 64); # 1964-3-23 12:10:11\n\nprint $datetime, \"\\n\"; # 23459.5070717593 (Excel date/time format)\n\n# Convert Excel Time to localtime\nmy @time = ExcelLocaltime($datetime);\nprint join(\":\", @time), \"\\n\";   # 11:10:12:23:2:64:1:0\n\n# Formatting\nprint ExcelFmt('yyyy-mm-dd', $datetime), \"\\n\"; # 1964-3-23\nprint ExcelFmt('m-d-yy',     $datetime), \"\\n\"; # 3-23-64\nprint ExcelFmt('#,##0',      $datetime), \"\\n\"; # 23,460\nprint ExcelFmt('#,##0.00',   $datetime), \"\\n\"; # 23,459.51\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "The \"Spreadsheet::ParseExcel::Utility\" module provides utility functions for working with\nParseExcel and Excel data.\n",
            "subsections": []
        },
        "Functions": {
            "content": "\"Spreadsheet::ParseExcel::Utility\" can export the following functions:\n\nExcelFmt\nExcelLocaltime\nLocaltimeExcel\ncol2int\nint2col\nsheetRef\nxls2csv\n\nThese functions must be imported implicitly:\n\n# Just one function.\nuse Spreadsheet::ParseExcel::Utility 'col2int';\n\n# More than one.\nuse Spreadsheet::ParseExcel::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);\n\nExcelFmt($formatstring, $number, $is1904)\nExcel stores data such as dates and currency values as numbers. The way these numbers are\ndisplayed is controlled by the number format string for the cell. For example a cell with a\nnumber format of '$#,##0.00' for currency and a value of 1234.567 would be displayed as follows:\n\n'$#,##0.00' + 1234.567 = '$1,234.57'.\n\nThe \"ExcelFmt()\" function tries to emulate this formatting so that the user can convert raw\nnumbers returned by \"Spreadsheet::ParseExel\" to a desired format. For example:\n\nprint ExcelFmt('$#,##0.00', 1234.567); # $1,234.57.\n\nThe syntax of the function is:\n\nmy $text = ExcelFmt($formatstring, $number, $is1904);\n\nWhere $formatstring is an Excel number format string, $number is a real or integer number and\n\"is1904\" is an optional flag to indicate that dates should use Excel's 1904 epoch instead of\nthe default 1900 epoch.\n\n\"ExcelFmt()\" is also used internally to convert numbers returned by the \"Cell::unformatted()\"\nmethod to the formatted value returned by the \"Cell::value()\" method:\n\nmy $cell = $worksheet->getcell( 0, 0 );\n\nprint $cell->unformatted(), \"\\n\"; # 1234.567\nprint $cell->value(),       \"\\n\"; # $1,234.57\n\nThe most common usage for \"ExcelFmt\" is to convert numbers to dates. Dates and times in Excel\nare represented by real numbers, for example \"1 Jan 2001 12:30 PM\" is represented by the number\n36892.521. The integer part of the number stores the number of days since the epoch and the\nfractional part stores the percentage of the day. By applying an Excel number format the number\nis converted to the desired string representation:\n\nprint ExcelFmt('d mmm yyyy h:mm AM/PM', 36892.521);  # 1 Jan 2001 12:30 PM\n\n$is1904 is an optional flag to indicate that dates should use Excel's 1904 epoch instead of the\ndefault 1900 epoch. Excel for Windows generally uses 1900 and Excel for Mac OS uses 1904. The\n$is1904 flag isn't required very often by a casual user and can usually be ignored.\n\nExcelLocaltime($exceldatetime, $is1904)\nThe \"ExcelLocaltime()\" function converts from an Excel date/time number to a \"localtime()\"-like\narray of values:\n\nmy @time = ExcelLocaltime($exceldatetime);\n\n#    0     1     2      3     4       5      6      7\nmy ( $sec, $min, $hour, $day, $month, $year, $wday, $msec ) = @time;\n\nThe array elements from \"(0 .. 6)\" are the same as Perl's \"localtime()\". The last element $msec\nis milliseconds. In particular it should be noted that, in common with \"localtime()\", the month\nis zero indexed and the year is the number of years since 1900. This means that you will usually\nneed to do the following:\n\n$month++;\n$year += 1900;\n\nSee also Perl's documentation for localtime():\n\nThe $is1904 flag is an optional. It is used to indicate that dates should use Excel's 1904\nepoch instead of the default 1900 epoch.\n\nLocaltimeExcel($sec, $min, $hour, $day, $month, $year, $wday, $msec, $is1904)\nThe \"LocaltimeExcel()\" function converts from a \"localtime()\"-like array of values to an Excel\ndate/time number:\n\n$exceldatetime = LocaltimeExcel($sec, $min, $hour, $day, $month, $year, $wday, $msec);\n\nThe array elements from \"(0 .. 6)\" are the same as Perl's \"localtime()\". The last element $msec\nis milliseconds. In particular it should be noted that, in common with \"localtime()\", the month\nis zero indexed and the year is the number of years since 1900. See also Perl's documentation\nfor localtime():\n\nThe $wday and $msec elements are usually optional. This time elements can also be zeroed if they\naren't of interest:\n\n# sec, min, hour, day, month, year\n$exceldatetime = LocaltimeExcel( 0,   0,   0,    1,   0,     101 );\n\nprint ExcelFmt('d mmm yyyy', $exceldatetime);  # 1 Jan 2001\n\nThe $is1904 flag is also optional. It is used to indicate that dates should use Excel's 1904\nepoch instead of the default 1900 epoch.\n\ncol2int($column)\nThe \"col2int()\" function converts an Excel column letter to an zero-indexed column number:\n\nprint col2int('A');  # 0\nprint col2int('AA'); # 26\n\nThis function was contributed by Kevin Mulholland.\n\nint2col($columnnumber)\nThe \"int2col()\" function converts an zero-indexed Excel column number to a column letter:\n\nprint int2col(0);  # 'A'\nprint int2col(26); # 'AA'\n\nThis function was contributed by Kevin Mulholland.\n\nsheetRef($cellstring)\nThe \"sheetRef()\" function converts an Excel cell reference in 'A1' notation to a zero-indexed\n\"(row, col)\" pair.\n\nmy ($row, $col) = sheetRef('A1'); # ( 0, 0 )\nmy ($row, $col) = sheetRef('C2'); # ( 1, 2 )\n\nThis function was contributed by Kevin Mulholland.\n\nxls2csv($filename, $region, $rotate)\nThe \"xls2csv()\" function converts a section of an Excel file into a CSV text string.\n\n$csvtext = xls2csv($filename, $region, $rotate);\n\nWhere:\n\n$region = \"sheet-colrow:colrow\"\nFor example '1-A1:B2' means 'A1:B2' for sheet 1.\n\nand\n\n$rotate  = 0 or 1 (output is rotated/transposed or not)\n\nThis function requires \"Text::CSVXS\" to be installed. It was contributed by Kevin Mulholland\nalong with the \"xls2csv\" script in the \"sample\" directory of the distro.\n\nSee also the following xls2csv utilities: Ken Prows' \"xls2csv\":\nhttp://search.cpan.org/~ken/xls2csv/script/xls2csv and H.Merijn Brand's \"xls2csv\" (which is part\nof Spreadsheet::Read): http://search.cpan.org/~hmbrand/Spreadsheet-Read/\n",
            "subsections": []
        },
        "AUTHOR": {
            "content": "Current maintainer 0.60+: Douglas Wilson dougw@cpan.org\n\nMaintainer 0.40-0.59: John McNamara jmcnamara@cpan.org\n\nMaintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org\n\nOriginal author: Kawai Takanori kwitknr@cpan.org\n",
            "subsections": []
        },
        "COPYRIGHT": {
            "content": "Copyright (c) 2014 Douglas Wilson\n\nCopyright (c) 2009-2013 John McNamara\n\nCopyright (c) 2006-2008 Gabor Szabo\n\nCopyright (c) 2000-2006 Kawai Takanori\n\nAll rights reserved.\n\nYou may distribute under the terms of either the GNU General Public License or the Artistic\nLicense, as specified in the Perl README file.\n",
            "subsections": []
        }
    },
    "summary": "Spreadsheet::ParseExcel::Utility - Utility functions for Spreadsheet::ParseExcel.",
    "flags": [],
    "examples": [],
    "see_also": []
}