How to Create a Complete Excel Spreadsheet From MySQL

If you are thinking about rendering your MySQL data to an Excel spreadsheet, then a CSV stands for Comma Separated Value would be sufficient towards this endeavor. An excel spreadsheet incorporates formulas, formatting and even graphics, on the other hand the CSV files are considered as data only.

However, in this coming section of this tutorial post you will learn to employ open source PHP components for generating a “real” Excel spreadsheet through MySQL SELECT statements. So, let’s go ahead and take a look over this-

Overview

PHP Excel is referred as a set of PHP classes which enables you to read and write distinct spreadsheet formats and also employ the spreadsheet themselves. However, prior to initiate, you will required PHP Excel copy.

The Fundamental Relationship Between Data Tables and Spreadsheets

A SQL SELECT statement delivers a set of results which incorporates labeled columns and rows with the data. However, in PHP/MySQL, each rows accompanied by result set most often revealed by an associative array which is also called as ‘column name‘.An Excel spreadsheet is defined as a series of worksheets which is accompanied by alphabetically labeled columns and numbered rows.

Therefore, the common technique implemented will be to map one SQL statement to one Excel worksheets/spreadsheet page, particularly by matching the name column with associative array delivered by POD retrieve command to alphabetical spreadsheet’s column labels.

Step 1: Comprehending How to Label Alphabetical Columns Accurately

The primary key behind this technique is the fundamental algorithm of column labeling, that will enable you to label alphabetical columns accurately regarding large quantity of columns arbitrarily. As you already know that in an Excel spreadsheet, rows are represented with numbers as well as columns with letters. However, if your query is seems to be comprise less than 26 columns, you can simply stop at “Column Z”. Regardless of this various queries incorporates substantially more columns.

Scheme of Column Labeling in Excel

The Column Labeling Algorithm

Here, let take a look over this PHP fragment that will say 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
}

The point should be noted that in account of implementing this alogrithm you can’t cross “ZZ”. However, this algorithm is has implemented in a marked distinct way in the accompanying source file in MySqlExcelBuilder::mapColumns member function.

Step 2: Format and Test Your SQL Statement

A Simple Schema

The below provided schema displays a simplified form of customer/order relationship. The SQL CREATE statement concerned to below given schema are added in the xls_sample.sql file in form of zip file which is incorporates this tutorial.

A “Quick & Dirty” SELECT Statement

This is considered as the most simple practice in context of congregating data exponentially form these tables in respect of SQL statements and will be appear something like this:

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

However, the most significant features of this “quick & dirty” statement is considered as it’s rapid and affable to program. Though the results appears generally don’t have a good look.

PHPMyAdmin is a quite obvious tool for MySQL database administration which flaunts with various hosting plans. However, you are free to employ SQL composition tools in context of prototype and analyze your excel spreadsheet pages which is constituted by PHPMyAdmin.

The snapshot sits below resembles the result for the above query:

At this place, I will show you a close up view of some columns names which seems to valuable for programmers but not effective to business user.

Here, you may notice that the employed column names are not implemented to “real word” rules as well as instead of space ‘underscores’ has been used.

With SELECT Statement Get More Attractive Results

Now, we are required to format the SELECT statement in order to convey a business report view to a spreadsheet. So, by employing the PHPMyAdmin tool, we are going to edit SQL statement that eventually results in column names to appear in real words as well as only those columns will be displayed the users wish to see. Take a look over this reformatted SQL statement which appears 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`

and results as:

Therefore, the above prototype demonstrates an identical look of an Excel spreadsheet page.

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

The class MySqlExcelBuilder abbreviates the functionality necessary to include SQL statements to a spreadsheet page by means of employing PHPExcel and PDO. The full class is available in constituted zip file.

The MySqlExcelBuilder Class

This class provide a lot of SQL result sets to be assigned over named pages in an entire Excel spreadsheet. Therefore, the below sitting snipped code will shows the significant 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 referred as PHP Data Object employed to query database.
The $phpExcel data member is the PHPExcel object which is useful to build as well manipulate an Excel spreadsheet.
The $sql_pagesarray comprises the SQL statement and the page naming/formating information.
The constructor which is hidden used to initializes the PHPExcel and PDO data members.

Preparing Each Page

The below provided image of a spreadsheet is a prototype built in Excel in order to show the desired look of a spreadsheet after being rendered.

The add_page member function is employed to include SQL Statements to the specific 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.
}

Hence, the information we wish to employ, in order to include SQL on pages is retained by the sql_pages.

Example of Member Function Usage

The following snippet of code is an excellent example of employing 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');

However, the below listed illustration displays how add_page member function should define to the spreadsheet.

Step 4: Building the Spreadsheet

Comprehending PHPExcel

So, while you comprehend accurately about manipulating a spreadsheet with your keyboard and mouse, you will become adept at accessing PHPExcel. PHPExcel is created on the principle of manipulating the fundamental excel spreadsheet model employing those command which are identical to command you used to implement in Excel. However, The PHPExcel Developer Documentation comprises the details.

The getExcel() Member Function

The getExcel() member functions employ PHPExcel towards creating each of the worksheets from SQL statements which have discussed above in Step 3. After accomplishment of worksheets, it used to return the PHPExcel object to caller. So, have a look over following four primary sections of getExcel member function.

A. Iterate Through the Pages

The primary loop of this member function persist through those pages which were added earlier with add_page. However, in each iteration, it used to generate a concerning page in PHPExcel object and later add the data. The PHPExcel’s createSheet member function is obvious towards building a new worksheets for each those pages has been added earlier.

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 below illustration says about how the code corresponds to an Excel spreadsheet. The column_map will be discussed later in the coming next section of this post.

B. “Start of Page” Logic

Each page comprises a specific formatting at the beginning. When a row is fetched from the database for a specific page for the first time, it accomplish the necessary tasks to perform the formatting of start-of-page. However, it implore “mapColumns” member function which has been discussed above in Step 1. Likewise Excel, PHPExcel uses a Letternumber pair to define a specific cell. In MySqlExcelBuilder it is considered as a CellKey which is created by concatenating a row number and a column label.

$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
}

Here I am listing some additional things with reference to the above snippet code that you must know about:

• setCellValue: It assign an actual value in the field. Always note that a cell is referred as a member of a worksheet and represented by the cellKey variable.
• GetStyle: It is obvious to delivers a reference to the style attribute to a specific cell, therefore it is affable to manipulate.
• GetColumnDimension: It is considered as a method of worksheet object. col variable is associated with a column dimension (width).

C. Fill in the Data

Almost you done column mapping and preparation, however, the method of placing every data item in a cell is now relatively flimsy. Now you are supposed to look up an Excel spreadsheet column regarding a specific data column, create a cell key and next assign the value to this 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 end part of getExcel () says how to add formulas to a spreadsheet of a PHPExcel. In this case, it is referred as column total. Likewise, in an Excel spreadsheet you put formulas into a specific cell, PHPExcel also do the same. The value prefixed by equal sign (=) in a cell is a formula. Here, see below to designate the SUM of a range of data cells.

And the code is:

$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: Employ the Finishing Touches

After retrieving the data from your MySQL database to an Excel spreadsheet in account of getExcel, now you are supposed to convey some finishing touches on your spreadsheet. Here, in this illustration we are going to include a title to each and every 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 employ an object factory in context of building a writer which used to write the spreadsheet you have created in the appropriate format. Here, I have employed “Excel15” because it is capable to read even very earlier spreadsheet programs.

/ 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 below assigned image states about a partial snapshot of a screen where final product has been created in account of included sample code. It comprises two spreadsheet pages, congregated from a sample database and formatted by employing custom titles on every page:

However, when your data from the database has been retrieved in PHPExcel object, you can implement other arbitrarily features of PHPExcel class to accomplish additional formatting a well as assign more formulas and save this in distinct file formats as well as anything whatever PHPExcel allows.