author: Krasimir Tsonev

Hi there, I'm . Senior front-end engineer with over 13 years of experience. I write, speak and occasionally code stuff. Follow me on Twitter, GitHub, Facebook or LinkedIn

PHP: export data to XLS file

Sometimes it is necessary to present your data in format which is popular and easy to work with. In most cases the CSV format will fit perfectly you and your clients, but sometimes you have to export the information in XLS file. These several functions will help you to do that.
function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
  }
  
  function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
  }
  
  function xlsWriteNumber($Row, $Col, $Value) {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
  }
  
  function xlsWriteLabel($Row, $Col, $Value) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
  }
  // prepare headers information
  header("Content-Type: application/force-download");
  header("Content-Type: application/octet-stream");
  header("Content-Type: application/download");
  header("Content-Disposition: attachment; filename=\\"
    export_ ".date("
    Y - m - d ").".xls\\ "");
  header("Content-Transfer-Encoding: binary");
  header("Pragma: no-cache");
  header("Expires: 0");
  // start 
  exportingxlsBOF();
  // first row 
  xlsWriteLabel(0, 0, "id");
  xlsWriteLabel(0, 1, "name");
  xlsWriteLabel(0, 2, "email");
  // second row 
  xlsWriteNumber(1, 0, 230);
  xlsWriteLabel(1, 1, "John");
  xlsWriteLabel(1, 2, "john@yahoo.com");
  // third row 
  xlsWriteNumber(2, 0, 350);
  xlsWriteLabel(2, 1, "Mark");
  xlsWriteLabel(2, 2, "mark@yahoo.com");
  // end exporting
  xlsEOF();
And the result is:[1]
If you enjoy this post, share it on Twitter, Facebook or LinkedIn. Or maybe comment below: