export to excel

angel777 · March 29, 2008

hi.. may i know how to export a table data to excel sheet ?

slpctrl · March 29, 2008

<?php
//Written by Dan Zarrella. Some additional tweaks provided by JP Honeywell
//pear excel package has support for fonts and formulas etc.. more complicated
//this is good for quick table dumps (deliverables)

include('DB_connection.php');
$result = mysql_query('select * from excel_test', $linkID);
$count = mysql_num_fields($result);

for ($i = 0; $i < $count; $i++){
    $header .= mysql_field_name($result, $i)."\t";
}

while($row = mysql_fetch_row($result)){
  $line = '';
  foreach($row as $value){
    if(!isset($value) || $value == ""){
      $value = "\t";
    }else{
# important to escape any quotes to preserve them in the data.
      $value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi line.
# the good news is that numbers remain numbers in Excel even though quoted.
      $value = '"' . $value . '"' . "\t";
    }
    $line .= $value;
  }
  $data .= trim($line)."\n";
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
  $data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
  $data = "\nno matching records found\n";
}

# This line will stream the file to the user rather than spray it across the screen
header("Content-type: application/octet-stream");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data;
?>

http://www.stargeek.com/php_scripts.php?script=2 <-----source

atl_andy · March 29, 2008

That was easier than expected.

Only one typo on the page:

$line = '';

Should be:

$line = ''";

webguync · March 30, 2008

this script worked well, but I get a 'no matching records found' when there s/b records found.

how can I debug this?

angel777 · March 30, 2008

thanks for the reply... but how do i combine with my code ?

after user press the export button .. how do i link to your script?

atl_andy · March 30, 2008

I got this to work as typed. The only changes you need to make are:

1. The connection to your db

2. The query

That's it. I listed the typo in a previous reply. It should work exactly as typed otherwise.

atl_andy · March 30, 2008

$line = "";

angel777 · March 30, 2008

may i know what is the meaning of $linkID

<?php

include('./uservar.php');

$connection=mysql_connect($server, $user, $pass);

if(!$connection)

die("Connection failed");

else

{

$result = mysql_query('select * from panel_registration', $linkID);

$count = mysql_num_fields($result);

........ your code.............

}

?>

many error comes out ..

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:\wamp\www\Ling web\successful_login.php on line 53

Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Ling web\successful_login.php on line 54

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Ling web\successful_login.php on line 60

Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\Ling web\successful_login.php:17) in C:\wamp\www\Ling web\successful_login.php on line 88

Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\Ling web\successful_login.php:17) in C:\wamp\www\Ling web\successful_login.php on line 91

Warning: Cannot modify header information -

Warning: Cannot modify header information -

no matching records found

angel777 · March 30, 2008

i changed to the below.. but even worst

$result = mysql_query('select * from panel_registration', $connection);

atl_andy · March 30, 2008

You should be able to leave $linkID out. It is probably included in the author's connection script.

angel777 · March 30, 2008

hi.. thanks for the effort to reply.

i had chaged my code to be as below.

<?php
include('./uservar.php');
$connection=mysql_connect($server, $user, $pass);
   if(!$connection)               
      die("Connection failed");      
               
   else
   {
      $result = mysql_query('select * from panel_registration');
      $count = mysql_num_fields($result);

                          ........ your code.............
              }

?>

but stil got error on the header.. why??

Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\Ling web\successful_login.php:17) in C:\wamp\www\Ling web\successful_login.php on line 89

Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\Ling web\successful_login.php:17) in C:\wamp\www\Ling web\successful_login.php on line 92

Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\Ling web\successful_login.php:17) in C:\wamp\www\Ling web\successful_login.php on line 93

Warning: Cannot modify header information - headers already sent by (output started at C:\wamp\www\Ling web\successful_login.php:17) in C:\wamp\www\Ling web\successful_login.php on line 94

redarrow · March 30, 2008

In this script USERVAR.PHP page you got white spaces makeing the header triger a error

solution......

please remember ob_start() and ob_flush() php function should only be used in

certon situations please ....

try this and see what happends....

<?php
ob_start();
include('./uservar.php');
$connection=mysql_connect($server, $user, $pass);
   if(!$connection)               
      die("Connection failed");      
               
   else
   {
      $result = mysql_query('select * from panel_registration');
      $count = mysql_num_fields($result);

                          ........ your code.............
              }
ob_flush();
?>

angel777 · March 30, 2008

same error !! cannot

my uservar

<?php

$server="localhost";

$user="root";

$pass="***";

$db="***";

?>

redarrow · March 30, 2008

post your current page please should off worked cheers.

angel777 · March 30, 2008

thanks for the effort.. thanks

below is my full code.. the uservar is as the previous post...

<?php
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<LINK href="oscc.css" type=text/css rel=stylesheet>

</head>

<body>
<table width="100%">
    <tr>
  <TD class=tdNB align=center ><B>Welcome:  <?php  echo $_SESSION[username] ?></B></TD>
</tr></table>



<TABLE 
style="BORDER-RIGHT: 1px dashed; BORDER-TOP: 1px dashed; BORDER-LEFT: 1px dashed; COLOR: #666633; BORDER-BOTTOM: 1px dashed" 
cellSpacing=1 cellPadding=2 width=600 align="center" border=0>
  <TBODY>
  <TR>
    <TD class=colspan align=middle colSpan=2>Explaination</TD>
  </TR>
  <TR>
    <TD vAlign=center><B>1.</B> Kata Laluan Asal: Masukkan kata laluan anda 
      yang asal. Pastikan kata laluan anda adalah betul.<BR><B>2.</B> Kata 
      Laluan Baru: Masukkan kata laluan yang baru, paling kurang 6 
      aksara.<BR><B>3.</B> Pengesahan Kata Laluan: Sahkan kata laluan anda yang 
      baru, pastikan pengesahan sama dengan kata laluan yang baru.<BR><B>4.</B> 
      Kata laluan tidak boleh kosong atau mengandungi sebarang ruang 
    kosong.<BR></TD></TR></TBODY></TABLE>


<INPUT class=button type=submit value=Export name=submit>
</body>
</html>
<?php
//Written by Dan Zarrella. Some additional tweaks provided by JP Honeywell
//pear excel package has support for fonts and formulas etc.. more complicated
//this is good for quick table dumps (deliverables)
ob_start();
include('./uservar.php');
$connection=mysql_connect($server, $user, $pass);
if(!$connection)					
	die("Connection failed");		

else
{
mysql_select_db($db);	
	$result = mysql_query('select * from panel_registration');
	$count = mysql_num_fields($result);

	for ($i = 0; $i < $count; $i++){
		$header .= mysql_field_name($result, $i)."\t";
	}

	while($row = mysql_fetch_row($result)){
	  $line = "";
	  foreach($row as $value){
		if(!isset($value) || $value == ""){
		  $value = "\t";
		}else{
	# important to escape any quotes to preserve them in the data.
		  $value = str_replace('"', '""', $value);
	# needed to encapsulate data in quotes because some data might be multi line.
	# the good news is that numbers remain numbers in Excel even though quoted.
		  $value = '"' . $value . '"' . "\t";
		}
		$line .= $value;
	  }
	  $data .= trim($line)."\n";
	}
	# this line is needed because returns embedded in the data have "\r"
	# and this looks like a "box character" in Excel
	  $data = str_replace("\r", "", $data);


	# Nice to let someone know that the search came up empty.
	# Otherwise only the column name headers will be output to Excel.
	if ($data == "") {
	  $data = "\nno matching records found\n";
	}

	# This line will stream the file to the user rather than spray it across the screen
	header("Content-type: application/octet-stream");

	# replace excelfile.xls with whatever you want the filename to default to
	header("Content-Disposition: attachment; filename=excelfile.xls");
	header("Pragma: no-cache");
	header("Expires: 0");

	echo $header."\n".$data;


}
ob_flush();
?>

redarrow · March 30, 2008

try that ob_start starts at beginig of page before session_start();

<?php ob_start();
session_start();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<LINK href="oscc.css" type=text/css rel=stylesheet>

</head>

<body>
<table width="100%">
    <tr>
  <TD class=tdNB align=center ><B>Welcome:  <?php  echo $_SESSION[username] ?></B></TD>
</tr></table>



<TABLE 
style="BORDER-RIGHT: 1px dashed; BORDER-TOP: 1px dashed; BORDER-LEFT: 1px dashed; COLOR: #666633; BORDER-BOTTOM: 1px dashed" 
cellSpacing=1 cellPadding=2 width=600 align="center" border=0>
  <TBODY>
  <TR>
    <TD class=colspan align=middle colSpan=2>Explaination</TD>
  </TR>
  <TR>
    <TD vAlign=center><B>1.</B> Kata Laluan Asal: Masukkan kata laluan anda 
      yang asal. Pastikan kata laluan anda adalah betul.<BR><B>2.</B> Kata 
      Laluan Baru: Masukkan kata laluan yang baru, paling kurang 6 
      aksara.<BR><B>3.</B> Pengesahan Kata Laluan: Sahkan kata laluan anda yang 
      baru, pastikan pengesahan sama dengan kata laluan yang baru.<BR><B>4.</B> 
      Kata laluan tidak boleh kosong atau mengandungi sebarang ruang 
    kosong.<BR></TD></TR></TBODY></TABLE>


<INPUT class=button type=submit value=Export name=submit>
</body>
</html>
<?php
//Written by Dan Zarrella. Some additional tweaks provided by JP Honeywell
//pear excel package has support for fonts and formulas etc.. more complicated
//this is good for quick table dumps (deliverables)

include('./uservar.php');
$connection=mysql_connect($server, $user, $pass);
if(!$connection)					
	die("Connection failed");		

else
{
mysql_select_db($db);	
	$result = mysql_query('select * from panel_registration');
	$count = mysql_num_fields($result);

	for ($i = 0; $i < $count; $i++){
		$header .= mysql_field_name($result, $i)."\t";
	}

	while($row = mysql_fetch_row($result)){
	  $line = "";
	  foreach($row as $value){
		if(!isset($value) || $value == ""){
		  $value = "\t";
		}else{
	# important to escape any quotes to preserve them in the data.
		  $value = str_replace('"', '""', $value);
	# needed to encapsulate data in quotes because some data might be multi line.
	# the good news is that numbers remain numbers in Excel even though quoted.
		  $value = '"' . $value . '"' . "\t";
		}
		$line .= $value;
	  }
	  $data .= trim($line)."\n";
	}
	# this line is needed because returns embedded in the data have "\r"
	# and this looks like a "box character" in Excel
	  $data = str_replace("\r", "", $data);


	# Nice to let someone know that the search came up empty.
	# Otherwise only the column name headers will be output to Excel.
	if ($data == "") {
	  $data = "\nno matching records found\n";
	}

	# This line will stream the file to the user rather than spray it across the screen
	header("Content-type: application/octet-stream");

	# replace excelfile.xls with whatever you want the filename to default to
	header("Content-Disposition: attachment; filename=excelfile.xls");
	header("Pragma: no-cache");
	header("Expires: 0");

	echo $header."\n".$data;


}
ob_flush();
?>

angel777 · March 30, 2008

hi .. thanks .. it has solve that problem.. thanks

however, huuhhh.. it did not export the database data out .. instead it exported whatever in the page i view..

Kata Laluan Asal: Masukkan kata laluan anda
yang asal. Pastikan kata laluan anda adalah betul.<BR><B>2.</B> Kata

Laluan Baru: Masukkan kata laluan yang baru, paling kurang 6

aksara.<BR><B>3.</B> Pengesahan Kata Laluan: Sahkan kata laluan anda yang

baru, pastikan pengesahan sama dengan kata laluan yang baru.<BR><B>4.</B>

Kata laluan tidak boleh kosong atau mengandungi sebarang ruang

kosong.<BR></TD></TR></TBODY></TABLE>

<INPUT class=button type=submit value=Export name=submit>

it shown all those thing out instead of the database data ...

angel777 · March 30, 2008

hi.. any idea expert. ?

angel777 · March 30, 2008

may be u can look at my link here

http://www.gvssb.mplace.org/successful_login.php

when export.. it is not reading the database data

Sign In

export to excel

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

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information