Open data from database in excel
Moderator: General Moderators
Open data from database in excel
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
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
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
?>- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
header('Content-type: application/vnd.ms-excel');