Showing posts with label export to xls in php. Show all posts
Showing posts with label export to xls in php. Show all posts

Tuesday, July 12, 2016

Export data as XLS using PHP

How to export data as xls using php ?

Need to export database fields as xls file using php

Make sure you are well connected

Configure following to start a Mysql Connection

$DB_Server = DB_HOST; //MySQL Server
$DB_Username = DB_USERNAME; //MySQL Username
$DB_Password = DB_PASSWORD;             //MySQL Password
$DB_DBName = DB_DATABASE;         //MySQL Database Name
$DB_TBLName = 'tablename'; //MySQL Table Name
$filename =   'data'.time();       //File Name for xls

//connect to db

$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
    or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
    or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());

// Make Query with required field in xls ,dont include fields not required in xls

$sql ='  Select  field1, field2, field4, field6 from tablename' ;

//execute query
$result = @mysql_query($sql , $Connect)
    or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
$file_ending = "xls";


//header info for browser
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename.xls");
header("Pragma: no-cache");
header("Expires: 0");

/*******Start of Formatting for Excel*******/
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character


//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo ucfirst(mysql_field_name($result,$i)) . "\t";
}
print("\n");
//end of printing column names


//start while loop to fetch data
  
  while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
           
if(!isset($row[$j])){
                            $schema_insert .= "NULL".$sep;
}else{
   $schema_insert .= "$row[$j]".$sep;
}
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
  $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
       print(trim($schema_insert));
       print "\n";
    }

Thanks

That's All