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

commented Apr 25, 2011

Thanks Matt, this could come in handy.

@scvinodkumar

This comment has been minimized.

Copy link

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

@jbizzay

This comment has been minimized.

Copy link

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

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

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

commented Sep 26, 2013

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

@Karthik4dec

This comment has been minimized.

Copy link

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

commented Nov 13, 2013

Thanks a lot, it's working perfectly!

Alberto

@marebe1602

This comment has been minimized.

Copy link

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

commented Jun 18, 2014

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

@avinashData

This comment has been minimized.

Copy link

commented Sep 11, 2014

How to download xls file with Unicode character ?

@MarkBiesheuvel

This comment has been minimized.

Copy link

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

commented Jun 18, 2015

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

@design2developindia

This comment has been minimized.

Copy link

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

commented Aug 27, 2015

The file cannot be opened by office 2010

@dhaval-parekh

This comment has been minimized.

Copy link

commented Sep 4, 2015

Grate tool...

@cunbong

This comment has been minimized.

Copy link

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

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

This comment has been minimized.

Copy link

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

@Ramonlord

This comment has been minimized.

Copy link

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

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

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

commented Jun 6, 2016

Good work, Thanks!

@idmeneo

This comment has been minimized.

Copy link

commented Jul 4, 2016

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

@Keirodev

This comment has been minimized.

Copy link

commented Jul 12, 2016

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

@sbsbessa

This comment has been minimized.

Copy link

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

commented Sep 19, 2016

Is it possible to merge two columns ?

@Blair2004

This comment has been minimized.

Copy link

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

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

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

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

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

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

`

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.