PHPhulp.nl PHPhulp.nl
35k+ leden Over PHPhulp Offerte opdracht Contact
S

phpExcelReader date

Door Slaper op 22-03-2009 13:56
1.062 views
Hallo,
Volgende script gebruik ik voor het lezen uit een excel sheet. Alles werkt goed maar als ik de laatste dag van de maand leest dan krijgt ik niet de juist datum. Moet normaal 2009-01-31 23:59:59 zijn maar ik krijg het volgende "2009-2-0 23:59:59" , dus iedere laatse datum van de maand is verkeerd

in de excel file staat er eerste kolom de datum en in de tweede de waarden

Weet er iemand raad?

<?php
// Test CVS

require_once 'phpExcelReader/Excel/reader.php';


// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();


// Set output Encoding.
$data->setOutputEncoding('CP1251');

/***
* if you want you can change 'iconv' to mb_convert_encoding:
* $data->setUTFEncoder('mb');
*
**/

/***
* By default rows & cols indeces start with 1
* For change initial index use:
* $data->setRowColOffset(0);
*
**/



/***
* Some function for formatting output.
* $data->setDefaultFormat('%.2f');
* setDefaultFormat - set format for columns with unknown formatting
*
* $data->setColumnFormat(4, '%.3f');
* setColumnFormat - set format for column (apply only to number fields)
*
**/

$data->read('phpExcelReader/SDT_09.xls');



/*
$data->sheets[0]['numRows'] //- count rows
$data->sheets[0]['numCols'] //- count columns
$data->sheets[0]['cells'][$i][$j] //- data from $i-row $j-column

$data->sheets[0]['cellsInfo'][$i][$j] //- extended info about cell

$data->sheets[0]['cellsInfo'][$i][$j]['type'] = "date" | "number" | "unknown"
if 'type' == "unknown" - use 'raw' value, because cell contain value with format '0.00';
$data->sheets[0]['cellsInfo'][$i][$j]['raw'] = value if cell without format
$data->sheets[0]['cellsInfo'][$i][$j]['colspan']
$data->sheets[0]['cellsInfo'][$i][$j]['rowspan']
*/

error_reporting(E_ALL ^ E_NOTICE);

for ($i = 8; $i <= $data->sheets[0]['numRows']; $i++) {
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
if ($j==1){
//echo $data->sheets[0]['cellsInfo'][$i][$j]['type']="date";
echo "\"".excel2mysql_date($data->sheets[0]['cells'][$i][$j])."\",";
//echo "\"".xl2timestamp($data->sheets[0]['cells'][$i][$j])."\",";
}
else{
echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";echo $i.$j;
}
}
echo "<br>";//"\n";

}


//print_r($data);
//print_r($data->formatRecords);
?>
<?php
function excel2mysql_date($days)
{
// extract the decimal part and calculate time
$hour_frac=($days-floor($days))*24;
$hour=floor($hour_frac);

$minute_frac=($hour_frac-floor($hour_frac))*60;

$minute=floor($minute_frac);

$seconds=round(($minute_frac-floor($minute_frac))*60);

if ($days <1) return "";
if ($days == 60)
{
return array('day'=>29,'month'=>2,'year'=>1900);
}
else
{
if ($days < 60)
{
// Because of the 29-02-1900 bug, any serial date
// under 60 is one off... Compensate.
++$days;
}
// Modified Julian to DMY calculation with an addition of 2415019
$l = $days + 68569 + 2415019;
$n = floor(( 4 * $l ) / 146097);
$l = $l - floor(( 146097 * $n + 3 ) / 4);
$i = floor(( 4000 * ( $l + 1 ) ) / 1461001);
$l = $l - floor(( 1461 * $i ) / 4) + 31;
$j = floor(( 80 * $l ) / 2447);
$nDay = $l - floor(( 2447 * $j ) / 80);
$l = floor($j / 11);
$nMonth = $j + 2 - ( 12 * $l );
$nYear = 100 * ( $n - 49 ) + $i + $l;

$ret = $nYear."-".$nMonth."-".floor($nDay)." ".$hour.":".$minute.":".$seconds;
return $ret;
}
}


?>
E
Ed
22-03-2009 14:30
Als gekeken of je input wel klopt. Want ik zie in je script heel wat rounds, floors, en zelfs iets als ++$days.
S
Slaper
22-03-2009 20:00
Heb nog eens goed gekeken, het jaar 1900 was geen schrikkeljaar, dus moest 2415019 en som moet een integer zijn en 0.5 bij telen op de dag en integer ervan nemen, nu werkt alles goed
 
<?php
function excel2mysql_date($days)
    {
        // extract the decimal part and calculate time
        $hour_frac=($days-floor($days))*24;
        $hour=floor($hour_frac);

        $minute_frac=($hour_frac-floor($hour_frac))*60;
        
        $minute=floor($minute_frac);
        
        $seconds=round(($minute_frac-floor($minute_frac))*60);

        if ($days <1) return "";
        if ($days == 60)
        {
            return array('day'=>29,'month'=>2,'year'=>1900);
           }
           else
           {
            if ($days < 60)
              {
                 // Because of the 29-02-1900 bug, any serial date
                 // under 60 is one off... Compensate.
                 ++$days;
              }
               // Modified Julian to DMY calculation with an addition of 2415019
             //echo $days."<br>";  
             $l = floor($days + 68569 + 2415019);//echo $l."<br>";
             $n = floor(( 4 * $l ) / 146097);//echo $n."<br>";
             $l = $l - floor(( 146097 * $n + 3 ) / 4);//echo $l."<br>";
             $i = floor(( 4000 * ( $l + 1 ) ) / 1461001);//echo $i."<br>";
             $l = $l - floor(( 1461 * $i ) / 4) + 31;//echo $l."<br>";
             $j = floor(( 80 * $l ) / 2447);//echo $j."<br>";
             $nDay = floor(0.5+($l - floor(( 2447 * $j ) / 80)));//echo $nDay."<br>";
             $l = floor($j / 11);
             $nMonth = $j + 2 - ( 12 * $l );
             $nYear = 100 * ( $n - 49 ) + $i + $l;
             
             $ret = $nYear."-".$nMonth."-".floor($nDay)." ".$hour.":".$minute.":".$seconds;
            return $ret;
        }
    } 
    

?>
P
Pepijn
22-03-2009 20:03
Zou je deze functie even tussen [*code*] en [/*code*] tags kunnen zetten, zonder sterretjes.

Reageren

Inloggen om te reageren