Using PHP to create Excel XML

[klb]

i am using a php script to generate excel xml. everything works fine as long as the datatype is "String", but nothing quite works right when i switch the datatype to "DateTime". the best i can get is to show a decimal value in the fields i have set to "DateTime", not the standard user-friendly excel display (eg "10:39:00") . i also am trying to write simple formulas to subtract time but have not had any success. the exported excel is useless without these two features and is not any better than a simple tab delimited text file. the whole point of the file is so that someone can view the results online, download the file as an excel spreadsheet, make changes to the data and have the formulas update the totals accordingly. playing with the DateTime format leaves my code looking something like this:

$tmp = '2007-11-01T12:31:00.000';
$xml .= '                <ss:Cell>
                    <ss:Data ss:Type="DateTime">'.$tmp.'</ss:Data>
                </ss:Cell>'."\n";
 

i would really like it to just display the time but the excel xml crashes every time i try to make the datatype "Time".

my first crack at the formula looks something like this:

<Cell ss:Formula="=ABS(C4-C3)+ABS(C2-C1)"/>

but i have a sneaking suspicion i'm going about that all wrong. i've been hunting down tutorials all afternoon and can't seem to find a concise answer to my two problems. any help would be greatly appreciated. i can go into more detail or give further examples if needed. thanks in advance.

[jkmyoung]

How Excel does it when saving to xml:
Declare a style for Time.

<Style ss:ID="s22">
   <NumberFormat ss:Format="[$-F400]h:mm:ss\ AM/PM"/>
</Style>

Add that style to the cell, eg

<Cell ss:StyleID="s22"><Data ss:Type="DateTime">2007-11-01T12:31:00.000</Data></Cell>

[klb]

thanks for the help, that did the trick. now i'm stuck on the formulas part and can't seem to find a good tutorial for writing the formulas i want. my spreadsheet will look something like this:

Day    In    Out    In    Out    Lunch    Hrs/%    Hrs/Time    Wk Hrs    Wk OT
1/1/07    7:30am    12:00pm    1:00pm    4:30pm    :45    8.0    8:00        
1/2/07    8:00am    12:00pm    12:30pm    5:00pm    :30    8.5    8:30        16.00    .5
 

i need to subtract the first "out" from the second "in" and display as time, using a formula so the dates can be changed. i also need to calc the total hours worked per day and per week, and a grand total at the bottom. the kicker is that there could potentially be more clocks than just four in a pay period, so i would have to adjust the forumlas at the time i create the file. i can do this all fine and good in php but am having a little trouble wrapping it up in excel. thanks in advance for any help.

[klb]

ok, as it turns out i've somewhat figured out how to work formulas, which is a bonus. now my main problems are back to formatting. for the lunch, i need it in H:MM format, and can't seem to get it formatted properly. if i open up the spreadsheet and reformat the decimal values or datetime values that are there, i get the correct result, so i know the formula is doing its job, it's just my formatting that is incorrect. i also need decimal values for total hours worked, eg 8.25 hours for 8:15 hours worked. i've been digging all over the web and can't seem to find a concise answer; if anyone can point me to such a tutorial or reference, i would greatly appreciate it. i'm so close to the end of this project i can taste it!

[jkmyoung]

Style for short time is like so 10:33

<Style ss:ID="s24">
 <NumberFormat ss:Format="Short Time"/>
</Style>

Medium time: 10:33 PM

<Style ss:ID="s25">
 <NumberFormat ss:Format="Medium Time"/>
</Style>

I'm guessing you figured out how to use the relative formulas to calculate the lunch time, eg like: <Cell ss:StyleID="s24 " ss:Formula="=RC[-2]-RC[-3]">

In order to convert hours to decimal format, multiply by 24 (we're counting hours, not days), and set it to the default format, eg like.

<Style ss:ID="s26">
   <NumberFormat/>
</Style>