[php] exporteren naar excel

[Liqued]

Eerst mijn probleem

Liqued schreef op zondag 11 april 2010 @ 20:31:
Ik loop tegen het volgende probleem aan. Ik open in excel een bestand en in dat bestand staat een kolom met allemaal datums (dd-mm-jjjj). Het probleem is dat excel 2003 deze niet herkend als datum waardoor het geen datumveld wordt. Ook als ik deze "hard" instel dmv rechtermuisknop->celeigneschappen->datum en dan een type aan geef wordt deze niet herkend. Het vreemde is dat als ik in de cel sta en in de formule balk ga sta en achter de datum een enter geef hij deze het wel herkend. Aangezien het om een paar duizend stuks gaat zou ik toch graag een snellere oplossing willen.

Suggesties?

Edit: Ik ben ondertussen iets verder. Als ik het bestand open in OpenOffice dan zie ik dat er een ' voor de datum staat (die zie ik in excel 2003 niet terug).

Nu vermoed ik dat de fout dus in mijn PHP verhaal zit, aangezien ik data vanuit hier exporteer. Om eerst een beeld te geven, dit zijn de functies die ik gebruik.

PHP:

function xlsBOF() { 
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
    return; 
} 

function xlsEOF() { 
    echo pack("ss", 0x0A, 0x00); 
    return; 
} 

function xlsWriteNumber($Row, $Col, $Value) { 
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0); 
    echo pack("d", $Value); 
    return; 
} 

function xlsWriteLabel($Row, $Col, $Value ) { 
    $L = strlen($Value); 
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); 
    echo $Value; 
return; 
}
// Send Header
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");;
    header("Content-Disposition: attachment;filename=export.xls ");
    header("Content-Transfer-Encoding: binary ");

xlsBOF();
$xlsRow = 1;
// een SQL query die ik om het simpel te houden even heb weg gelaten
while($rij =mysql_fetch_object($result)) {
    //LogDatum is al in het nederlands formaat ( dd-mm-jjjj )
    xlsWriteLabel($xlsRow,0,$rij->LogDatum);
    $xlsRow++;
}
xlsEOF();
exit();

Zoals je kunt zien schrijft dit script de excel cellen weg in het "xlsWriteLabel" formaat (xlsWriteNumber is niet de oplossing). Nu vermoed ik dat er voor datavelden een ander packformaat bestaat. Echter kan ik dit nergens vinden. Kan iemand mij hier aan helpen? of me in de goede richting sturen?

[RaZ]

Edit: Ik ben ondertussen iets verder. Als ik het bestand open in OpenOffice dan zie ik dat er een ' voor de datum staat (die zie ik in excel 2003 niet terug).

Dat komt omdat dat teken in Excel wordt gebruikt om aan te geven dat het Tekst is, en niet bijvoorbeeld nummeriek waar de cel-eigenschappen op kunnen staan.

Zo voorkom je dat Excel bijvoorbeeld gaat rekenen, omzetten naar datum/tijd of de formule uitvoert.

Die je in je cel dus niet, maar bovenin, bij de formule zie je die wel staan.

Edit: En uit je andere - inmiddels gesloten topic - wil je dus juist wel andere cel-eigenschappen. Je kan een simpele search/replace uitvoeren op een kolom, en daar dat teken kompleet weghalen.

[ Voor 15% gewijzigd door RaZ op 11-04-2010 22:07 ]

[Liqued]

RaZ schreef op zondag 11 april 2010 @ 22:05:
[...]

Dat komt omdat dat teken in Excel wordt gebruikt om aan te geven dat het Tekst is, en niet bijvoorbeeld nummeriek waar de cel-eigenschappen op kunnen staan.

Zo voorkom je dat Excel bijvoorbeeld gaat rekenen, omzetten naar datum/tijd of de formule uitvoert.

Die je in je cel dus niet, maar bovenin, bij de formule zie je die wel staan.

Dat vermoeden had ik al. Goed om te weten dat dit ook het probleem is. Hoe kan ik dit nu oplossen?

[Janoz]

Misschien is dit wat? http://www.tek-tips.com/v...d.cfm?qid=1429281&page=10

[Kalentum]

Ik gebruik de notatie YYYY-MM-DD om een datum weg te schrijven naar een Excel. Ik heb net even geprobeerd om te rekenen in Excel met data in dat formaat en dat werkt.

[mtsr]

Voorzover ik weet gebruikt excel in de opslag geen geformatteerde datums (incorrect, maar leesbaar, meervoud van datum) in datumvelden, maar het aantal dagen sinds 0 januari 1900. Deze standaard is overgenomen van lotus-123 en bevat bovendien ook nog een fout (er wordt een schrikkeljaar teveel gerekend, waardoor alle datums vanaf dat punt met 1 afwijken) die ook al in lotus zat, ivm de compatibiliteit.

Dit betreft overigens het oude xls format, de nieuwe versie ken ik niet.

[stappel_]

PHP:

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

    if (($year < 100) and ($year > 0)) $year += 1900;
    if ($year < 1900) return 0;
    if ($month > 12) return 0;
    if ($day > 31) 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 $day
    for ($k = 1; $k < $month; $k++)
    {
        $days += $m_days [$k - 1];
    }
    $days += $day;

    return $days;
}

[mtsr]

stappel_ schreef op maandag 12 april 2010 @ 14:03:
<snip>

Dat is 'em, inderdaad.

[Janoz]

Om eerlijk te zijn vind ik die code nogal brak eigenlijk. Bovenin staat iets over de range waarin hij werkt, maar verderop lijkt het als hij ook met 2400 om zou kunnen gaan. Verderop echter blijkt weer dat hij inderdaad mis gaat lopen na het schrikkeljaar 2100, en alsof dat nog niet alles is, gaat dat stilletjes. Geen enkele indicatie dat de waarde die je terug krijgt na 2100 niet klopt.

Nog los van het feit dat ik 0 een beetje een vreemde error indicator vindt, maar aan de andere kant lever deze functie eigenlijk altijd een waarde van 1 of hoger op waardoor dat door de vingers gezien kan worden (en in php zo lekker makkelijk naar false wordt ge-evalueerd)

[mtsr]

Tsja, misschien werkt de code niet voor gevallen buiten de aangegeven range, maar het is op basis van deze code wel precies duidelijk wat er moet gebeuren. Uitbreiden/herschrijven is een eitje.

Wat betreft de 0, dat lijkt me een redelijk logische error indicator, een 0 heeft in dit geval namelijk geen waarde, omdat het aantal dagen sinds 0 januari 1900 wordt geteld. Je zou deze ook door false kunnen vervangen als je wil, dan heb je wel de toegevoegde mogelijkheid om === te gebruiken.