> From: "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]>
> Subject: RE: Database to Excel script...
>
> OK... I am stuck once again. I am to pull the data from the
> database and export it directly to an Excel file. Now, I am trying to
> figure out the formatting... The first problem I am running into is
> reformatting dates from the output of MySQL to the desired Excel format.
> Here is a same of what I am attempting:
>
> MySQL date: 2002-09-02 13:13:00
>
> Desired Excel date: 9/2/2002 13:13
>
> I know that there is a set_num_format function in the WriteExcel
> module, but this works with epoch numbers. I am not sure about transforming
> one date format to another date format. Any help would be greatly
> appreciated.
Actually, you should read the docs again. set_num_format works on any kind
of numbers. Dates should be in the internal excel format, which are the
decimal equivalent of days since the Excel epoch (0 January 1900 or 1
January 1904 depending on the OS). You should have a read of the docs which
show several concise examples, including:
$format->set_num_format('dd/mm/yyyy hh:mm AM/PM');
$worksheet->write($row, $col, $timestr, $format);
There are helper routines in the Spreadsheet::WriteExcel::Utility. Their use
is also documented in perldoc Spreadsheet::WriteExcel (In the section Dates
in Excel).
An example given:
$timestr = xl_date_list(2002, 1, 1);
Since these routines are built on-top of Date::Calc and Date::Manip, you
might look into those options as well.
There are plenty of MySQL solutions as well, such as:
SELECT DATE_FORMAT('2002-01-01', '%d/%m/%Y');
These are less portable but still viable. DATE_FORMAT and other date
manipulation functions are documented in the MySQL Reference Manual. Start
with Chapter 7 Functions for Use in SELECT and WHERE Clauses.
You might consider posting questions on Perl modules that are outside of the
scope of the DBI to the appropriate newsgroup: comp.lang.perl.modules.
cp
--
http://www.pryce.net
