exportSpreadsheets PHP Class

 

The exportSpreadsheets class is an abstraction layer for the Spreadsheet_Excel_Writer package available for download at the PHP Extension and Application Repository (PEAR) website (http://pear.php.net/package/Spreadsheet_Excel_Writer).  The class helps you export data from one or more tables in a single MySQL database into a Microsoft Excel spreadsheet (workbook) comprised of one or more worksheets.

 

You create a spreadsheet by passing a workbook array containing one or more worksheet arrays with values for each worksheet desired.  If you pass an empty workbook array to the class, the Excel spreadsheet will contain a single worksheet listing all table fields in an unsorted fashion, with each row of the spreadsheet containing one record from a single database table.  The field names become the names of the columns in Excel.  The empty workbook array causes the class to use global variables for the server name, database name, user name, user password, and table name.

 

The worksheet array can contain 3 types of sub-arrays:

 

A “connection” array contains variables to be used instead of the global variables for the server name, database name, user name, user password, and table name.  This allows a single workbook to contain worksheets from different databases on the same server or on different servers.  The connection array may also contain a MySQL query statement that can extract data from multiple tables within a single database.  The query can return calculated data such as min, max, sum, count, etc.  The query can join multiple relational tables together, and can contain “where”, “group by”, and “order by” clauses.  There can only be one connection array per worksheet array.

 

A “sheet” array contains information assigning a name to a single worksheet; a title for the page; and worksheet formatting parameters for page orientation, horizontal and vertical centering, page margins, and the desired maximum print size (e.g., 3 pages wide by 2 pages tall).  The array can also contain the name and sort order (ascending or descending) of a field used to sort the results.  If the connection array contains a query that already has an “order by” clause, the sort field and sort order in the sheet array will be ignored.  There can only be one sheet array per worksheet array.

 

A “column” array can contain column formatting parameters, such as assigning Excel column titles to field names, column alignment, and column summation.  If a non-numeric column is “summed,” the column will list the number of occurrences for each unique data element.  For example, if a column contained shirt sizes of small, medium, large and extra large; the summation would list the total number of each shirt size listed in the column.  The column array may contain one or more row formulas, identified by the reserved field name “rowFormula”.  A sample row formula might be “=column3-column2” or “=column5*column6”.  The equals sign is required.


The following files are included with the class:

 

exportSpreadsheetsClass.html

This documentation file.

createExportDatabase.sql

A MySQL file that will create the database, tables, user name, and password that are used in the 3 demonstration files.

export_spreadsheets_class.php

The PHP class.

export_simple.php

A file demonstrating passing an empty array to the class, resulting in a single worksheet being exported that lists all fields from a single table in an unsorted fashion.

export_fancy.php

A file demonstrating the export of a spreadsheet containing 3 worksheets.

exportComplexQueries.php

A file demonstrating the use of complex queries and row formulas.

exportSpreadsheetConditions.html

A document listing the output that should result from each of the 16 different sets of input conditions for the class.

regressionTests.php

A file that demonstrates the 16 different sets of input conditions.  Line 3 of that file contains a variable called $testCondition, which can be set to the numbers 1 through 16 to demonstrate the individual input conditions.  Setting $testCondition = "20" will create a single workbook containing 15 worksheets, one sheet each for conditions 2 through 16.  Line 26 of the file contains a global debug variable named   $myExport->debug.  If set to “true,” the MySQL query statement used to extract the data for a particular worksheet will be printed at the top of that worksheet.

copying.txt

The GNU General Public License (v3).

 

The class contains 6 public properties: mysqlServer, mysqlUsername, mysqlPassword, mysqlDatabaseName, mysqlTableName, and debug.  The first 5 properties are required for an empty workbook array.  The first 4 properties are required for any worksheet array that does NOT contain a connection array with either a mysqlTableName element or a mysqlQuery element.

 

The class contains one method (export_spreadsheet) that is passed two variables: workbook_name and workbook_array.  The first variable is the name of the Excel file that will be created, and the second variable is an array containing an array of values for each worksheet desired.  If the workbook array is null a single worksheet named “All Fields” will be generated in an unsorted fashion, with each row of the spreadsheet containing one record from the database table.

 

A properly formatted workbook array may contain three types of sub-arrays; one type specifies parameters for a database connection, one type specifies parameters for formatting a worksheet and the other type specifies parameters for a single column within a worksheet.  All sub-arrays are optional, but a valid workbook array must contain a worksheet name and at least one of the three sub-arrays.  The following simplified workbook array definition will produce a single worksheet comprised of three columns:

 

workbook_array['worksheet name'] = array(

'connection' => array (database connection parameters),

'sheet' => array (worksheet parameters),

'column1' => array (column1 parameters),

'column2' => array (column2 parameters),

'column3' => array (column3 parameters))

 

In the above example, the associative array key labeled ‘worksheet name’ becomes the name of the worksheet that appears in the tab across the bottom of the Excel workbook.  The word ‘sheet’ is required to identify the array of worksheet parameters.  The words ‘column1,’ ‘column2,’ and ‘column3’ can be any word other than ‘sheet’ or “connection,” but the use of ‘column1’ through ‘columnN’ is recommended, and if the row formula capability is used, the column arrays MUST be named ‘column1’, “column2’, through ‘columnN’, sequentially.  In other words, you cannot skip any column numbers.

 

A “connection” parameter array may have 6 elements.  Some of the values may not be present, or may be null values.  The parameters are described in the following table:

 

Parameter Name

Contains

mysqlServer

A server name such as databaseserver.company.com

mysqlUsername

A valid database user name for that server

mysqlPassword

The password for the above user name

mysqlDatabaseName

The database name

mysqlTableName

<optional> A database table name, or null

mysqlQuery

<optional> A MySQL query statement, or null

 


A “sheet” parameter array may have 14 elements.  Some of the values may be null values.  The parameters are described in the following table:

 

Parameter Name

Allowable Values

sortFieldName

A field name from the database table

sortFieldDirection

‘ASC,’ ‘DESC,’ or null

pageTitle

A title to be displayed in cell A1, or null

orientation

‘portrait,’ or ‘landscape’

numberPagesWide

An integer or null

numberPagesTall

An integer or null

repeatRows

(not currently implemented)

repeatColumns

(not currently implemented)

leftMargin

A value in inches such as ‘0.25’

rightMargin

A value in inches such as ‘0.25’

topMargin

A value in inches such as ‘0.25’

bottomMargin

A value in inches such as ‘0.25’

centerHorizontally

‘yes,’ ‘no,’ or null

centerVertically

‘yes,’ ‘no,’ or null

 

A “column” parameter array may have 7 elements.  The parameters are described in the following table:

 

Parameter Name

Allowable Values

fieldName

A field name from the database table, or “rowFormula

columnTitle

The desired column title, or null

alignment

‘left,’ ‘right,’ ‘center’, or null

vAlign

‘top,’ ‘vCenter,’ ‘bottom’, or null

sum

‘yes,’ ‘no,’, or null

formula

<required if fieldName = “rowFormula”, otherwise ignored>

A valid Excel formula such as “=column5*column6”

numberFormat

<optional> Refer to http://www.openoffice.org/sc/excelfileformat.pdf for a complete list of valid formats.

 

The demonstration file “export_simple.php” uses an empty workbook array, resulting in a single worksheet being exported that lists all fields from a single table in an unsorted fashion.

 

The demonstration file “export_fancy.php” uses a workbook array containing three worksheet parameters arrays, each followed by three column parameters arrays, resulting in a spreadsheet containing 3 worksheets.

 

The demonstration file “regressionTests.php” can be used to create 17 separate workbooks, one at a time.  All except one of the 17 workbooks will contain a single worksheet.  The 17th workbook will contain 15 worksheets, named “condition2” through “condition16,” inclusive.