Export to Excel

dfowler · 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!

NL_Rosko · 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;

dfowler · 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.

dfowler · 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.

rhodesa · 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

dfowler · 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?

rhodesa · February 19, 2008

Give the user all the options you can offer

dfowler · 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.

rhodesa · 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

dfowler · 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.

rhodesa · 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;
?>

dfowler · February 19, 2008

Haha, done and done. It works perfectly. Sorry to be a bother.

Sign In

Export to Excel

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information