trying to write dates from perl to Excel

[marlboroguy58]

hi,

i have written a script that interacts with MS Excel.
it extracts data from a database and populates an excel sheet.
there are some date fields in the database, but when the script writes the value in the sheet it writes it as a string. i tried formating the cells for date but it doesn't help.

Is there a way where i can convert the date value in a way that excel recognizes it.
here is the piece of code that i use.

sub setXLCellValue
{
my ($sheet, $row, $col, $val) = @_;
$sheet->Cells($row,$col)->{'Value'} = $val;
}

here $val is a string of the format MM/DD/YYYY...how do i convert it to date format that excel can recognize it. As i said earlier i have tried formatting the sheet, it does not help.

 

[stevexff]

From memory Excel holds dates as integers, (so many days since some fixed date in the past) and times as decimal fractions of a 'day'. Then it just formats them as dates.

How are you writing to Excel? You might consider writing the data as a CSV, then importing it - it should recognise them as dates during the import?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[marlboroguy58]

hi stevexff,
i use....

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');

# open Excel file
my $Book = $Excel->Workbooks->Open($BookName);

my $Sheet = $Book->Worksheets($SheetName);
#the above code is to open the book and select the sheet.then i use the below code to write into the cells.
sub writeExcelvalues()
{
my ($sheet,$startrow,$startcol, $order, %map) = @_;
my $offset = 0;
my @loopdata = split(/\|/,$order);
foreach my $key (@loopdata)
{
#~ log1("set $key => $map{$key}\n");
my $val = $map{$key};
$sheet->Cells($startrow,$startcol+$offset)->{'Value'} = $val;
$offset = $offset + 1;
}
}

this function takes the values from the hash and writes it. and one of the values in the Hash is a date string.
the function takes the order of keys and writes all the values in a single row.
how do i convert the date value into Excel date format and write it.

 

[brigmar]

Excel does indeed hold dates as integers.
I actually came across this when pulling dates from excel into .DBFs
The dates are held as the number of days since 30th December 1899.

You can use:

use Date::Calc qw( Delta_Days );

and then:

$Dd = Delta_Days(1899,12,30,$year,$month,$day);

and write $Dd to Excel.
You'll need to parse the date as held in your hash into the YMD components.

 

[marlboroguy58]

thank u brigmar,stevexff

i was able to do something similar to the above, was able to write dates into excel.