Excel via php

Posted on 2011-10-26
Last Modified: 2012-05-12
See attached. This code generates an Excel Spreadsheet on a web server. Works beautifully.

Except for a few formatting issues.

Note the column with content <td align="left"><? print $hrmn; ?></td> and the next one. These are hhmm in military time.

The customer wants there to be leading zeros on the hour if the hour is less than 10. I have tried a lot of approaches, but when the sheet is opened in Excel, Excel of course throws out the leading zeros.

I can't see a way to trick Excel into thinking the column is text.

How can I fix this?
Question by:Richard Korts
    LVL 10

    Expert Comment


    <td align="left"><? print $hrmn; ?></td>

    <td align="left"><?php print date("Hi", strtotime($hrmn)); ?> </td>

    There is a space in the last part, this should make excel think its text :)

    Author Comment

    by:Richard Korts

    Sorry, doesn't work, same result.

    I'll try later with &nbsp; instead of space. I think the space gets dropped.
    LVL 82

    Expert Comment

    by:Dave Baldwin
    Put an apostrophe in front of it like below.  Excel through 2007 still takes that to mean 'text follows'.
    <td align="left"><? print "'".$hrmn; ?></td>

    Open in new window


    Author Comment

    by:Richard Korts

    It keeps the leading 0 but it puts the ' in the cell. I'm using 2010, I'll try it in 2003.
    LVL 10

    Expert Comment

    you could always postfix it with "hrs" this would solve the problem
    LVL 107

    Expert Comment

    by:Ray Paseur
    Here is the contents of the file that was attached.  Around line 130 we find this:

          $pu = $r['pickup'];
          $puhr = substr($pu,11,2);
          $hrmn = $puhr . substr($pu,14,2);
          $hrmn = (string)$hrmn;

    Just a guess, but you might try changing that to something like this, inserting a colon between the hours and minutes:

          $pu = $r['pickup'];
          $puhr = substr($pu,11,2);
          $hrmn = "$puhr:" .  substr($pu,14,2);
          $hrmn = (string)$hrmn;

    You might also want to have a look at this article which shows some good ways of dealing with DATETIME values in PHP and MySQL.  For example, if you wanted to add some days to a DATETIME string, you could do it like this:

    $new = date('c', strtotime($old . ' + 5 DAYS'));
    <? header("Content-type: application/");
    $fn = "excel" . date('Y-m-d_h_i') . ".xls";
    header("Content-Disposition: attachment; filename=" . $fn);
    function conv_date($x){
    	$dt = substr($x,6,4) . "-" . substr($x,0,2) . "-" . substr($x,3,2);
    	return $dt;
    function add_days($d, $n) {
    	$day = substr($d,8,2);
    	$nd = $day + $n;
    	$mo = substr($d,5,2);
    	$dl = 31;
    	if ($mo == 4 || $mo == 6 || $mo == 9 || $mo == 11) {
    		$dl = 30;
    	if ($mo == 2) {
    		$dl = 28;
    	if ($nd <= $dl) {	
    		if ($nd < 10) {
    			$nd = "0" . $nd;
    		$rd = substr($d,0,8) . $nd;
    	} else {
    		$nd = 1;
    		if ($mo <= 12) {
    			if ($mo < 10) {
    				$mo = "0" . $mo;
    			$rd = substr($d,0,5) . $mo . "-01";
    		} else {
    			$yr = substr($d,0,4) + 1;
    			$rd = $yr . "-01-01";	
    	return $rd;
    if ($_SESSION['vavusr'] == "") {
    	header ("location: login.php");
    $tdiff = time() - $_SESSION['alast_used'];
    if ($tdiff > 7200) {
    	header ("location: login.php");
    $_SESSION['alast_used'] = time();	
    // set up database
    $Host = "";
    $User = "db1234";
    $Password = "abcxyz";
    $DBName = "db1234";
    $Link = mysql_connect ($Host, $User, $Password);
    $dt1 = conv_date($_POST['sdate']);
    $dt2 = conv_date($_POST['edate']);
    $dt2 = add_days($dt2,1);
    $obcl = "order by ";
    $sb = $_POST['sb'];
    if ( $sb == "D") {
    	$obcl = $obcl . " pickup";
    } else {
    	if ($sb == "N") {
    		$obcl = $obcl . " lname, fname";
    	} else {
    		$obcl = $obcl . " comm";
    // get data
    $qry = "SELECT * from resv where pickup >= '" . $dt1 . "' and pickup < '" . $dt2 . "' " . $obcl;
    //echo $qry . "<br>";
    $res = mysql_db_query ($DBName, $qry, $Link);
    $nr = mysql_num_rows($res);
    // get max length of notes
    $ln = 0;
    for ($i = 0; $i < $nr; $i++) {
    	$r = mysql_fetch_array($res);
    	$ln = max ($ln, strlen($r['vav_notes']));
    $wd = 15 * $ln;
    $res = mysql_db_query ($DBName, $qry, $Link);?>
    	<title>Village Airport Van - Reservations Report</title>
    <style type="text/css">
    	.pt18 {font-size: 18pt; font-family: Arial}
    	.pt14 {font-size: 14pt; font-family: Arial}	
    	.pt12 {font-size: 12pt; font-family: Arial}
    	.pt11b {font-size: 11pt; font-family: Arial; font-weight:bold;}
    	.pt11br {font-size: 11pt; font-family: Arial; font-weight:bold; color:#FF0000;}	
    	.pt10 {font-size: 10pt; font-family: Arial}
    	.pt8  {font-size: 8pt; font-family: Arial}
    	.pt9  {font-size: 9pt; font-family: Arial}	
    	.pt7  {font-size: 7pt; font-family: Arial}
    <table class="pt11b" border="1">
    	<tr style="background-color: #C5E1C5;">
    		<td>First Name</td>	
    		<td>Last Name</td>
    		<td>House #</td>
    		<td>Street Name</td>					
    		<td>Num Pass</td>
    		<td>Conf. #</td>
    <? for ($i = 0; $i < $nr; $i++) { 	
    	$r = mysql_fetch_array($res);
    	$style= "color: black;";
    	if ($r['ad'] == "A") {
    		$style = "color: red;";
    	if ($r['late'] == "Y") {
    		 $style = "background-color: #808080; color: #FFFF00;";
    	$pu = $r['pickup'];
    	$puhr = substr($pu,11,2);
    	$hrmn = $puhr . substr($pu,14,2);
    	$hrmn = (string)$hrmn;
    	$fthr = substr($r['ftime'],11,2);
    	$ftime = $fthr . substr($r['ftime'],14,2);
    	$ts = mktime(substr($pu,11,2), substr($pu,14,2),0,substr($pu,5,2), substr($pu,8,2), substr($pu,0,4));
    	$dt = substr($pu,5,2) . "/" . substr($pu,8,2);
    	$wkdy = date('D', $ts);
    	$ph = $r['phone'];
    	if ($ph == "") {
    		$ph = $r['cell'];
    	$flight = "";
    	if ($r['flight'] != "") {
    		$flight =  "#" . $r['flight'];
    	<tr style="<? print $style; ?>">
    		<td><? print $dt; ?></td>
    		<td><? print $wkdy; ?></td>
    		<td><? print $ph; ?></td>
    		<td align="left"><? print $hrmn; ?></td>	
    		<td align="left"><? print $ftime; ?></td>
    		<td><? print $r['fname']; ?></td>
    		<td><? print $r['lname']; ?></td>
    		<td><? print $r['number']; ?></td>			
    		<td><? print $r['street']; ?></td>
    		<td><? print $r['comm']; ?></td>
    		<td><? print $r['npass']; ?></td>	
    		<td>$<? print $r['feeamt']; ?></td>
    		<td><? print $r['driver']; ?></td>
    		<td><? print $r['confno']; ?></td>
    		<td><? print $r['gate']; ?></td>
    		<td><? print $r['airline']; ?></td>
    		<td><? print $flight; ?></td>	
    		<td width="<? print $wd; ?>"><? print $r['vav_notes']; ?></td>										
    <? } ?>	

    Open in new window

    LVL 10

    Expert Comment

    @Ray, What has this got to do with the problem?

    Author Comment

    by:Richard Korts
    To all,

    Nothing works. The customer DOES NOT want "hrs" to follow the actual time in every case. They prefer it without the leading zero.
    LVL 82

    Accepted Solution

    In another question, we found this about 'mso' formatting.  And this:

    Excel appears to ignore 'normal' CSS but apparently will respond to 'mso' Microsoft Office formatting.  If you do an HTML export from Excel, these styles are what you should see.

    Author Closing Comment

    by:Richard Korts
    That works PERFECTLY. A simple, elegant solution. MS Excel's "private" CSS.
    LVL 82

    Expert Comment

    by:Dave Baldwin
    Glad it helped.  You'll see that kind of stuff in an HTML export from word also.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now