exporting mysql table to excel

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
anna_cm
Forum Newbie
Posts: 5
Joined: Fri Apr 20, 2007 9:04 pm

exporting mysql table to excel

Post by anna_cm »

Hi All,

I need a script to download the mysql table to an excel file. I used the phpfreaks tutorial script but it is not opening in excel sheet.

Please help

Thanks,

anna
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

The first thing you need to do is read viewtopic.php?t=8815..
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

Anna,

Follow this link: http://www.phpfreaks.com/tutorials/114/0.php

Try using search engines to find scripts i.e. in this instance search for something as simple as "php to excel".

Hope the link helps :D
User avatar
Gente
Forum Contributor
Posts: 252
Joined: Wed Jun 13, 2007 9:43 am
Location: Ukraine, Kharkov
Contact:

Post by Gente »

User avatar
yacahuma
Forum Regular
Posts: 870
Joined: Sun Jul 01, 2007 7:11 am

use navicat

Post by yacahuma »

you can also use navicat, point and click no code needed
User avatar
phpdevuk
Forum Contributor
Posts: 220
Joined: Mon Jul 04, 2005 5:31 am
Location: UK
Contact:

Post by phpdevuk »

There are loads of ways to create a file in php that can be opened in excel, you can export in HTML or CSV, or even write a binary xls file.
anna_cm
Forum Newbie
Posts: 5
Joined: Fri Apr 20, 2007 9:04 pm

Post by anna_cm »

Hi All,

Thanks for your reply.

I cannot use pear class as it is not installed in my clients server.

Below is my code, this is downloading an excel file but when opened it is showing as a text file with values seperated with tab.

Code: Select all

<?php
include('../../connection.php');
$result = mysql_query('select * from subscriber');
$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{

      $value = str_replace('"', '""', $value);


      $value = '"' . $value . '"' . "\t";
    }
    $line .= $value;
  }
  $data .= trim($line)."\n";
}


  $data = str_replace("\r", "", $data);



if ($data == "") {
  $data = "\nno matching records found\n";
}

header("Content-type: application/x-excel");
//header("Content-type: application/x-msdownload");
header ("Cache-Control: no-cache, must-revalidate");  
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo "$header\n$data"; 
?>
If anyone knows what went wrong in the code, Please let me know.

anna
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

That's because the code, which you obviously copied without understanding it, generates CSV (which Excel should be able to parse....)
Post Reply