472,119 Members | 1,552 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

Get Mysql to export rows in excel format

142 100+

I want my clients to login, press a button and then MySQL export the assigned fields into a excel doc for them to download...

Where can I get a script to convert MySQL fields to excel format or is there a simple way to do something in PHP?

Kind Regards
Sep 20 '07 #1
4 3082
5,821 Expert 4TB
Heya, Webandwe.

There's a few threads on this site about exporting to a CSV file, which Excel can import, but there is very little support for exporting in Excel format.
Sep 20 '07 #2
142 100+
Hi, thanks.

I found a script on the net but had to fix it a but but it is still not working, if you run the file it wants to save the php file and does not run and create a excel file, can someone please take a look at it and maybe see if you can find any faults with the script.



This PHP script Extracts MySQL table and downloads into an Excel Spreadsheet.
Script by Jeff Johns, for a full explanation and tutorial on this, see: http://www.phpfreaks.com/tutorials/114/0.php

YOUR DATABASE HOST = (ex. localhost)
USERNAME = username used to connect to host
PASSWORD = password used to connect to host
DB_NAME = your database name
TABLE_NAME = table in the database used for extraction
To extract specific fields and not the whole table, simply replace
the * in the $select variable with the fields you want
define(db_host, "__MUNGED__");
define(db_user, "__MUNGED__");
define(db_pass, "__MUNGED__");
define(db_link, mysql_connect(db_host,db_user,db_pass));
define(db_name, "companion");
Build query, call it, and find the number of fields
$select = "SELECT * FROM brandmetlogin";
$export = mysql_query($select);
$count = mysql_num_fields($export);
Extract field names and write them to the $header variable
for ($i = 0; $i < $count; $i++) {
$header .= mysql_field_name($export, $i)."t";
Extract all data, format it, and assign to the $data variable
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "t";
$line .= $value;
$data .= trim($line)."n";
$data = str_replace("r", "", $data);
Set the default message for zero records
if ($data == "") {
$data = "n(0) Records Found!\n";
Set the automatic download section
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=spreadsheet.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$headern$data";


Sep 21 '07 #3
5,821 Expert 4TB
Heya, Webandwe.

For your protection, I have removed your MySQL connection info from your code.
Sep 21 '07 #4
142 100+
thanks, did not even see it there...
Sep 25 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Damien | last post: by
11 posts views Thread by Mike MacSween | last post: by
2 posts views Thread by PerryC | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.