PEAR is archived and read-only

This mirror preserves historical PEAR package releases and metadata so existing references remain available.

Home » File Formats » Spreadsheet_Excel_Writer » Manual

Package for generating Excel spreadsheets

Introduction

Introduction – how to generate Excel files

What is Spreadsheet_Excel_Writer?

Spreadsheet_Excel_Writer is a tool for creating Excel files without the need for COM components. The files generated by the current version of Spreadsheet_Excel_Writer correspond to the Excel 5 (BIFF5) format, so all functionality until that version of Excel (but not beyond) should be available.

Using it

The most common use for Spreadsheet_Excel_Writer will be spitting out large (or not so large) amounts of information in the form of a spreadsheet, which is easy to manipulate with a fairly ubiquitous spreadsheet program such as Excel (or OpenOffice).

So let's cut to the chase and see how this is done:

Typical usage

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

// sending HTTP headers
$workbook->send('test.xls');

// Creating a worksheet
$worksheet =& $workbook->addWorksheet('My first worksheet');

// The actual data
$worksheet->write(0, 0, 'Name');
$worksheet->write(0, 1, 'Age');
$worksheet->write(1, 0, 'John Smith');
$worksheet->write(1, 1, 30);
$worksheet->write(2, 0, 'Johann Schmidt');
$worksheet->write(2, 1, 31);
$worksheet->write(3, 0, 'Juan Herrera');
$worksheet->write(3, 1, 32);

// Let's send the file
$workbook->close();
?>

The first thing you should notice, is that we created a workbook before any worksheets. All worksheets are contained within a workbook, and a workbook may contain several worksheets.

Another important thing, which you should have in mind when programming with Spreadsheet_Excel_Writer, is that ampersand sign (&) that appears when we created our worksheet. That ampersand means we are referencing a Worksheet object instead of copying it. If you don't know what that means, don't worry, all you have to remember is to always use ampersands when calling addWorksheet() for creating a worksheet, or addFormat() for creating a format.

Saving to a regular file

You may have noticed also the following line:


// sending HTTP headers
$workbook->send('test.xls');

What that means is that we are sending our spreadsheet to a browser. But what if we just want to save the spreadsheet in our machine? Well, you just have to omit that line and give a valid file path to the workbook constructor.

For example, if we wanted to save the same spreadsheet we created in our first example to a file named 'test.xls', we would do it like so:

Saving to a regular file

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// We give the path to our file here
$workbook = new Spreadsheet_Excel_Writer('test.xls');

$worksheet =& $workbook->addWorksheet('My first worksheet');

$worksheet->write(0, 0, 'Name');
$worksheet->write(0, 1, 'Age');
$worksheet->write(1, 0, 'John Smith');
$worksheet->write(1, 1, 30);
$worksheet->write(2, 0, 'Johann Schmidt');
$worksheet->write(2, 1, 31);
$worksheet->write(3, 0, 'Juan Herrera');
$worksheet->write(3, 1, 32);

// We still need to explicitly close the workbook
$workbook->close();
?>

More tutorials

If you would like to learn about formatting (fonts, cell color, text alignment, etc...) with Spreadsheet_Excel_Writer, you can check the formatting tutorial here.

Formatting Tutorial

Formatting Tutorial – how to format cells in a spreadsheet

What is a format?

A format is an object of type Spreadsheet_Excel_Writer_Format. This format can be applied to cells inside a spreadsheet so that these cells inherit the properties of the format (text alignment, background color, border colors, etc...).

Using it

Formats can't be created directly by a new call. You have to create a format using the addFormat() method from a Workbook, which associates your Format with this Workbook (you can't use the Format with another Workbook).

Let's see how addFormat() is used:

addFormat usage

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

// Creating the format
$format_bold =& $workbook->addFormat();
$format_bold->setBold();

?>

There, we just created a bold format. Notice the ampersand sign (&) that appears when we created our format. If you don't create your format like that it will appear as if all the format's properties you set are ignored.

Making something useful

Well, we just created our first format, but we didn't use it. Not very smart. So let's do something useful with a format.

Let's say you want to make your regular data filled spreadsheet. Only this time, when you proudly present your beautiful creation to your boss, the thing you most dread happens:

Pointy haired boss - Mmmmhhh, seems OK.

You - Yes, I added those totals as you requested.

Pointy haired boss - Mmmmhhh, you know, there's going to be a lot of customers using this spreadsheet...

You - So...

Pointy haired boss - Mmmmhhh, what do you think of changing the style for those headers there?

You - ...

Of course it won't be just those headers: "why don't we center this title here?", "Could you merge those cells over there?", "what do you think of using the company's colors for those titles?".

There are a number of ways for dealing with this situation, but in this tutorial we will stick to the one which will keep your job.

So let's begin work on the spreadsheet for DotCom.com.

First example

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();

$format_bold =& $workbook->addFormat();
$format_bold->setBold();

// We need a worksheet in which to put our data
$worksheet =& $workbook->addWorksheet();
// This is our title
$worksheet->write(0, 0, "Profits for Dotcom.Com", $format_bold);
// And now the data
$worksheet->write(0, 0, 0);

?>

There. Now all of those VC's out there are going to be calling like crazy asking for an oportunity to invest on DotCom.com. Wait a minute. These are not regular VC's we are talking about. These are very selective guys who wouldn't trust their money to the first start-up they happen to see on the internet. I know! Let's put the company's colors in there!

Second example

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();

$format_bold =& $workbook->addFormat();
$format_bold->setBold();

$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('yellow');
$format_title->setPattern(1);
$format_title->setFgColor('blue');

$worksheet =& $workbook->addWorksheet();
$worksheet->write(0, 0, "Quarterly Profits for Dotcom.Com", $format_title);
// While we are at it, why not throw some more numbers around
$worksheet->write(1, 0, "Quarter", $format_bold);
$worksheet->write(1, 1, "Profit", $format_bold);
$worksheet->write(2, 0, "Q1");
$worksheet->write(2, 1, 0);
$worksheet->write(3, 0, "Q2");
$worksheet->write(3, 1, 0);

$workbook->send('test.xls');
$workbook->close();
?>

Merging cells

If you just tested the previous example you might have noticed that the title would need several cells to be seen correctly, but the format we applied only works for the first cell. So our title does not look very nice.

What can we do to fix that? Well, you could tell your boss that the title looks ok to you, and that he really needs to visit an ophthalmologist. Or you could use cell merging in order to make the title spread over several cells.

For this you have to use the setAlign() method with 'merge' as argument, and create some empty cells so the title can 'use' them as a sort of background (there will be a better way to do this in a future version of Spreadsheet_Excel_Writer).

Applying merging to our example script, we would have this:

Merging cells

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer();

$format_bold =& $workbook->addFormat();
$format_bold->setBold();

$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('yellow');
$format_title->setPattern(1);
$format_title->setFgColor('blue');
// let's merge
$format_title->setAlign('merge');

$worksheet =& $workbook->addWorksheet();
$worksheet->write(0, 0, "Quarterly Profits for Dotcom.Com", $format_title);
// Couple of empty cells to make it look better
$worksheet->write(0, 1, "", $format_title);
$worksheet->write(0, 2, "", $format_title);
$worksheet->write(1, 0, "Quarter", $format_bold);
$worksheet->write(1, 1, "Profit", $format_bold);
$worksheet->write(2, 0, "Q1");
$worksheet->write(2, 1, 0);
$worksheet->write(3, 0, "Q2");
$worksheet->write(3, 1, 0);

$workbook->send('test.xls');
$workbook->close();
?>

Workbook::close

Workbook::close – Calls finalization methods for the workbook

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

mixed Workbook::close ( )

Description

Calls finalization methods for the workbook. This method should always be the last one to be called on every workbook.

Return value

returns true on success, PEAR_Error on failure

Note

This function can not be called statically.

Workbook::&addWorksheet

Workbook::&addWorksheet – Add a new worksheet to the Excel workbook.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

object reference Workbook::&addWorksheet ( string $name='' )

Description

Add a new worksheet to the Excel workbook. If no name is given the name of the worksheet will be Sheeti with i in [1..].

Parameter

Return value

returns a reference to a worksheet object on success, PEAR_Error on failure

Note

This function can not be called statically.

Example

Using &addWorksheet()

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer('test.xls');
$worksheet =& $workbook->addWorksheet('My first worksheet');
if (PEAR::isError($worksheet)) {
    die($worksheet->getMessage());
}
$workbook->close();
?>

Workbook::&addFormat

Workbook::&addFormat – Add a new format to the Excel workbook.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

object reference Workbook::&addFormat ( array $properties=array() )

Description

Add a new format to the Excel workbook. Also, pass any properties to the Format constructor. Valid properties are:

Parameter

Return value

object reference - to an Excel Format

Note

This function can not be called statically.

Example

Using &addFormat()

<?php
require_once "Spreadsheet/Excel/Writer.php";
$workbook = new Spreadsheet_Excel_Writer();
$format =& $workbook->addFormat(array('Size' => 10,
                                      'Align' => 'center',
                                      'Color' => 'white',
                                      'Pattern' => 1,
                                      'FgColor' => 'magenta'));
$worksheet =& $workbook->addWorksheet();
$worksheet->writeString(1, 0, "Magenta Hello", $format);
$workbook->close();
?>

Workbook::setCountry

Workbook::setCountry – Set the country identifier for the workbook

Synopsis

require_once 'Spreadsheet/Excel/Writer/Workbook.php';

void Workbook::setCountry ( integer $code )

Description

This package is not documented yet.

Parameter

integer $code

Is the international calling country code for the chosen country.

Throws

throws no exceptions thrown

Note

This function can not be called statically.

Workbook::&setTempDir

Workbook::&setTempDir – Sets the temp dir used for storing the OLE file.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

boolean Workbook::&setTempDir ( string $dir )

Description

Sets the temp dir used for storing the OLE file. Use this method if you don't have the right to write in the default temporary dir.

Parameter

Return value

boolean - TRUE if given dir is valid, FALSE otherwise

Note

This function can not be called statically.

Example

Using &setTempDir()

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer('test.xls');
$worksheet =& $workbook->addWorksheet('My first worksheet');
$workbook->setTempDir('/home/xnoguer/temp');
$worksheet->write(0, 0, "did this work?");
$workbook->close();
?>

Workbook::setVersion

Workbook::setVersion – Sets the BIFF version.

Synopsis

require_once 'Spreadsheet/Excel/Writer/Workbook.php';

void Workbook::setVersion ( integer $version )

Description

This method exists just to access experimental functionality from BIFF8. It will be deprecated ! Only possible value is 8 (Excel 97/2000). For any other value it fails silently.

Parameter

integer $version

The BIFF version

Note

This function can not be called statically.

Workbook::setCustomColor

Workbook::setCustomColor – Change the RGB components of the elements in the colour palette.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

integer Workbook::setCustomColor ( integer $index , integer $red , integer $green , integer $blue )

Description

Change the RGB components of the elements in the colour palette. The new color, defined by the given RGB components, will "overwrite" the color previously defined for the given index.

Parameter

Return value

integer - The palette index for the custom color

Note

This function can not be called statically.

Example

Using setCustomColor()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

// "regular" green
$format_regular_green =& $workbook->addFormat();
$format_regular_green->setFgColor('green');

// "special" green
$format_special_green =& $workbook->addFormat();
$format_special_green->setFgColor(11);

// our green (overwriting color on index 12)
$workbook->setCustomColor(12, 10, 200, 10);
$format_our_green =& $workbook->addFormat();
$format_our_green->setFgColor(12);

$worksheet->setColumn(0, 0, 30);

$worksheet->write(0, 0, "Regular green", $format_regular_green);
$worksheet->write(1, 0, "Special green (index 11)", $format_special_green);
$worksheet->write(2, 0, "Our green", $format_our_green);

$workbook->send('greens.xls');
$workbook->close();
?>

Workbook::worksheets

Workbook::worksheets – An accessor for the _worksheets[] array.

Synopsis

require_once 'Spreadsheet/Excel/Writer/Workbook.php';

array Workbook::worksheets ( )

Description

Returns an array of the worksheet objects in a workbook

Throws

throws no exceptions thrown

Note

This function can not be called statically.

Worksheet::getName

Worksheet::getName – Retrieve the worksheet name. This is usefull when creating worksheets

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

string Worksheet::getName ( )

Description

Retrieve the worksheet name. This is usefull when creating worksheets without a name.

Return value

string - The worksheet's name

Note

This function can not be called statically.

Example

Using getName()

<?php

?>

Worksheet::setInputEncoding

Worksheet::setInputEncoding – Allow writing for different charsets.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setInputEncoding ( string $encoding )

Description

It allows writing for different charsets by setting the worksheet's "current" charset. It has been tested for UTF-8, ISO-8859-7. It requires iconv for any charset other than UTF-16LE.

Parameter

Note

This function can not be called statically.

Example

Using setInputEncoding()

<?php
require_once 'Spreadsheet/Excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer('test.xls');
$workbook->setVersion(8);
$worksheet =& $workbook->addWorksheet('International worksheet');

$russian = "\xD0\xBF\xD0\xBE\xD0\xBA\xD0\xB0";
$greek = "\342\345\353\355\341";

$worksheet->setInputEncoding('ISO-8859-7');
$worksheet->write(0, 0, $greek);
$worksheet->setInputEncoding('utf-8');
$worksheet->write(1, 0, $russian);
$workbook->close();
?>

Worksheet::select

Worksheet::select – Set this worksheet as a selected worksheet, i.e. the worksheet has its tab

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::select ( )

Description

Set this worksheet as a selected worksheet, i.e. the worksheet has its tab highlighted.

Note

This function can not be called statically.

Example

Using select()

<?php

?>

Worksheet::activate

Worksheet::activate – Set this worksheet as the active worksheet, i.e. the worksheet that is

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::activate ( )

Description

Set this worksheet as the active worksheet, i.e. the worksheet that is displayed when the workbook is opened. Also set it as selected.

Note

This function can not be called statically.

Example

Using activate()

<?php

?>

Worksheet::setFirstSheet

Worksheet::setFirstSheet – Set this worksheet as the first visible sheet. This is necessary

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setFirstSheet ( )

Description

Set this worksheet as the first visible sheet. This is necessary when there are a large number of worksheets and the activated worksheet is not visible on the screen.

Note

This function can not be called statically.

Example

Using setFirstSheet()

<?php

?>

Worksheet::protect

Worksheet::protect – Set the worksheet protection flag

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::protect ( string $password )

Description

Set the worksheet protection flag to prevent accidental modification and to hide formulas if the locked and hidden format properties have been set.

Parameter

Note

This function can not be called statically.

Example

Using protect()

<?php

?>

Worksheet::setColumn

Worksheet::setColumn – Set the width of a single column or a range of columns.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setColumn ( integer $firstcol , integer $lastcol , float $width , mixed $format=0 , integer $hidden=0 )

Description

Set the width of a single column or a range of columns.

Parameter

Note

This function can not be called statically.

Example

Using setColumn()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

$radius = 20;
$worksheet->setColumn(0,$radius*2,1);

// Face
for ($i = 0; $i < 360; $i++)
{
    $worksheet->write(floor(sin((2*pi()*$i)/360)*$radius) + $radius + 1, floor(cos((2*pi()*$i)/360)*$radius) + $radius + 1, "x");
}
// Eyes (maybe use a format instead?)
$worksheet->writeURL(floor($radius*0.8), floor($radius*0.8), "0");
$worksheet->writeURL(floor($radius*0.8), floor($radius*1.2), "0");

// Smile
for ($i = 65; $i < 115; $i++)
{
    $worksheet->write(floor(sin((2*pi()*$i)/360)*$radius*1.3) + floor($radius*0.2), floor(cos((2*pi()*$i)/360)*$radius*1.3) + $radius + 1, "x");
}

// hide gridlines so they don't mess with our Excel art.
$worksheet->hideGridLines();

$workbook->send('face.xls');
$workbook->close();
?>

Worksheet::writeCol

Worksheet::writeCol – Write an array of values as a column

Synopsis

require_once 'Spreadsheet/Excel/Writer/Worksheet.php';

mixed Worksheet::writeCol ( integer $row , integer $col , array $val , mixed $format = null )

Description

This package is not documented yet.

Parameter

integer $row

The first row (uppermost row) we are writing to

integer $col

The col we are writing to

array $val

The array of values to write

mixed $format

The optional format to apply to the cell

Return value

returns PEAR_Error on failure

Throws

throws no exceptions thrown

Note

This function can not be called statically.

Worksheet::writeRow

Worksheet::writeRow – Write an array of values as a row

Synopsis

require_once 'Spreadsheet/Excel/Writer/Worksheet.php';

mixed Worksheet::writeRow ( integer $row , integer $col , array $val , mixed $format = null )

Description

This package is not documented yet.

Parameter

integer $row

The row we are writing to

integer $col

The first col (leftmost col) we are writing to

array $val

The array of values to write

mixed $format

The optional format to apply to the cell

Return value

returns PEAR_Error on failure

Throws

throws no exceptions thrown

Note

This function can not be called statically.

Worksheet::setSelection

Worksheet::setSelection – Set which cell or cells are selected in a worksheet

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setSelection ( integer $first_row , integer $first_column , integer $last_row , integer $last_column )

Description

Set which cell or cells are selected in a worksheet

Parameter

Note

This function can not be called statically.

Example

Using setSelection()

<?php

?>

Worksheet::freezePanes

Worksheet::freezePanes – Set panes and mark them as frozen.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::freezePanes ( array $panes )

Description

Set panes and mark them as frozen. One can use this method to mark certain regions in the worksheet so that they are "frozen" in the sense that when scrolling through the worksheet these regions are not affected by the scrolling and remain where they are on the screen. This is the same functionality as provided by Microsoft Excel through the Window->Freeze Panes menu command.

Parameter

Note

This function can not be called statically.

Example

Using freezePanes()

<?php
$worksheet =& $workbook->addWorksheet("Some Worksheet");

/* ... */

/* This freezes the first six rows of the worksheet: */
$worksheet->freezePanes(array(6, 0));

/* To freeze the first column, one must use the following syntax: */
$worksheet->freezePanes(array(0, 1));
?>

If one needs to further specify the scrolling region, the following syntax can be used:

<?php
/* Freeze the first six rows and start the scrollable region at row nine: */
$worksheet->freezePanes(array(6, 0, 9, 0));
?>

Worksheet::thawPanes

Worksheet::thawPanes – Set panes and mark them as unfrozen.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::thawPanes ( array $panes )

Description

Set panes and mark them as unfrozen.

Parameter

Note

This function can not be called statically.

Example

Using thawPanes()

<?php

?>

Worksheet::hideScreenGridlines

Worksheet::hideScreenGridlines – Set the option to hide gridlines on the worksheet (as seen on the screen).

Synopsis

require_once 'Spreadsheet/Excel/Writer/Worksheet.php';

void Worksheet::hideScreenGridlines ( )

Description

This package is not documented yet.

Throws

throws no exceptions thrown

Note

This function can not be called statically.

Worksheet::setPortrait

Worksheet::setPortrait – Set the page orientation as portrait.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setPortrait ( )

Description

Set the page orientation as portrait.

Note

This function can not be called statically.

Example

Using setPortrait()

<?php

?>

Worksheet::setLandscape

Worksheet::setLandscape – Set the page orientation as landscape.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setLandscape ( )

Description

Set the page orientation as landscape.

Note

This function can not be called statically.

Example

Using setLandscape()

<?php

?>

Worksheet::setPaper

Worksheet::setPaper – Set the paper type. Ex. 1 = US Letter, 9 = A4

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setPaper ( integer $size=0 )

Description

Set the paper type. Ex. 1 = US Letter, 9 = A4

Parameter

Note

This function can not be called statically.

Example

Using setPaper()

<?php

require_once 'Spreadsheet/Excel/Writer.php';

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();

// sending HTTP headers
$workbook->send('test.xls');

// Creating a worksheet
$worksheet =& $workbook->addWorksheet('My first worksheet');

//set paper size
$worksheet->setPaper(9);

// The actual data
$worksheet->write(0, 0, 'Name');
$worksheet->write(0, 1, 'Age');
$worksheet->write(1, 0, 'John Smith');
$worksheet->write(1, 1, 30);
$worksheet->write(2, 0, 'Johann Schmidt');
$worksheet->write(2, 1, 31);
$worksheet->write(3, 0, 'Juan Herrera');
$worksheet->write(3, 1, 32);

// Let's send the file
$workbook->close();
?>

Worksheet::setHeader

Worksheet::setHeader – Set the page header caption and optional margin.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setHeader ( string $string , float $margin=0.5 )

Description

Set the page header caption and optional margin.

Parameter

Note

This function can not be called statically.

Example

Using setHeader()

<?php

?>

Worksheet::setFooter

Worksheet::setFooter – Set the page footer caption and optional margin.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setFooter ( string $string , float $margin=0.5 )

Description

Set the page footer caption and optional margin.

Parameter

Note

This function can not be called statically.

Example

Using setFooter()

<?php

?>

Worksheet::setMerge

Worksheet::setMerge – Sets a merged cell range

Synopsis

require_once 'Spreadsheet/Excel/Writer/Worksheet.php';

void Worksheet::setMerge ( integer $first_row , integer $first_col , integer $last_row , integer $last_col )

Description

This package is not documented yet.

Parameter

integer $first_row

First row of the area to merge

integer $first_col

First column of the area to merge

integer $last_row

Last row of the area to merge

integer $last_col

Last column of the area to merge

Throws

throws no exceptions thrown

Note

This function can not be called statically.

Example

Using setMerge()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook  = new Spreadsheet_Excel_Writer();
$worksheet = $workbook->addWorksheet('SetMerge');

// Sets the color of a cell's content
$format = $workbook->addFormat();
$format->setFgColor(10);
$worksheet->write(0, 0, 'Cell Start', $format);

// Merge cells from row 0, col 0 to row 2, col 2
$worksheet->setMerge(0, 0, 2, 2);
   
$workbook->send('SetMerge.xls');
$workbook->close();
?>

Worksheet::centerHorizontally

Worksheet::centerHorizontally – Center the page horizontally.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::centerHorizontally ( integer $center=1 )

Description

Center the page horizontally.

Parameter

Note

This function can not be called statically.

Example

Using centerHorizontally()

<?php

?>

Worksheet::centerVertically

Worksheet::centerVertically – Center the page vertically.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::centerVertically ( integer $center=1 )

Description

Center the page vertically.

Parameter

Note

This function can not be called statically.

Example

Using centerVertically()

<?php

?>

Worksheet::setMargins

Worksheet::setMargins – Set all the page margins to the same value in inches.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setMargins ( float $margin )

Description

Set all the page margins to the same value in inches.

Parameter

Note

This function can not be called statically.

Example

Using setMargins()

<?php

?>

Worksheet::setMargins_LR

Worksheet::setMargins_LR – Set the left and right margins to the same value in inches.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setMargins_LR ( float $margin )

Description

Set the left and right margins to the same value in inches.

Parameter

Note

This function can not be called statically.

Example

Using setMargins_LR()

<?php

?>

Worksheet::setMargins_TB

Worksheet::setMargins_TB – Set the top and bottom margins to the same value in inches.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setMargins_TB ( float $margin )

Description

Set the top and bottom margins to the same value in inches.

Parameter

Note

This function can not be called statically.

Example

Using setMargins_TB()

<?php

?>

Worksheet::setMarginLeft

Worksheet::setMarginLeft – Set the left margin in inches.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setMarginLeft ( float $margin=0.75 )

Description

Set the left margin in inches.

Parameter

Note

This function can not be called statically.

Example

Using setMarginLeft()

<?php

?>

Worksheet::setMarginRight

Worksheet::setMarginRight – Set the right margin in inches.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setMarginRight ( float $margin=0.75 )

Description

Set the right margin in inches.

Parameter

Note

This function can not be called statically.

Example

Using setMarginRight()

<?php

?>

Worksheet::setMarginTop

Worksheet::setMarginTop – Set the top margin in inches.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setMarginTop ( float $margin=1 )

Description

Set the top margin in inches.

Parameter

Note

This function can not be called statically.

Example

Using setMarginTop()

<?php

?>

Worksheet::setMarginBottom

Worksheet::setMarginBottom – Set the bottom margin in inches.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setMarginBottom ( float $margin=1 )

Description

Set the bottom margin in inches.

Parameter

Note

This function can not be called statically.

Example

Using setMarginBottom()

<?php

?>

Worksheet::repeatRows

Worksheet::repeatRows – Set the rows to repeat at the top of each printed page.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::repeatRows ( integer $first_row , integer $last_row=NULL )

Description

Set the rows to repeat at the top of each printed page.

Parameter

Note

This function can not be called statically.

Example

Using repeatRows()

<?php
  require_once 'Spreadsheet/Excel/Writer.php';

  $workbook = new Spreadsheet_Excel_Writer('test.xls');
  $worksheet =& $workbook->addWorkSheet();
  $worksheet->setColumn(0,0,100);
  $worksheet->write(0,0,"99 Bottles Of Beer On The Wall- The Complete Lyrics");
  // repeat only the first row
  $worksheet->repeatRows(0);
  for ($i = 99; $i > 0; $i--)
  {
      if ($i > 1) {
          $next = $i - 1;
      }
      else {
          $next = "no more";
      }
      $worksheet->write(100 - $i,0,"$i Bottles of beer on the wall, $i bottles of beer, ".
                                   "take one down, pass it around, ".
                                   "$next bottles of beer on the wall.");
  }
  $workbook->close();
?>

Worksheet::repeatColumns

Worksheet::repeatColumns – Set the columns to repeat at the left hand side of each printed page.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::repeatColumns ( integer $first_col , integer $last_col=NULL )

Description

Set the columns to repeat at the left hand side of each printed page.

Parameter

Note

This function can not be called statically.

Example

Using repeatColumns()

<?php

?>

Worksheet::printArea

Worksheet::printArea – Set the area of each worksheet that will be printed.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::printArea ( integer $first_row , integer $first_col , integer $last_row , integer $last_col )

Description

Set the area of each worksheet that will be printed.

Parameter

Note

This function can not be called statically.

Example

Using printArea()

<?php

?>

Worksheet::hideGridlines

Worksheet::hideGridlines – Set the option to hide gridlines on the printed page.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::hideGridlines ( )

Description

Set the option to hide gridlines on the printed page.

Note

This function can not be called statically.

Example

Using hideGridlines()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

$radius = 20;
$worksheet->setColumn(0,$radius*2,1);

// Face
for ($i = 0; $i < 360; $i++)
{
    $worksheet->write(floor(sin((2*pi()*$i)/360)*$radius) + $radius + 1, floor(cos((2*pi()*$i)/360)*$radius) + $radius + 1, "x");
}
// Eyes (maybe use a format instead?)
$worksheet->writeURL(floor($radius*0.8), floor($radius*0.8), "0");
$worksheet->writeURL(floor($radius*0.8), floor($radius*1.2), "0");

// Smile
for ($i = 65; $i < 115; $i++)
{
    $worksheet->write(floor(sin((2*pi()*$i)/360)*$radius*1.3) + floor($radius*0.2), floor(cos((2*pi()*$i)/360)*$radius*1.3) + $radius + 1, "x");
}

// hide gridlines so they don't mess with our Excel art.
$worksheet->hideGridLines();

$workbook->send('face.xls');
$workbook->close();
?>

Worksheet::printRowColHeaders

Worksheet::printRowColHeaders – Set the option to print the row and column headers on the printed page.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::printRowColHeaders ( integer $print=1 )

Description

Set the option to print the row and column headers on the printed page.

Parameter

Note

This function can not be called statically.

Example

Using printRowColHeaders()

<?php

?>

Worksheet::fitToPages

Worksheet::fitToPages – Store the vertical and horizontal number of pages that will define the

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::fitToPages ( integer $width , integer $height )

Description

Store the vertical and horizontal number of pages that will define the maximum area printed. It doesn't seem to work with OpenOffice.

Parameter

Note

This function can not be called statically.

Example

Using fitToPages()

<?php

?>

Worksheet::setHPagebreaks

Worksheet::setHPagebreaks – Store the horizontal page breaks on a worksheet (for printing).

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setHPagebreaks ( array $breaks )

Description

Store the horizontal page breaks on a worksheet (for printing). The breaks represent the row after which the break is inserted.

Parameter

Note

This function can not be called statically.

Example

Using setHPagebreaks()

<?php

?>

Worksheet::setVPagebreaks

Worksheet::setVPagebreaks – Store the vertical page breaks on a worksheet (for printing).

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setVPagebreaks ( array $breaks )

Description

Store the vertical page breaks on a worksheet (for printing). The breaks represent the column after which the break is inserted.

Parameter

Note

This function can not be called statically.

Example

Using setVPagebreaks()

<?php

?>

Worksheet::setZoom

Worksheet::setZoom – Set the worksheet zoom factor.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setZoom ( integer $scale=100 )

Description

Set the worksheet zoom factor.

Parameter

Note

This function can not be called statically.

Example

Using setZoom()

<?php

?>

Worksheet::setPrintScale

Worksheet::setPrintScale – Set the scale factor for the printed page.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setPrintScale ( integer $scale=100 )

Description

Set the scale factor for the printed page. It turns off the "fit to page" option

Parameter

Note

This function can not be called statically.

Example

Using setPrintScale()

<?php

?>

Worksheet::write

Worksheet::write – Map to the appropriate write method acording to the token recieved.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::write ( integer $row , integer $col , mixed $token , mixed $format=0 )

Description

Map to the appropriate write method acording to the token recieved.

Parameter

Note

This function can not be called statically.

Example

Using write()

<?php

?>

Worksheet::writeNumber

Worksheet::writeNumber – Write a double to the specified row and column (zero indexed).

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::writeNumber ( integer $row , integer $col , float $num , mixed $format=0 )

Description

Write a double to the specified row and column (zero indexed). An integer can be written as a double. Excel will display an integer. $format is optional. Returns 0 : normal termination -2 : row or column out of range

Parameter

Note

This function can not be called statically.

Example

Using writeNumber()

<?php

?>

Worksheet::writeString

Worksheet::writeString – Write a string to the specified row and column (zero indexed).

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::writeString ( integer $row , integer $col , string $str , mixed $format=0 )

Description

Write a string to the specified row and column (zero indexed). NOTE: there is an Excel 5 defined limit of 255 characters. $format is optional. Returns 0 : normal termination -1 : insufficient number of arguments -2 : row or column out of range -3 : long string truncated to 255 chars

Parameter

Note

This function can not be called statically.

Example

Using writeString()

<?php

?>

Worksheet::writeNote

Worksheet::writeNote – Writes a note associated with the cell given by the row and column.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::writeNote ( integer $row , integer $col , string $note )

Description

Writes a note associated with the cell given by the row and column. NOTE records don't have a length limit.

Parameter

Note

This function can not be called statically.

Example

Using writeNote()

<?php

?>

Worksheet::writeBlank

Worksheet::writeBlank – Write a blank cell to the specified row and column (zero indexed).

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::writeBlank ( integer $row , integer $col , mixed $format )

Description

Write a blank cell to the specified row and column (zero indexed). A blank cell is used to specify formatting without adding a string or a number. A blank cell without a format serves no purpose. Therefore, we don't write a BLANK record unless a format is specified. This is mainly an optimisation for the write_row() and write_col() methods. Returns 0 : normal termination (including no format) -1 : insufficient number of arguments -2 : row or column out of range

Parameter

Note

This function can not be called statically.

Example

Using writeBlank()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();
// we can set set all properties on instantiation
$upper_right_side_brick =& $workbook->addFormat(array('right' => 5, 'top' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
// or set all properties one by one
$upper_left_side_brick =& $workbook->addFormat();
$upper_left_side_brick->setLeft(5);
$upper_left_side_brick->setTop(5);
$upper_left_side_brick->setSize(15);
$upper_left_side_brick->setPattern(1);
$upper_left_side_brick->setBorderColor('blue');
$upper_left_side_brick->setFgColor('red');

$lower_right_side_brick =& $workbook->addFormat(array('right' => 5, 'bottom' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
$lower_left_side_brick =& $workbook->addFormat(array('left' => 5, 'bottom' => 5, 'size' => 15,
                                                     'pattern' => 1, 'bordercolor' => 'blue',
                                                     'fgcolor' => 'red'));

$worksheet->setColumn(0, 20, 6);

// Sky
$sky =& $workbook->addFormat(array('fgcolor' => 'cyan', 'pattern' => 1, 'size' => 15));
for ($i = 0; $i <= 10; $i++)
{
    for ($j = 0; $j < 20; $j++) {
        $worksheet->writeBlank($i, $j, $sky);
    }
}

// Cloud
$cloud =& $workbook->addFormat(array('fgcolor' => 'white', 'pattern' => 1, 'size' => 15));
$worksheet->writeBlank(5, 7, $cloud);
$worksheet->writeBlank(4, 8, $cloud);
$worksheet->writeBlank(5, 8, $cloud);
$worksheet->writeBlank(6, 8, $cloud);
$worksheet->writeBlank(4, 9, $cloud);
$worksheet->writeBlank(5, 9, $cloud);
$worksheet->writeBlank(5, 10, $cloud);

// Bricks
for ($j = 0; $j < 20; $j++)
{
    for ($i = 5; $i <= 11; $i++)
    {
        if (($i + $j)%2 == 1) // right side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_right_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_right_side_brick);
        }
        else // left side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_left_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_left_side_brick);
        }
    }
}

// hide gridlines so they don't mess with our Excel art.
$worksheet->hideGridLines();

$workbook->send('bricks.xls');
$workbook->close();
?>

Worksheet::writeFormula

Worksheet::writeFormula – Write a formula to the specified row and column (zero indexed).

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

integer Worksheet::writeFormula ( integer $row , integer $col , string $formula , mixed $format=0 )

Description

Write a formula to the specified row and column (zero indexed). In case of error it will write the error message (instead of the formula) in the corresponding row and column.

Parameter

Return value

integer - 0 for normal termination, -1 for an error in the formula, -2 for row or column out of range.

Note

This function can not be called statically.

Formulas must start with an equal sign ('=').

Arguments given to an Excel function should be separated by comas (','), not by semicolons (';').

Example

Using writeFormula()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer('formula.xls');
$worksheet =& $workbook->addWorksheet();

$worksheet->write(0, 0, 2);
$worksheet->write(0, 1, "and");
$worksheet->write(0, 2, 2);
$worksheet->write(0, 3, "makes");
$worksheet->writeFormula(0, 4, "=SUM(A1,C1)");

$workbook->close();
?>

Worksheet::writeUrl

Worksheet::writeUrl – Write a hyperlink. This is comprised of two elements: the visible label and

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::writeUrl ( integer $row , integer $col , string $url , string $string='' , mixed $format=0 )

Description

Write a hyperlink. This is comprised of two elements: the visible label and the invisible link. The visible label is the same as the link unless an alternative string is specified. The label is written using the writeString() method. Therefore the 255 characters string limit applies. $string and $format are optional and their order is interchangeable. The hyperlink can be to a http, ftp, mail, internal sheet, or external directory url. Returns 0 : normal termination -1 : insufficient number of arguments -2 : row or column out of range -3 : long string truncated to 255 chars

Parameter

Note

This function can not be called statically.

Example

Using writeUrl()

<?php

?>

Worksheet::setRow

Worksheet::setRow – This method is used to set the height and XF format for a row.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setRow ( integer $row , integer $height , mixed $format=0 )

Description

This method is used to set the height and XF format for a row. Writes the BIFF record ROW.

Parameter

Note

This function can not be called statically.

Example

Using setRow()

<?php

?>

Worksheet::mergeCells

Worksheet::mergeCells – This is an Excel97/2000 method. It is required to perform more complicated

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::mergeCells ( integer $first_row , integer $first_col , integer $last_row , integer $last_col )

Description

This is an Excel97/2000 method. It is required to perform more complicated merging than the normal setAlign('merge'). It merges the area given by its arguments.

Parameter

Note

This function can not be called statically.

Example

Using mergeCells()

<?php

?>

Worksheet::insertBitmap

Worksheet::insertBitmap – Insert a 24bit bitmap image in a worksheet. The main record required is

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::insertBitmap ( integer $row , integer $col , string $bitmap , integer $x=0 , integer $y=0 , integer $scale_x=1 , integer $scale_y=1 )

Description

Insert a 24bit bitmap image in a worksheet. The main record required is IMDATA but it must be proceeded by a OBJ record to define its position.

Parameter

Note

This function can not be called statically.

Example

Using insertBitmap()

<?php

?>

Worksheet::setOutline

Worksheet::setOutline – This method sets the properties for outlining and grouping

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Worksheet::setOutline ( bool $visible=true , bool $symbols_below=true , bool $symbols_right=true , bool $auto_style=false )

Description

This method sets the properties for outlining and grouping. The defaults correspond to Excel's defaults.

Parameter

Note

This function can not be called statically.

Example

Using setOutline()

<?php

?>

Spreadsheet_Excel_Writer::Spreadsheet_Excel_Writer

Spreadsheet_Excel_Writer::Spreadsheet_Excel_Writer – The constructor. It just creates a Workbook

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

object The Spreadsheet_Excel_Writer::Spreadsheet_Excel_Writer ( string $filename='' )

Description

The constructor. It just creates a Workbook

Parameter

Return value

object The - Workbook created

Note

This function can not be called statically.

Example

Using Spreadsheet_Excel_Writer()

<?php

?>

Spreadsheet_Excel_Writer::send

Spreadsheet_Excel_Writer::send – Send HTTP headers for the Excel file.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Spreadsheet_Excel_Writer::send ( string $filename )

Description

Send HTTP headers with the correct content-type (application/vnd.ms-excel), cache control and filename for the file.

Parameter

Note

This function can be called statically, but it is easiest when called from the workbook object.

Example

Using send()

<?php
$workbook->send('filename.xls');
?>

Spreadsheet_Excel_Writer::rowcolToCell

Spreadsheet_Excel_Writer::rowcolToCell – Utility function for writing formulas.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

string Spreadsheet_Excel_Writer::rowcolToCell ( integer $row , integer $col )

Description

Utility function for writing formulas. Converts a cell's coordinates to the A1 format.

Parameter

Return value

returns The cell identifier in A1 format on success, PEAR_Error on failure

Example

Using rowcolToCell()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer('rowcol.xls');
$worksheet1 =& $workbook->addWorksheet("rowcol");

$first = 1;
$last = 10;
for ($i = $first; $i <= $last; $i++) {
    $worksheet1->write($i, 1, $i);
}
$cell1 = Spreadsheet_Excel_Writer::rowcolToCell($first, 1);
$cell2 = Spreadsheet_Excel_Writer::rowcolToCell($last, 1);
$worksheet1->write($last + 1, 0, "Total =");
$worksheet1->writeFormula($last + 1, 1, "=SUM($cell1:$cell2)");
$workbook->close();
?>

Format::setAlign

Format::setAlign – Set cell alignment.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setAlign ( string $location )

Description

Set cell alignment.

Parameter

Note

This function can not be called statically.

Example

Using setAlign()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

// put text at the top
$format_top =& $workbook->addFormat();
$format_top->setAlign('top');
$format_top->setTextWrap(1);

// center the text horizontally
$format_center =& $workbook->addFormat();
$format_center->setAlign('center');

// put text at the top and center it horizontally
$format_top_center =& $workbook->addFormat();
$format_top_center->setAlign('top');
$format_top_center->setAlign('center');

$worksheet->write(0, 0, 'On top of the world!',
                  $format_top);
$worksheet->write(1, 0, 'c', $format_center);
$worksheet->write(2, 0, 'tc', $format_top_center);

$workbook->send('align.xls');
$workbook->close();
?>

Format::setVAlign

Format::setVAlign – Set cell alignment.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setVAlign ( string $location )

Description

Set cell alignment. This is an alternative to setAlign()

Parameter

Note

This function can not be called statically.

Example

Using setVAlign()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

// justify 
$format_justify =& $workbook->addFormat('vAlign' => 'vjustify');

// center the text vertically
$format_center =& $workbook->addFormat('vAlign' => 'vcenter');

// justify and center vertically
$format_justify_center =& $workbook->addFormat();
$format_justify_center->setVAlign('vjustify');
$format_justify_center->setVAlign('vcenter');

$worksheet->write(0, 0, 'justify', $format_justify);
$worksheet->write(1, 0, 'center', $format_center);
$worksheet->write(2, 0, 'jc', $format_justify_center);

$workbook->send('align.xls');
$workbook->close();
?>

Format::setHAlign

Format::setHAlign – Set cell alignment.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setHAlign ( string $location )

Description

Set cell alignment. This is an alternative to setAlign()

Parameter

Note

This function can not be called statically.

Example

Using setHAlign()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

// put text at the top
$format_top =& $workbook->addFormat();
$format_top->setHAlign('top');
$format_top->setTextWrap(1);

// center the text horizontally
$format_center =& $workbook->addFormat();
$format_center->setHAlign('center');

// put text at the top and center it horizontally
$format_top_center =& $workbook->addFormat();
$format_top_center->setHAlign('top');
$format_top_center->setHAlign('center');

$worksheet->write(0, 0, 'On top of the world!',
                  $format_top);
$worksheet->write(1, 0, 'c', $format_center);
$worksheet->write(2, 0, 'tc', $format_top_center);

$workbook->send('align.xls');
$workbook->close();
?>

Format::setMerge

Format::setMerge – This is an alias for the unintuitive setAlign('merge')

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setMerge ( )

Description

This is an alias for the unintuitive setAlign('merge')

Note

This function can not be called statically.

Format::setLocked

Format::setLocked – Locks a cell.

Synopsis

require_once 'Spreadsheet/Excel/Writer/Format.php';

void Format::setLocked ( )

Description

This package is not documented yet.

Throws

throws no exceptions thrown

Note

This function can not be called statically.

Format::setUnLocked

Format::setUnLocked – Unlocks a cell. Useful for unprotecting particular cells of a protected sheet.

Synopsis

require_once 'Spreadsheet/Excel/Writer/Format.php';

void Format::setUnLocked ( )

Description

This package is not documented yet.

Throws

throws no exceptions thrown

Note

This function can not be called statically.

Format::setBold

Format::setBold – Sets the boldness of the text.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setBold ( integer $weight=1 )

Description

Sets the boldness of the text. Bold has a range 100..1000. 0 (400) is normal. 1 (700) is bold.

Parameter

Note

This function can not be called statically.

Example

Using setBold()

<?php

?>

Format::setBottom

Format::setBottom – Sets the width for the bottom border of the cell

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setBottom ( integer $style )

Description

Sets the width for the bottom border of the cell

Parameter

Note

This function can not be called statically.

Example

Using setBottom()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();
// we can set set all properties on instantiation
$upper_right_side_brick =& $workbook->addFormat(array('right' => 5, 'top' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
// or set all properties one by one
$upper_left_side_brick =& $workbook->addFormat();
$upper_left_side_brick->setLeft(5);
$upper_left_side_brick->setTop(5);
$upper_left_side_brick->setBottom(1);
$upper_left_side_brick->setSize(15);
$upper_left_side_brick->setPattern(1);
$upper_left_side_brick->setBorderColor('blue');
$upper_left_side_brick->setFgColor('red');

$lower_right_side_brick =& $workbook->addFormat(array('right' => 5, 'bottom' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
$lower_left_side_brick =& $workbook->addFormat(array('left' => 5, 'bottom' => 5, 'size' => 15,
                                                     'pattern' => 1, 'bordercolor' => 'blue',
                                                     'fgcolor' => 'red'));

$worksheet->setColumn(0, 20, 6);

// Sky
$sky =& $workbook->addFormat(array('fgcolor' => 'cyan', 'pattern' => 1, 'size' => 15));
for ($i = 0; $i <= 10; $i++)
{
    for ($j = 0; $j < 20; $j++) {
        $worksheet->writeBlank($i, $j, $sky);
    }
}

// Cloud
$cloud =& $workbook->addFormat(array('fgcolor' => 'white', 'pattern' => 1, 'size' => 15));
$worksheet->writeBlank(5, 7, $cloud);
$worksheet->writeBlank(4, 8, $cloud);
$worksheet->writeBlank(5, 8, $cloud);
$worksheet->writeBlank(6, 8, $cloud);
$worksheet->writeBlank(4, 9, $cloud);
$worksheet->writeBlank(5, 9, $cloud);
$worksheet->writeBlank(5, 10, $cloud);

// Bricks
for ($j = 0; $j < 20; $j++)
{
    for ($i = 5; $i <= 11; $i++)
    {
        if (($i + $j)%2 == 1) // right side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_right_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_right_side_brick);
        }
        else // left side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_left_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_left_side_brick);
        }
    }
}

// hide gridlines so they don't mess with our Excel art.
$worksheet->hideGridLines();

$workbook->send('bricks.xls');
$workbook->close();
?>

Format::setTop

Format::setTop – Sets the width for the top border of the cell

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setTop ( integer $style )

Description

Sets the width for the top border of the cell

Parameter

Note

This function can not be called statically.

Example

Using setTop()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();
// we can set set all properties on instantiation
$upper_right_side_brick =& $workbook->addFormat(array('right' => 5, 'top' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
// or set all properties one by one
$upper_left_side_brick =& $workbook->addFormat();
$upper_left_side_brick->setLeft(5);
$upper_left_side_brick->setTop(5);
$upper_left_side_brick->setSize(15);
$upper_left_side_brick->setPattern(1);
$upper_left_side_brick->setBorderColor('blue');
$upper_left_side_brick->setFgColor('red');

$lower_right_side_brick =& $workbook->addFormat(array('right' => 5, 'bottom' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
$lower_left_side_brick =& $workbook->addFormat(array('left' => 5, 'bottom' => 5, 'size' => 15,
                                                     'pattern' => 1, 'bordercolor' => 'blue',
                                                     'fgcolor' => 'red'));

$worksheet->setColumn(0, 20, 6);

// Sky
$sky =& $workbook->addFormat(array('fgcolor' => 'cyan', 'pattern' => 1, 'size' => 15));
for ($i = 0; $i <= 10; $i++)
{
    for ($j = 0; $j < 20; $j++) {
        $worksheet->writeBlank($i, $j, $sky);
    }
}

// Cloud
$cloud =& $workbook->addFormat(array('fgcolor' => 'white', 'pattern' => 1, 'size' => 15));
$worksheet->writeBlank(5, 7, $cloud);
$worksheet->writeBlank(4, 8, $cloud);
$worksheet->writeBlank(5, 8, $cloud);
$worksheet->writeBlank(6, 8, $cloud);
$worksheet->writeBlank(4, 9, $cloud);
$worksheet->writeBlank(5, 9, $cloud);
$worksheet->writeBlank(5, 10, $cloud);

// Bricks
for ($j = 0; $j < 20; $j++)
{
    for ($i = 5; $i <= 11; $i++)
    {
        if (($i + $j)%2 == 1) // right side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_right_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_right_side_brick);
        }
        else // left side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_left_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_left_side_brick);
        }
    }
}

// hide gridlines so they don't mess with our Excel art.
$worksheet->hideGridLines();

$workbook->send('bricks.xls');
$workbook->close();
?>

Format::setLeft

Format::setLeft – Sets the width for the left border of the cell

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setLeft ( integer $style )

Description

Sets the width for the left border of the cell

Parameter

Note

This function can not be called statically.

Example

Using setLeft()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();
// we can set set all properties on instantiation
$upper_right_side_brick =& $workbook->addFormat(array('right' => 5, 'top' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
// or set all properties one by one
$upper_left_side_brick =& $workbook->addFormat();
$upper_left_side_brick->setLeft(5);
$upper_left_side_brick->setTop(5);
$upper_left_side_brick->setSize(15);
$upper_left_side_brick->setPattern(1);
$upper_left_side_brick->setBorderColor('blue');
$upper_left_side_brick->setFgColor('red');

$lower_right_side_brick =& $workbook->addFormat(array('right' => 5, 'bottom' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
$lower_left_side_brick =& $workbook->addFormat(array('left' => 5, 'bottom' => 5, 'size' => 15,
                                                     'pattern' => 1, 'bordercolor' => 'blue',
                                                     'fgcolor' => 'red'));

$worksheet->setColumn(0, 20, 6);

// Sky
$sky =& $workbook->addFormat(array('fgcolor' => 'cyan', 'pattern' => 1, 'size' => 15));
for ($i = 0; $i <= 10; $i++)
{
    for ($j = 0; $j < 20; $j++) {
        $worksheet->writeBlank($i, $j, $sky);
    }
}

// Cloud
$cloud =& $workbook->addFormat(array('fgcolor' => 'white', 'pattern' => 1, 'size' => 15));
$worksheet->writeBlank(5, 7, $cloud);
$worksheet->writeBlank(4, 8, $cloud);
$worksheet->writeBlank(5, 8, $cloud);
$worksheet->writeBlank(6, 8, $cloud);
$worksheet->writeBlank(4, 9, $cloud);
$worksheet->writeBlank(5, 9, $cloud);
$worksheet->writeBlank(5, 10, $cloud);

// Bricks
for ($j = 0; $j < 20; $j++)
{
    for ($i = 5; $i <= 11; $i++)
    {
        if (($i + $j)%2 == 1) // right side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_right_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_right_side_brick);
        }
        else // left side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_left_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_left_side_brick);
        }
    }
}

// hide gridlines so they don't mess with our Excel art.
$worksheet->hideGridLines();

$workbook->send('bricks.xls');
$workbook->close();
?>

Format::setRight

Format::setRight – Sets the width for the right border of the cell

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setRight ( integer $style )

Description

Sets the width for the right border of the cell

Parameter

Note

This function can not be called statically.

Example

Using setRight()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();
// we can set set all properties on instantiation
$upper_right_side_brick =& $workbook->addFormat(array('right' => 5, 'top' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
// or set all properties one by one
$upper_left_side_brick =& $workbook->addFormat();
$upper_left_side_brick->setLeft(5);
$upper_left_side_brick->setTop(5);
$upper_left_side_brick->setSize(15);
$upper_left_side_brick->setPattern(1);
$upper_left_side_brick->setBorderColor('blue');
$upper_left_side_brick->setFgColor('red');

$lower_right_side_brick =& $workbook->addFormat(array('right' => 5, 'bottom' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
$lower_left_side_brick =& $workbook->addFormat(array('left' => 5, 'bottom' => 5, 'size' => 15,
                                                     'pattern' => 1, 'bordercolor' => 'blue',
                                                     'fgcolor' => 'red'));

$worksheet->setColumn(0, 20, 6);

// Sky
$sky =& $workbook->addFormat(array('fgcolor' => 'cyan', 'pattern' => 1, 'size' => 15));
for ($i = 0; $i <= 10; $i++)
{
    for ($j = 0; $j < 20; $j++) {
        $worksheet->writeBlank($i, $j, $sky);
    }
}

// Cloud
$cloud =& $workbook->addFormat(array('fgcolor' => 'white', 'pattern' => 1, 'size' => 15));
$worksheet->writeBlank(5, 7, $cloud);
$worksheet->writeBlank(4, 8, $cloud);
$worksheet->writeBlank(5, 8, $cloud);
$worksheet->writeBlank(6, 8, $cloud);
$worksheet->writeBlank(4, 9, $cloud);
$worksheet->writeBlank(5, 9, $cloud);
$worksheet->writeBlank(5, 10, $cloud);

// Bricks
for ($j = 0; $j < 20; $j++)
{
    for ($i = 5; $i <= 11; $i++)
    {
        if (($i + $j)%2 == 1) // right side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_right_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_right_side_brick);
        }
        else // left side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_left_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_left_side_brick);
        }
    }
}

// hide gridlines so they don't mess with our Excel art.
$worksheet->hideGridLines();

$workbook->send('bricks.xls');
$workbook->close();
?>

Format::setBorder

Format::setBorder – Set cells borders to the same style

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setBorder ( integer $style )

Description

Set cells borders to the same style

Parameter

Note

This function can not be called statically.

Example

Using setBorder()

<?php

?>

Format::setBorderColor

Format::setBorderColor – Sets all the cell's borders to the same color

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setBorderColor ( mixed $color )

Description

Sets all the cell's borders to the same color

Parameter

Note

This function can not be called statically.

Example

Using setBorderColor()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();
// we can set set all properties on instantiation
$upper_right_side_brick =& $workbook->addFormat(array('right' => 5, 'top' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
// or set all properties one by one
$upper_left_side_brick =& $workbook->addFormat();
$upper_left_side_brick->setLeft(5);
$upper_left_side_brick->setTop(5);
$upper_left_side_brick->setSize(15);
$upper_left_side_brick->setPattern(1);
$upper_left_side_brick->setBorderColor('blue');
$upper_left_side_brick->setFgColor('red');

$lower_right_side_brick =& $workbook->addFormat(array('right' => 5, 'bottom' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
$lower_left_side_brick =& $workbook->addFormat(array('left' => 5, 'bottom' => 5, 'size' => 15,
                                                     'pattern' => 1, 'bordercolor' => 'blue',
                                                     'fgcolor' => 'red'));

$worksheet->setColumn(0, 20, 6);

// Sky
$sky =& $workbook->addFormat(array('fgcolor' => 'cyan', 'pattern' => 1, 'size' => 15));
for ($i = 0; $i <= 10; $i++)
{
    for ($j = 0; $j < 20; $j++) {
        $worksheet->writeBlank($i, $j, $sky);
    }
}

// Cloud
$cloud =& $workbook->addFormat(array('fgcolor' => 'white', 'pattern' => 1, 'size' => 15));
$worksheet->writeBlank(5, 7, $cloud);
$worksheet->writeBlank(4, 8, $cloud);
$worksheet->writeBlank(5, 8, $cloud);
$worksheet->writeBlank(6, 8, $cloud);
$worksheet->writeBlank(4, 9, $cloud);
$worksheet->writeBlank(5, 9, $cloud);
$worksheet->writeBlank(5, 10, $cloud);

// Bricks
for ($j = 0; $j < 20; $j++)
{
    for ($i = 5; $i <= 11; $i++)
    {
        if (($i + $j)%2 == 1) // right side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_right_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_right_side_brick);
        }
        else // left side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_left_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_left_side_brick);
        }
    }
}

// hide gridlines so they don't mess with our Excel art.
$worksheet->hideGridLines();

$workbook->send('bricks.xls');
$workbook->close();
?>

Format::setBottomColor

Format::setBottomColor – Sets the cell's bottom border color

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setBottomColor ( mixed $color )

Description

Sets the cell's bottom border color

Parameter

Note

This function can not be called statically.

Example

Using setBottomColor()

<?php

?>

Format::setTopColor

Format::setTopColor – Sets the cell's top border color

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setTopColor ( mixed $color )

Description

Sets the cell's top border color

Parameter

Note

This function can not be called statically.

Example

Using setTopColor()

<?php

?>

Format::setLeftColor

Format::setLeftColor – Sets the cell's left border color

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setLeftColor ( mixed $color )

Description

Sets the cell's left border color

Parameter

Note

This function can not be called statically.

Example

Using setLeftColor()

<?php

?>

Format::setRightColor

Format::setRightColor – Sets the cell's right border color

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setRightColor ( mixed $color )

Description

Sets the cell's right border color

Parameter

Note

This function can not be called statically.

Example

Using setRightColor()

<?php

?>

Format::setFgColor

Format::setFgColor – Sets the cell's foreground color

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setFgColor ( mixed $color )

Description

Sets the cell's "foreground color".

The term "foreground color" is misleading. Here, "foreground" means the top layer of a cell's background. To set the color of a cell's contents, use the setColor() method.

The color actually seen may depend on the pattern and background color being used.

The example entitled "How background and foreground colors interact with patterns" is very helpful.

Parameter

Using colors

The following colors can be defined by name: black, white, red, green, blue, yellow, magenta and cyan.

To learn what the other indexed colors look like, read Color Palette and the 56 Excel ColorIndex Colors. Beware that the color indexes listed there are displaced by 1 with respect to those used by Spreadsheet_Excel_Writer.

If the predifined colors don't meet your requirements, use the setCustomColor() method.

Note

This function can not be called statically.

Example

Using setFgColor()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

// "regular" green
$format_regular_green =& $workbook->addFormat();
$format_regular_green->setFgColor('green');

// "special" green
$format_special_green =& $workbook->addFormat();
$format_special_green->setFgColor(11);

// our green (overwriting color on index 12)
$workbook->setCustomColor(12, 10, 200, 10);
$format_our_green =& $workbook->addFormat();
$format_our_green->setFgColor(12);

$worksheet->setColumn(0, 0, 30);

$worksheet->write(0, 0, "Regular green", $format_regular_green);
$worksheet->write(1, 0, "Special green (index 11)", $format_special_green);
$worksheet->write(2, 0, "Our green", $format_our_green);

$workbook->send('setFgColor.xls');
$workbook->close();
?>

See

setBgColor(), setPattern(), setColor()

Format::setBgColor

Format::setBgColor – Sets the cell's background color

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setBgColor ( mixed $color )

Description

Sets the cell's "background color".

The term "background color" is misleading. Here, "background" means the bottom layer of a cell's background.

This method only comes into play when creating patterns via the setPattern() method. In general, chances are you are more interested in using the setFgColor() method.

The example entitled "How background and foreground colors interact with patterns" is very helpful.

Parameter

Note

This function can not be called statically.

Example

Using setBgColor()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet('testing bg color');
$worksheet->setRow(1, 30);

$format6 =& $workbook->addFormat();
$format6->setBgColor('green');
$format6->setPattern(6);
$worksheet->write(1, 1, 'the bg', $format6);

$workbook->send('setBgColor.xls');
$workbook->close();
?>

See

setFgColor(), setPattern(), setColor()

Format::setColor

Format::setColor – Sets the color of a cell's content

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setColor ( mixed $color )

Description

Sets the color of a cell's content

Parameter

Note

This function can not be called statically.

Example

Using setColor()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook  = new Spreadsheet_Excel_Writer();
$worksheet = &$workbook->addWorksheet('SetColor');

for ($inc =0; $inc<64; $inc++) {
    // Sets the color of a cell's content
    $format = $workbook->addFormat();
    $format->setColor($inc);
    $worksheet->write($inc, 0, 'Color (index '.$inc.')', $format);
}
$workbook->send('setColor.xls');
$workbook->close();
?>

See

setFgColor(), setBgColor()

Format::setPattern

Format::setPattern – Sets the fill pattern attribute of a cell

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setPattern ( integer $arg=1 )

Description

Sets the fill pattern attribute of a cell. Use in combination with the setBgColor() and setFgColor() methods.

The example entitled "How background and foreground colors interact with patterns" is very helpful.

Parameter

Note

This function can not be called statically.

Example

How background and foreground colors interact with patterns

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet('testing colors and patterns');
$worksheet->setRow(1, 30);
$worksheet->setRow(2, 30);
$worksheet->setRow(3, 30);

// valid patterns are 0 to 18
for ($i = 0; $i <= 18; $i++)
{
    // green in different patterns
    $another_format1 =& $workbook->addFormat();
    $another_format1->setBgColor('green');
    $another_format1->setPattern($i);
    $worksheet->write(1, $i, "pattern $i", $another_format1);

    // red in different patterns
    $another_format2 =& $workbook->addFormat();
    $another_format2->setFgColor('red');
    $another_format2->setPattern($i);
    $worksheet->write(2, $i, "pattern $i", $another_format2);

    // mixed red and green according to pattern
    $another_format3 =& $workbook->addFormat();
    $another_format3->setBgColor('green');
    $another_format3->setFgColor('red');
    $another_format3->setPattern($i);
    $worksheet->write(3, $i, "pattern $i", $another_format3);
}

$workbook->send('setPattern.xls');
$workbook->close();
?>

See

setFgColor(), setBgColor(), setColor()

Format::setUnderline

Format::setUnderline – Sets the underline of the text

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setUnderline ( integer $underline )

Description

Sets the underline of the text

Parameter

Note

This function can not be called statically.

Example

Using setUnderline()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

  $workbook = new Spreadsheet_Excel_Writer();
  $format_underline =& $workbook->addFormat();
  $format_underline->setUnderline(1);
  $worksheet =& $workbook->addWorksheet();
  $worksheet->write(0, 0, "This is underlined", $format_underline);
  $format_underline->setUnderline(2);
  $worksheet->write(1, 0, "This is twice underlined", $format_underline);
?>

Format::setItalic

Format::setItalic – Sets the font style as italic

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setItalic ( )

Description

Sets the font style as italic

Note

This function can not be called statically.

Example

Using setItalic()

<?php

?>

Format::setSize

Format::setSize – Sets the font size

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setSize ( integer $size )

Description

Sets the font size

Parameter

Note

This function can not be called statically.

Example

Using setSize()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();
// we can set set all properties on instantiation
$upper_right_side_brick =& $workbook->addFormat(array('right' => 5, 'top' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
// or set all properties one by one
$upper_left_side_brick =& $workbook->addFormat();
$upper_left_side_brick->setLeft(5);
$upper_left_side_brick->setTop(5);
$upper_left_side_brick->setSize(15);
$upper_left_side_brick->setPattern(1);
$upper_left_side_brick->setBorderColor('blue');
$upper_left_side_brick->setFgColor('red');

$lower_right_side_brick =& $workbook->addFormat(array('right' => 5, 'bottom' => 5, 'size' => 15,
                                                      'pattern' => 1, 'bordercolor' => 'blue',
                                                      'fgcolor' => 'red'));
$lower_left_side_brick =& $workbook->addFormat(array('left' => 5, 'bottom' => 5, 'size' => 15,
                                                     'pattern' => 1, 'bordercolor' => 'blue',
                                                     'fgcolor' => 'red'));

$worksheet->setColumn(0, 20, 6);

// Sky
$sky =& $workbook->addFormat(array('fgcolor' => 'cyan', 'pattern' => 1, 'size' => 15));
for ($i = 0; $i <= 10; $i++)
{
    for ($j = 0; $j < 20; $j++) {
        $worksheet->writeBlank($i, $j, $sky);
    }
}

// Cloud
$cloud =& $workbook->addFormat(array('fgcolor' => 'white', 'pattern' => 1, 'size' => 15));
$worksheet->writeBlank(5, 7, $cloud);
$worksheet->writeBlank(4, 8, $cloud);
$worksheet->writeBlank(5, 8, $cloud);
$worksheet->writeBlank(6, 8, $cloud);
$worksheet->writeBlank(4, 9, $cloud);
$worksheet->writeBlank(5, 9, $cloud);
$worksheet->writeBlank(5, 10, $cloud);

// Bricks
for ($j = 0; $j < 20; $j++)
{
    for ($i = 5; $i <= 11; $i++)
    {
        if (($i + $j)%2 == 1) // right side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_right_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_right_side_brick);
        }
        else // left side of brick
        {
            $worksheet->writeBlank(2*$i, $j, $upper_left_side_brick);
            $worksheet->writeBlank(2*$i + 1, $j, $lower_left_side_brick);
        }
    }
}

// hide gridlines so they don't mess with our Excel art.
$worksheet->hideGridLines();

$workbook->send('bricks.xls');
$workbook->close();
?>

Format::setTextWrap

Format::setTextWrap – Sets text wrapping

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setTextWrap ( )

Description

Sets text wrapping

Note

This function can not be called statically.

Example

Using setTextWrap()

<?php

?>

Format::setTextRotation

Format::setTextRotation – Sets the orientation of the text

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setTextRotation ( integer $angle )

Description

Sets the orientation of the text

Parameter

Note

This function can not be called statically.

Example

Using setTextRotation()

<?php

?>

Format::setNumFormat

Format::setNumFormat – Sets the numeric format.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setNumFormat ( string $num_format )

Description

Sets the numeric format. It can be date, time, currency, etc... The following table lists possible values for $num_format and the corresponding types that a numeric format expects as arguments.

Numeric formats and types
0 Decimal The amount of zeros specifies the amount of digits that will be shown
0.00 Decimal The amount of zeros after the decimal dot specifies the amount of decimal digits that will be shown
#.## Decimal The amount of sharp signs after the decimal dot specifies the maximum amount of decimal digits that will be shown
0% Percent The amount of zeros specifies the amount of digits that will be shown.
0.000% Percent The amount of zeros after the decimal dot specifies the amount of decimal digits that will be shown.
$#.#;[Red]($#.#) Currency Zeros and sharp signs have the same meaning as in other formats.
??/?? Fraction The amount of question signs in the denominator determines its precision (maximum amount of digits in the denominator).
# ??/?? Fraction A fraction with an integer part. Zeros and sharp signs are used for defining the integer part, and they have the same meaning as in other formats.
0.00E+# Scientific In scientific notation base and exponent are formated according to the same rules applied to decimals. For scientific notation zeros and sharp signs appear to be equivalent.
D-MMM-YY Date A date represented in the given notation. Month can be a one or two digits month, or a three letter month. Year can have 2 or 4 digits. The argument to be formated as a date is considered to be the number of days since December 30 1899 (Excel's day zero). For dates preceding day zero, negative numbers can be used.
D/M/YYYY h:mm:ss Date/Time A date represented in the given notation. The argument to be formated as a date is considered to be the number of days since Excel's day zero.
h:mm:ss AM/PM Time A time represented in the given notation. Be careful, the argument to be formated as a time has to be given in days. For example an argument of 0.5 would be presented as '12:00:00 PM'.

The information here presented comes from OpenOffice.org's Documentation of the Microsoft Excel File Format (http://sc.openoffice.org/excelfileformat.pdf).

Parameter

Note

This function can not be called statically.

Example

Using setNumFormat()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

// We'll show dates with a three letter month and four digit year format
$date_format =& $workbook->addFormat();
$date_format->setNumFormat('D-MMM-YYYY');

// number of seconds in a day
$seconds_in_a_day = 86400;
// Unix timestamp to Excel date difference in seconds
$ut_to_ed_diff = $seconds_in_a_day * 25569;

// show Excel's day zero date
$worksheet->write(0, 0, "Excel's day zero");
$worksheet->write(0, 1, 0, $date_format);

// show today's date
$now = time();
$worksheet->write(1, 0, "Today's date:");
$worksheet->write(1, 1, ($now + $ut_to_ed_diff) / $seconds_in_a_day, $date_format);

$workbook->send('num_formatting.xls');
$workbook->close();
?>

Format::setStrikeOut

Format::setStrikeOut – Sets font as strikeout.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setStrikeOut ( )

Description

Sets font as strikeout.

Note

This function can not be called statically.

Example

Using setStrikeOut()

<?php

?>

Format::setOutLine

Format::setOutLine – Sets outlining for a font.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setOutLine ( )

Description

Sets outlining for a font.

Note

This function can not be called statically.

Example

Using setOutLine()

<?php

?>

Format::setShadow

Format::setShadow – Sets font as shadow.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setShadow ( )

Description

Sets font as shadow.

Note

This function can not be called statically.

Example

Using setShadow()

<?php

?>

Format::setScript

Format::setScript – Sets the script type of the text

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setScript ( integer $script )

Description

Sets the script type of the text

Parameter

Note

This function can not be called statically.

Example

Using setScript()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

  $workbook = new Spreadsheet_Excel_Writer();
  // creating the formats
  $format_superscript =& $workbook->addFormat();
  $format_superscript->setScript(1);
  $format_subscript =& $workbook->addFormat();
  $format_subscript->setScript(2);

  $worksheet =& $workbook->addWorksheet();
  $worksheet->write(0, 0, "This is superscript", $format_superscript);
  $worksheet->write(1, 0, "This is subscript", $format_subscript);
?>

Format::setFontFamily

Format::setFontFamily – Sets the font family.

Synopsis

require_once "Spreadsheet/Excel/Writer.php";

void Format::setFontFamily ( string $font_family )

Description

Sets the font family.

Parameter

Note

This function can not be called statically.

Example

Using setFontFamily()

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();

$format_times =& $workbook->addFormat();
$format_times->setFontFamily('Times New Roman');

$format_courier =& $workbook->addFormat();
$format_courier->setFontFamily('Courier');

$worksheet->write(0, 0, "Hello World, Arial (default)");
$worksheet->write(1, 0, "Hello World, Times New Roman", $format_times);
$worksheet->write(2, 0, "Hello World, Courier", $format_courier);

$workbook->send('fonts.xls');
$workbook->close();
?>