Received some other date format while read the data from xlsx file.

[Ramkumar85]

Hi All,

I have received some other date value from the .xlsx file. Used the Spreadsheet::XL SX module to read the data from the file.

Source file value : 19/07/1988 14:00:00
The actual data : 32343.58 ( The values return from the cell while read the data from that cell).

I also used the Spreadsheet::XL SX::Utility2007 ;Spreadsheet::X LSX::Fmt2007; module to get the correct date but not yet resolve.

Can any one give me the idea to resolve the problem.

Regards,
Ram.

[numberwhun]

Can you please share your code with us for how this data is being extracted from the spreadsheet?

Regards,

Jeff

[Ramkumar85]

Hi Jeff,

I below mentioned code used to get the data from xlsx file.

use strict;
use warnings;
use Spreadsheet::XLSX;
use Spreadsheet::XLSX::Utility2007;
use Spreadsheet::XLSX::Fmt2007;
foreach my $sheet (@{$oExcel -> {Worksheet}}) 
{
    # print "Entered \n"; <STDIN>;
    $sheetName = $sheet->{Name};
    next unless (exists ($sheet->{MaxRow}) and (exists ($sheet->{MaxCol})));
    foreach my $row ($sheet->{MinRow}+1 .. $sheet->{MaxRow})
    {
                my $DateOfDelivery;    
if($sheet->{Cells}[$row][29])
        {
            $DateOfDelivery = $sheet->{Cells}[$row][29]->Value;
 
            print "DATE_OF_DELIVERY :: <$DateOfDelivery> \n";<STDIN>;
            # $DateOfDelivery= Spreadsheet::XLSX::Utility2007->LocaltimeExcel($DateOfDelivery);
            # $DateOfDelivery = Spreadsheet::XLSX::Utility2007->ExcelFmt($hFmtDefault{0x0E}, $DateOfDelivery), 
            $DateOfDelivery = Spreadsheet::XLSX::Utility2007->ExcelFmt('yyyy-mm-dd', $DateOfDelivery);
            print "DATE_OF_DELIVERY AFTER FORMAT :: <$DateOfDelivery> \n";<STDIN>;
        }
    }
}
 

Regards,
Ram.

[aselder]

Based on my calculation, it's the number of days since 12/30/1899

He's a snippet of ruby code

require 'time'
require 'activesupport'
 
d = Time.parse('1899-12-30')
d + (32343.58).days
 => 1988-07-19 13:55:12 -0700