Article : Reading and Writing Spreadsheets with PHP

Reading and Writing Spreadsheets with PHP

Breaking The Language Barrier

When it comes to playing nice with data in different formats, PHP’s pedigree is hard to beat. Not only does the language make it a breeze to deal with SQL resultsets and XML files, but it comes with extensions to deal with formats as diverse as Ogg/Vorbis audio files, ZIP archives and EXIF headers. So it should come as no surprise that PHP can also read and write Microsoft Excel spreadsheets, albeit with a little help from PEAR.

In this article, I’ll introduce you to two packages that make it surprisingly easy to hook your PHP scripts up to a Microsoft Excel spreadsheet and extract the data contained therein. I’ll also show you how to dynamically create a new spreadsheet from scratch, complete with formulae and formatting, and import data from a spreadsheet into a database. So come on it, and let’s get started!

Putting The Pieces Together

There isn’t currently a unified reader/writer package for Excel spreadsheets in PEAR, so this tutorial will make use of two separate packages:

For the latter package, you’ll also need to download and install the PEAR OLE package, from http://pear.php.net/package/OLE

You can install these packages manually, or using the PEAR installer, as below:

shell> pear install Spreadsheet_Excel_Writer

I should mention at this point certain changes you might need to make to your development environment in order to get the PHP-ExcelReader package working. As noted above, this package is currently not maintained and so, simply include()-ing the main class file, ‘reader.php’, in your script, as suggested in the package documentation, is insufficient and generates a ‘missing file’ error.

To get things working, extract the file ‘oleread.inc’ from the distribution archive and save it to a location in your PHP include path using the directory structure ‘Spreadsheet/Excel/Reader/OLERead.php’. Once you do this, the error should disappear and things should begin working normally. There’s a caveat, though: this procedure works with the version of PHP-ExcelReader dated Jul 5 2007 (the most current version at the time of writing) but may not work with other versions.

The Number Game

Now that you have all the pieces installed, let’s see them in action. Assume for a moment that you have the following Excel spreadsheet:


Now, let’s put together a simple PHP script that reads this spreadsheet and displays the number of worksheets, rows and columns in it:

<code>read('Book1.xls');

// print number of rows, columns and sheets
echo "Number of sheets: " . sizeof($excel->sheets) . "\n";
for ($x=0; $xsheets); $x++) {
  echo "Number of rows in sheet " . ($x+1) . ": " . $excel->sheets[$x]["numRows"] . "\n";
  echo "Number of columns in sheet " . ($x+1) . ": " . $excel->sheets[$x]["numCols"] . "\n";
}
?>
</code>

Here’s the output:

How did this happen? It’s actually not very difficult, because PHP-ExcelReader does most of the heavy lifting for you. The first step is to include the package file and instantiate a new Spreadsheet_Excel_Reader object. This object exposes a read() method, which accepts the name and path to the source Excel spreadsheet as input argument and then goes to work reading the spreadsheet data and converting it into PHP-readable data structures.

Every Excel spreadsheet file contains one or more worksheets; these worksheets are now represented in the Spreadsheet_Excel_Reader’s ‘sheets’ property, as array elements indexed from 0. As a result, worksheet 1 may be accessed using the notation $obj->sheets[0], worksheet 2 as $obj->sheets[1], and so on.

For each worksheet, PHP-ExcelReader also creates some special array keys with sheet-level information – for example, the ‘numRows’ key holds the number of rows in the worksheet, while the ‘numCols’ holds the number of columns. The script above merely uses this information to iterate over the array of worksheets and print the number of rows and columns in each.

Off The Grid

Now, while this is interesting, it’s not really all that useful – after all, what you’re really after is the data within the spreadsheet. PHP-ExcelReader has you covered there too: the cells of each worksheet are nested inside the corresponding worksheet array, and are accessible via the ‘cells’ array key. An individual cell is accessed using its row and column coordinates – for example, the cell at row 5 and column C would be accessed using the notation $obj->sheets[0]['cells'][5][3]. Row and column indexing starts from 1.

With this in mind, consider the next PHP script, which uses the PHP-ExcelReader to represent the example spreadsheet as an HTML table:

<code><html>
  <head>
  <style type="text/css">
  table {
     border-collapse: collapse;
  }   
  td {
     border: 1px solid black;
     padding: 0 0.5em;
  }   
  </style>
  </head>
  <body>
  <table>
  <?php
  // include class file
  include 'reader.php';
   
  // initialize reader object
  $excel = new Spreadsheet_Excel_Reader();
   
  // read spreadsheet data
  $excel->read('Book1.xls');   
   
  // iterate over spreadsheet cells and print as HTML table
  $x=1;
  while($x<=$excel->sheets[0]['numRows']) {
  echo "\t<tr>\n";
  $y=1;
  while($y<=$excel->sheets[0]['numCols']) {
  $cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
  echo "\t\t<td>$cell</td>\n";  
  $y++;
  }  
  echo "\t</tr>\n";
  $x++;
  }
  ?>   
  </table>
  </body>
</html>
</code>

It’s now a simple task to represent the data from a worksheet as an HTML table. All that’s needed are two loops, one iterating over the rows and the other iterating over the columns in each row, and a little bit of PHP code to access the value of the cell at their point of intersection. That’s precisely what the script above does, together with some CSS code to render the table neatly.

Here’s what the output looks like:

Interestingly, if your spreadsheet cells contain formulae, PHP-ExcelReader will return the final result of the formulae as the corresponding cell value (instead of the raw formula string). However, when it comes to cell formatting, PHP-ExcelReader returns the raw value of each cell, rather than the formatted cell value. To illustrate both these facts, let’s revise the spreadsheet so it contains both formulae and formatting:

And now, when you re-run the previous script, you should see something like this:


Turning The Tables

With the power of PHP-ExcelReader at your command, it’s also quite easy to transfer data from a spreadsheet into a database, so that you can run queries on it. Consider the following script, which illustrates the process by bringing together PHP-ExcelReader, PDO and prepared statements to read data from a spreadsheet and INSERT it into a MySQL table:

<code><?php
// include class file
include 'reader.php';

// initialize reader object
$excel = new Spreadsheet_Excel_Reader();

// read spreadsheet data
$excel->read('Book2.xls');

// attempt a connection
try {
  $pdo = new PDO('mysql:dbname=test;host=localhost', 'user', 'pass');
} catch (PDOException $e) {
  die("ERROR: Could not connect: " . $e->getMessage());
}

// iterate over spreadsheet rows and columns
// convert into INSERT query
$sql = "INSERT INTO data (country, sales) VALUES (?, ?)";
if ($stmt = $pdo->prepare($sql)) {
  $x=2;
  while($x<=$excel->sheets[0]['numRows']) {
  $stmt->bindParam(1, $excel->sheets[0]['cells'][$x][1]);
  $stmt->bindParam(2, $excel->sheets[0]['cells'][$x][2]);
  if (!$stmt->execute()) {
  echo "ERROR: Could not execute query: $sql. " . print_r($pdo->errorInfo());
  }  
  $x++;
  }
} else {
  echo "ERROR: Could not prepare query: $sql. " . print_r($pdo->errorInfo());
}

// close connection
unset($pdo);
?>
</code>

the script above will read the data in the spreadsheet and save each spreadsheet row as a record in the named MySQL table. It begins by reading the spreadsheet into a Spreadsheet_Excel_Reader object, and then opening a PDO connection to the MySQL database. It then prepares an INSERT statement with $pdo->prepare() and then iterates over the spreadsheet rows (skipping the first row, which is the table header row), binding the values in each row to the prepared statement before executing it. This process continues until all the rows in the spreadsheet are processed.

At the end of the process, you should see the data safely transferred to your MySQL table:

mysql> SELECT * FROM data;
+----------------+-------+
| country  | sales |
+----------------+-------+
| United States  |  7583 |
| United Kingdom |  4359 |
| France  | 45885 |
| Germany  |  3833 |
| Spain  |  8738 |
| Italy  |  5239 |
| Greece  | 38282 |
+----------------+-------+
7 rows in set (0.01 sec)

There’s one key thing to remember when performing this kind of spreadsheet-to-database conversion: your spreadsheet data must be correctly and consistently structured, with all the values lining up neatly, or else your table fields will end up with missing or incorrect values. So it’s a good idea to visually check the source spreadsheet for consistency once before reading it into an SQL database.

Back To Class

So that takes care of reading Excel spreadsheets. Now, how about writing them?

Well, with PEAR’s Spreadsheet_Excel_Writer package, this is actually a piece of cake. Consider the following script, which illustrates the process:

<code><?php
// spreadsheet data
$data = array(
  array('', 'Math', 'Literature', 'Science'),
  array('John', 24, 54, 38),
  array('Mark', 67, 22, 57),
  array('Tim', 69, 32, 58),
  array('Sarah', 81, 78, 68),
  array('Susan', 16, 44, 38),
);

// include package
include 'Spreadsheet/Excel/Writer.php';

// create empty file
$excel = new Spreadsheet_Excel_Writer('grades.xls');

// add worksheet
$sheet =& $excel->addWorksheet('Class I');

// add data to worksheet
$rowCount=0;
foreach ($data as $row) {
  foreach ($row as $key => $value) {
  $sheet->write($rowCount, $key, $value);   
  }  
  $rowCount++;
}

// save file to disk
if ($excel->close() === true) {
  echo 'Spreadsheet successfully saved!';  
} else {
  echo 'ERROR: Could not save spreadsheet.';
}
?>
</code>

There’s a fairly standard sequence of actions to follow when creating a new Excel spreadsheet. First, include the class file and initialize an object of the Spreadsheet_Excel_Writer class, passing the name and path of the output spreadsheet file to the object constructor. Once you’ve got your empty spreadsheet initialized, add a new, empty worksheet to it with the object’s addWorksheet() method; pass the worksheet name to the method as an additional argument.

At this point, your spreadsheet is ready for some data. Inserting data is a task for the write() method, which accepts three arguments: the row number, the column number and the value to be inserted. Row and column indexing starts from 0. So, for example, to write the value ‘Hello’ to the cell at row 4 and column F, you’d call $obj->write(3,5,’Hello’).

In the previous example, a loop iterates over the array of source data, using write() to add each element to the spreadsheet as a cell. Once you’ve finished writing data to the spreadsheet, the close() method wraps things up and saves the output file to disk. You can now read this spreadsheet in Microsoft Excel – open it up, and you’ll see something like this:

If, instead of writing the output file to disk, you’d prefer to have it sent directly to the user’s browser, Spreadsheet_Excel_Writer has you covered there too! To do this, call the Spreadsheet_Excel_Writer’s send() method with the output file name instead of passing it to the object constructor, as in the following revision of the previous example:

<code><?php
// spreadsheet data
$data = array(
  array('', 'Math', 'Literature', 'Science'),
  array('John', 24, 54, 38),
  array('Mark', 67, 22, 57),
  array('Tim', 69, 32, 58),
  array('Sarah', 81, 78, 68),
  array('Susan', 16, 44, 38),
);

// include package
include 'Spreadsheet/Excel/Writer.php';

// create empty file
$excel = new Spreadsheet_Excel_Writer();

// send client headers
$excel->send('grades.xls');

// add worksheet
$sheet =& $excel->addWorksheet('Class I');

// add data to worksheet
$rowCount=0;
foreach ($data as $row) {
  foreach ($row as $key => $value) {
  $sheet->write($rowCount, $key, $value);   
  }  
  $rowCount++;
}

// close and output file
if ($excel->close() !== true) {
  echo 'ERROR: Could not save spreadsheet.';
}
?>
</code>

The send() method sends the client browser headers that identify the data that follows as a spreadsheet. The browser should then offer the user a choice to view the spreadsheet directly in Microsoft Excel, or save the file to disk.

The Secret Formula

Why stop with just values? It’s easy to create cells containing formulae as well – simply call the writeFormula() method with the row and column coordinates of the target cell and the formula to be inserted into that cell. As per normal Excel operation, formulae must be preceded with an equality symbol (=). Consider the following revision of the previous example, which averages the grades of each student across the three subjects:

<code><?php
// spreadsheet data
$data = array(
  array('', 'Math', 'Literature', 'Science'),
  array('John', 24, 54, 38),
  array('Mark', 67, 22, 57),
  array('Tim', 69, 32, 58),
  array('Sarah', 81, 78, 68),
  array('Susan', 16, 44, 38),
);

// include package
include 'Spreadsheet/Excel/Writer.php';

// create empty file
$excel = new Spreadsheet_Excel_Writer('grades.xls');

// add worksheet
$sheet =& $excel->addWorksheet('Class I');

// add data to worksheet
$rowCount=0;
foreach ($data as $row) {
  for($x=0; $x<sizeof($row); $x++) {
  $sheet->write($rowCount, $x, $row[$x]);   
  }  
  // get cell coordinates
  $start = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, 1);
  $end = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, (sizeof($row)-1));
  // add AVERAGE() formula to terminating cell of each row
  // except the first (header) row
  if ($rowCount != 0) {
  $sheet->writeFormula($rowCount, sizeof($row), "=AVERAGE($start:$end)");   
  }
  $rowCount++;
}

// save file to disk
if ($excel->close() === true) {
  echo 'Spreadsheet successfully saved!';  
} else {
  echo 'ERROR: Could not save spreadsheet.';
}
?>
</code>

Here, Excel’s AVERAGE() formula is used to calculate the average grade of each student, by dynamically generating the formula string and placing it at the end of each row of grades. Notice the script’s use of the rowcolToCell() static method, which accepts a row and column number and returns an Excel-compliant alphanumeric grid identifier corresponding to that cell.

Here’s what the output looks like:

In addition to the writeFormula() method, the Spreadsheet_Excel_Writer also comes with writeNote() and writeUrl() methods, which come in handy to attach notes or hyperlinks to a cell. The following example illustrates both these in action:

<code><?php
// include package
include 'Spreadsheet/Excel/Writer.php';

// create empty file
$excel = new Spreadsheet_Excel_Writer('Book1.xls');

// add worksheet
$sheet =& $excel->addWorksheet('Untitled');

// add note
$sheet->write(0, 0, 647384.65);   
$sheet->writeNote(0, 0, 'This assumes g=16%');   

// add URLs as cell values
$sheet->writeUrl(1, 0, 'http://www.yahoo.com/', 'Yahoo!');   
$sheet->writeUrl(2, 0, 'http://www.google.com/', 'Google');   
$sheet->writeUrl(3, 0, 'http://www.php.net/', 'PHP.net');   

// save file to disk
if ($excel->close() === true) {
  echo 'Spreadsheet successfully saved!';  
} else {
  echo 'ERROR: Could not save spreadsheet.';
}
?>
</code>

Looking Pretty

Spreadsheet_Excel_Writer also offers a large number of methods to format your spreadsheet cells and make them look pretty. There are two steps to using formats: first, define a new format with the addFormat() method and then, apply the format to a cell by specifying it as an additional argument to the write() method.

The following example illustrates the process:

<code><?php
// spreadsheet data
$data = array(
  array('', 'Math', 'Literature', 'Science'),
  array('John', 24, 54, 38),
  array('Mark', 67, 22, 57),
  array('Tim', 69, 32, 58),
  array('Sarah', 81, 78, 68),
  array('Susan', 16, 44, 38),
);

// include package
include 'Spreadsheet/Excel/Writer.php';

// create empty file
$excel = new Spreadsheet_Excel_Writer('grades.xls');

// add worksheet
$sheet =& $excel->addWorksheet('Class I');

// create format for header row
// bold, red with black lower border
$firstRow =& $excel->addFormat();
$firstRow->setBold();
$firstRow->setColor('red');
$firstRow->setBottom(1);
$firstRow->setBottomColor('black');

// create format for header column
// blue with black right border
$firstCol =& $excel->addFormat();
$firstCol->setColor('blue');
$firstCol->setRight(1);
$firstCol->setRightColor('black');

// create format for top left corner
$firstRowCol =& $excel->addFormat();
$firstRowCol->setBottom(1);
$firstRowCol->setRight(1);
$firstRowCol->setBorderColor('black');

// create format for numeric cells
$num =& $excel->addFormat();
$num->setNumFormat('0');

// create format for averaged values
$avg =& $excel->addFormat();
$avg->setNumFormat('0.00');

// add data to worksheet
$rowCount=0;
foreach ($data as $row) {
  for($colCount=0; $colCount<sizeof($row); $colCount++) {
  if ($rowCount == 0 && $colCount == 0) {
  $format = 'firstRowCol';
  } else if ($rowCount == 0) {
  $format = 'firstRow';   
  } else if ($colCount == 0) {
  $format = 'firstCol';   
  } else {
  $format = 'num';   
  }
  $sheet->write($rowCount, $colCount, $row[$colCount], $format);   
  }  
  // get cell coordinates
  $start = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, 1);
  $end = Spreadsheet_Excel_Writer::rowcolToCell($rowCount, (sizeof($row)-1));
  // add average() formula to terminating cell of each row
  // except the first (header) row
  if ($rowCount != 0) {
  $sheet->writeFormula($rowCount, sizeof($row), "=AVERAGE($start:$end)", $avg);   
  }
  $rowCount++;
}

// save file to disk
if ($excel->close() === true) {
  echo 'Spreadsheet successfully saved!';  
} else {
  echo 'ERROR: Could not save spreadsheet.';
}
?>
</code>

This example defines five different formats: $firstRow, which has red text and a bottom border; $firstCol, which has blue text and a right border; $firstRowCol, which has both a right and bottom border; $num, which formats numbers as integer values; and $avg, which formats numbers as decimal values. Each of these formats is created by calling the Spreadsheet_Excel_Writer’s addFormat() method to create a new Format object, and then calling methods of this Format object to set various formatting attributes such as color, size and weight.

So, for example, the Format object’s setColor() method sets the text color for the format, while the setBorderColor() method sets the border color. Similarly, there’s a setSize() method to set text size, a setAlign() method to set the text alignment, a setTextWrap() method to define text wrapping, and a setNumFormat() method to define how numeric values are formatted. Some of these methods are used in the previous example.

Once the various Format objects are defined, all that’s left is to apply them to the relevant cells. This is accomplished by using the relevant object when invoking the write() method. Thus, in the script above, all subject names (the first row) are formatted using $firstRow, all student names (the first column) are formatted using $firstCol, and all grades (the table content) are formatted using $num.

Here’s what the output looks like:

It should be noted that the Format object exposes many more formatting methods than the few shown in the previous example. To obtain a complete list, look in the documentation for the Spreadsheet_Excel_Writer package, at http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer.php; there’s also an excellent tutorial on formatting there that you’ll undoubtedly find useful!

Around The World In 80 Rows

Earlier on in this article, I showed you how to read data from an Excel spreadsheet and transfer it to an SQL database. Now it’s time to do the reverse: transform an SQL result set into an Excel spreadsheet. In the following example, I’ll use the MySQL example ‘world’ database, and turn all the records from the ‘country’ table into rows of an Excel spreadsheet. Take a look at the code:

<code><?php
// include class file
include 'Spreadsheet/Excel/Writer.php';

// initialize reader object
$excel = new Spreadsheet_Excel_Writer();

// send client headers
$excel->send('country.xls');

// add worksheet
$sheet =& $excel->addWorksheet('SQL_Output');

// attempt a connection
try {
  $pdo = new PDO('mysql:dbname=world;host=localhost', 'user', 'pass');
} catch (PDOException $e) {
  die("ERROR: Could not connect: " . $e->getMessage());
}

// read data from database
// convert into spreadsheet
$rowCount = 0;
$sql = "SELECT * FROM country";
if ($result = $pdo->query($sql)) {
  // get header row
  for ($x=0; $x<$result->columnCount(); $x++) {
  $meta = $result->getColumnMeta($x);   
  $sheet->write($rowCount, $x, $meta['name']);   
  }
  // get data rows
  $rowCount++;
  while($row = $result->fetch()) {
  foreach ($row as $key => $value) {
  $sheet->write($rowCount, $key, $value);   
  }
  $rowCount++;
  }
} else {
  echo "ERROR: Could not execute $sql. " . print_r($pdo->errorInfo());
}

// close connection
unset($pdo);

// save file to disk
if ($excel->close() === true) {
  echo 'Spreadsheet successfully saved!';  
} else {
  echo 'ERROR: Could not save spreadsheet.';
}
?>
</code>

If you’ve been following along, there shouldn’t be any major surprises here. The script begins by initializing a new spreadsheet and attaching a blank worksheet to it. It then opens a PDO connection to the ‘world’ database and executes a SELECT query to return all the records from the ‘country’ table.

Having obtained a result set, the script then begins building the spreadsheet. First, it creates the header row by retrieving a list of all the fields in the result set, obtaining each field’s name, and writing this to the spreadsheet as the first row. Then, it iterates over the result set, writing the contents of each record to the spreadsheet as a new row. This continues until the entire result set is processed, at which time the spreadsheet file is closed and sent to the user’s browser.

If you look inside the resulting output file, here’s a snippet of what you should see:

And that’s about it for this article. Over the last few pages, I showed you how to read spreadsheet data and insert it into an SQL database, dynamically create a new spreadsheet, attach formulae, formatting, notes and hyperlinks to spreadsheet cells, and re-render SQL records in spreadsheet format. You probably wouldn’t have thought all of this possible using just PHP code…but it is, and it isn’t even very difficult.