1. Introduction
  2. Formatting Tutorial
  3. Workbook::close
  4. Workbook::&addWorksheet
  5. Workbook::&addFormat
  6. Workbook::setCountry
  7. Workbook::&setTempDir
  8. Workbook::setVersion
  9. Workbook::setCustomColor
  10. Workbook::worksheets
  11. Worksheet::getName
  12. Worksheet::
    setInputEncoding
  13. Worksheet::select
  14. Worksheet::activate
  15. Worksheet::setFirstSheet
  16. Worksheet::protect
  17. Worksheet::setColumn
  18. Worksheet::writeCol
  19. Worksheet::writeRow
  20. Worksheet::setSelection
  21. Worksheet::freezePanes
  22. Worksheet::thawPanes
  23. Worksheet::
    hideScreenGridlines
  24. Worksheet::setPortrait
  25. Worksheet::setLandscape
  26. Worksheet::setPaper
  27. Worksheet::setHeader
  28. Worksheet::setFooter
  29. Worksheet::setMerge
  30. Worksheet::
    centerHorizontally
  31. Worksheet::
    centerVertically
  32. Worksheet::setMargins
  33. Worksheet::setMargins_LR
  34. Worksheet::setMargins_TB
  35. Worksheet::setMarginLeft
  36. Worksheet::
    setMarginRight
  37. Worksheet::setMarginTop
  38. Worksheet::
    setMarginBottom
  39. Worksheet::repeatRows
  40. Worksheet::repeatColumns
  41. Worksheet::printArea
  42. Worksheet::hideGridlines
  43. Worksheet::
    printRowColHeaders
  44. Worksheet::fitToPages
  45. Worksheet::
    setHPagebreaks
  46. Worksheet::
    setVPagebreaks
  47. Worksheet::setZoom
  48. Worksheet::setPrintScale
  49. Worksheet::write
  50. Worksheet::writeNumber
  51. Worksheet::writeString
  52. Worksheet::writeNote
  53. Worksheet::writeBlank
  54. Worksheet::writeFormula
  55. Worksheet::writeUrl
  56. Worksheet::setRow
  57. Worksheet::mergeCells
  58. Worksheet::insertBitmap
  59. Worksheet::setOutline
  60. Spreadsheet_Excel_Writer
  61. send
  62. rowcolToCell
  63. Format::setAlign
  64. Format::setVAlign
  65. Format::setHAlign
  66. Format::setMerge
  67. Format::setLocked
  68. Format::setUnLocked
  69. Format::setBold
  70. Format::setBottom
  71. Format::setTop
  72. Format::setLeft
  73. Format::setRight
  74. Format::setBorder
  75. Format::setBorderColor
  76. Format::setBottomColor
  77. Format::setTopColor
  78. Format::setLeftColor
  79. Format::setRightColor
  80. Format::setFgColor
  81. Format::setBgColor
  82. Format::setColor
  83. Format::setPattern
  84. Format::setUnderline
  85. Format::setItalic
  86. Format::setSize
  87. Format::setTextWrap
  88. Format::setTextRotation
  89. Format::setNumFormat
  90. Format::setStrikeOut
  91. Format::setOutLine
  92. Format::setShadow
  93. Format::setScript
  94. Format::setFontFamily

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(00"Profits for Dotcom.Com"$format_bold);
// And now the data
$worksheet->write(000);

?>

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(00"Quarterly Profits for Dotcom.Com"$format_title);
// While we are at it, why not throw some more numbers around
$worksheet->write(10"Quarter"$format_bold);
$worksheet->write(11"Profit"$format_bold);
$worksheet->write(20"Q1");
$worksheet->write(210);
$worksheet->write(30"Q2");
$worksheet->write(310);

$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(00"Quarterly Profits for Dotcom.Com"$format_title);
// Couple of empty cells to make it look better
$worksheet->write(01""$format_title);
$worksheet->write(02""$format_title);
$worksheet->write(10"Quarter"$format_bold);
$worksheet->write(11"Profit"$format_bold);
$worksheet->write(20"Q1");
$worksheet->write(210);
$worksheet->write(30"Q2");
$worksheet->write(310);

$workbook->send('test.xls');
$workbook->close();
?>
how to generate Excel files (Previous) Calls finalization methods for the workbook (Next)
Last updated: Sat, 16 Feb 2019 — Download Documentation
Do you think that something on this page is wrong? Please file a bug report.
View this page in:
  • English

User Notes:

Note by: ngiraldo@securities.com
Excel 2010 does open the file correctly.

You just have to add this before creating the worksheets:

$workbook->setVersion(8);

Note by: shusting@aeteam.net
Windows 7 Excel 2010 does not open the Excel downloads generated by this PHP code (while Excel in Win XP and Vista do).

Someone confirmed this, saying, "Yes, it seems that Excel will no longer open files with the .xls extension if they are formatted as TAB-delimited text or CSV."

In previous versions of Windows, the file downloaded and opened in Excel fine. But in the 2010 scenario in Win 7, for the same files, a dialog pops up with, "The document is corrupt and cannot be opened. To try and repair it, use the Open and Repair command in the Open dialog box and select Extract Data when prompted." Following these directions fails silently.

Is there an update we should be aware of for Excel 2010 in Win 7?
Note by: pratiksha88.j@gmail.com
Hi, I tried this code but i am getting my webpage contents printed to the excel sheet. any help really appreciated.

I installed spreadsheet and ole latest versions.
here is my code

<?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(00'Name');
$worksheet->write(01'Age');
$worksheet->write(10'John Smith');
$worksheet->write(1130);
$worksheet->write(20'Johann Schmidt');
$worksheet->write(2131);
$worksheet->write(30'Juan Herrera');
$worksheet->write(3132);

// Let's send the file
$workbook->close();
?>
Note by: zuraforl2@gmail.com
(Spreadsheet/Excel/Writer.php)

for this download http://pear.php.net/package/Spreadsheet_Excel_Writer/download
Note by: mustang342891@GMAIL.com
(Spreadsheet/Excel/Writer.php) WHERE THIS FILE ...............