• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Excel via php

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?
report1.php
0
Richard Korts
Asked:
Richard Korts
  • 4
  • 3
  • 3
  • +1
1 Solution
 
effxCommented:
Change:

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

To:
<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 :)
0
 
Richard KortsAuthor Commented:
effx:

Sorry, doesn't work, same result.

I'll try later with &nbsp; instead of space. I think the space gets dropped.
0
 
Dave BaldwinFixer of ProblemsCommented:
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

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Richard KortsAuthor Commented:
DaveBaldwin:

It keeps the leading 0 but it puts the ' in the cell. I'm using 2010, I'll try it in 2003.
0
 
effxCommented:
you could always postfix it with "hrs" this would solve the problem
0
 
Ray PaseurCommented:
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'));

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
<? header("Content-type: application/vnd.ms-excel");
$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;
		$mo++;
		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;
}				
session_start();
if ($_SESSION['vavusr'] == "") {
	header ("location: login.php");
	exit;
}
$tdiff = time() - $_SESSION['alast_used'];
if ($tdiff > 7200) {
	session_destroy();	
	header ("location: login.php");
	exit;
}
$_SESSION['alast_used'] = time();	
// set up database
$Host = "db380492857.db.1and1.com";
$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);?>
<html>
<head>
	<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}
</style>		
</head>
<body>
<table class="pt11b" border="1">
	<tr style="background-color: #C5E1C5;">
		<td>Date</td>
		<td>Day</td>
		<td>Phone</td>
		<td>PUP/SAT</td>
		<td>Depart</td>
		<td>First Name</td>	
		<td>Last Name</td>
		<td>House #</td>
		<td>Street Name</td>					
		<td>Village</td>	
		<td>Num Pass</td>
		<td>$$$</td>
		<td>Driver</td>
		<td>Conf. #</td>
		<td>Gate</td>
		<td>Airline</td>
		<td>Flight</td>	
		<td>Notes</td>				
	</tr>
<? 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>										
	</tr>	
<? } ?>	
</table>


</body>
</html>

Open in new window

0
 
effxCommented:
@Ray, What has this got to do with the problem?
0
 
Richard KortsAuthor Commented:
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.
0
 
Dave BaldwinFixer of ProblemsCommented:
In another question, we found this http://cosicimiento.blogspot.com/2008/11/styling-excel-cells-with-mso-number.html about 'mso' formatting.  And this: http://agoric.com/sources/software/htmltoExcel

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.
0
 
Richard KortsAuthor Commented:
That works PERFECTLY. A simple, elegant solution. MS Excel's "private" CSS.
0
 
Dave BaldwinFixer of ProblemsCommented:
Glad it helped.  You'll see that kind of stuff in an HTML export from word also.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now