Create a gist now

Instantly share code, notes, and snippets.

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

Thanks Matt, this could come in handy.

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

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

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

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 commented Sep 26, 2013

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

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

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.

v-leonhou commented Jun 5, 2016 edited

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?

ky61k commented Jun 6, 2016

Good work, Thanks!

idmeneo commented Jul 4, 2016

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

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

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?

Is it possible to merge two columns ?

Blair2004 commented Nov 29, 2016 edited

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 commented May 12, 2017

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

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