Exporting database to Excel (via website, NOT phpMyAdmin)?

Edward · August 18, 2007

Hi,

I know via phpMyAdmin I can export my database in Microsoft Excel 2000 format, and can check a box to 'Create field names as first row', but it doesn't give me the php code used to achieve this. I need to have a page on a website so the administrators will be able to do this for themselves, does anyone know if this is possible?

Thanks in advance...

NArc0t1c · August 18, 2007

Have you tried google yet?

Or look at phpmyadmin's source code, maybe you'll find it there.

Edward · August 18, 2007

phpMyAdmin doesn't give me the option of viewing the source code for this operation. The phpfreaks tutorial on this has been removed (http://www.phpfreaks.com/tutorials/114/0.php) and most Google searches eventually lead back too that. Can anyone offer any help?

NArc0t1c · August 18, 2007

phpMyAdmin doesn't give me the option of viewing the source code for this operation. The phpfreaks tutorial on this has been removed (http://www.phpfreaks.com/tutorials/114/0.php) and most Google searches eventually lead back too that. Can anyone offer any help?

What I mean is, Get phpmyadmin, and view it's source files(the .php ones)

sasa · August 18, 2007

try

<form method="POST">
Database name: <input type="text" name="db_name" value="test"><br />
Table name: <input type="text" name="table"><br >
Replace NULL by <input type="text" name="nul" value="NULL"><br />
<input type="checkbox" name="header_row"> Put fields names at first row<br />
Field name: <input type="text" name="field" value="test">.xls<br />
<input type="submit" name="Submit" value="Convert to Excel 2000">
</form>
<?php
function convert_to_xls($table_name, $first_row = false, $data_base = 'test', $null_to = 'NULL') {
mysql_connect('localhost','root');
$result = mysql_query('show databases');
$test = false;
while ($row = mysql_fetch_row($result)){
	if ($row[0] == $data_base) $test = true;
}
if (!$test){
	die('Not exsist DB');
}
mysql_select_db($data_base);
$result = mysql_query('show tables');
$test = false;
while ($row = mysql_fetch_row($result)){
	if ($row[0] == $table_name) $test = true;
}
if (!$test){
	die('Not exsist table: '. $table_name);
}
$out = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"
xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
xmlns=\"http://www.w3.org/TR/REC-html40\">

<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">
<html>
<head>
<meta http-equiv=\"Content-type\" content=\"text/html;charset=utf-8\" />
<style id=\"Classeur1_16681_Styles\">
</style>

</head>
<body>

<div id=\"Classeur1_16681\" align=center x:publishsource=\"Excel\">

<table x:str border=0 cellpadding=0 cellspacing=0 width=100% style='border-collapse: collapse'>\n";
$result = mysql_query('describe '.$table_name);
 $num_types = array( 'NUMERIC', 'DECIMAL', 'INTEGER', 'SMALLINT', 'FLOAT', 'REAL', 'DOUBLE PRECISION');
$out .=$first_row ? "<tr>\n" : '';
while ($row = mysql_fetch_array($result)){
	$f_name[] = $row['Field'];
	$out .=$first_row ? '<td class=xl2216681 nowrap><b>'.$row['Field'].'</b></td>'."\n" : '';
}
$out .= $first_row ? "</tr>\n" : '';
$result = mysql_query('select * from '.$table_name);
while ($row = mysql_fetch_array($result)){
	$out .= '<tr>';
	foreach ($f_name as $k) $out .= '<td class=xl2216681 nowrap>'. ($row[$k]== null ? $null_to : $row[$k] ).'</td>'."\n";
	$out .= "</tr>\n";
}
return $out .= '</table>
</div>
</body>
</html>';
}
if ($_POST['Submit']){
$hr = isset($_POST['header_row']) ? true : false;
$file = $_POST['field'];
if ($file){
	$file .= eregi('.xls',$_POST['field']) ? '' : '.xls';
	$fp = fopen($file, 'w');
	fwrite($fp ,convert_to_xls($_POST['table'],$hr,$_POST['db_name'],$_POST['nul']));
	fclose($fp);
	echo '<hr /><a href="',$file,'">FILE</a>';
} else echo '<hr />No file name';
//echo convert_to_xls($_POST['table'],$hr,$_POST['db_name'],$_POST['nul']);
}
?>

Hypnos · August 18, 2007

http://www.phphacks.com/content/view/26/33/

http://pear.php.net/package/Spreadsheet_Excel_Writer

Edward · August 20, 2007

Thank you Sasa and everyone for your help, I've got it working now!

jber · August 27, 2007

I´ve been following this thread being redirected from another board on this forum. My question is, i can get it working if i want to have the full table into an xls file. But if i want to select some fields from a table and passing them into an xls file it does not work properly, because i can´t get the name of the fields properly.

The query that selects me some fields is



$query = " SELECT " .$select_cols . " FROM " .$_SESSION["nomtab"]. " ";
$result = mysql_query($query);

where select_cols contains the name of the fields i want to get and nomtab is the name of the table.

My modification to the great code provided before is this :


$query = " SELECT " .$select_cols . " FROM " .$_SESSION["nomtab"]. " ";
$result = mysql_query($query); 
$num_types = array( 'NUMERIC', 'DECIMAL', 'INTEGER', 'SMALLINT', 'FLOAT', 'REAL', 'DOUBLE PRECISION');

$out .=$first_row ? "<tr>\n" : '';
while ($row = mysql_fetch_array($result)){
	$f_name[] = $row['Field'];
	$out .=$first_row ? '<td class=xl2216681 nowrap><b>'.$row['Field'].'</b></td>'."\n" : '';
}
$out .= $first_row ? "</tr>\n" : '';







$query = " SELECT " .$select_cols . " FROM " .$_SESSION["nomtab"]. " ";
$result = mysql_query($query); 
//echo mysql_errno($db) . ": " . mysql_error($db) . "\n";
while ($row = mysql_fetch_array($result)){
	$out .= '<tr>';
	foreach ($f_name as $k) $out .= '<td class=xl2216681 nowrap>'. ($row[$k]== null ? $null_to : $row[$k] ).'</td>'."\n";
	$out .= "</tr>\n";
}

But it does not work...please, can you help me?

sasa · August 27, 2007

try

//query = " SELECT " .$select_cols . " FROM " .$_SESSION["nomtab"]. " ";
//$result = mysql_query($query); 
//$num_types = array( 'NUMERIC', 'DECIMAL', 'INTEGER', 'SMALLINT', 'FLOAT', 'REAL', 'DOUBLE PRECISION');

$out .=$first_row ? "<tr>\n" : '';
        $result = explode(',', $select_cols);  
//while ($row = mysql_fetch_array($result)){
        foreach($result as $field){
	$f_name[] = trim($field);;
	$out .=$first_row ? '<td class=xl2216681 nowrap><b>'.trim($field).'</b></td>'."\n" : '';
}
$out .= $first_row ? "</tr>\n" : '';

jber · August 28, 2007

Thanks a lot, it does work.

I´ve implemented your code in only one php file, extracting the names of the tables, cols and db from another previous forms. It does work properly.

Only one minor question. The table i want to pass has numbers. When i see the table in excel it shows me as a kind of error and i have to tell excel to treat some text as numbers. Is there a way to force excel to treat numbers as numbers?

sasa · August 28, 2007

change x:str from <table ..> tag (<table x:str border=0 cellpadding=0 ...) to x:num

big number will be importen in scient form

or

you can use x:str and x:num in <td ...> tag

jber · August 28, 2007

thanks it worked (i used the second option x:str in table and x:num in td ) perfectly.

I have one minor problem related maybe with the language i spoke (spanish) where some words have ´ above some letters (accent ) like Málaga . When i see the string in table format, not xls , i see it properly, i see Málaga but when i pass the information to excel, it does not work like it should, showing something like Mᬡga.

Is there any way to solve this or it is a language issue? why it does not pass the string as it is and makes changes ?

Jessica · August 28, 2007

I think you need to change the character set - where you do it, I don't know :/

sherbetlemon · June 19, 2008

hi! I'm also using namespace for generating excel files.. there are several columns where data are formatted (i.e., there are <p>, <br/>, etc..) the problem is that when excel encounters <p> or <br/> tag it moves from the current cell to next row's cell.. Is there a way to prevent this?? I know that ALT+Enter makes it possible in MS Excel but how do I say ALT+Enter in my php code??

Thanks in advance!!

sherbetlemon · June 19, 2008

hi! I'm also using namespace for generating excel files.. there are several columns where data are formatted (i.e., there are <p>,
<br/>
, etc..) the problem is that when excel encounters <p> or
<br/>
tag it moves from the current cell to next row's cell.. Is there a way to prevent this?? I know that ALT+Enter makes it possible in MS Excel but how do I say ALT+Enter in my php code??

Thanks in advance!!

I think it is also worth mentioning that the data of the columns i'm referring to is somewhat of TEXT or LONG TEXT category in MySQL..

sherbetlemon · June 19, 2008

guys,, how can i simulate ALT-Enter in HTML or in PHP?? chr(10), chr(13), \n is not working.. how can i insert line break without moving to the next cell??? i really need this.. Thanks in advance for you help.. :-)

sherbetlemon · June 20, 2008

up!! ???

Sign In

Exporting database to Excel (via website, NOT phpMyAdmin)?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information