...

View Full Version : Exporting SQL to a XLS file on the Local server for email.



merval2k8
04-23-2009, 11:09 PM
I already have the export to excel working, its awesome but the bad part is I can't get it to save on the server. I have to invoke the download in a browser and then I need to be able to echo a Y for save and type the location. Here is the script i currently use.

Anything you can do to help me would be great! Thanks!

<?php
// Edit the $Host, $User $Password, $DBName and $TableName vars only! //
$Host = "localhost";
$User = "user";
$Password = "password";
$DBName = "database";
$TableName = "table";

// DO NOT EDIT BELOW HERE //
$link = mysql_connect ($Host, $User, $Password) or die('Could not connect: ' . mysql_error());
mysql_select_db($DBName) or die('Could not select database');

$select = "SELECT * FROM `".$TableName."`";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

for ($i = 0; $i < $fields; $i++) {
$csv_output .= mysql_field_name($export, $i) . "\t";
}

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);

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=sql_dump.xls");
header("Pragma: no-cache");
header("Expires: 0");
readfile("sql_dump.xls");
exit;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Download MySQL Table Code</title>
</head>
<body>

</body>
</html>

Old Pedant
04-24-2009, 06:27 AM
Won't get it on the server that way.

That is strictly for causing MS Excel to display the data in a spreadsheet from the browser.

If you want to get the data into Excel on the server, you'll have to script the Excel object, and I doubt whether PHP will do that. Other option might be to connect to Excel using the JET OLE DB driver, if PHP will make that connection (it should).

merval2k8
04-24-2009, 04:07 PM
If you want to get the data into Excel on the server, you'll have to script the Excel object, and I doubt whether PHP will do that. Other option might be to connect to Excel using the JET OLE DB driver, if PHP will make that connection (it should).

I am fairly new to MySQL and know enough php to get by. I am not sure I understand what JET OLE DB is. Sorry for sounding dumb. :-\

Old Pedant
04-24-2009, 11:22 PM
JET OLE DB is the Microsoft driver that allows you to treat various kinds of text files (e.g., ".csv" and tab-delimited) AND ".xls" (excel) files as if they were an Access-style database.

It will only work on a Windows machine, of course. Has nothing to do with MySQL. You would need a separate connection to that driver from PHP. I've never worked with PHP and JET together, but I have to assume there is a way to do so.

tomason
02-09-2011, 03:52 AM
"Your issue looks interesting. I want to help you with a method of data export. If you want to export data from database, you may choose it, it's very fast and stable when you export data from database. And now, it's free for everyone. You may visit here for further information:
http://www.codeproject.com/KB/cs/Excel_PDF_Word_ExportWiz.aspx"



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum