Exporting database data into excel format

Question

I have the following code for exporting data from database to excel.

<?PHP
include '../connection.php';
  $colnames = array(
    "fname" => "First Name",
    "lname" => "Last Name",
    "email" => "Email Address",
    "phone" => "Phone No.",
    "timeFrom" => "Time From",
    "timeto" => "Time to",
    "comments" => "User Comments"
  );


   function cleanData(&$str)
     {
       $str = preg_replace("/\t/", "\\t", $str);
       $str = preg_replace("/\r?\n/", "\\n", $str);
       if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
     }


   function map_colnames($input){
    global $colnames;
    return isset($colnames[$input])? $colnames[$input]:$input;
   }

  // filename for download
  $filename = "website_data_" . date('Ymd') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

  $flag = false;
  $result = mysql_query("SELECT * FROM futurehomeowner1 ORDER BY id") or die(mysql_error());
  while(false !== ($row = mysql_fetch_assoc($result))) {
    if(!$flag) {
      // display field/column names as first row
      $firstline=array_map('map_colnames',array_keys($row));
      fputs($firstline,',','"');
      echo implode("\t", array_keys($row)) . "\r\n";
      $flag = true;
    }

    array_walk($row, 'cleanData');

    echo implode("\t", array_values($row)) . "\r\n";
  }
  exit;
?>

It is working fine and getting data from database to excel. I want to use header names as my own not a database column names. The problem is that when the excel file downloads there is no header names and showing this message "Warning: fputs() expects parameter 1 to be resource, array given". Below of this message data is being showing correctly but in one column.

Is .xls a strict requirement or would .csv work for your scenario? Also you should avoid using the deprecated mysql_* functions. — Joe Meyer
– Joe Meyer, Commented Jun 2, 2014 at 3:29
@JoeMeyer .xls is a strict requirement. Regarding mysql_* functions I will use it in the future because my project is in the last stages. — qaser
– qaser, Commented Jun 2, 2014 at 5:40

Jacer Omri · Accepted Answer · 2014-06-01 21:25:36Z

You used fputs in a wrong way, you should pass a reference to the file then the string. First you should open a file handler to your filename to pass it later.

<?PHP
include '../connection.php';
  $colnames = array(
    "fname" => "First Name",
    "lname" => "Last Name",
    "email" => "Email Address",
    "phone" => "Phone No.",
    "timeFrom" => "Time From",
    "timeto" => "Time to",
    "comments" => "User Comments"
  );


   function cleanData(&$str)
     {
       $str = preg_replace("/\t/", "\\t", $str);
       $str = preg_replace("/\r?\n/", "\\n", $str);
       if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
     }


   function map_colnames($input){
    global $colnames;
    return isset($colnames[$input])? $colnames[$input]:$input;
   }

  // filename for download
  $filename = "website_data_" . date('Ymd') . ".xls";
  $handler = fopen($filename, "w+");
  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

  $flag = false;
  $result = mysql_query("SELECT * FROM futurehomeowner1 ORDER BY id") or die(mysql_error());
  while(false !== ($row = mysql_fetch_assoc($result))) {
    if(!$flag) {
      // display field/column names as first row
      $firstline=array_map('map_colnames',array_keys($row));
      fputs($handler, implode("\t",$firstline));
      $flag = true;
    }

    array_walk($row, 'cleanData');

    fputs($handler, implode("\t", array_values($row)) . "\r\n");
  }
  exit;
?>

have a look at php manual

Collectives™ on Stack Overflow

Exporting database data into excel format

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related