MySQL -> Excel Help Needed for tweaking

[HairyHun]

After looking for a script to export mysql data into excel using PHP. I found one that was function.

Most things were incomplete or had others issues.

This one works, but it gives me each row into a column. I've been trying to swtich taht, so that I get rows. ( first row: column names, second row +: one record per row )

Could someone help me do that switch? I tried but it just broke the code. (Or if you have a complete, working code i could use )

Thanks a lot

Below is the code:

<?
include 'library/config.php';
include 'library/opendb.php';

$query = "SELECT fname, lname FROM students";
$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 = 'mysql-to-excel.xls';
header("Content-type: application/vnd.ms-excel"); 
header("Content-Disposition: attachment; filename=$fileName");

//echo $tsv;
echo $html;

include 'library/closedb.php';
?>

 

[Houdas]

*untested*

<?php
    $fileName = 'mysql-to-excel.xls';
    header("Content-type: application/vnd.ms-excel"); 
    header("Content-Disposition: attachment; filename=$fileName");

    //

    $host = 'localhost';
    $user = 'username';
    $pass = 'password';
    $name = 'db_name';

    $table = 'table_name';

    //

    $c = mysql_connect($host, $user, $pass);
    mysql_selectdb($name);

    $rows = array();
    $res = mysql_query("SHOW COLUMNS FROM $table");
    while($row = mysql_fetch_assoc($res)) {
        $rows[] = $row['Field'];
    }

    echo implode(';', $rows)."\n";
    
    $res = mysql_query("SELECT * FROM $table");
    while($row = mysql_fetch_array($res, MYSQL_NUM)) {
        $r = array();
        for($i=0; $i<count($row); $i++) $r[] = $row[$i];
        echo implode(';', $r)."\n";
    }
    
    mysql_close($c);
?>

 

[HairyHun]

haha! this is about the closest I got to something operational!

But! excel gave me a few errors, then crashed.

In text edit, The data is well formatted ( by rows ) but the separator is a ";". Could that be what crashed excel?

Switching that to a "." will give me a csv file i guess? Other suggestions to make it a proper excel file?


Thanks
HH

 

[HairyHun]

I tried commas, the error I still get in excel is SYLK.

SO I proceed to google... and I find

SYLK: The natural lubricant. For vaginal/menopausal dryness

Just what I neede! /me loves the internet

 

[HairyHun]

So now it opens fine as a CSV in excel. But when when I put the filename back to xls, its all stuck on one line.

Is it possible to *relatively easily* format an excel document in php so it comes out as a proper excel doc and have the first line (attributes) bolded?

thanks
HH

 

[Houdas]

There are classes for it, like Class: Excel Writer (excel, xls, excel writer, excelwriter) - PHP Classes, but I have no experience with it.

 

[wdmny]

You can output HTML with tables, styles, etc and Excel will display it with your formatting.