...

View Full Version : converting PHP records to excel file..



darkcrimson
07-06-2005, 04:01 AM
I have preview page that shows simple details of properties. you can see address, price etc.. There is a sort option on this page where you for example select records 1,3,12,10 and click a button and all of records that are not selected disappear. If you click details link in depth details are listed.

I need to find a way to convert these records from the PHP DB to an excel file.

I found a script that will convert the entire table to a excel file but I need to find a method that will only convert certain records (the ones that are selected)

here is that code, I figure it might be a good place to start:


<?php
//Written by Dan Zarrella. Some additional tweaks provided by JP Honeywell
//pear excel package has support for fonts and formulas etc.. more complicated
//this is good for quick table dumps (deliverables)

include('DB_connection.php');
$result = mysql_query('select * from excel_test', $linkID);
$count = mysql_num_fields($result);

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

while($row = mysql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
}else{
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
$data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
$data = "\nno matching records found\n";
}

# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data;
?>


any ideas? :confused:

Spookster
07-06-2005, 07:19 AM
If you only want certain records exported then redefine the SQL statement to restrict the number of records based on the conditions you want



query = "SELECT * from excel_test WHERE column1value = 'foo' AND column2value LIKE 'bar'"

$result = mysql_query($query, $linkID);

chump2877
07-06-2005, 07:30 AM
My initial idea is to take all of your selected records, create a new (possibly temporary) table, insert the values from the selected records into your new table, and convert the table to an Excel file (using your script)...then you can either choose to keep the new table, truncate it, or drop it, depending on what you're trying to do....

In other words, you can still use your script the way it is ---assuming that it works.....And I'm looking at the script: Does this script take your information and attach or embed the info as an Excel spreadsheet in an e-mail message? If it does, that's pretty cool...Also, is this the entire script? ....And, you say this actually works?....just curious....:D


Edit: Modify the query like Spookster said....but I'd still like to know exactly what this script does when it's run..

darkcrimson
07-07-2005, 03:25 AM
The page I have DOES work currently, but it does not do the excel stuff yet.

you can see the page the way it is now @ http://www.lenderrecovery.com/list.php?contract_num=2

the code for that page is below:

<?php

// session check
//session_start();
//if (!session_is_registered("SESSION")) {
// if session check fails, invoke error handler
//header("Location: error.php?e=2");
//exit();
//}

// search.phtml
include ("common5.php");
include ("functions.php");
include ("foreclosure_listing_header.php");

//$stmt = "SELECT * FROM fuser WHERE email = '".$_SESSION['SESSION_UNAME']."' AND selected_counties like '%$county%'";

if (!($dblink = mysql_pconnect($host, $user, $pass)))
{
print("Error connecting to host<br>");
print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
exit();
}

if (!mysql_select_db($db, $dblink))
{
print("Error selecting database<br>");
print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
exit();
}

// execute the statement


//if (!$result = mysql_query($stmt, $dblink))
//{
//print("Error verifying your identity<br>");
//print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
// exit();
//}

//if (mysql_num_rows($result) < 1)
//{
// print("You aren't allowed to view this county.<br>");
// print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
// exit();
//}

$stmt = "SELECT ID,address,city,state,county,units,bedrooms,baths,assessed_value,contract_num FROM portfolio WHERE contract_num = '".$contract_num."'";

$list = '';
if(!empty($_POST['kc']) && is_array($_POST['kc'])) {
foreach($_POST['kc'] as $kc) {
$kc = (int)$kc;
if($kc > 0) {
$list .= "'$kc','$kc'";
}
}
$stmt .= " AND ID IN ($list)";
}

if ($sortby==1)
$stmt .= " ORDER BY address";

elseif ($sortby==2)

$stmt .= " ORDER BY city";

elseif ($sortby==3)

$stmt .= " ORDER BY state";

elseif ($sortby==4)

$stmt .= " ORDER BY county";

elseif ($sortby==5)

$stmt .= " ORDER BY units";

elseif ($sortby==6)

$stmt .= " ORDER BY bedrooms";

elseif ($sortby==7)

$stmt .= " ORDER BY baths";

elseif ($sortby==8)

$stmt .= " ORDER BY assessed_value";

else

$stmt .= " ORDER BY address";

if (!($dblink = mysql_pconnect($host, $user, $pass))) {
print("Error connecting to host ");
print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
exit();
}

if (!mysql_select_db($db, $dblink)) {
print("Error selecting database ");
print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
exit();
}

?><title>Portfolio Listings</title>

<style type="text/css">
<!--
.style1 {
font-family: Verdana, Arial, Helvetica, sans-serif;
font-size: 12px;
}
-->
</style>
<table width="690" cellspacing="0" cellpadding="3" border="0" style="font-family: sans-serif; font-size: 9pt" align="center">
<!--DWLayoutTable-->
<tr>
<td width="31" height="21" valign="top"><div align="center"><strong>View</strong></div>
</td>
<td width="139" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=1" class="content">Address</a></div>
</td>
<td width="111" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=2" class="content">City</a></div>
</td>
<td width="28" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=3" class="content">State</a></div>
</td>
<td width="100" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=4" class="content">County</a></div>
</td>
<td width="35" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=5" class="content">Units</a></div>
</td>
<td width="58" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=6" class="content">Bedrooms</a></div>
</td>
<td width="32" align="center" valign="top" nowrap><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=7" class="content">Baths</a></div>
</td>
<td width="102" valign="top"><div align="center"><a href="list.php?contract_num=<?echo $contract_num?>&sortby=8" class="content">Assessed
Value</a></div>
</td>
<td width="102" valign="top">
</form>
<form name='filter' action='' method='post'>
<input type='submit' name='submit' value='filter' ></td> </tr>





<?// execute the statement

if (!$result = mysql_query($stmt, $dblink)) {
print("There was and error in the query ".mysql_error());
print("<input type=\"button\" value=\"Back\" onClick=\"self.history.back();\">");
exit();
} else {
while ($row = mysql_fetch_array($result)) {
$row_color = altBgColor();
print ("<tr bgcolor=\"".$row_color."\">\n");
print "\n<td align=\"left\" nowrap>
<a href=\"show_property.php?id=
".$row["ID"]."\">Details</a></td>";
print "\n<td align=\"left\">".$row["address"]."</td>";
print "\n<td align=\"left\">".$row["city"]."</td>";
print "\n<td align=\"left\">".$row["state"]."</td>";
print "\n<td align=\"center\">".$row["county"]."</td>";
print "\n<td align=\"left\">".$row["units"]."</td>";
print "\n<td align=\"left\">".$row["bedrooms"]."</td>";
print "\n<td align=\"left\"nowrap>".$row["baths"]."</td>";
print "\n<td align=\"left\"nowrap>".$row["assessed_value"]."</td>";
print "\n<td align=\"left\"nowrap><input type='checkbox' name='kc[]' value='{$row['ID']}' /></td>";
print "</tr>";
}
}
?>
</table>


The code has been medified a bit but I still am having problems getting it working and I am not quite sure where to put it in my working page code.

the modifications are below: (this does not work yet, thats where I need help)

function xls_format_row( $field ) {
//-- $field is array containing columns values of single row of xls document --//
//-- the code below was wrriten by Dan --//
$line = '';
foreach($field as $value){
if(!isset($value) || $value == "") $value = "\t";
else{
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
return trim($line)."\n";
}


function xls_send( $headers, $rows ) {
//-- http headers were implemented by Dan--//
# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $headers."\n".$rows;
}


if(! isset( $_POST[ 'selections' ])) {
include('DB_connection.php');

//-- retrive records
$result = mysql_query('select * from excel_test', $linkID);
$count = mysql_num_fields($result);

//-- retrive column names
$headers= '';
for ($i = 0; $i < $count; $i++)
$headers .= mysql_field_name($result, $i)."\t";

if( isset( $_POST['cache'] )) echo 'You\'ll need to select something...';

$i = 0;
echo '<form method="post">';
while($row = mysql_fetch_row($result)) {
echo '<input type="Checkbox" name="selections[]" value="'. $i++ .'" >' . implode ( ' ', $row ) . '<br>';

//-- my idea is also to cache cache the retrived data so that we will not have
//-- to connect to db again... it should speed up the script when few checkboxes
//-- of large collection will be selected
//-- one can also use mysql_query once again after posting the form

//-- i need to use htmlspecialchars because quotation marks are treated as
//-- html code that ends attibute "value" of input
echo '<input type="Hidden" name="cache[]" value="'. htmlspecialchars( serialize($row) ) .'">';
}
//-- we can also cache columns' headers
echo '<input type="Hidden" name="headers" value="'. htmlspecialchars( serialize($headers) ) .'">';
echo '<input type="submit" value="Produce xls format" >';
echo '</form>';
} else {
$cache = $_POST['cache'];
$selections = $_POST['selections'];
$selected_from_cache = array();

//-- retrive values from cache --//
foreach( $selections as $row_id ) $selected_from_cache[] = unserialize( $cache[ $row_id ] );

//-- retrive xls columns'headers from cache --//
$headers = unserialize( $_POST['headers'] );

//-- format data xls style --//
$xls_rows = '';
foreach( $selected_from_cache as $row )
$xls_rows .= xls_format_row( $row );

//-- send prepared data to client --//
xls_send( $headers, $xls_rows );
}

Any Ideas?

darkcrimson
07-07-2005, 02:40 PM
anyone have any ideas?

Spookster
07-07-2005, 05:06 PM
Well like I said previously if you want only certain records then modify your query. Currently your query is hardcoded to this:



$result = mysql_query('select * from excel_test', $linkID);


That is going to select all records from your database. If you only want certain records then define the SQL statement more.

darkcrimson
07-07-2005, 05:12 PM
well my problem right now is I cannot get the data to actually go in the excel file, it just says "no matches found", first I'd like to get the data to show up then I will refine it.

I guess I am stuck on what I need to change for the generic code to fit my needs, I am not very versed in PHP so I am not sure what parts of that code are variables that need to be changed to my data.. obviously I know I need to change the dbconnection file and I think I need to change the from_excel_test to my table name which is portfolio but what else has to be changed?

And in this line of code you posted from the script, what is $linkID and do I need to change this to a variable of my own?

darkcrimson
07-07-2005, 06:20 PM
Also-- I found that if I remove $linkID, the data WILL work with excel, what is $linkID for?

also any one have a clue how to only show certain fields?

basspower
07-21-2005, 12:45 PM
i have this script:


<?php

$timestart = microtime();
set_time_limit(900);

$server = "127.0.0.1";
$username = "usern";
$password = "pass";
$dbname = "Intranet";

$db = mssql_connect($server,$username,$password);
mssql_select_db($dbname,$db);

# begin of script
$query = "SELECT * FROM opzoek_list WHERE cust_number LIKE '100001%'";
$result = mssql_query($query,$db);

$count = mssql_num_fields($result);
echo "Downloading $count fields";

for ($i = 0; $i < $count; $i++){
$header .= mssql_field_name($result, $i)."\t";
}

while($row = mssql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
}else{
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
$data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
$data = "\nno matching records found\n";
}

# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data;

# end of script


When i execute i get this error:
Notice: Undefined variable: header in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 21

Notice: Undefined variable: data in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 38

Warning: Cannot modify header information - headers already sent by (output started at c:\inetpub\wwwroot\sqltoexcel\index2.php:18) in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 52

Warning: Cannot modify header information - headers already sent by (output started at c:\inetpub\wwwroot\sqltoexcel\index2.php:18) in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 55

Warning: Cannot modify header information - headers already sent by (output started at c:\inetpub\wwwroot\sqltoexcel\index2.php:18) in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 56

Warning: Cannot modify header information - headers already sent by (output started at c:\inetpub\wwwroot\sqltoexcel\index2.php:18) in c:\inetpub\wwwroot\sqltoexcel\index2.php on line 57

what to do?

chleng
07-21-2005, 06:57 PM
Hi,

http://www.sitepoint.com/article/php-apps-excel-worksheet-server

I'm able to run the above on XP, my machine,

so my question is must I install openoffice or any other stuff on my server besides all the necessary php class.

thks

rgds



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum