Spreadsheet XLSX

lpanokarren has asked for the wisdom of the Perl Monks concerning the following question:

Hello,
I am using the Spreadsheet::XLSX module to convert a spreadsheet to a series of pipe-delimited text file. While most of the conversion seems to have worked fine, worksheets that have 'Time' fields in them are getting converted incorrectly.

For e.g.
"1:10:00 AM" is being translated to "1:10:00 AM/PM". Anyone seen this before? Is there anything I can change in the modules to correct this?? I would truly appreciate any help you could offer!

Comment on Spreadsheet XLSX

Replies are listed 'Best First'.
Re: Spreadsheet XLSX by Jer2911 (Acolyte) on Nov 12, 2013 at 13:54 UTC
I use 'ExcelLocaltime' from Spreadsheet::ParseExcel::Utility and sprintf to reformat the date: `$data_cell_val = $data_cell -> {Val}; my ($iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iWDay, $iMSec) = Exce +lLocaltime ($data_cell_val); $iYear += 1900; # $iYear is number of years since 1900 $iMon += 1; # $iMon (month) is zero indexed $data_cell_val = sprintf("%04d.%02d.%02d", "$iYear", $iMon, $iDay);` [download]	[reply] [d/l]
Re: Spreadsheet XLSX by builat (Monk) on Nov 11, 2013 at 18:07 UTC
In fact have no idea. But the lame way is to convert all your data in 24 hour format it could work.	[reply]
Re^2: Spreadsheet XLSX by lpanokarren (Novice) on Nov 11, 2013 at 18:12 UTC
I just realized the output of the perl conversion is in fact in the 24 hour format - so 7:20:00 PM is in fact being output as 19:20:00 AM/PM - I sure can deal with that!!! I figured this excerpt comes back with the poorly formatted output - $cell->{_Value} = $oBook->{FmtClass}->ValFmt($cell, $oBook); Although, shouldnt this have been cleaner? Or is there a way to correct this in the module that I am not aware of?	[reply]
Re^3: Spreadsheet XLSX by Corion (Patriarch) on Nov 11, 2013 at 18:15 UTC
For the formatting, you have to look at Spreadsheet::ParseExcel, which is what Spreadsheet::XLSX relies on for formatting etc.	[reply]
Re^3: Spreadsheet XLSX by builat (Monk) on Nov 11, 2013 at 18:18 UTC
It was just lucky guess. I have never use this module before. So I not the guy who can help you with that.	[reply]
Re^3: Spreadsheet XLSX by Bloodnok (Vicar) on Nov 12, 2013 at 10:38 UTC
Hmmm, Maybe you could have used that example (the 19:200 one) to start with ? A user level that continues to overstate my experience :-))	[reply]