Exporting to Excel

[anandi]

Hi

I have some data that i display on screen and i need to be able to export to excel as is. Whatever i have found online only refers to data in a single table but my sql statement is of joins. I am searching for a simple way to do this using this code below. Would very much appreciate some help. thanks. anandis

$OR = " SELECT * FROM address a LEFT JOIN Cities  USING(CITYID) JOIN Customers c USING(CID) WHERE a.SID = '$sid'  ORDER BY c.`Company` asc,  `CITYID` asc   ";

$resultOR = mysqli_query($link2, $OR) or die(mysqli_errorno());
$numofrows = mysqli_num_rows($resultOR);


echo "<table border='1' style='border-collapse: collapse' bordercolor='#111111' width='100%'>\n";
echo "<tr bgcolor='#FFEAED'>



<td><b><font size=3 face=Calibri color=brown>S.No</b></td>
<td><b><font size=3 face=Calibri color=brown>CAID</b></td>      
<td><b><font size=3 face=Calibri color=brown>CID</b></td>
<td><b><font size=3 face=Calibri color=brown>Company</b></td>
<td><b><font size=3 face=Calibri color=brown>Street</b></td>
ETC
\n";

for ($p = 1; $p <= $numofrows; $p++) {
            $myrow = mysqli_fetch_array($resultOR);
            $caid = $myrow["CAID"];
            $cid = $myrow["CID"];
            $cityid= $myrow['CITYID'];

            $result2 = mysqli_query($link2, "SELECT  * FROM Cities WHERE `CITYID` = '$cityid' ");
            $myrow2 = mysqli_fetch_array($result2);
            $city = $myrow2["City"];
         
 
           $sql22 = "SELECT * FROM Customers WHERE `CID` = '$cid' ";
			$result22 = mysqli_query($link2, $sql22) or die (mysqli_errorno ());
			$myrow22=mysqli_fetch_array($result22);
			$company = $myrow22["Company"];
			
			
			
			if ($p % 2) { //this means if there is a remainder
                echo "<TR bgcolor=\"#FAF0F0\">\n";
            } else { //if there isn't a remainder we will do the else
                echo "<TR bgcolor=\"white\">\n";
            }
            
            echo "
        <td><font face=\"Calibri\" size=\"3\" color=\"brown\">$p </TD>
        <td><font face=\"Calibri\" size=\"3\" color=\"brown\">$city </TD>
		<td><font face=\"Calibri\" size=\"3\" color=\"brown\">$cid </TD>
        <td><font face=\"Calibri\" size=\"3\" color=\"brown\">$company</TD>
		<td><font face=\"Calibri\" size=\"3\" color=\"brown\">" . $myrow["Street"] . " </TD>
		  echo "</TR>\n";
}	
        echo "</TABLE>\n";  

 

[ginerjm]

The way to do this would be to generate a CSV file while you are building your html table.  Save that file somewhere and give the user a button to click that can be used to cause your script to show he/she what that filename is and were it can be found.   A CSV file is MUCH easier to import to excel.

[gizmola]

ginerjm is exactly right.

CSV format is simple. 

• If you have a string you need to delimit it with double quotes.  In other words, you need double quotes around any string field.  It is generally safe to put double quotes around all your fields, although this might affect the way the particular spreadsheet handles the data if it is numeric 
  • That introduces the possibility that there could be those same quotes within the string, so you need to first take the step of "escaping" any of those quotes.
• Each field needs to be separated with a comma
• Each line needs to end with a carriage return/line feed combination.

Now you can certainly write this code yourself, but the modern way would be to use a library that already handles these details for you, and reduces your requirements to using the library and letting the library do the work for you.  If it's a decent library it has unit tests and you are leveraging not only the work other people put into the library, but also the testing they did to give you confidence their code works correctly and to the csv spec.  Here is one such library that is easy to use:  https://github.com/thephpleague/csv

What will help you here is to load the data into an array and use that to output your html table rather than doing that will fetching each row.

Put that code into a function which you keep in an included script (including the query code).

The csv output can then rely on using this same query code to re-run the query.  Your "csv script" will:

• use the same function to execute the query and load the array
• It will then set the http header as needed to return the data with the mimetype for csv, and optionally to force the browser to download it as a file
  • You do this setting the http header to Content-Disposition: attachment; filename="somefile.csv"
• Use your routines or a csv library to convert the array to csv format
• return that data

[maxxd]

While I wholeheartedly agree the way to go is to save out a CSV and let somebody open it with Excel, if you absolutely have to deliver a native Excel file, there are a few libraries available. This one seems to be the most used/respected in my Googling.