Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Simple Excel Writer in 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;
}
}
@victusfate

This comment has been minimized.

Copy link

@victusfate victusfate commented Apr 25, 2011

Thanks Matt, this could come in handy.

@scvinodkumar

This comment has been minimized.

Copy link

@scvinodkumar scvinodkumar commented Sep 30, 2012

This is really great to see a simple class...but it would be great if you provide how to use this class

@jkuchynka

This comment has been minimized.

Copy link

@jkuchynka jkuchynka commented Feb 13, 2013

Example of how to use this:

$xls = new Excel('Report');
foreach ($rows as $num => $row) {
  $xls->home();
  $xls->label($row['id']);
  $xls->right();
  $xls->label($row['title']);
  $xls->down();
}
ob_start();
$data = ob_get_clean();
file_put_contents(__DIR__ .'/report.xls', $data);
@tbattur22

This comment has been minimized.

Copy link

@tbattur22 tbattur22 commented Feb 14, 2013

Thanks Matt, great simple example to export data to excel file. Is it possible to create multiple sheets within the same excel file?

@petrovitch

This comment has been minimized.

Copy link

@petrovitch petrovitch commented Mar 10, 2013

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); ?>
@Olli79

This comment has been minimized.

Copy link

@Olli79 Olli79 commented Sep 26, 2013

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

@Karthik4dec

This comment has been minimized.

Copy link

@Karthik4dec Karthik4dec commented Nov 8, 2013

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

@extremecommunication

This comment has been minimized.

Copy link

@extremecommunication extremecommunication commented Nov 13, 2013

Thanks a lot, it's working perfectly!

Alberto

@marebe1602

This comment has been minimized.

Copy link

@marebe1602 marebe1602 commented Jun 6, 2014

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

@sufimalek

This comment has been minimized.

Copy link

@sufimalek sufimalek commented Jun 18, 2014

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

@avinashData

This comment has been minimized.

Copy link

@avinashData avinashData commented Sep 11, 2014

How to download xls file with Unicode character ?

@MarkBiesheuvel

This comment has been minimized.

Copy link

@MarkBiesheuvel MarkBiesheuvel commented Oct 17, 2014

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?

@andrebian

This comment has been minimized.

Copy link

@andrebian andrebian commented Jun 18, 2015

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

@design2developindia

This comment has been minimized.

Copy link

@design2developindia design2developindia commented Aug 11, 2015

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

@zhanrongwang

This comment has been minimized.

Copy link

@zhanrongwang zhanrongwang commented Aug 27, 2015

The file cannot be opened by office 2010

@dhaval-parekh

This comment has been minimized.

Copy link

@dhaval-parekh dhaval-parekh commented Sep 4, 2015

Grate tool...

@cunbong

This comment has been minimized.

Copy link

@cunbong cunbong commented Oct 20, 2015

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???

@jvila

This comment has been minimized.

Copy link

@jvila jvila commented Mar 16, 2016

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);
@Nasirinezhad

This comment has been minimized.

Copy link

@Nasirinezhad Nasirinezhad commented Mar 17, 2016

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();

@omeha2

This comment has been minimized.

Copy link

@omeha2 omeha2 commented Apr 10, 2016

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

@amit-joshi00089

This comment has been minimized.

Copy link

@amit-joshi00089 amit-joshi00089 commented Apr 16, 2016

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.

@v-leonhou

This comment has been minimized.

Copy link

@v-leonhou v-leonhou commented Jun 5, 2016

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?

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Jun 6, 2016

Good work, Thanks!

@idmeneo

This comment has been minimized.

Copy link

@idmeneo idmeneo commented Jul 4, 2016

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

@Keirodev

This comment has been minimized.

Copy link

@Keirodev Keirodev commented Jul 12, 2016

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

@sbsbessa

This comment has been minimized.

Copy link

@sbsbessa sbsbessa commented Aug 3, 2016

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?

@baljindersingh88

This comment has been minimized.

Copy link

@baljindersingh88 baljindersingh88 commented Sep 19, 2016

Is it possible to merge two columns ?

@Blair2004

This comment has been minimized.

Copy link

@Blair2004 Blair2004 commented Nov 29, 2016

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.

@anikn

This comment has been minimized.

Copy link

@anikn anikn commented May 12, 2017

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

@cylix99

This comment has been minimized.

Copy link

@cylix99 cylix99 commented Feb 22, 2018

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

@rafaelcentric

This comment has been minimized.

Copy link

@rafaelcentric rafaelcentric commented Apr 26, 2018

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

@hardeep360

This comment has been minimized.

Copy link

@hardeep360 hardeep360 commented May 4, 2018

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;

}

@ChipNeedham

This comment has been minimized.

Copy link

@ChipNeedham ChipNeedham commented Jan 11, 2019

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

`

@Marian0

This comment has been minimized.

Copy link

@Marian0 Marian0 commented May 4, 2021

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment