How to Generate a Complete Excel Spreadsheet From MySQL

How to Generate a Complete Excel Spreadsheet From MySQL:

A CSV (Comma Separated Value) file is usually sufficient for exporting MySQL data as an Excel Spreadsheet. These CSV files are data only though. A real Excel spreadsheet has formatting, formulas and perhaps even graphics — the difference between a simple method of data transfer and a professional report.

This tutorial shows how to use open source PHP components to create "real" Excel spreadsheets from MySQL SELECT statements. Interested? Let’s get started!

Overview

PHPExcel is a set of PHP classes that allow you to to read and write different spreadsheet file formats as well as manipulate the spreadsheets themselves. Before you begin, you’ll need a copy of PHPExcel. To get the latest copy of the PHPExcel component, go to http://www.phpexcel.net and download the latest stable release.

An Excel spreadsheet is a series of pages (called Worksheets) which have numbered rows and alphabetically labeled columns.

The Fundamental Relationship Between Data Tables and Spreadsheets

A SQL SELECT statement returns a result set that is a collection of labeled columns with the data contained in rows. In PHP/MySQL, each row of a result set can be (and often is) represented by an associative array, where the key to the associative array is the column name.

An Excel spreadsheet is a series of pages (called Worksheets) which have numbered rows and alphabetically labeled columns.

The general technique will be to map one SQL statement to one spreadsheet page (worksheet), specifically by matching column names in the associative array returned by the PDO fetch command to alphabetical column labels on the spreadsheet.

Step 1: Understand How to Properly Label Alphabetical Columns

The key to this technique is the underlying column labeling algorithm, which will allow you to properly label alphabetical columns for an arbitrarily large number of columns. As previously noted, in an Excel spreadsheet, columns are identified with letters and rows are identified with numbers. If your query will contain less than 26 columns, you could simply stop at "Column Z". However, many queries have substantially more columns than that.

Excel Column Labelling Scheme

When the column labels get to Z a second letter is added: "A… Z,AA, AB … AZ". After "AZ" comes "BA" as shown below.

The Column Labeling Algorithm

The following PHP snippet shows how to label column headers from "A" to "ZZ".

$keys = array_keys($row); // Get the Column Names
$min = ord("A"); // ord returns the ASCII value of the first character of string.
$max = $min + count($keys);
$firstChar = ""; // Initialize the First Character
$abc = $min;   // Initialize our alphabetical counter
for($j = $min; $j <= $max; ++$j)
{
 $col = $firstChar.chr($abc);   // This is the Column Label.
 $last_char = substr($col, -1);
 if ($last_char> "Z") // At the end of the alphabet. Time to Increment the first column letter.
 {
  $abc = $min; // Start Over
  if ($firstChar == "") // Deal with the first time.
   $firstChar = "A";
  else
  {
   $fchrOrd = ord($firstChar);// Get the value of the first character
   $fchrOrd++; // Move to the next one.
   $firstChar = chr($fchrOrd); // Reset the first character.
  }
  $col = $firstChar.chr($abc); // This is the column identifier
 }
 /*
  Use the $col here.
  */

 $abc++; // Move on to the next letter
}

This algorithm will not go beyond ZZ. The algorithm is implemented (in a slightly different form) in the accompanying source file in the MySqlExcelBuilder::mapColumns member function.

---

Step 2: Format and Test Your SQL Statement

A Simple Schema

The schema below shows a simplified customer/order relationship. The SQL CREATE statements for the schema below are included in the file xls_sample.sql in the zip file that accompanies this tutorial.

A "Quick and Dirty" SELECT Statement

It’s common practice to simply grab the data quickly from these tables in a SQL statement that looks something like this:

SELECT * FROM `order`,`customer`,`order_item`
        WHERE `customer_id` = `customer`.`id`
        AND item_id = `order_item`.`id`

The primary advantage of this "quick and dirty" statement is that it’s fast and easy to program. The results usually don’t look very good.

PHPMyAdmin is a tool for MySQL database administration that comes standard with many hosting plans. To prototype and test your spreadsheet pages you can use the SQL composition tools that come with PHPMyAdmin.

The following screenshot shows the result of the query above:

And a close up of some of the columns shows that the column names are meaningful to programmers, but unattractive to business users.

The column names are not capitalized according to "real world" rules, there are underscores instead of spaces, etc.

Get More Attractive Results From a SELECT Statement

We want to format the SELECT statement to look like business a report on the spreadsheet. So, using the PHPMyAdmin tool, edit the SQL statement so that the column names are real words and only the columns the business user wants to see are displayed. The reformatted SQL Statement looks like:

SELECT `name` AS `Customer Name`,
       `email_address` AS `Email Address`,
       CONCAT( right(`phone_number`,3) , '-' , mid(`phone_number`,4,3) , '-', right(`phone_number`,4)) AS `Phone Number`,
       `item_sku` AS `Part Number`,
       `item_name` AS `Item Name`,
        `price` AS `Price`,
       `order_date` as `Order Date`
FROM `order`,`customer`,`order_item`
WHERE `customer_id` = `customer`.`id`
        AND item_id = `order_item`.`id`

Resulting in :

The prototype above demonstrates very much what the spreadsheet page will look like.

---

Step 3: Displaying A MySQL Result Set on A Spreadsheet Page

The Class MySqlExcelBuilder encapsulates the functionality necessary to add SQL statements to an Excel spreadsheet page using PDO and PHPExcel. The full class is in the accompanying zip file.

The MySqlExcelBuilder Class

This class enables an arbitrary number of SQL result sets to be placed on named pages within an Excel spreadsheet. The following code snippet shows the important data members.

<?
class MySqlExcelBuilder
{
       protected $pdo; // PHP Data Object
       public $phpExcel; // PHP Excel
       protected $sql_pages = array(); //Sheet Name, Sql Statement, Options

• The $pdo data member is the PHP Data Object used to query the database.


• The $phpExcel data member is the PHPExcel object used to build and manipulate the spreadsheet.


• The $sql_pagesarray holds the SQL Statement and the page formating/naming information.


• The constructor (not shown) initializes the PDO and PHPExcel data members.

Preparing Each Page

The spreadsheet image below is a prototype made in Excel to show what we might want the spreadsheet to look like when it is rendered.

The add_page member function is used to add SQL Statements to the named pages:

public function add_page($wsName,$sql,$total_colums=null,$start_col="A",$start_row="1")
{
  // $wsName, is the Work Sheet Name that will be shown on the tab at the bottom of the spreadhseet
 $this->sql_pages[$wsName]['Sql'] = $sql; // This is the statement to be executed
 $this->sql_pages[$wsName]['Col'] = $start_col; // This is the column to start putting data into.
                                               // Note that it must be between "A" and "Z", staring in Column "AA" and after is not supported.
 $this->sql_pages[$wsName]['Row'] = $start_row; // This the row number to start putting data into
 $this->sql_pages[$wsName]['Totals'] = $total_colums; // This is a comma delimted list of Column Names (NOT Column Labels) that will be totaled.
                                                     //If null it will be ignored.

}

The sql_pages data member holds the information we want to use to put sql on pages.

Member Function Usage Example

This snippet is an example of how to use the add_page member function:

$xls_sql = new MySqlExcelBuilder('database','username','password');
$sql_statement = <<<END_OF_SQL

SELECT `name` AS `Customer Name`,
       `email_address` AS `Email Address`,
       CONCAT( right(`phone_number`,3) , '-' , mid(`phone_number`,4,3) , '-', right(`phone_number`,4)) AS `Phone Number`,
       `item_sku` AS `Part Number`,
       `item_name` AS `Item Name`,
        `price` AS `Price`,
       `order_date` as `Order Date`
FROM `order`,`customer`,`order_item`
WHERE `customer_id` = `customer`.`id`
        AND item_id = `order_item`.`id`
        AND `item_sku` = 'GMG1'

END_OF_SQL;

$xls_sql->add_page('Gold Mugs',$sql_statement,'Price');

The illustration below shows how the add_page member function should map to the spreadsheet.

---

Step 4: Building the Spreadsheet

Understanding PHPExcel

If you understand how to manipulate an Excel spreadhseet with your mouse and keyboard, you can become quite adept at using PHPExcel. PHPExcel is built on the principle of manipulating the underlying spreadhseet model using commands that are similar to the commands you would give to Excel itself. The PHPExcel Developer Documentation has details.

The getExcel() Member Function

The member function getExcel()uses PHPExcel to build each of the worksheets from the SQL statements you defined in Step 3. When the worksheets have been built, it returns the PHPExcel object to the caller. There are four major sections of the getExcel member function described below.

A. Iterate Through the Pages

The main loop of this member function iterates through the pages previously added with add_page. In each iteration, it creates the corresponding page in the phpExcel object and then adds the data. The createSheet member function of PHPExcel is used to create a new worksheet for each page previously added.

       public function getExcel()
       {
           $i = 0;
           foreach($this->sql_pages as $wsName=>$page)
           {
               $start_of_page = true;
               $sql = $page['Sql'];
               $start_col = $page['Col'];
               $start_row = $page['Row'];
               $this->phpExcel->createSheet();
               $sheet = $this->phpExcel->setActiveSheetIndex($i);

               if ($sh = $this->pdo->query($sql))
               {

The illustration below shows how the code corresponds to the spreadsheet. The column_map is discussed in the next section.

B. “Start of Page” Logic

Each page has special formatting at the start. The first time a row is retrieved from the database for a particular page, it performs the tasks necessary to perform the start-of-page formating. It invokes the “mapColumns” member function that was discussed in Step 1. PHPExcel, like Excel uses the a LetterNumber pair to identify a particular cell. In MySqlExcelBuilder it’s referred to as a cellKey. A cell key is built by concatenating a column label and a row number.

 $rowNum = $start_row;
 while($row = $sh->fetch(PDO::FETCH_ASSOC))
 {
  $keys = array_keys($row); // Get the Column Names
  if ($start_of_page) // Initialize the Page
  {
   $this->mapColumns($wsName,$keys,$start_col);
   foreach($keys as $key)
   {
      $col = $this->column_map[$wsName]['xls'][$key];
      $cellKey = $col.$rowNum;
    $sheet->setCellValue($cellKey,$key);
    // The next two lines are for formatting your header
    $style = $sheet->getStyle($cellKey);
    $style->getFont()->setBold(true);
    $sheet->getColumnDimension($col)->setAutoSize(true);
   }
   $rowNum++; // The next row is for data
   $start_of_page = false; // Done with Intialization
  }

Some additional things to note in the above code snippet:

* setCellValue – Puts the actual value in the field. Note that a cell is a member of a worksheet. The specific cell is identified by the cellKey variable.


* getStyle – This returns a reference to the style attribute of a particular cell, so it can be manipulated.

* getColumnDimension is a method of the worksheet object. A column dimension (width) is associated with the col variable.

C. Fill in the Data

Thanks to the preparation and column mapping, the process of actually putting each data item in the cell is now relatively trivial. We look up the spreadsheet column for a particular data column, build a cell key and then put the value in a cell.

foreach($keys as $key) // Put the value of the data into each cell
{
  $col = $this->column_map[$wsName]['xls'][$key]; // Get the appropriate column
  $cellKey = $col.$rowNum; // Build the column key
  $val = $row[$key]; // Get the data value
  $sheet->setCellValue($cellKey,$val); // Put it in the cell.
}
$rowNum++;

D. Add in the Formulas

The last part of getExcel() shows how to add forumulas to a PHPExcel spreadhseet. In this case, it’s a column total. PHPExcel puts formulas into cells exactly as you would with an Excel Spreadsheet. The value of a cell starts with an equal sign ( = ) and contains a forumla. In this case, the SUM of a range of data cells. See below:

And now the code:

 $col = $this->column_map[$wsName]['xls'][$key];
 // Add the Total Label
 $cellLabelKey = $col.$rowNum;
 $total_label = "Total $key";
 $sheet->setCellValue($cellLabelKey,$total_label);
 $style = $sheet->getStyle($cellLabelKey);
 $style->getFont()->setBold(true);

 // Add the actual totals
 $total_row = $rowNum+1;
 $cellKey = $col.$total_row;
 $startTotal = $col.$start_row;
 $endTotal = $col.$this->sql_pages[$wsName]['lastDataRow'];
 $total_forumla = "=SUM($startTotal:$endTotal)";
 $sheet->setCellValue($cellKey,$total_forumla);
 $style = $sheet->getStyle($cellKey);
 $style->getFont()->setBold(true);

---

Step 5: Put on the Finishing Touches

After you’ve gotten an Excel spreadsheet that’s been filled with data from your MySQL database with getExcel it’s time to put the finishing touches on the spreadsheet. In this example, we add a title to each worksheet.

// Get the spreadsheet after the SQL statements are built...
$phpExcel = $mysql_xls->getExcel(); // This needs to come after all the pages have been added.

$phpExcel->setActiveSheetIndex(0); // Set the sheet to the first page.
// Do some addtional formatting using PHPExcel
$sheet = $phpExcel->getActiveSheet();
$date = date('Y-m-d');
$cellKey = "A1";
$sheet->setCellValue($cellKey,"Gold Mugs Sold as Of $date");
$style = $sheet->getStyle($cellKey);
$style->getFont()->setBold(true);

$phpExcel->setActiveSheetIndex(1); // Set the sheet to the second page.
$sheet = $phpExcel->getActiveSheet();
$sheet->setCellValue($cellKey,"Tea Sold as Of $date");
$style = $sheet->getStyle($cellKey);
$style->getFont()->setBold(true);

$phpExcel->setActiveSheetIndex(0); // Set the sheet back to the first page, so the first page is what the user sees.

---

Step 6: Save The File

PHPExcel uses an object factory to create a writer that will write the spreadsheet you’ve built into the appropriate format. In this case I used "Excel5" because even very old spreadsheet programs can read it, so my report is available to the biggest demographic possible.

// Write the spreadsheet file...
$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5'); // 'Excel5' is the oldest format and can be read by old programs.
$fname = "TestFile.xls";
$objWriter->save($fname);

// Make it available for download.
echo "<a href=\"$fname\">Download $fname</a>";

---

Final Product

The image below shows a partial screen shot of the final product built by the included sample code. It’s a two page spreadsheet, populated from the sample database and formatted with custom titles on each page:

---

Conclusion

Once the data from the database is in the PHPExcel object, you can use the other features of the PHPExcel class to perform addtional formatting, add more formulas, save it to different file formats and anything else that PHPExcel allows you to do.

The MySqlExcelBuilder class could, for example, be extended to use the features of PHPExcel to populate an existing spreadsheet template with data from your MySQL database. Since PDO is the underlying database interface, the DSN in the constructor of MySqlExcelBuilder can be altered for other databases quite easily.

If you have any questions or have run into any troubles, please let me know in the comments section below. Thank you so much for reading!