how to generate excel file using php

Question

avocado_juice 0 Newbie Poster

14 Years Ago

Hi, I have this code. It is not working properly. Anybody may know the solution?

Thank you

<?php include 'admin_homepage.php' ?>
<div id="sidebar1">
<b></b><br /></br></br><img src="MMU_logo.jpg" width="180" height="150"/><br />
<a href="admin_view_student.php" style="font-size:13px" >View Student</a><br />
<a href="admin_new_student_reg.php" style="font-size:13px">Add Student</a><br />
<a href="student_excel.php" style="font-size:13px">Get Excel File</a><br />
<a href="student_mailing_list.php" style="font-size:13px">Get Mailing List</a><br />   
<a href="admin_email_all_students.php" style="font-size:13px">Send Email to All Students</a><br />  
		<a href="admin_edit_student_email.php">Email to student (admin) - (first phase)</a><br>	
		<a href="edit_student_email.php">Email to student (first phase)</a><br>		
</div>
  	<div id="mainContent">
  		<table width="530" border="0px">
			<tr>
				<td width="530" height="10" bgcolor="#006" align="center"  
				style="font:Times New Roman; font-size:10px; color:#FFF; font-weight:bold" >Get Excel File</td>
			</tr>
        	<tr bgcolor="#FFFFFF">
                <td>   
                    <div style="width:527px; height:470px; overflow:scroll ">
					<form name="regform" action="stud_excel.php" method="post" enctype="multipart/form-data">
					<table border="0" bgcolor="#FFFFFF">
					<tr>
					<td>Period of Industrial Training</td>
					</tr>
					<td><br>
					Session <select name=year>
					<option value="" selected></option>
					<?php
					$sql=mysql_query("select * from year");
					while ($data=mysql_fetch_array($sql)){
					  echo "<option value={$data['year']}>{$data['year']}</option>";
					}
					?>
					</select>
					Trimester <select name=trimester>
					<option value="" selected></option>
					<?php
					$sql1=mysql_query("select * from trimester");
					while ($data=mysql_fetch_array($sql1)){
					  echo "<option value={$data[trimester]}>{$data[trimester]}</option>";
					}
					?>
					</select>
					</td>
					<tr> 
					<td colspan="2"><br><input name="btnsub" type="submit" value="Submit"></td>
					</tr>
					<br>


<?php
if (isset($_POST["btnsub"]) && ($_POST["btnsub"] == "Submit"))
{

$year = $_POST['year'];
$trimester = $_POST['trimester'];

$sql2 = mysql_query("SELECT * FROM student_registration where year = '$year' and trimester = '$trimester' ORDER BY stu_ID");
$count   = mysql_num_fields($sql2);
$header = '';
$data = '';

for ($i = 0; $i < $count; $i++) {
  $header .= mysql_field_name($sql2, $i)."\t";
}
 
while($row = mysql_fetch_assoc($sql2)) {
  $line = '';
  foreach($row as $key => $value) {
 
    if ($key === 'date_of_reg') {
      $value  = '"' . date('d/m/Y', $value) . '"';
    } else {
      $value  = '"' . $value . '"';
    }
 
    if ($line === '') {
      $line  = $value;
    } else {
      $line .= "\t" . $value;
    }
  }
 
  $data .= $line . "\n";
}

if ($data == "") 
{
	$data = "There is no record inside the table.";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=stud_excel.xls");
header("Pragma: no-cache");
header("Expires: 0");

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

}
?>            
					 

 </table></form>
</td>
</tr>
                    </div>
                </td>
           	</tr>
        </table>
	</div>
	<div class="clearfloat"></div>
  	<div id="footer">
    	<center>Copyright © 2010</center>
  	</div>
</div>
</body>
</html>

microsoft-office php

Edited 14 Years Ago by Ezzaral because: Added code tags. Please use them to format all code that you post.

2 Contributors
4 Replies
822 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by avocado_juice

hielo 65 Veteran Poster

14 Years Ago

Based on what you posted, it looks like what you are actually doing is:

a. send some HTML markup by including admin_homepage.php'(probably the <html><head>...</head><body>...)
b. query the db
c. download excel file

The problem is that if you are going to force a download, you cannot send any HTML markup (or any other content for that matter) to the browser before you send the contents of the file.

So what you need to do is:
a. Determine if something was posted
If YES =>query the db and force the file download ( send the header stuff before you even query the db) and call

exit()

as soon as you send the excel data.

If NO => THEN you include your template file and put whatever else needs to be displayed on the "initial" page (possibly download and/or search options).

Edited 14 Years Ago by hielo because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

avocado_juice 0 Newbie Poster · Answer 1 · 2010-10-31T12:48:32+00:00

actually what I want is to generate excel file with this sql :
"SELECT * FROM student_registration where year = '$year' and trimester = '$trimester' ORDER BY stu_ID"
which year and trimester are get from user's input.

May u give me further explanation about this?

Thank you

hielo 65 Veteran Poster · Answer 2 · 2010-10-31T21:27:38+00:00

I am a newbie here and just wanna say Hi to everyone. I am Crystal from Louisiana, US.

Hi Crystal from Louisiana - Welcome!

actually what I want is to generate excel file with this sql :
"SELECT * FROM student_registration where year = '$year' and trimester = '$trimester' ORDER BY stu_ID"
which year and trimester are get from user's input.
May u give me further explanation about this?

avocado_juice, what was not clear about my previous post? As stated earlier, your problem is that you are first sending HTML and THEN you are attempting to query the db to send the excel file. You need to reverse those steps. Essentially the BEGINNING of your file should be:

<?php
if (isset($_POST["btnsub"]) && ($_POST["btnsub"] == "Submit"))
{
  //here you query the db and send the excel data. You already have the
  //the code for this

  //NOTICE: It is very important that you add the following
  //at the end of this if clause
  exit();
}

//AFTER the if clause you then begin with your HTML
include 'admin_homepage.php';
?>

Thus on initial load, the if clause will evaluate to false and go straight to the include statement which is the start of your HTML document. Once you post, the if clause will be true and it will query the db and send the query output.

avocado_juice 0 Newbie Poster · Answer 3 · 2010-11-01T06:48:53+00:00

avocado_juice 0 Newbie Poster

14 Years Ago

hielo, it works!

Thank you so much