Page 1 of 1

Formatting in excel

Posted: Fri Sep 17, 2004 9:45 am
by colmtourque
I have outputted a mysql query to an excel file. Is there any way to format the file when it is outputted.

Posted: Fri Sep 17, 2004 9:52 am
by pickle
In all the Excel creating classes I've seen, they all have the ability to format the contents of cells, just like you would in Excel. I've also never seen one that works 100% of the time :?

Posted: Fri Sep 17, 2004 10:11 am
by colmtourque
Sorry, I must not have been clear, I want the formatting done by the code, so the user gets a completed product and does not have to do any formatting.

Posted: Fri Sep 17, 2004 10:14 am
by ldomingues
If you output an HTML table, using FONT, BGCOLOR, etc. formatting Excel will use those formats for cells.

Posted: Fri Sep 17, 2004 10:39 am
by pickle
I must not have been clear either :). The classes themselves have the ability to format the worksheets, so you can format the cells before dumping the spreadsheet to the browser. PEAR has a class for making Excel spreadsheets, but like I said, it doesn't always work - sometimes it just won't dump to the browser.

Posted: Fri Sep 17, 2004 1:24 pm
by ldomingues
Me and Pickle are talking of two different ways of sending Excel to the browser.

Pickle's choice is to use Pear classes to generate an Excel file. I've never tried those, but it should have more features than the method I usually use.

My choice is to simply output an HTML table, with HTML formatting. Outputing this with the correct headers (xls file), Excel will open the table as if it was an Excel file and will import most of the HTML formatting applied to the table.

Posted: Mon Sep 20, 2004 1:06 pm
by colmtourque
feyd | Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


The PEAR class did not work. Actually I'm not even sure I was even able to get PEAR to work (but that is another post).

I'm using the standard PHP manual code:

Code: Select all

<?php
include  "../incfiles/connect.inc";

header('Content-type: application/vnd.ms-excel');

$select = "SELECT security.idnumber, security.lastname, security.firstname, ipahometable.policynumber, ipahometable.insuredfn, ipahometable.insuredln, DATE_FORMAT(ipahometable.distoagentdate, '%c/%e/%Y') AS formatdad, DATE_FORMAT(ipahometable.ssrandate, '%c/%e/ %Y') AS formatssrd, DATE_FORMAT(ipahometable.plrandate, '%c/%e/ %Y') AS formatplrd, DATE_FORMAT(ipahometable.propldate, '%c/%e/ %Y') AS formatproprd, DATE_FORMAT(ipahometable.dspresdate, '%c/%e/ %Y') AS formatdspd, DATE_FORMAT(ipahometable.ysdate, '%c/%e/ %Y') AS formatysd, DATE_FORMAT(ipahometable.npdate, '%c/%e/ %Y') AS formatnpd, DATE_FORMAT(ipahometable.newdsdate, '%c/%e/ %Y') AS formatnewd, DATE_FORMAT(ipahometable.npdsdate, '%c/%e/ %Y') AS formatnpdsd, ipahometable.notes FROM (ipahometable INNER JOIN linktable ON ipahometable.policynumber = linktable.policynumber) INNER JOIN security ON linktable.agentid = security.idnumber WHERE ((($search) LIKE '%$svalue%')) ORDER BY $order";                
$export = mysql_query($select) or die("Query failed : " . mysql_error());
$count = mysql_num_fields($export);

for ($i = 0; $i < $count; $i++) 
	{    
	$header .= "<strong>".mysql_field_name($export, $i)."</strong>\t";
	}

while ($row = mysql_fetch_row($export)) 
	{    
	$line = '';    
	foreach($row as $value) 
		{ 
		if ((!isset($value)) OR ($value == "")) 
			{            
			$value = "\t";        
			} 
			else 
				{            
				$value = str_replace('"', '""', $value);            
				$value = '"' . $value . '"' . "\t";        
				}        
			$line .= $value;    
		}    
		$data .= trim($line)."\n";
	}
	$data = str_replace("\r", "", $data);

if ($data == "") 
	{    
	$data = "\n(0) Records Found!\n";
	}

header
("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=spreadsheet.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";	

?>
It outputs itself in excel and I've tried adding simple html such as font color and such into the code, and that has all failed. How would I reformat as a table in html?


feyd | Please use

Code: Select all

tags when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Mon Sep 20, 2004 1:24 pm
by ldomingues
Try this:

Code: Select all

<?php
	include  "../incfiles/connect.inc";
	header('Content-type: application/vnd.ms-excel');
	$select = "SELECT security.idnumber, security.lastname, security.firstname, ipahometable.policynumber, ipahometable.insuredfn, ipahometable.insuredln, DATE_FORMAT(ipahometable.distoagentdate, '%c/%e/%Y') AS formatdad, DATE_FORMAT(ipahometable.ssrandate, '%c/%e/ %Y') AS formatssrd, DATE_FORMAT(ipahometable.plrandate, '%c/%e/ %Y') AS formatplrd, DATE_FORMAT(ipahometable.propldate, '%c/%e/ %Y') AS formatproprd, DATE_FORMAT(ipahometable.dspresdate, '%c/%e/ %Y') AS formatdspd, DATE_FORMAT(ipahometable.ysdate, '%c/%e/ %Y') AS formatysd, DATE_FORMAT(ipahometable.npdate, '%c/%e/ %Y') AS formatnpd, DATE_FORMAT(ipahometable.newdsdate, '%c/%e/ %Y') AS formatnewd, DATE_FORMAT(ipahometable.npdsdate, '%c/%e/ %Y') AS formatnpdsd, ipahometable.notes FROM (ipahometable INNER JOIN linktable ON ipahometable.policynumber = linktable.policynumber) INNER JOIN security ON linktable.agentid = security.idnumber WHERE ((($search) LIKE '%$svalue%')) ORDER BY $order";
	$export = mysql_query($select) or die("Query failed : " . mysql_error());
	$count = mysql_num_fields($export);

	$header="<table><tr>"; // newline 

	for ($i = 0; $i < $count; $i++)     
	{        
		$header .= "<td><strong>".mysql_field_name($export, $i)."</strong></td>"; // changed
	}
	
	$header="</tr>"; // newline 

	while ($row = mysql_fetch_row($export))     
	{        
		$line = '<tr>'; // newline
		foreach($row as $value)         
		{         
			if ((!isset($value)) OR ($value == ""))             
			{                        
				$value = "<td></td>";  // changed
			}
			else
			{                            
				$value = str_replace('"', '""', $value);
				$value = '<td>' . $value . '"' . "</td>";  // changed
                        }
			$line .= $value;
		}
		$data .= trim($line). "</tr>"; // changed
	}
	//$data = str_replace("\r", "", $data); // removed

	if ($data == "")     
	{
		$data = "<td>(0) Records Found!<td>";
	}
	$data.="</table>"; // newline
	header("Content-type: application/octet-stream");
	header("Content-Disposition: attachment;filename=spreadsheet.xls");
	header("Pragma: no-cache");
	header("Expires: 0");
	print "$header\n$data";
    ?>
[/quote]

Posted: Mon Sep 20, 2004 1:50 pm
by colmtourque
Will do.

Posted: Wed Sep 22, 2004 8:49 am
by colmtourque
The data still came out as an excel file, but I tried to for instance add in background color=blue and it did not come out as blue. I also tried adding in <font color> etc... dif html tags, and css nothing seems to work. Any other thoughts?

Oh sorry Feyd for not posting the code in a box.

I'll read the Faq and behave.

Posted: Wed Sep 22, 2004 9:26 am
by ldomingues
It works for me.

I've created a file with this code:
<table>
<tr>
<td><font color="red">123</td>
</tr>
</table>

And saved with html extension. When I open it in Excel it is correctly formated.

I'm using Excel 2002 SP3

Posted: Wed Sep 22, 2004 10:55 am
by colmtourque
It comes out with the html in the cells. Sorry I just expanded some cells and realized that. Strange.

Posted: Wed Sep 22, 2004 10:59 am
by colmtourque
Figured it out

had to make a change to your code

Line 15 header .=

Thanks!!!

Still don't know why it was acting so wierd on some of the simple font commands but it is formatting now.

Only thing I have to do next is figure out what to do with the actual formatting...FUN!!!

Again thanks!!!

Just in Case this helps anyone

Posted: Wed Sep 22, 2004 12:03 pm
by colmtourque
I did some pretty large changes so I could work with a notes field This spits out the information as formatted and crazy as you wanna get

Code: Select all

<?php
include  "../incfiles/connect.inc";    

header('Content-type: application/vnd.ms-excel');    

$select = "SELECT security.idnumber AS 'ID Number', security.lastname AS 'Last Name', security.firstname AS 'First Name', ipahometable.policynumber AS 'Policy Number', ipahometable.insuredfn AS 'Insured FN', ipahometable.insuredln AS 'Insured LN', DATE_FORMAT(ipahometable.distoagentdate, '%c/%e/%Y') AS 'Distributed Date', DATE_FORMAT(ipahometable.ssrandate, '%c/%e/ %Y') AS 'SS Ran Date', DATE_FORMAT(ipahometable.plrandate, '%c/%e/ %Y') AS 'Pres L Ran Date', DATE_FORMAT(ipahometable.propldate, '%c/%e/ %Y') AS 'Prop L Ran Date', DATE_FORMAT(ipahometable.dspresdate, '%c/%e/ %Y') AS 'DS Ran Date', DATE_FORMAT(ipahometable.ysdate, '%c/%e/ %Y') AS 'NI Date', DATE_FORMAT(ipahometable.npdate, '%c/%e/ %Y') AS 'NP Date', DATE_FORMAT(ipahometable.newdsdate, '%c/%e/ %Y') AS 'New DS Date', DATE_FORMAT(ipahometable.npdsdate, '%c/%e/ %Y') AS 'New Prism Date', ipahometable.notes FROM (ipahometable INNER JOIN linktable ON ipahometable.policynumber = linktable.policynumber) INNER JOIN security ON linktable.agentid = security.idnumber WHERE ((($search) LIKE '%$svalue%')) ORDER BY $order";  
  
$export = mysql_query($select) or die("Query failed : " . mysql_error());    

$count = mysql_num_fields($export);   
 
$header="<table border=2><tr>"; // newline   
  
for ($i = 0; $i < ($count-1); $i++)         
{                
$header .= "<td bgcolor=red style='border-color: Green; font-style: oblique; font-variant: small-caps; font-family: cursive;'><strong>".mysql_field_name($export, $i)."</strong></td>"; // changed   
}    
    
$header.="</tr>"; // newline    
 
while ($myrow = mysql_fetch_array($export)) 
		{
		$data .= "<tr style='font: normal normal smaller 'Times New Roman', Times, serif;'>";
		$data .= "<td>".$myrow["ID Number"]."</td>"."<td>".$myrow["Last Name"]."</td>";
		$data .= "<td>".$myrow["First Name"]."</td><td>";
		$data .= $myrow["Policy Number"]."</td><td>".$myrow["Insured LN"].",".$myrow["Insured FN"]."</td>";
		$data .= "<td>".$myrow["Distributed Date"]."</td>";
		$data .= "<td>".$myrow["SS Ran Date"]."</td><td>".$myrow["Pres L Ran Date"]."</td>";
		$data .= "<td>".$myrow["Prop L Ran Date"]."</td><td>".$myrow["DS Ran Date"]."</td>";
		$data .= "<td>".$myrow["NI Date"]."</td><td>".$myrow["NP Date"]."</td>";
		$data .= "<td>".$myrow["New DS Date"]."</td><td>".$myrow["New Prism Date"]."</td>";	
		$data .= "</tr>";
		$data .= "<tr style='font: normal normal smaller 'Times New Roman', Times, serif;'><td colspan=15>".$myrow["notes"]."<P></td>";
		}
		echo "</table>"; 
header("Content-type: application/octet-stream");    
header("Content-Disposition: attachment;filename=spreadsheet.xls");    
header("Pragma: no-cache");    
header("Expires: 0");    
print "$header\n$data";    

?>
and yes I used the code box.
;)