Trouble exporting a php/html table to an excel spread sheet

andrewaiello
andrewaiello used Ask the Experts™
on
Hi, I am trying to export a table to an excel spreadsheet.  I've done this before but this time is a little different.  I have do while's and if's thrown in through out the table, showing some rows and repeating others.  the format I am using for exporting is this:

    <?php
    $file="test.xls";
    $test="<table border=1><tr><td>Cell 1</td><td>Cell 2</td></tr></table>";
    header("Content-type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=$file");
    echo $test;
    ?>

Open in new window


But I can't seem to figure out how to incorporate the if's and do while's.  I'm trying to do it like this:

       <td>
              ". $row_rsSelf['healthHealthCoverage']."
        </td>
 </tr>" . if ($totalRows_rsSpouse > 0){ ."
<tr>
        <td>
        </td>

Open in new window


But that doesn't seem to work.  The code is kind of lengthy (about 500 lines) so I figured I wouldn't post it right away, if you would like me to post it,  let me know.  Thank you in advanced.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Here's a page on using various techniques to export from PHP to Excel including sample code:
http://www.the-art-of-web.com/php/dataexport/

The first example in section one starts out with a manual array, but you could build the data any way you need, including if statements to decide which rows to include, and then echo what needs to be displayed in the file.

Author

Commented:
Okay, so I read the link you sent me and it seems it is using two methods of getting the data.  An array and a sql statement.  I am actually using data from 3 different tables joined together with the main one (table1) may or may not have 1 or more records from table 2 and table 3.  I don't know how to formulate a sql statement and and have it structured in a easy to read format.  However the array method I can use and I will be working towards that now, so I thank you for that.  

But a coworker who programs in coldfusion showed me an example he did where he just created the html/coldfusion table and was able to output it to excel.  So I found a similar example in php and it works for an html table and even with php variables but the if's and do while's give errors.  I guess a simple example of the issue I am having with my current code would be this:

<?php
$tableVar = "<table>". if($somevar == "2"){."<tr><td>cell1</td><tr>" . } . "<tr><td>cell2</td></tr></table>" ;
?>

Open in new window


How can you use if's and other similar structures when moving in and out of quotes and storing the whole thing to a single variable?  There may not be a way to do it, I'm just curious if there is or isn't, and if there is how do you do it.

Author

Commented:
I forgot a / in the closing </tr> of the first row.  I just noticed that now.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2016
Commented:
Usually you can write a CSV file from your PHP script.  CSV stands for "comma-separated values" and it is almost universally associated with Excel.  PHP supports the fputcsv() command to help you with this.  
http://us3.php.net/manual/en/function.fputcsv.php
I think your syntax may be the issue, actually. You're using the php concatenation, but you're trying to simultaneously concatenate and run an if statement at the same time. Something like this might work better:
<?php
$tableVar = "<table>";
if($somevar == "2"){
$tableVar .= "<tr><td>cell1</td><tr>"}
$tableVar .= "<tr><td>cell2</td></tr></table>";
?>

Open in new window

Author

Commented:
Allright, so its easiest to put my data into and array, implode it upon a comma and use fputcsv().  Thank you
Also, section 6 in that link I gave originally covers the fputcsv function, if you want an example.

Author

Commented:
ahh, I just came back to the page to clarify something and your post came up telyni19.  I can't believe I couldn't come up with that on my own.  Thats exactly what I needed.  I can make my current code work now.  Thank you all so much.  I now know the different options I have for doing something like this.  But since I have the table format made, and it has to be user friendly to people who barely know how to turn on a computer I will go with the original method.  Thank you all again.
Most Valuable Expert 2011
Top Expert 2016

Commented:
...put my data into and array, implode it upon a comma and use fputcsv()

Yes, except you do not need to implode(), you just feed the array to fputcsv().

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial