Home » File Formats » Spreadsheet_Excel_Writer » Bug #3832

[2005-03-15 21:09 UTC] caos at safepoint dot nl

Description:
------------
I 've created a little PHP program that reads records from Postgresql and creates a Excel sheet (of course with Spreadsheet_Excel_writer).

At first sight the produced Excel file seems to be OK, but when a (excel) sort is done on the colum that contains dates it doesn't sort properly or excel wants you to choose on how to interpreted the values (as tekst or numbers) .
This is because the dates in this column are not interpreted as dates by excel.

When the excel sheet is read with OpenOffice 1.1.2 'Calc' you can see a quote before the date value (which would mean it should be interpreted as text by Calc, but this can also be a 'bug' in the excel import filter of calc).

I alread added a date format to the column with the dates :
$date_format =& $workbook->addFormat();
$date_format->setNumFormat('DD-MM-YYYY');

The format is set in the excel file, but it doesn't help

Used Software:
MS Excel 2002, Openoffice 1.1.2 Calc (all on windows 2000)
Spreadsheet_Excel_Writer version 0.8
Webbrowser: IE 6.x

Reproduce code:
---------------
Source file:
http://www.safepoint.nl/bugs/uren_export.sphp

example: excel file
http://www.safepoint.nl/bugs/Uren_export.xls

Expected result:
----------------
Excel file with proper date values in date column

Actual result:
--------------
Excel file with date values as text in date column

[2005-03-17 08:09 UTC] christianm at amplix dot de

Addition:
It's a problem with all versions of Spreadsheet_Excel_Writer down to 0.6 (only tested til this version) with date/time-fields
Excel corrects this error, OO Calc (1.1.4 and 2 beta) not.
Writing an excel file with Excel 97 and adding a field with a time within (hh:mm:ss), then open this file in OO Calc don't show this error with leading ' ,so I think, this isn't an error in the import filter of Calc.
And it's not an error of database-extraction, even normal written $worksheet->write($i,$j,'hh-mm-ss') produce this error.

Webbrowsers: IE6,Mozilla 1.7, Firefox 1.0

[2005-03-25 10:18 UTC] thierry dot besancon at laposte dot net

I do have the bug in Spreadsheet_Excel_writer 0.4 too.

[2005-03-29 10:25 UTC] Thierry dot Besancon at laposte dot net

I found a workaround that lets me think there's probably no bug in PHP Spreadsheet_Excel_writer.

I used the following :

$dformat =& $workbook->addFormat();
$dformat->setNumFormat('DD/MM/YYYY'); // french date format
$worksheet->write($i,$j,"=DATEVALUE(\"1967-03-27\")", $dformat) ; // my birthday :-)

According to the document of the excel writer module for perl, a date is something internally coded as a number of days since some time origin. So you just can't write "1967-03-27" and expect it to be seen through PHP Spreadsheet_Excel_writer as a date in a XLS file. That's why I used the EXCEL function "DATEVALUE" that computes in EXCEL the number of days since the origin. As the computing is done in the spreadsheet, it has no impact on the PHP side. Writing :
$worksheet->write($i,$j,"1967-03-27") ;
or
$worksheet->write($i,$j,"=DATEVALUE(\"1967-03-27\")", $dformat) ;
is the same thing in terms of performance on the web server with php side.

That worked with excel 2003 and php 4.3.x with Spreadsheet_Excel_writer 0.4 and at this time, I think the workaround works for versions up to 0.8.

Best regards.
Thierry Besancon

[2005-03-29 13:06 UTC] caos at safepoint dot nl

First let me say, thank you Thierry!
This is a working solution and I'am using it right now.

Although I'am happy I have this solution, I feel this is a workaround and is not solving the problem at the right level.
The Excel_writer package should be able to produce a proper Microsoft date value. I would be happy if the Excel_writer was extended with a methode to create a MS date value (used by Excel) from a given date.

[2005-04-18 15:21 UTC] florent dot masson at francetelecom dot com

Hi,

I has the same issue and investigated.
According to : http://sc.openoffice.org/excelfileformat.pdf
"All dates are stored as count of days past this base date." (Base date is 1899-Dec-31 by default)

So in order to have a correct date in excel, you have to write a count of days in the cell, and not a string.

Here's some example code that works for me :

function date_to_excel($date) {//converts a dd/mm/yyyy date to excel count of days
if (ereg("^([0-9]{1,2})/([0-9]{1,2})/([0-9]{2,4})$",$date,$t)) {
return ceil(strtotime("$t[2]/$t[1]/$t[3]")/60/60/24)+25569;
}
else return $date;
}

$fcelldate =& $xls->addFormat();
$fcelldate->setAlign("center");
$fcelldate->setNumFormat(15);

$sheet->write($i,1,date_to_excel($ligne[1]), $fcelldate);

Someone should probably add a writeDate() function that takes a php timestamp as input (remember Base date for php is 1970-01-01)

Regards,
Florent Masson

[2005-04-18 15:59 UTC] florent dot masson at francetelecom dot com

RTFM :/

The answer was in the doc. See examlple at :
http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.spreadsheet-excel-writer-format.setnumformat.php

[2005-06-28 12:34 UTC] s dot appel at bigfoot dot com

A big problem is that most systems use 1-1-1970 for start date and excell uses 1-1-1900. So any date before 1970 will go wrong with strtotime. This is a little function i wrote

function MakeExcelSerial($date = false, $month = false, $year = false)
{
// works between 1-1-1900 and 31-12-2100

if ($month === false) $month = Date ("n");
if ($date === false) $date = Date ("j");
if ($year === false) $year = Date ("Y");

if (($year < 100) and ($year > 0)) $year += 1900;
if ($year < 1900) return 0;

$m_days = Array (31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31);
if (($year % 4 == 0) && ($year % 100 > 0) || ($year % 100 == 0 && $year % 400 == 0))
{
$m_days[1] = 29;
// leap-years can be: 1600, 2000, 2400, etc.
// but excel also thinks 1900 is a leap year
}

$d_year = $year - 1900;
$days = 1; // 1-1-1900 equals 1
$days += $d_year * 365;

$days += floor (($d_year-1) / 4); // compensate for leap-years (note leap day is after the leap year 1-jan)

// go forward (+), based on $month and $date
for ($k = 1; $k < $month; $k++)
{
$days += $m_days [$k - 1];
}
$days += $date;

return $days;
}

[2005-09-07 13:42 UTC] madeinlisboa at yahoo dot com

It seems that the sample in the SetNumFormat page is outputing the date with one hour difference from the original. I guess the patched code looks like this:
// number of seconds in a day
$seconds_in_a_day = 86400;
// Unix timestamp to Excel date difference in seconds
$ut_to_ed_diff = $seconds_in_a_day * 25569 + 3600;

[2005-10-25 21:54 UTC] xnoguer at php dot net

As florent says this is documented for the setNumFormat() method, a date is actually a number (days since December 30 1899) with a particular format. Will consider writing a writeDate() method later to make this simpler.