Generating Excel Spreadsheet using PHP from MySQL Database

I have read many of the current posts on this subject, and though I have been able to do this task using ColdFusion in the past, I am having problems getting any suggested code to work using PHP.

Could someone please help me?

I have a populated database and just need to pull the data into a spreadsheet, not a CSV file.

Then, after I have figured that part out, I need to learn how to format the Excel Spreadsheet to have row colors, and some added columns for totaling the records at the bottom.

Any help is greatly appreciated.
Elizabeth2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
The part about "not a CSV file" puts a big stumbling block in your path.  The file extension CSV is almost universally associated with Excel spreadsheets, and it's the easiest way to get data from PHP into Excel-usable format.  I've looked at some of the libraries and I've never found one that could do a good job of styling cells, but maybe something built on the Micorsoft OpenXML platform could be made to work.

Some resources here:
https://www.google.com/#q=PHP+excel+library

If you decide you can use a CSV file, I have a script that will work against one table.  No reason it couldn't work against many tables, so long as the Excel output is only one spreadsheet.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Terry WoodsIT GuruCommented:
I've had some success using PHPExcel to open an existing .xlsx file for use as a template then adding data to it and saving as a new file.

The below code (a PHPExcel example file, modified) was done as a proof of concept a year or so ago, and works; I don't know how different it is from the original PHPExcel example that it was based on. It opens a file "davids_excel_template.xlsx", adds some data, and saves the file as "invoice.xlsx". I've attached the template and resulting file so you can see what's happening.

I believe it's ok to post this under the terms of the license.

<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2011 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * 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.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
 *
 * @category   PHPExcel
 * @package    PHPExcel
 * @copyright  Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 * @version    1.7.6, 2011-02-27
 */

/** Error reporting */
error_reporting(E_ALL);

date_default_timezone_set('Europe/London');

/** PHPExcel_IOFactory */
require_once 'PHPExcel/Classes/PHPExcel/IOFactory.php';

print "<pre>\n";
echo date('H:i:s') . " Load from Excel2007 template\n";
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("davids_excel_template.xlsx");

echo date('H:i:s') . " Add new data to the template\n";
$data = array(array('title'		=> 'Excel for dummies',
					'price'		=> 17.99,
					'quantity'	=> 2
				   ),
			  array('title'		=> 'PHP for dummies',
					'price'		=> 15.99,
					'quantity'	=> 1
				   ),
			  array('title'		=> 'Inside OOP',
					'price'		=> 12.95,
					'quantity'	=> 1
				   )
			 );

$objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel(time()));

$baseRow = 5;
foreach($data as $r => $dataRow) {
	$row = $baseRow + $r;
	$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);

	$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $r+1);
	$objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $dataRow['title']);
	$objPHPExcel->getActiveSheet()->setCellValue('C'.$row, $dataRow['price']);
	$objPHPExcel->getActiveSheet()->setCellValue('D'.$row, $dataRow['quantity']);
	$objPHPExcel->getActiveSheet()->setCellValue('E'.$row, '=C'.$row.'*D'.$row);
}
$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);

echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('invoice.xlsx');

// Echo memory peak usage
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n";

// Echo done
echo date('H:i:s') . " Done writing file.\r\n";

Open in new window

invoice.xlsx
davids-excel-template.xlsx
1
Elizabeth2Author Commented:
Wow! Hi Terry... This is a little over my head, but I did try at least. I'm just getting errors, and of course, I would need to alter this to fit my db table, which is actually quite simple. The only fields are FirstName, LastName, Email, Company, and Phone. Very simple, but transferring the call for my files to your template would take me some time to figure out.

Thank you so much for sharing this with me, but my boss is asking me to pull a lot together in a very short time, and so I was hoping for something simple, like you click a button and it queries the db and creates a somewhat formatted Excel spreadsheet, at least with each field in a separate column. Years ago, I was able to do that pretty easily in ColdFusion, so maybe my expectations are off a bit.

Any help or suggestions are greatly appreciated.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Elizabeth2Author Commented:
Hi Ray,

It sounds like your script would at least give me a good start. Would you mind sharing that with me and let me give it a try. I read somewhere when researching this subject that the reason for not using a CSV file was something about leading zeros, but I doubt that would matter in my case. All the data is just in the form of strings anyway.

Thank you so much!
0
Terry WoodsIT GuruCommented:
In case you decide to pursue it further, to run the code I posted, you'll need to download PHPExcel and place the PHPExcel folder in the same folder as the script. It's loaded in the line:
require_once 'PHPExcel/Classes/PHPExcel/IOFactory.php';

Open in new window


The library is likely changing over time, and there's no guarantee it's backwards compatible with the version I used, but it may be easier that you think.
0
Elizabeth2Author Commented:
Hi Terry,

Yes, thank you. I did do that and yet I'm getting these errors:

http://www.csmediagroup.com/email1/excel.php.

It's not finding the file, but I've used a relative path. I've also tried just putting the Excel file in that last folder, that didn't work either. I must be missing something! ;-)

Any suggestions from the errors?

I've got to run right now, but will be back tomorrow, if you have time to help me.

Thank you so much!!
0
Dave BaldwinFixer of ProblemsCommented:
I don't have an example for you but I'm pretty sure that Excel thru ODBC can get the info directly from MySQL.  See if anything on this page helps you: http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-examples-tools-with-wordexcel.html
0
Terry WoodsIT GuruCommented:
The error indicates you may have changed the template file path to start with a / character, so that it is an absolute path rather than a relative one. It may be set to this?
$objPHPExcel = $objReader->load("/elizabeths_code/davids-excel-template.xlsx");

Open in new window

You probably want to set a path relative to where the php file is, without a / character at the start.
0
Elizabeth2Author Commented:
I thank you all very much for trying to help me with this task. As it turned out, I found that from within PHP MyAdmin, a server tool provided in cPanel from my hosting provider, I can choose "export" and select as a CSV file, which works perfectly without using PHP at all. Then I just open that file using Excel, and it basically adds the columns as I needed. I can use this for now, temporarily...

However, this doesn't solve my original task of having a button that is clicked and it queries the database, and either creates or exports to an Excel Spreadsheet that you can then save to your computer. I still need help to figure that out.

So far, I have not been able to do that based on these responses, but I'm hopeful that someone can still help me, so if you could, please advise.

Thank you so much!
0
Dave BaldwinFixer of ProblemsCommented:
PHP MyAdmin does use PHP, just that you don't have to do the programming.  Have you taken a look at my suggestion of using ODBC to connect Excel directly to the MySQL database?  You wouldn't have to do any programming that way either.
0
Elizabeth2Author Commented:
I have not had any success with any of these kind suggestions. It would be great if there were just a script to attach to a button. Are there any other suggestions? I can export a CSV file directly from PHP MyAdmin, I've learned on my own, but that's not what I need. I need the client to click a button and have a download of an Excel spreadsheet happen. So far, I don't have a solution for that yet.
0
Elizabeth2Author Commented:
Although none of these answers worked for me, I am grateful for the help, so I'm awarding the points for that reason. I did learn something by following their suggestions, so it was somewhat helpful.
0
SvenSrCommented:
Terry Woods gives a fine example on how to use the PHPExcel.

He errors when in the original script he calls (at line 39):
$objPHPExcel = $objReader->load("davids_excel_template.xlsx");

Open in new window

while he added davids-excel-template.xlsx

In his third contribution, he writes:
$objPHPExcel = $objReader->load("/elizabeths_code/davids-excel-template.xlsx");

Open in new window


Now nobody else but Elizabeth might us that last line, but here is where Terry gives the right name of the file.
It took me quite a while before I noticed that the file name in the script used underscores (_) while the actual file name has hyphens (-).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.