Home is for the experience. Projects are for the ones who are interested!

How To Export HTML to Excel using PHP
PHP

How To Export HTML to Excel using PHP

Excel is among the most used file types to export your website data, so in this tutorial I will show you how to export html to excel using php. It's not that hard I will give you the main class of this functionality, then you only need to call the function and provide the required data to construct the table of the excel and that's it. All the hard work is done by using this class, in the below code you will find all necessary functions to create an excel file with your custom data. Besides this class you will need to construct your data in a special way so the excel to be generated but I will show you how your arrays should look like.

<?php
/**
 * Simple excel writer class with no external dependencies, drop it in and have fun
 * @author Matt Nowack
 * @link https://gist.github.com/ihumanable/929039/edit
 * @license Unlicensed
 * @version 1.0
 */
class Excel {
  private $col;
  private $row;
  private $data;
  private $title;
  /**
   * Safely encode a string for use as a filename
   * @param string $title The title to use for the file
   * @return string The file safe title
   */
  static function filename($title) {
    $result = strtolower(trim($title));
    $result = str_replace("'", '', $result);
    $result = preg_replace('#[^a-z0-9_]+#', '-', $result);
    $result = preg_replace('#\-{2,}#', '-', $result);
    return preg_replace('#(^\-+|\-+$)#D', '', $result);
  }
  /**
   * Builds a new Excel Spreadsheet object
   * @return Excel The Spreadsheet
   */
  function __construct($title) {
    $this->title = $title;
    $this->col = 0;
    $this->row = 0;
    $this->data = '';
    $this->bofMarker();
  }
  /**
   * Transmits the proper headers to cause a download to occur and to identify the file properly
   * @return nothing
   */
  function headers() {
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header("Content-Disposition: attachment;filename=" . Excel::filename($this->title) . ".xls ");
    header("Content-Transfer-Encoding: binary ");
  }
  function send() {
    $this->eofMarker();
    $this->headers();
    echo $this->data;
  }
  /**
   * Writes the Excel Beginning of File marker
   * @see pack()
   * @return nothing
   */
  private function bofMarker() { 
    $this->data .= pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
  }
  /**
   * Writes the Excel End of File marker
   * @see pack()
   * @return nothing
   */
  private function eofMarker() { 
    $this->data .= pack("ss", 0x0A, 0x00); 
  }
  /**
   * Moves internal cursor left by the amount specified
   * @param optional integer $amount The amount to move left by, defaults to 1
   * @return integer The current column after the move
   */
  function left($amount = 1) {
    $this->col -= $amount;
    if($this->col < 0) {
      $this->col = 0;
    }
    return $this->col;
  }
  /**
   * Moves internal cursor right by the amount specified
   * @param optional integer $amount The amount to move right by, defaults to 1
   * @return integer The current column after the move
   */
  function right($amount = 1) {
    $this->col += $amount;
    return $this->col;
  }
  /**
   * Moves internal cursor up by amount
   * @param optional integer $amount The amount to move up by, defaults to 1
   * @return integer The current row after the move
   */  
  function up($amount = 1) {
    $this->row -= $amount;
    if($this->row < 0) {
      $this->row = 0;
    }
    return $this->row;
  }
  /**
   * Moves internal cursor down by amount
   * @param optional integer $amount The amount to move down by, defaults to 1
   * @return integer The current row after the move
   */
  function down($amount = 1) {
    $this->row += $amount;
    return $this->row;
  }
  /**
   * Moves internal cursor to the top of the page, row = 0
   * @return nothing
   */
  function top() {
    $this->row = 0;
  }
  /**
   * Moves internal cursor all the way left, col = 0
   * @return nothing
   */
  function home($col) {
    if(empty($col)){
    $this->col = 0;
  }
  else{
    $this->col = $col;
  }
  }
  /**
   * Writes a number to the Excel Spreadsheet
   * @see pack()
   * @param integer $value The value to write out
   * @return nothing
   */
  function number($value) { 
    $this->data .= pack("sssss", 0x203, 14, $this->row, $this->col, 0x0); 
    $this->data .= pack("d", $value); 
  }
  /**
   * Writes a string (or label) to the Excel Spreadsheet
   * @see pack()
   * @param string $value The value to write out
   * @return nothing
   */
  function label($value) { 
    $length = strlen($value);
    $this->data .= pack("ssssss", 0x204, 8 + $length, $this->row, $this->col, 0x0, $length); 
    $this->data .= $value; 
  }
}

This is the main class you will all the time to use in order to generate excel files. Now just copy paste the code in a file and upload to your server. Now we need to create a function which will send the data to this function to download the file later. In the below example you will see I am using $wpdb, I am using this because I am using wordpress to get the data I need to put in the excel, but this class can be used in any framework or cms. 

public function export_to_excel($fid){
    global $wpdb;
    $fid = intval($fid);
    $form_title = 'Excel';
    $data = $this->getFieldsByFormId($fid);	
    if(!empty($data)){
      //Check that the class exists before trying to use it
      $arrHeader = array();
      foreach($data[0] as $k => $v){
        $arrHeader[] = $k;
      }
      if(!class_exists('Excel')){
        //Include excel class file 
        require_once(dirname(__FILE__).'/inc/Excel.class.php');
        //create excel class object
        $xls = new Excel($form_title);
        $i=0;
        foreach($arrHeader as $colName ){
          $xls->home($i);
          $xls->label($colName);
          $i++;
        }
        foreach ($data as $k => $v){
          $i=0;	
          $xls->down();
          foreach ($v as $k2 => $v2){
            $colVal = ((isset($v[$k2])) ? htmlspecialchars_decode($v[$k2]) : '');
            $xls->home($i);
            $xls->label($colVal);
            $i++;
          }
        }
        $xls->send();
        exit;
      }
    }
  }

As you can see in the above function first time I get all fields I need then, first I create the header of the table represented by the arrHeader array then I pass all the data after I check if the class Excel exists. Using the xls->send() it will create the excel then it will be auto downloaded by the browser. Is quite simple to implement this functionality and and it is very helpful. However if you need an even easier method to export your data you can follow this tutorial where I show you how to export html table in csv using jquery. I hope you this tutorial will help you to achive this functionality, let me know in the comments section.

 

Leave a Reply

Your email address will not be published. Required fields are marked *