[SOLVED] Formatting in excel
Moderator: General Moderators
-
colmtourque
- Forum Newbie
- Posts: 20
- Joined: Wed Aug 25, 2004 11:59 am
Formatting in excel
I have outputted a mysql query to an excel file. Is there any way to format the file when it is outputted.
-
colmtourque
- Forum Newbie
- Posts: 20
- Joined: Wed Aug 25, 2004 11:59 am
-
ldomingues
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 06, 2004 1:15 pm
- Location: Portugal
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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
-
ldomingues
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 06, 2004 1:15 pm
- Location: Portugal
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.
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.
-
colmtourque
- Forum Newbie
- Posts: 20
- Joined: Wed Aug 25, 2004 11:59 am
feyd | Please use
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]
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";
?>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]-
ldomingues
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 06, 2004 1:15 pm
- Location: Portugal
Try this:
[/quote]
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";
?>-
colmtourque
- Forum Newbie
- Posts: 20
- Joined: Wed Aug 25, 2004 11:59 am
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.
Oh sorry Feyd for not posting the code in a box.
I'll read the Faq and behave.
-
ldomingues
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 06, 2004 1:15 pm
- Location: Portugal
-
colmtourque
- Forum Newbie
- Posts: 20
- Joined: Wed Aug 25, 2004 11:59 am
-
colmtourque
- Forum Newbie
- Posts: 20
- Joined: Wed Aug 25, 2004 11:59 am
-
colmtourque
- Forum Newbie
- Posts: 20
- Joined: Wed Aug 25, 2004 11:59 am
Just in Case this helps anyone
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
and yes I used the code box.

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";
?>