PHP and Excel, Excel CSV

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

PHP and Excel, Excel CSV

Post by igoy »

I want know how to dump mysql table to excel or excel csv format .. without using any Class or library. .. . (rigid client and server admins :( )

can anybody help me . . plzzzzz
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You can do this via phpMyAdmin:
http://www.phpmyadmin.net

Mac
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Excel can read and convert files. An easy format to generate is a tab delimited file with a new line for each row returned from the database.
For example:

Code: Select all

<?php

// This will force the browser to popup the "Save as..." dialog and prompt you to save the content of $FileStr as a file named tabbed-table-file.txt
$FileStr = "ID\tName\tE-mail
123\tJohn\tdoe@server.com
234\tHomer\tdumb@loser.net";

header("Cache-Control: no-store");
header('Content-Length: '.strlen($FileStr));
header('Content-Type: application/octet');
header('Content-Disposition: attachment; filename="tabbed-table-file.txt"');

echo $FileStr;
?>
Then, if you use excel to open this file, it will detect exactly what you wanted it too. Or at least exactly as I think you want it to.
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

mySQL table to Excel CSV........

Post by igoy »

Well I'm myself using PHPmyadmin. . (by the way it's one of the great tools) but i don't think it's a good idea to give client access to such tool...
(more over server guys are very rigid.. :( )

what i would like to some method where pure php can be used to do so...

I guess heavy have something to say. . which looks like can make my life easier. . :)

I will post again after trying this code...
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Play with this. It is somewhat off topic, but does a database dump without having to use phpMyAdmin:

Code: Select all

<?php
//Script name: "dump.php"

$DB_NAME = $_GET['db_name']; // The name of the database you wish to dump
$DB_USER = $_GET['db_user']; 
$DB_PASSWORD = $_GET['db_passwd'];

/*
TAKE NOTE, THE ABOVE THREE ROWS ARE CONSIDERED A SECURITY HOLE.
DO NOT USE THIS SCRIPT ON A PUBLIC NETWORK WITHOUT REPLACING THEM WITH A REAL LOGIN PROCEDURE.
NEVER SEND DB ACCESS INFO ACROSS THE INTERNET LIKE THIS.

And don't just replace the $_GET vars with the correct working values, because if you do, 
anyone can surf to the script and get to see all the content of your database.

You can test the script like this:
Browse: http://localhost/path_to_script/dump.ph ... d=DBPASSWD
*/


// The following assumes you connect to a mysql-server on running localhost:
if (strpos($_SERVER['OS'],"Windows")){
	$Command = "c:\mysql\bin\mysqldump.exe -u $DB_USER --password='$DB_PASSWORD' $DB_NAME";
}else{
	$Command = "/usr/bin/mysqldump -u $DB_USER --password='$DB_PASSWORD' $DB_NAME";
}

//The following is just about how the client OS handles line breaks:
if (strpos($_SERVER['HTTP_USER_AGENT'],"Windows")){
	$LineBreaker = "\r\n"; 
}else{
	// Mac uses \r if my memory is correct.
	// assuming unix:
	$LineBreaker = "\n"; 
}

//Here, a database dump is performed:
exec($Command,$ArrData);

$Data = "# SQL Dump for database $DB_NAME";
//Here, The result in $ArrData is joined into a multiline string:
$Data .= $LineBreaker . join($ArrData,$LineBreaker);

//Send it to you:
header("Cache-Control: no-store");
header('Content-Type: text/plain');
header('Content-Length: '.strlen($Data)); 
//if you uncomment the following line, a "Save As.." dialog will be displayed by the browser:
//header('Content-Disposition: attachment; filename="sqldump-'.$_SERVER['HTTP_HOST'].'-'.strftime('%Y-%M-%d-%H-%M').'.sql"');

echo $Data;

?>
If you use this script as is, and publish it on the Internet, you will send the db access info in plain text over the Internet. Thus, anyone with access to Internet equipment might see what you sent.
That's why you shouldn't do that.
User avatar
igoy
Forum Contributor
Posts: 203
Joined: Fri May 02, 2003 11:57 pm
Location: India
Contact:

Thank Heavy

Post by igoy »

Damn you are my personal jesus christ man .. .:)

Thanks a ton for that thing. . I guess I can play around with that stuff and get things rolling. . .

by the way I found this great class yesterday while searching for solution
It works quite well..

check this out...

http://www.phpclasses.org/browse.html/package/644.html

thanks again.. 8)
Post Reply