Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 968
  • Last Modified:

Export php result table to EXCE sheet

Greetings experts,
I am in an urgent need for your help regarding some codes to export php result into excel sheet.
Lets say I have the following php codes :-

$hostname = "HOSTNAME";
$username = "sa";
$password = "sapassword";
$dbName = "mydatabase";
MSSQL_CONNECT($hostname,$username,$password) or DIE("DATABASE FAILED TO RESPOND.");
mssql_select_db($dbName) or DIE("Table unavailable");

$query = "SELECT Nationality,applicants,familysize FROM custormerstable";
$result = mssql_query($query);
echo ("<CAPTION><B><FONT SIZE='4' color='black' > Customers by Nationality</FONT></B></CAPTION><br>");
echo ("\n<table border=2 BORDERCOLORLIGHT='black' BORDERCOLORDARK='black' width='95%'>\n<thead>\n<tr>\t\n");
echo ("<th align='center'style='background-color:#66FF00'>No</th>");
echo ("<th align='center'style='background-color:#66FF00'>Nationality</th>");
echo ("<th align='center'style='background-color:#66FF00'>Applications</th>");
echo ("<th align='center'style='background-color:#66FF00'>Size</th>");
echo ("\n</tr>\n</thead>\n<tbody>");
$k=0;
for ($i = 0; $i < mssql_num_rows($result); $i++) {
echo("\n<tr style='background-color:#CCFF99'>");
$k=$k+1;
echo ("\n\t<td><center>" . $k . "</center></td>");
$row_array = mssql_fetch_row($result);
for ($j = 0; $j < mssql_num_fields($result); $j++) {
echo ("\n\t<td><CENTER>" . $row_array[$j] . "</CENTER></td>");
}
echo ("\n</tr>");
}
echo ("\n</tbody>\n</table>");
}




The result would show something like :-

No   Nationality  Applications  Size
1          USA          12            22  
2           UK            6             24
3          AUL           2              7
4           TR           4              15


(sometimes the result wold be more than 4 rows .. not necessarly to be 4 rows only)
Now .. what I want to do is .. to put a button ( like form/ action submit ) at the end of the result table .. something like ( click here to exprot to excel sheet) and when you click on that button it should take you to microsoft excel and show you the results in a spreadsheet table.
I heard that the date has problems when exporting php to excel .. ifyou can give me the correct codes that could work even with numbers and dates fields I will be very greatful.
Thank you very much.
0
npiut
Asked:
npiut
  • 5
  • 2
  • 2
  • +1
1 Solution
 
npiutAuthor Commented:
Thank you very much
0
 
DreamMasterCommented:
header("Content-Type: application/vnd.ms-excel; name='excel'");
header("Content-disposition:  attachment; filename="yourfile.xls");

If  you put that at the top of your document it should do the trick..

Regards,
Max.
0
 
npiutAuthor Commented:
I dont' think i understand you correctly.. what document ? the php file ?
php file called ( nationality.php ) it starts with :-
<HTML>
<HEAD>
<TITLE>--</TITLE>
</HEAD>
<body>
<?
then the php codes here and at the end :-
?>
</body>
</html>
I tried several places for your code but couldn't figure out how it works .. also .. what I need is a button at the end of the table ..( excel sheet to be generated when required only).
Regards
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
designbaiCommented:
Try this.

// connection to mysql server
$con = mysql_connect("localhost","username","password");

// selecting the db
mysql_select_db("customerstable",$con);

// preparing sql query
$query = "SELECT Nationality,applicants,familysize FROM custormerstable";
$result = mysql_query($query);

// this is the title of the excel sheet
$title = "No,Nationality,Applications,Size\n";

// looping the query resuls
while ($row = mysql_fetch_array($result)) {
   $cnt++;
   // concatinating the contents with \n character
   $content .= $cnt.",".$row['Nationality'].",".$row['applicants'].",".$row['familysize']."\n";
}

$export_data = $title.$content;

$filename = "your writable folder/export.csv";

// Let's make sure the file exists and is writable first.
if (is_writable($filename)) {

   // In our example we're opening $filename in write mode.
   // that's where $export_data will go when we fwrite() it.
   if (!$handle = fopen($filename, 'w')) {
        print "Cannot open file ($filename)";
        exit;
   }

   // Write $export_data to our opened file.
   if (!fwrite($handle, $export_data)) {
       print "Cannot write to file ($filename)";
       exit;
   }
   
   print "Success, wrote to file ($filename)";
   
   fclose($handle);
   header("Pragma: public"); // required
   header("Expires: 0");
   header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
   header("Cache-Control: private",false); // required for certain browsers
   header("Content-Type: application/ms-excel");
   header("Content-Disposition: attachment; filename=Sales_Report.csv");
   header("Content-Transfer-Encoding: binary");
   header("Content-Length: ".filesize($filename));
   readfile("$filename");
} else {
   print "The file $filename is not writable";
}

Hope this helps
0
 
_GeG_Commented:
There is a real cool PEAR class that does it for you: PEAR::spreadsheet-excel-writer
http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.php
0
 
DreamMasterCommented:
Doesn't matter where you want it...you could put it in the same file and only execute that part upon submittion of your form...but if you can't figure it out...try designbai's code...that should work...

Regards,
Max.
0
 
npiutAuthor Commented:
Thank you very much designbai .. it worked perfectly .. thanks all for your supports
0
 
npiutAuthor Commented:
One more question though .. when I run it it display all the results on the screen .. how can I change it so that it just say ( result were sent to export.csv (without displaying the results on the screen with all those column's contents ) ?
I tried to remark the line print "Success, wrote to file ($filename)"; with //
didn't work though .. it still display all results on the screen.
Thanks again
0
 
npiutAuthor Commented:
Strange .. when I run it again it asks me save it or open it and it saves it under Sales_Report.csv ? Am I doing something wrong ??
0
 
designbaiCommented:
Nothing strange. Dont worry. The following line is the line which you are taking about. In that we have specified the file name.

Our whole code will create a .csv file and give the option to download the file to the user. Sales_report.csv is nothing but the file name to save it. Changing the filename in the following line will give you different filename while saving.

header("Content-Disposition: attachment; filename=Sales_Report.csv");


hope you understand.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now