Formatting in excel
Posted: Fri Sep 17, 2004 9:45 am
I have outputted a mysql query to an excel file. Is there any way to format the file when it is outputted.
A community of PHP developers offering assistance, advice, discussion, and friendship.
http://forums.devnetwork.net/
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";
?>Code: Select all
tags when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]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";
?>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";
?>