MySQL as Excel?

Posted by Geoff Cox on February 3, 2009, 6:50 pm

Please log in for more thread options

Hello,

Can anyone please point to any code for getting data from an online
MySQL database in the Excel format?

Cheers,

Geoff

Posted by JRough on February 3, 2009, 7:13 pm

Please log in for more thread options

show/hide quoted text

Are you referring to a php script that would download a page to excel
from a query to MySQL? If so this is how i do it the two functions
just get a list of the column headers in the mysql database and then
output it to excel. The $result is the result of a query. This is
assuming you know how to program in PHP and is just a not very good
example. There are also some Excel classes in pear.php.net that you
could get better examples and the classes so all you have to do is
write your query and incorporate the classes into a php script. HTH.
$file_name =3D 'Bad_Orders';

        $header =3D makexclheader();
$data =3D makexcldata($result);

        if (empty($data)) {
                        $data =3D "\n(0) Records Found!\n";}
        header("Content-type: application/xmsdownload");
        header("Content-Disposition: attachment; filename=3D".$file_name.date
("Y:m:d H:i").
        ".xls");
        header("Pragma: no-cache");
        header("Expires; 0");
        print "$header\n$data";

}

Posted by Geoff Cox on February 4, 2009, 2:30 am

Please log in for more thread options

wrote:

show/hide quoted text

Thanks for your reply which I will read in a second. I have gor code
which now works except that the date format in the excel file is eg

2.00902E+13

How and where do I change that? The MySQL is version 3 - pretty old I
know.

Cheers

Geoff

<?php
@require(dirname(__FILE__) .
etc

$link = mysql_connect ($Host, $User, $Password) or die('Could not
connect: ' . mysql_error());
mysql_select_db($DBName) or die('Could not select database');

$query = "SELECT * FROM `".$TableName."` ORDER by DT";

$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);
show/hide quoted text
}

$tsv = implode("\r\n", $tsv);
show/hide quoted text

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

echo $tsv;
//echo $html;

show/hide quoted text

Posted by toby on February 3, 2009, 7:23 pm

Please log in for more thread options

show/hide quoted text

Tab-delimited or CSV is trivial, is that good enough?

The following CGI script will do a tab-delimited dump:

#!/bin/bash
# Setting the mime-type may or may not lead to a correct association
in your client
echo Content-type: application/vnd.ms-excel
echo
mysql -u root test -B -e 'SELECT id,sample FROM t'

show/hide quoted text

Posted by Geoff Cox on February 4, 2009, 2:48 am

Please log in for more thread options

On Tue, 3 Feb 2009 16:23:33 -0800 (PST), toby

show/hide quoted text

show/hide quoted text
php but cannot get the right date/time format...

In the excel I get the date/time as

2.00902E+13 etc.

How and where do I change that?

Cheers

Geoff

<?php
@require(dirname(__FILE__) .
etc

$link = mysql_connect ($Host, $User, $Password) or die('Could not
connect: ' . mysql_error());
mysql_select_db($DBName) or die('Could not select database');

$query = "SELECT * FROM `".$TableName."` ORDER by DT";

$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);
show/hide quoted text
}

$tsv = implode("\r\n", $tsv);
show/hide quoted text

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

echo $tsv;
//echo $html;

show/hide quoted text

show/hide quoted text