Simple Excel Writer in PHP

Excel.php

<?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() {
    $this->col = 0;
  }
  
  /**
   * 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; 
  }
}

Very useful library.. you helped me with my POS http://codecanyon.net/item/nexopos-extendable-php-point-of-sale-/16195010
Grocery Crud Export feature was broken.

I got the xls file saved, but its blank. Please provide me the solution

To get data into xls change the send function to:

function send($loc) {
$this->eofMarker();
file_put_contents($loc, $this->data);
}

where $loc is the file location and name

It is working fine in LibreOffice and MSExcel2016

if your excel file is blank, it is because you have to add $xls->send() to your code as Olli79 said. I added it after the for loop. But $xls->send will force the file to be downloaded. If you want to save the file to a specific directory like me, you have to follow cylix99 and edit the Excel Class and look for the function send() and change it to:

function send($loc) {
$this->eofMarker();
file_put_contents($loc, $this->data);
}

Don't forget to add the file location to your $xls->send($loc);

Hi,
I have just added two new functions to it. One to download the file and another one to save file. Please see if you find it useful.
function downloadFile()
{
$this->eofMarker();
$filename = Excel::filename($this->title).".xls";
$fp = fopen( $filename, "w" );
$this->headers();

  if($fp){
      fwrite($fp,$this->data); // Write information to the file
      
      readfile($filename);
      fclose($fp); // Close the file
  }

}

function saveFile($dir, $filename = false ) {
$this->eofMarker();
//$this->headers();
if( $filename == false ) {
$filename = Excel::filename($this->title).".xls";
}

file_put_contents(rtrim($dir,"/").'/'.$filename, $this->data);
//echo $this->data;

}

here's a little something that saves a lot of time, provided you have the data you want inserted comma delimited. Made converting .csv to .xls a breeze.
Input example: "employee id,first name,last name,role id,role name,date begin,time begin,date end,time end,end status"
`

function rowCSV($theString){
$theArray = explode(',',$theString);
$numOfCol = sizeof($theArray);
foreach ($theArray as $colText){
$length = strlen($colText);
$this->data .= pack("ssssss", 0x204, 8 + $length, $this->row, $this->col, 0x0, $length);
$this->data .= $colText;
$this->col += 1;
}
$this->row += 1;
$this->col = 0;
}

`

Hey everybody,

I made utf8 working by changing the following lines on the snippet :

  /**
   * Writes a string (or label) to the Excel Spreadsheet
   * @see pack()
   * @param string $value The value to write out
   * @return nothing
   */
  function label($value)
  {
    $value = mb_convert_encoding($value,'utf-16','utf-8');         👈🏼👈🏼👈🏼👈🏼👈🏼👈🏼👈🏼
    $length = strlen($value);
    $this->data .= pack("ssssss", 0x204, 8 + $length, $this->row, $this->col, 0x0, $length);
    $this->data .= $value;
  }

Note that only utf8 will be supported on label method.