Open data from database in excel

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
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Open data from database in excel

Post by ddragas »

Hi all!

I've got script that opens contents from database (mysql) in excel.
I know that it works becouse on my local machine excel is instaled. What do I need to do to be able to open data from databae from server in excel?

Regards - ddragas
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Or you store it as CSV on the server,
Or you install ODBC for UNIX (not sure if this will work)
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

Isn't it posible to open direct on server. In browser opens MS excel?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the browser requires a plug-in to view excel data directly. There's a link somewhere on the microsoft sites for it.
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

But it opens on local machine. Why it doesn't open when it is opening data from server?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

maybe your server isn't sending the right content-types?
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

It sends plain text. How to make to open excel with data from database
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

Here is script if semeone needs it

Code: Select all

<?php

/********************************************

PHP - Excel Extraction Tutorial Code

Page: excel.php

Developer: Jeffrey M. Johns

Support: binary.star@verizon.net

Created: 10/01/2003

Modified: N/A

*********************************************

Notes/Comments: This code is a very basic/replica of

the code that is in the tutorial. To make it work you must

define your connection variables below. Make sure you

replace the following all CAPS text with your proper values.

*********************************************

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

*********************************************

This code will extract the data from your table and format

it for an excel spreadsheet download. It is very quick,

simple, and to the point. If you only want to extract 

certain fields and not the whole table, simply replace

the * in the $select variable with the fields you want

to extract.

*********************************************

Disclaimer: Upon using this code, it is your responsibilty

and I, Jeffrey M. Johns, can not be held accountable for

any misuse or anything that may go wrong.

*********************************************

Other: Support will not be provided if the code is

enhanced or changed. I do not have the time for 

figuring out your changes and modifications. I will only

offer simple support for the code listed below.

/********************************************/

define(db_host, "localhost");

define(db_user, "");

define(db_pass, "");

define(db_link, mysql_connect(db_host,db_user,db_pass));

define(db_name, "databasename");

mysql_select_db(db_name);

/********************************************

Write the query, call it, and find the number of fields

/********************************************/

$select = "SELECT * FROM 'table'";				

$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 downloadn section

/********************************************/

header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=spreadsheet.xls");

header("Pragma: no-cache");

header("Expires: 0");

print "$header\n$data";

?>
<?php

?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

header('Content-type: application/vnd.ms-excel');
User avatar
ddragas
Forum Contributor
Posts: 445
Joined: Sun Apr 18, 2004 4:01 pm

Post by ddragas »

Thank you
It works
Post Reply