Page 1 of 1

Open data from database in excel

Posted: Tue Aug 31, 2004 12:54 pm
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

Posted: Tue Aug 31, 2004 12:56 pm
by timvw
Or you store it as CSV on the server,
Or you install ODBC for UNIX (not sure if this will work)

Posted: Tue Aug 31, 2004 12:57 pm
by ddragas
Isn't it posible to open direct on server. In browser opens MS excel?

Posted: Tue Aug 31, 2004 1:01 pm
by feyd
the browser requires a plug-in to view excel data directly. There's a link somewhere on the microsoft sites for it.

Posted: Tue Aug 31, 2004 1:07 pm
by ddragas
But it opens on local machine. Why it doesn't open when it is opening data from server?

Posted: Tue Aug 31, 2004 1:10 pm
by feyd
maybe your server isn't sending the right content-types?

Posted: Tue Aug 31, 2004 1:12 pm
by ddragas
It sends plain text. How to make to open excel with data from database

Posted: Tue Aug 31, 2004 1:14 pm
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

?>

Posted: Tue Aug 31, 2004 1:16 pm
by feyd

Code: Select all

header('Content-type: application/vnd.ms-excel');

Posted: Tue Aug 31, 2004 1:20 pm
by ddragas
Thank you
It works