Solved

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

Posted on 2011-09-02
9
531 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:andrewaiello
  • 4
  • 3
  • 2
9 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 334 total points
ID: 36475282
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.
0
 
LVL 1

Author Comment

by:andrewaiello
ID: 36475741
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.
0
 
LVL 1

Author Comment

by:andrewaiello
ID: 36475785
I forgot a / in the closing </tr> of the first row.  I just noticed that now.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 166 total points
ID: 36475800
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
0
 
LVL 12

Assisted Solution

by:telyni19
telyni19 earned 334 total points
ID: 36475819
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

0
 
LVL 1

Author Comment

by:andrewaiello
ID: 36475824
Allright, so its easiest to put my data into and array, implode it upon a comma and use fputcsv().  Thank you
0
 
LVL 12

Expert Comment

by:telyni19
ID: 36475842
Also, section 6 in that link I gave originally covers the fputcsv function, if you want an example.
0
 
LVL 1

Author Comment

by:andrewaiello
ID: 36475855
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.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 36475858
...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().
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses how to create an extensible mechanism for linked drop downs.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question