The purpose of BiffWriter is to create BIFF 2.1 compatible streams straight from PHP with a minimum amount of code writing. Using a widely accepted spread sheet format, compatible with Microsoft Excel allows sharing of information for a wide range of users. Excel compatible formats are accepted by all Windows versions, Microsoft Excel and associated viewers. In addition most of the UNIX derivatives include Excel compatible spread sheet programs. The application range of BiffWriter is almost unlimited: Customized price lists via the web, product catalogs, web logs and last but not least a complete hard copy of database tables.
BiffWriter supports the following features:
BIFF aka Binary File Format is the common denominator across spreadsheet programs. A BIFF file is a stream of records describing an Excel compatible spreadsheet. Such records contain for instance font information, cell data and password information. The BIFF format expects the individual records in a particular order to recognize the stream as a valid, Excel compatible file. BiffWriter takes care of assembling the various records into the expected order without having the user to remember that for instance formats come before cells, etc. This is accomplished by a unique parser putting the stream into correct order of records. All the user (programmer) needs to remember is that the stream must be closed at the end of the script. All other function can be called as desired.
BiffWriter consists currently of 2 classes:
The shortest simple example, writing text into Row 1 and
Column A contains only 4 lines of PHP code:
<?php
include "biff.php";
$myxls = new BiffWriter();
$myxls->xlsWriteText(0,0,"BiffWriter speeds up information exchange");
$myxls->xlsParse();
?>
BiffWriter currently supports writing of text and floating point numbers. Date/Time formats can be written as text or converted to a numerical value depending on the clients use. Date and time values need to be converted to a numerical value if the client wants to perform math or sorting on these values.
Acknowledgments and Disclaimer:
This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. The use of this library is free for non commercial purposes. This class is copyright of Christian Novak.
You can reach the author of this library, Christian Novak at cnovak@gmx.net. This class is a further development of a simple library ("_excel.php") developed by the author in 2000 and published at PX: PHP Code Exchange in November 2000.
The information about the BIFF specification was taken from a document called "Excel.txt" written by Mark O'Brien from Microsoft Corporation, dated February 18, 1988. This is an excellent start to understand the most common and simple spreadsheet format. I developed the first class for writing Excel binary files in 1995 using Delphi. This was further translated into a C library for use on non Windows platforms.
I would also like to acknowledge the work of Erol Ozcan who further developed my first simple Excel library into a real usable PHP class available at http://psxlsgen.sourceforge.net. I believe it is important too, to mention the continuous support of my patient partner Laura who always understands my desire about code writing and always encourages me to explore new areas in my professional life.
Your feedback, comments and help is greatly appreciated. Please drop me a note via mail or via the web site dedicated to this project.
Christian Novak
Wilhelm-Hertz-Str. 20
80805 Germanyphone +49 89 71998127
e-mail cnovak@gmx.net
What is next?
I am considering to implement the following features providing that there is enough interest in the PHP community. Additional functionality will be implemented as time permits and votes are provided.
| Ref | Function | Notes | Req. votes |
|---|---|---|---|
| 1 | Horizontal page breaks | Set horizontal page breaks for printing | complete |
| 2 | Vertical page breaks | Set vertical page breaks for printing | complete |
| 3 | Default row height | Set sheet wide row height | complete |
| 4 | Cell notes | Apply notes to individual cells | complete |
| 5 | Pane | Freeze top and left pane | 50 |
| 6 | Formula parser | Parse formulas as per the Excel notation. Note: Excel stores formulas in RPN (reverse polish notation) | 200 |
| 7 | Work book capability | Multiple work sheets per work book with internal and external links | 500 |
You can vote for for adding additional functionality at http://www.cnovak.com. This greatly helps me in setting further development priorities. Please note that you need to provide a real e-mail address for your vote to be taken into consideration. Please no "nospam@somehere.com", I am verifying all e-mail addresses to focus on the work the community is really interested in.
I have used a number of of different styles and layout in this manual to help to differentiate between different kind of information. Some examples are:
| Parameters: | ||
|---|---|---|
| picture | string |
Any Excel valid formatting picture. Taking into account differences in regional settings. |
This describes in more detail the parameters used for a method. Optional
parameters are in Italics, default parameters are highlighted in
red.
// Sample 1:
// write 12345.6789 into row 1, column B using default settings
xlsWriteNumber(0, 1, 12345.6789);
// same using A1 notation
xlsWriteNumber('B1', 0, 12345.6789);
// Sample 2:
// write 12345.6789 into row 1, column by using font 0, picture 12 (short
day,
// short month and long year), align center and flag the cell locked.
xlsWriteNumber(0, 1, 12345.6789, 0, 12, IDX_FONT0, ALIGN_CENTER, CELL_LOCKED
+ CELL_HIDDEN);
All public functions are prefixed with "xls".
| Public Functions: | |
|---|---|
| xlsAddFormat | add a customized picture format |
| xlsAddHPageBreak | add a horizontal page break |
| xlsAddVPageBreak | add a vertical page break |
| xlsCellNote | add a note to a cell |
| xlsDate | convert a UNIX date into an Excel compatible integer |
| xlsFooter | add a print footer to the spreadsheet |
| xlsHeader | add a print header to the spreadsheet |
| xlsParse | close the spreadsheet and parse it to the browser |
| xlsPrintMargins | set print margins |
| xlsProtectSheet | protect the sheet or cells |
| xlsSetBackup | set backup flag |
| xlsSetColWidth | set the column width for a range of columns manually |
| xlsSetDefFonts | create default fonts |
| xlsSetDefRowHeight | set the default row height for the entire spread sheet |
| xlsSetFont | set a customized font |
| xlsSetPrintGridLines | print gridlines |
| xlsSetPrintHeaders | print row and column references |
| xlsWriteDateTime | write an SQL datetime stamp into a cell |
| xlsWriteNumber | write a number into a cell |
| xlsWriteText | write a string into a cell |
| Public Constants: | |
| Font index - integer | • FONT_0, FONT_1, FONT_2, FONT_3 |
| Font styles - integer | • FONT_NORMAL, FONT_BOLD, FONT_ITALIC, FONT_UNDERLINE, FONT_STRIKEOUT |
| Cell attributes - integer | • ALIGN_GENERAL, ALIGN_LEFT, ALIGN_CENTER,
ALIGN_RIGHT • CELL_FILL, CELL_LEFT_BORDER, CELL_RIGHT_BORDER, CELL_TOP_BORDER, CELL_BOTTOM_BORDER, CELL_BOX_BORDER • CELL_SHADED • CELL_FILL |
| Cell status - integer | • CELL_LOCKED, • CELL_HIDDEN |
| Other - integer | • XLS_DATE |
| Public variables and arrays: | |
| err_level - integer | err_level defines the error/warning levels. By defaults no errors are trapped unless PHP stops the script itself. Set err_level to 1 to stop the script at all Biff2.1 specification errors. The default is 0. |
| outfile - string | this is the name of the file that the browser will suggest to the user when opening or saving the stream. The default is sample.xls. This can be omitted if the file is saved by specifying the name in xlsParse(). |
xlsAddFormat -- add a format/picture string
int xlsAddFormat(string picture)
xlsAddFormat adds a picture string to the internally formatting table and returns a pointer to the newly created entry. By default there are 21 pictures defined in Excel. See the table below showing more details. There is no known limitation on the number of picture strings to be possible added.
| Parameters: | ||
|---|---|---|
| picture | string |
Any Excel valid formatting picture. Taking into account differences in regional settings. |
Excel in the simplest file format comes with 21 formats also called pictures built in. The format index is referenced by an zero based pointer.
The following tables shows the internal defined picture formats. The sample number used for illustrating purposes is 33333.33. This number evaluates to April 5, 1991 at 7:59:57 AM using a date/time format. Otherwise it is treated as standard IEEE floating point number.
// example 1, adding an additional format pattern and returning
a pointer to this pattern
$idx_fmt = $myxls->xlsAddFormat('[Blue] 0 "US$"');
Internal formats:
| Index |
Display
|
Excel compatible picture string
|
|---|---|---|
| 0 |
33333.3333
|
General
|
|
1 |
33333
|
0 |
|
2 |
33333.33
|
0.00 |
|
3 |
33,333
|
#,##0 |
|
4 |
33,333.33
|
#,##0.00 |
|
5 |
$33,333
|
$#,##0_);($#,##0) |
|
6 |
$33,333
|
$#,##0_);[Red]($#,##0) |
|
7 |
$33,333.33
|
$#,##0.00_);($#,##0.00)
|
|
8 |
$33,333.33
|
$#,##0.00_);[Red]($#,##0.00)
|
|
9 |
3333333%
|
0%
|
|
10 |
3333333.33%
|
0.00%
|
|
11 |
3.33E+04
|
0.00E+00
|
|
12 |
4/5/1991
|
m/d/yyyy
|
|
13 |
5-Apr-91
|
d-mmm-yy
|
|
14 |
5-Apr
|
d-mmm
|
|
15 |
Apr-91
|
mmm-yy
|
|
16 |
7:59 AM
|
h:mm AM/PM
|
|
17 |
7:59:57 AM
|
h:mm:ss AM/PM
|
|
18 |
7:59
|
h:mm
|
|
19 |
7:59:57
|
h:mm:Ss
|
|
20 |
4/5/1991 7:59
|
m/d/yyyy h:mm
|
Care must be taken while defining picture strings for date and time formats. The above table assumes that the users system is set to English regional settings. However, other languages use different codes for naming days, month, years, hours, minutes and seconds. For instance to display 05-Apr-91 on a German system, the picture string #13 would read "t-mmm-jj".
xlsAddHPageBreak -- add a horizontal page break for printing
void xlsAddHPageBreak(int row)
xlsAddHPageBreak adds a page break after the row specified.
xlsAddVPageBreak -- add a vertical page break for printing
void xlsAddVPageBreak(mixed column)
xlsAddVPageBreak adds a page break after the column specified. Column is in mixed notation, integer, zero based or string 'A' to 'IV'.
// example 1, numeric notation:
xlsAddVPageBreak(10);
// example 2, A1 notation:
xlsAddVPageBreak('J');
xlsCellNote -- Add a note to a cell
void xlsCellNote(mixed row, int col, string note)
This function adds a cell note. Notes can be up to 2048 characters in length. Row is in mixed notation, integer 0 based or string 'A' to 'IV'.
| Parameters: | ||
|---|---|---|
| row | mixed | Cell rows are zero based. Valid ranges are from 0 to 65535. Row is in mixed notation, integer 0 based or string 'A1' to 'IV16384'. |
| col | int | Cell column zero based. Valid ranges are from 0 to 255. This must be set to zero if the A1 notation is used as a row parameter. |
| note | string | String of characters to assign to a note |
xlsDate -- Convert a UNIX time stamp into Excels "1900" format
int xlsDate(int month,int day, int year)
The Excel date starts on January 1st, 1900 while UNIX dates start on January 1st, 1970. This function converts a UNIX date into the corresponding Excel date returning an integer based on the 1900 format. This function is useful together with xlsWriteNumber() applying a date/time format picture.
| Parameters: | ||
|---|---|---|
| month | int | Month value, valid range is 1-12 |
| day | int | Day value, valid range is 1-31 |
| year | int | Year value, valid range is not determined |
ExcelDate = xlsDate(4, 26, 1962)
// returns the Excel date serial number for April 26, 1962.
xlsFooter -- Specify a print footer
xlsHeader -- Specify a print header
void xlsFooter(string footer)
void xlsHeader(string header)
| Parameters: | ||
|---|---|---|
| footer, header | string |
Text to be printed on every page. In addition to
regular text, the footer/header accepts the following macro expressions: |
// Sample 1:
// print a centered header using font size 12
xlsHeader('&12BiffWriter Demo (c) C. Novak - cnovak@gmx.net');
// Sample 2:
// print a left, center and right aligned section
// left: Page x of y using font size 12
// center: Date and time
// right: Filename and line feed
xlsFooter('&L&12Page &P of &N &C&D &T &R&F/&A');
xlsParse -- Assembles the BIFF data and sends the stream or saves the content.
string xlsParse(string filename);
xlsParse assembles all the calls to the previous BIFF functions and writes the stream to the browser or a local file. This command concludes all BIFF actions and exits the class.
| Parameters: | ||
|---|---|---|
| filename | string | Fully qualified file name, default is NULL. If omitted, a header specifying an attachment and the output is sent, otherwise the file filename is created locally. |
// Sample 1:
// stream the contents straight to the browser
xlsParse();
// Sample 2:
// save the file locally and offer a download link
$link = xlsParse('YourFile.xls');
print '<A HREF="' .$link. '">'.$link.'</A>';
xlsPrintMargins -- Set left, right, top and bottom print margins
void xlsPrintMargins(float left[, float right[, float top[, float bottom]]])
Specify the print margins in INCHES using xlsPrintMargins. The defaults values are 0.5 inches.
| Parameters: | ||
|---|---|---|
| left, right, top, bottom | float | Margin in inches. The default margin calling xlsPrintMargins is 0.5 inches |
xlsProtectSheet -- set worksheet protection.
void xlsProtectSheet(string password[, bool protect_cells])
xlsProtectSheet protects the worksheet
against changes.
There are 2 levels of protection:
- For the entire worksheet except for cells not flagged "locked".
and
- For cells flagged "locked".
The default value for both parameters is FALSE.
| Parameters: | ||
|---|---|---|
| protect_sheet | string |
Password to protect changes flagged with CELL_LOCKED or any empty cell. The default is an empty string = no password |
| protect_cells | bool | TRUE = enables protection of individual cells. The default is FALSE. |
// Sample 1:
// turn on cell protection for all cells where the locked flag is set
xlsProtectSheet('', TRUE);
// Sample 2:
// protect sheet properties AND cells where the locked flag is set
xlsProtectSheet('MYPASSWORD', TRUE);
xlsSetBackup -- turn on backup option
void xlsSetBackup();
Call xlsSetBackup to turn on the creation of backup files while changes are applied to the spread sheet. Excel saves the backup file prepending "Backup of " and with the extension "XLK".
xlsSetColWidth -- Set column width
void xlsSetColWidth(mixed colstart, mixed colend, int width)
Sets the width for a range of columns. The column width is internally calculated as 1/256 of a point. Since the required column width to display data is based on the used font, you won't necessarily make cell contents fit into a cell by only taking the length of the data.
| Parameters: | ||
|---|---|---|
| colstart | mixed | First column to set the width. This is in mixed notation, either integer, zero based or 'A' to 'IV'. |
| colend | mixed | Last column to set the width. This is in mixed notation, either integer, zero based or 'A' to 'IV'. |
| width | int | Width in characters based on "Courier New" 10px. |
// Sample:
// set the width of column A, B, C, D & E to 10 characters
xlsSetColWidth(0, 4, 10);
// this is the same using A1 notation
xlsSetColWidth('A', 'E', 10);
xlsSetDefFonts -- Creates 4 default fonts
void xlsSetDefFonts();
This function creates 4 default fonts for the use with cell format. The font index is zero based from 0 to 3. Fonts can be referred to by the constants FONT_0, FONT_1, FONT_2, FONT_3. The default fonts are:
| Font name | Font style | Index |
|---|---|---|
| Arial | Arial 10 normal | FONT_0 |
| Courier | Courier New 10 normal | FONT_1 |
| Times | Times New Roman 10 normal | FONT_2 |
| System | System | FONT_3 |
xlsSetDefRowHeigth -- set the default row height for the entire spreadsheet
void xlsSetDefRowHeight(int points);
This function sets the default row height for all rows in the entire spread sheet. The unit of the value passed to this function is points.
| Parameters: | ||
|---|---|---|
| points | int | row height in points |
xlsSetFont -- create a new font entry
void xlsSetFont(string name[, int size[, int format]]);
The Excel or BIFF 2.1 allows for 4 fonts per spreadsheet. Fonts can be added to the internal font table by calling xlsSetFont specifying the font name, font size and attributes such as bold, underline etc. The first font added becomes FONT_0, the second FONT_1 and so on.
| Parameters: | ||
|---|---|---|
| name | string | Font name as specified by the operating system. Exact spelling matters. |
| size | int | Font size in points, the default is 10 points. |
| format | int |
One or more of the following font attributes in
any combination: |
// Sample:
// create font 0 Arial 10, font 1 Arial 10 bold + underlined
xlsSetFont('Arial');
xlsSetFont('Arial', 10, FONT_BOLD + FONT_UNDERLINE);
xlsSetPrintGridLines -- turn printing of Gridlines on
void xlsSetPrintGridLines();
Call xlsSetPrintGridLines to turn the printing of grid lines on.
xlsSetPrintHeaders -- turn printing of row and column headers on.
void xlsSetPrintHeaders();
Call xlsSetPrintHeaders to turn on printing of row and column headers.
xlsWriteDateTime -- Writes an SQL datetime stamp into a cell
void xlsWriteDateTime(mixed row, int col, double value [, int width [, int picture [, int font [, int attribute [, int status]]]]])
Writes an SQL datetime stamp into a floating point value (double) into a cell at a given row and column. Excel expects valid date/times as numeric value. This function converts an SQL datetime stamp into a floating point value
| Parameters: | ||
|---|---|---|
| row | mixed | Cell row zero based or absolute cell position in A1 to IV 65536 notation. |
| col | int | Cell column zero based. Valid ranges are from 0 to 255. Must be set to zero if A1 notation is used. |
| value | string | Datetime stamp in the format of 'YYYYMMDD[HH[MM[SS]]]'. |
| width | int | Cell width, for details please see chapter Cell Formatting |
| picture | int | Index to picture format, for details please see chapter Cell Formatting |
| font | int | Index to FONT_0 to FONT_3, for details please see chapter Cell Formatting |
| attribute | int | Cell attributes, for details please see chapter Cell Formatting |
| status | int | Cell protection, for details please see chapter Cell Formatting |
// Sample 1:
// write SQL datetime of 01-May-2002 11:55:00 into row 1, column B using default
settings
xlsWriteDateTime(0, 1, '20020501115500);
// same using A1 notation
xlsWriteDateTime('B1', 0, '20020501115500');
// Sample 2:
// write a SQL date of 01-May-2002 into row 1, column B by using default settings
xlsWriteDateTime(0, 1, '20020501');
xlsWriteNumber -- Writes a number into a cell
void xlsWriteNumber(mixed row, int col, double value [, int width [, int picture [, int font [, int attribute [, int status]]]]])
Writes a floating point value (double) into a cell at a given row and column.
| Parameters: | ||
|---|---|---|
| row | mixed | Cell row zero based or absolute cell position in A1 to IV 65536 notation. |
| col | int | Cell column zero based. Valid ranges are from 0 to 255. Must be set to zero if A1 notation is used. |
| value | double | Cell content as double value |
| width | int | Cell width, for details please see chapter Cell Formatting |
| picture | int | Index to picture format, for details please see chapter Cell Formatting |
| font | int | Index to FONT_0 to FONT_3, for details please see chapter Cell Formatting |
| attribute | int | Cell attributes, for details please see chapter Cell Formatting |
| status | int | Cell protection, for details please see chapter Cell Formatting |
// Sample 1:
// write 12345.6789 into row 1, column B using default settings
xlsWriteNumber(0, 1, 12345.6789);
// same using A1 notation
xlsWriteNumber('B1', 0, 12345.6789);
// Sample 2:
// write 12345.6789 into row 1, column by using font 0, picture 12 (short day,
// short month and long year), align center and flag the cell locked.
xlsWriteNumber(0, 1, 12345.6789, 0, 12, IDX_FONT0, ALIGN_CENTER, CELL_LOCKED
+ CELL_HIDDEN);
void xlsWriteText(int row, int col, string text [, int width [, int picture [, int font [, int attribute [, int status]]]]])
This function writes text into a cell positioned at row and column. The length of text can be up to 256 characters per cell.
| Parameters: | ||
|---|---|---|
|
row |
int |
Cell row zero based or absolute cell position in A1 to IV 65536 notation. |
|
col |
int |
Cell column zero based. Valid ranges are from 0 to 255. Must be set to zero if A1 notation is used. |
|
text |
string |
Cell contents as string, up to 256 characters. |
| width | int | Cell width, for details please see chapter Cell Formatting |
| picture | int | Index to picture format, for details please see chapter Cell Formatting |
| font | int | Index to FONT_0 to FONT_3, for details please see chapter Cell Formatting |
| attribute | int | Cell attributes, for details please see chapter Cell Formatting |
| status | int | Cell protection, for details please see chapter Cell Formatting |
// Sample 1:
// write "Hello World" into row 2, column B using default settings
xlsWriteText(1, 1, "Hello World);
// Sample 2:
// write "Hello World" into row 2, column B using auto width, second
defined font,
// general picture, align center and flag the cell locked.
xlsWriteText(1, 1, "Hello World", 0, 0, FONT_1, ALIGN_CENTER, CELL_LOCKED);
The formatting of cells is accomplished by specifying the attributes width, picture, font, alignment and status.
The cell width is an optional parameter. The width of a cell
is calculated by taking the length of the string ("auto width") or
by using an explicit setting. The actual width required to display all characters
in a cell varies by font and by the width of each individual character within
the cell. On Windows based systems Arial 10 seems to lead the most consistent
results. Explicitly setting the width allows for closer control of each column
and is the preferred method to create "ready to go" Excel files.
The sample picture below illustrates the issue with the required cell width versus the calculated cell width based on 4 sample fonts:

The sample below explains how column width are derived:

| Column A: |
All cell entries are set to 0, "auto width". BiffWriter takes the longest cell within all cells in column A to determine the maximum required width. |
|
Column C: |
All cell entries are set to -1, "don't care", then a width of 10 characters has been applied using xlsSetColWidth. |
|
Column E: |
All cell entries are set to -1, "dont' care". Excel uses the default width which is 8.43 characters |
Here is a another example including the source code:
<?php
/* The first 3 rows are set to "auto width" since the 3rd argument
(col_width) has been omitted.
** So the largest in size wins, this is "A2" with a width of 15 characters.
** "A4" is set to -1, "don't care" and is not taken into
consideration.
*/
include('biff.php');
$xls = new BiffWriter();
$xls->xlsWriteText('A1', 0, str_repeat('X' ,10));
$xls->xlsWriteText('A2', 0, str_repeat('X', 15));
$xls->xlsWriteText('A3', 0, str_repeat('X', 5));
$xls->xlsWriteText('A4', 0, str_repeat('X', 30), -1);
$link = $xls->xlsParse("width.xls");
print '<A HREF="' .$link. '">'.$link.'</A>';
?>
While parsing the stream, BiffWriter takes the largest width of a cell set via "auto width" or a explicit set cell width and applies this setting to the whole column. The default value is 0, "auto width".
Cell pictures aka display formats are set with the help of an optional picture parameter. Pictures are referred by an zero based index. The default value is 0, "General" format.
The BIFF 2.1 specification supports only 4 default fonts
in a spread sheet. By default, Excel assumes the font set in the configuration
page. In most cases this is Arial 10. BiffWriter allows setting of four individual
fonts by calling xlsSetFont. As as shortcut, calling
xlsSetDefFonts creates four default fonts, namely
Arial 10, Courier New 10, Times New Roman 10 and System right away. Fonts are
referred to with the public constants FONT_0 to FONT_3 respectively. Attributes
such as bold, underlined, italics and or strikeout can be applied to each of
the four fonts in any combination. The default
font is FONT_0.
Borders are set by using the optional attribute parameter. Border values can be applied in any possible combination. Border values are combined with the cell alignment attributes. The default values are ALIGN_GENERAL and no border.
The cell status (hidden or protection) is determined by the optional status parameter. The default values are unlocked and non-hidden.
| Parameter | Type | Description |
|---|---|---|
|
width |
int |
Determines the width of the cell, the width of the
whole column. The default is 0, auto width. |
|
picture |
int | Formatting picture as defined by the first 21 internal formats or formats added by xlsAddFormat. The default is 0, "General" |
|
font |
int | Pointer to font 0 to font 3, referenced as FONT_0....FONT_3 respectively. The default is FONT_0. |
|
attribute |
int |
Cell alignment -- specifies the alignment of the
cell content: Borders -- specifies cell borders: Other: The default is ALIGN_GENERAL, no border, no shading and no fill. |
|
status |
int |
Cell protection -- protects or hides a cell: The default is cell unlocked and not hidden. |