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