dfowler Posted February 18, 2008 Share Posted February 18, 2008 Hey guys, I've tried searching several times and can't come up with any definite results. It also appears there was a tutorial, but I know that they are down currently. So any help would be greatly appreciated. I have created a reports page that allows a user to query a database for different results. These results are then placed in a table for them to view. They have asked me if once they have the results they can save them as an excel file. I know this can be done, but I'm not sure how to do it. Thanks for any information! Quote Link to comment Share on other sites More sharing options...
NL_Rosko Posted February 18, 2008 Share Posted February 18, 2008 maybe its better to save the results in an csv file which can be easily opened in excel. there are tons of examples on this subject csv files gives you more flexibility but here is an example $query = "SELECT * FRoM TABLE"; $result = mysql_query($query) or die('Error, query failed'); $tsv = array(); $html = array(); while($row = mysql_fetch_array($result, MYSQL_NUM)) { $tsv[] = implode("\t", $row); $html[] = "<tr><td>" .implode("</td><td>", $row) . "</td></tr>"; } $tsv = implode("\r\n", $tsv); $html = "<table>" . implode("\r\n", $html) . "</table>"; $fileName = 'somefile.xls'; header("Content-type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=$fileName"); echo $tsv; Quote Link to comment Share on other sites More sharing options...
dfowler Posted February 18, 2008 Author Share Posted February 18, 2008 Here is the code that I am working with for the results. Hopefully this will help, I'm not sure where to input what you recommended NL_Rosko. \ if($_POST['cmdSearchSubmit']) { $keyword=$_POST['txtSearchKeyword']; $sql="SELECT * FROM `user` WHERE 1 AND `firstname` LIKE '%".$keyword."%' OR `middlename` LIKE '%".$keyword."%' OR `lastname` LIKE '%".$keyword."%' OR `email` LIKE '%".$keyword."%' OR `projectareaname` LIKE '%".$keyword."%' OR `membershiptype` LIKE '%".$keyword."%' OR `degrees` LIKE '%".$keyword."%' OR `title` LIKE '%".$keyword."%' OR `program` LIKE '%".$keyword."%' OR `website` LIKE '%".$keyword."%' OR `country` LIKE '%".$keyword."%' OR `address` LIKE '%".$keyword."%' OR `city` LIKE '%".$keyword."%' OR `state` LIKE '%".$keyword."%' OR `organization` LIKE '%".$keyword."%'"; $result=mysql_query($sql) or die("Error ::-> ".mysql_error()); $numoffound=mysql_num_rows($result); echo "<TABLE width='100%' border='1' align='center' class='tableclass' bordercolor='#778890'>"; echo "<TR>"; echo "<TD colspan='8'>Records Found :: <b>".$numoffound."</b></TD>"; echo "</TR>"; echo "<TD nowrap class='header_menu'><b><font color='#FFFFFF'>Last Name,First Name</b></TD>"; echo "<TD nowrap class='header_menu'><b><font color='#FFFFFF'>Title</font></b></TD>"; echo "<TD nowrap class='header_menu'><b><font color='#FFFFFF'>Full Address</font></b></TD>"; echo "<TD nowrap class='header_menu'><b><font color='#FFFFFF'>Phone</font></b></TD>"; echo "<TD nowrap class='header_menu'><b><font color='#FFFFFF'>Fax</font></b></TD>"; echo "<TD nowrap class='header_menu'><b><font color='#FFFFFF'>EMail</font></b></TD>"; echo "<TD nowrap class='header_menu'><b><font color='#FFFFFF'>Website</font></b></TD>"; echo "<TD nowrap class='header_menu'><b><font color='#FFFFFF'>Action</font></b></TD>"; echo "</TR>"; while($row=mysql_fetch_array($result)) { echo "<TR>"; if($row['lastname']=="") { $lastname=" "; } else { $lastname=$row['lastname']; } if($row['firstname']=="") { $firstname=" "; } else { $firstname=$row['firstname']; } echo "<TD >".$lastname.",".$firstname."</TD>"; if($row['title']=="") { $title=" "; } else { $title=$row['title']; } echo "<TD >".$title."</TD>"; if($row['address']==null || $row['city']==null || $row['state']==null || $row['zip']==null) { echo "<TD > </TD>"; } else { echo "<TD >".$row['address'].",".$row['city'].",".$row['state'].",".$row['zip']."</TD>"; } if($row['telephone']=="") { $telephone=" "; } else { $telephone=$row['telephone']; } echo "<TD >".$telephone."</TD>"; if($row['fax']=="") { $fax=" "; } else { $fax=$row['fax']; } echo "<TD >".$fax."</TD>"; if($row['email']=="") { $email=" "; } else { $email=$row['email']; } echo "<TD >".$email."</TD>"; if($row['website']=" ") { $website=" "; } else { $website=$row['website']; } echo "<TD >".$website."</TD>"; echo "<TD ><a href='edit_user.php?id=".$row['user_id']."&tab=ncsd_user'>Edit</a> <a href='delete_user_client.php?id=".$row['user_id']."&tab=ncsd_user'>Delete</a></td>"; echo "</TR>"; }//While CLose. echo "</TABLE>"; }//if End. Quote Link to comment Share on other sites More sharing options...
dfowler Posted February 19, 2008 Author Share Posted February 19, 2008 I still can't seem to get this to work. I thought about adding a button and on clicking would prompt to convert the table to a CSV, but I'm getting nowhere. Quote Link to comment Share on other sites More sharing options...
rhodesa Posted February 19, 2008 Share Posted February 19, 2008 This post should help you create the excel file: http://www.phpfreaks.com/forums/index.php/topic,180789.0.html What I normally do for this case is have all the data for making the table passed via GET. So the url for the page you already have would be something like: results.php?search=test&sort=col1&etc=more Then, as you suggested, have a button or href link that opens a new window called excel.php and pass the same GET vars. excel.php would then be a script following the above post. Let me know if you have any problems following that Quote Link to comment Share on other sites More sharing options...
dfowler Posted February 19, 2008 Author Share Posted February 19, 2008 To help clean things up a bit, here is what I've done. On the main page I created a button called "Printer Friendly Version" when they click it it goes here: <?php session_start(); require_once("../include.php"); ?> <link rel="stylesheet" href="admin_style.css" type="text/css"> <TABLE width="80%" border="1" align="center" class="tableclass" bordercolor="#778890"> <TR> <TD colspan="8">Records Found :: <b><?php echo $_SESSION['numoffoundU']; ?></b></TD> </TR> <TD nowrap class="header_menu"><b><font color="#ffffff">Last Name,First Name</b></TD> <TD nowrap class="header_menu"><b><font color="#ffffff">Title</font></b></TD> <TD nowrap class="header_menu"><b><font color="#ffffff">Full Address</font></b></TD> <TD nowrap class="header_menu"><b><font color="#ffffff">Phone</font></b></TD> <TD nowrap class="header_menu"><b><font color="#ffffff">Fax</font></b></TD> <TD nowrap class="header_menu"><b><font color="#ffffff">EMail</font></b></TD> <TD nowrap class="header_menu"><b><font color="#ffffff">Website</font></b></TD> </TR> <?php $counter_p = 1; while($counter_p <= $_SESSION['numoffoundU']) { ?> <TR> <TD valign="top"><?php echo $_SESSION['lastnameU'][$counter_p].", ".$_SESSION['firstnameU'][$counter_p]; ?></TD> <TD valign="top"><?php echo $_SESSION['titleU'][$counter_p]; ?></TD> <TD valign="top"><?php echo $_SESSION['addressU'][$counter_p]." - ".$_SESSION['cityU'][$counter_p].", ".$_SESSION['stateU'][$counter_p].",".$_SESSION['zipU'][$counter_p]; ?></TD> <TD valign="top"><?php echo $_SESSION['telephoneU'][$counter_p]; ?></TD> <TD valign="top"><?php echo $_SESSION['faxU'][$counter_p]; ?></TD> <TD valign="top"><?php echo $_SESSION['emailU'][$counter_p]; ?></TD> <TD valign="top"><?php echo $_SESSION['websiteU'][$counter_p]; ?></TD> </TR> <?php $counter_p++; }//While CLose. ?> </TABLE> I thought about adding the "Export to CSV" here. Do you think this will work better? Quote Link to comment Share on other sites More sharing options...
rhodesa Posted February 19, 2008 Share Posted February 19, 2008 Give the user all the options you can offer Quote Link to comment Share on other sites More sharing options...
dfowler Posted February 19, 2008 Author Share Posted February 19, 2008 Ok, I tried, and I must be doing something wrong. Here is my code: <?php session_start(); include("excelwriter.inc.php"); $excel=new ExcelWriter("query.xls"); if($excel==false) echo $excel->error; $myArr=array("First Name","Last Name","Title","Address","City","State","Zip","Telephone","Fax","Email","Website"); $excel->writeLine($myArr); $counter_v = 1; while($counter_v <= $_SESSION['numoffoundU']) { $excel->writeRow(); $excel->writeCol($_SESSION['firstnameU'][$counter_v]); $excel->writeCol($_SESSION['lastnameU'][$counter_v]); $excel->writeCol($_SESSION['titleU'][$counter_v]); $excel->writeCol($_SESSION['addressU'][$counter_v]); $excel->writeCol($_SESSION['cityU'][$counter_v]); $excel->writeCol($_SESSION['stateU'][$counter_v]); $excel->writeCol($_SESSION['zipU'][$counter_v]); $excel->writeCol($_SESSION['telephoneU'][$counter_v]); $excel->writeCol($_SESSION['faxU'][$counter_v]); $excel->writeCol($_SESSION['emailU'][$counter_v]); $excel->writeCol($_SESSION['websiteU'][$counter_v]); $counter_v++; }//While CLose. $excel->close(); echo "data written successfully."; ?> It is writing the file, but how do I prompt for a download? I'm hoping that when they click on the button it prompts for a download then redirects them back to the initial page. Quote Link to comment Share on other sites More sharing options...
rhodesa Posted February 19, 2008 Share Posted February 19, 2008 Sorry, should have specified...further along in that thread, I posted an updated version of excelWriter and a slightly different usage. Check out this part of the thread: http://www.phpfreaks.com/forums/index.php/topic,180789.msg806782.html#msg806782 Quote Link to comment Share on other sites More sharing options...
dfowler Posted February 19, 2008 Author Share Posted February 19, 2008 Ok, I saw where you added your version, however; I noticed it has a lot of stuff taken away. I tried messing with it to get it to work, but no results. It was writing the excel document perfectly, but placing it in the server. I added your send() function to my current excelwriter and now it prompts for a download, but there is no information in it. I had to change print $this->data; to print $this->fp; but now all it shows is Resource ID#2. Why is this so frustrating? I attached my excelwriter, maybe you can see the error. Quote Link to comment Share on other sites More sharing options...
rhodesa Posted February 19, 2008 Share Posted February 19, 2008 Just use my version, and change your script to this: <?php session_start(); include("excelwriter.inc.php"); $excel=new ExcelWriter(); $myArr=array("First Name","Last Name","Title","Address","City","State","Zip","Telephone","Fax","Email","Website"); $excel->writeLine($myArr); $counter_v = 1; while($counter_v <= $_SESSION['numoffoundU']) { $myArr = array(); $myArr[] = $_SESSION['firstnameU'][$counter_v]; $myArr[] = $_SESSION['lastnameU'][$counter_v]; $myArr[] = $_SESSION['titleU'][$counter_v]; $myArr[] = $_SESSION['addressU'][$counter_v]; $myArr[] = $_SESSION['cityU'][$counter_v]; $myArr[] = $_SESSION['stateU'][$counter_v]; $myArr[] = $_SESSION['zipU'][$counter_v]; $myArr[] = $_SESSION['telephoneU'][$counter_v]; $myArr[] = $_SESSION['faxU'][$counter_v]; $myArr[] = $_SESSION['emailU'][$counter_v]; $myArr[] = $_SESSION['websiteU'][$counter_v]; $excel->writeLine($myArr); $counter_v++; }//While CLose. $excel->send("query"); exit; ?> Quote Link to comment Share on other sites More sharing options...
dfowler Posted February 19, 2008 Author Share Posted February 19, 2008 Haha, done and done. It works perfectly. Sorry to be a bother. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.