Solved

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

Posted on 2011-09-02
9
528 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
 
LVL 108

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 108

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now