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; 
  }
}

Getting no results. I can echo information within the ob_start block and get a valid xls file, but no output from the Excel class. Even tried moving the opening op_start() to different locations.Fails on Linux and Windows-7.

top(); $xls->home(); foreach ($colors as $color) { $xls->label($color); $xls->right(); $xls->down(); }; $data = ob_get_clean(); file_put_contents('report.xls', $data); ?>

You need to fire up the $xls->send() to get it work ;)

Hi this code works fine but can you please share how to create multiple sheets in single file?
Thanks in advance

Thanks a lot, it's working perfectly!

Alberto

Thanks for the tool!
Why didn't you add
header("Content-Type: application/vnd.ms-excel; charset=iso-8859-1");
in header function?

Is it possible to create multiple sheets within the same excel file?

How to download xls file with Unicode character ?

My users are getting a security warning when opening an excel file generated with this plugin.
See https://technet.microsoft.com/library/security/2501584

Is anyone else having this issue?

@MarkBiesheuvel I have this behavior too. Do you fix it?

Hi
I want to make first cell text bold. So for that what i need to code in label?

The file cannot be opened by office 2010

Grate tool...

if my data:
$data = array(array('Item ID', 'Description', 'Main Category', 'Quantity', 'Approx. EXT Wholesale Value'));
when I write, function $xls->label($data); can not writer. So $data is a string, it can writer. Why???

Hi @petrovitch Did you solve your problem?
I can generate the xls file but it contains no results.
Does anyone know the solution?

My code is:
$this->load->library('excel');

    $title = "Sheet1";
    $colors = array("red", "blue", "green", "yellow", "orange", "purple");

    $xls = new Excel($title);

    foreach ($colors as $color)
    {
        $xls->home();
        $xls->label($color);
        $xls->right();
        $xls->down();
    };
    ob_start();
    $data = ob_get_clean();
    file_put_contents('report.xls', $data);

it don't work with utf-8 ?!
$xls->home();
$xls->label('ردیف\t');
$xls->right();
$xls->label('کد شعبه');
$xls->right();
$xls->label('کد پرسنلی');
$xls->right();
$xls->label('نام و نام خانوادگی');
$xls->right();
$xls->label('مبلغ کل');
$xls->down();

Dear author, can you please tell how to make it work with UTF8 characters?
Thanks

Hello,
can you please let me know what is $data ? how data will come in that?
we tried same but we got blank excel sheet.

when i created the excel file with the library ?i got the chinese Garbled ，and i checked the result garbled characters‘ encode using the mb_detect_encoding() function ，the result is UTF-8,and i convert the encode to gbk,the converted strings is also Garbled,so can you help with me?

Good work, Thanks!

Sorry but it doesn't work, the xls file is empty.

For thoses who want to handle special caracters, my gist solution here

we're using @Keirodev's fork to generate excel files and we can successfully open them with LibreOffice but MSOffice says the file is corrupt. Anyone had this problem before?

Is it possible to merge two columns ?

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.