I need to export data from php (data retrieved from mysql database) to excel. I'm using Zend Framework. I need to do some changes to my data before exporting to excel. Actually, what I really need is to generate a monthly cash book.
I have read a lot of documents, but ended up in a mess. I really can't understand how I should begin. Do I really need PEAR? Do I need to download any class library?Isn't there a simple way to do this?
Thanks in advance
I would suggest you to use great PHPExcel library. It is really powerful, supports variety of formats, can do visual formatting and is easy to use.
You can find more about it at their webpage:
http://phpexcel.codeplex.com/. (PHPExcel has already moved at https://github.com/PHPOffice/PHPExcel)
PHPExcel has now been superceded by PhpSpreadsheet GitHub
.
Example of usage:
$objPHPExcel = new PHPExcel();
/** You can set many properties */
$objPHPExcel->getProperties()->setCreator("My company")
->setLastModifiedBy("John Doe")
->setTitle("Annual report")
->setSubject("Sales")
->setDescription("Annual sales report by John Doe")
->setCategory("Finance");
/** Choose one of sheets */
$activeSheet = $objPHPExcel->setActiveSheetIndex(0);
/** Simply set value of cell */
$activeSheet->setCellValue("A1", 'plural');
You can do a lot more of course, reading excel files, setting visual styles, creating plots, expressions and lot more.
I wrote a 94 line inventory variance report wherein I grab data from MySQL (6k+ records per table), create a multidimensional array from the MySQL data pulled from multiple tables, and then dump everything into a single string and generate an .xls thusly:
<?php
////////////////////////////////// BEGIN SETUP
$filename ="document_name.xls";
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
////////////////////////////////// END SETUP
////////////////////////////////// BEGIN GATHER
// Your MySQL queries, fopens, et cetera go here.
// Cells are delimited by \t
// \n is just like you might expect; new line/row below
// E.G:
$stuff="PART\tQTY\tVALUE\t\n";
$stuff=$stuff."01-001-0001\t37\t28.76\t\n";
$stuff=$stuff."01-001-0002\t6\t347.06\t\n";
$stuff=$stuff."01-001-0003\t12\t7.11\t\n";
////////////////////////////////// END GATHER
// The point is to get all of your data into one string and then:
////////////////////////////////// BEGIN DUMP
echo $stuff;
////////////////////////////////// END DUMP
?>
No extensions required.
The only caveats are that I've not yet figured out how to spit out an .xls without Excel telling me the data may be corrupt - and I haven't tried to do any formatting or anything more complex than simply "exporting" the data. The file/data is fine, of course, but it does generate a warning from Excel.
EDIT: I should note that the 'setup' and 'dump' are from the following: daniweb.com
You can use CSV to make a importable format for excel. This is the simpliest way to do it.
CSV looks like this :
"my first cellcontent", "my second cell content", "my third cell content"
"second line, first cell content", "etc", "etc
Each row represents an Excel row, you put cell content between double quotes, and separated by commas.
Be careful to \r\n if you've got some in your datas, it can break your CSV and create unwanted new lines.
With a bit of google you would have found this: http://www.the-art-of-web.com/php/dataexport/
Preparing the data
$data = array(
array( "firstname" => "Mary", "lastname" => "Johnson", "age" => 25 ),
array( "firstname" => "Amanda", "lastname" => "Miller", "age" => 18 ),
array( "firstname" => "James", "lastname" => "Brown", "age" => 31 ),
array( "firstname" => "Patricia", "lastname" => "Williams", "age" => 7 ),
array( "firstname" => "Michael", "lastname" => "Davis", "age" => 43 ),
array( "firstname" => "Sarah", "lastname" => "Miller", "age" => 24 ),
array( "firstname" => "Patrick", "lastname" => "Miller", "age" => 27 )
);
The first step is to output the data in a tab-delimited format (CSV can also be used but is slightly more complicated). To achieve this we use the following code:
<?php
header("Content-Type: text/plain");
$flag = false;
foreach( $data as $row ) {
if( !$flag ) {
// display field/column names as first row
echo implode( "\t", array_keys( $row ) ) . "\r\n";
$flag = true;
}
echo implode( "\t", array_values( $row ) ) . "\r\n";
}
exit;
?>
We set the content type to text/plain so that the output can more easily be viewed in the browser. Otherwise, because there is no HTML formatting, the output would appear as a single line of text.
The first line of output will be the column headings (in this case the field names are used). Values are separated with a tab \t and rows with a line break \n. The output should look something like the following:
firstname lastname age
Mary Johnson 25
Amanda Miller 18
James Brown 31
Patricia Williams 7
Michael Davis 43
Sarah Miller 24
Patrick Miller 27
There's already a weakness in this code that may not be immediately obvious. What if one of the fields to be ouput already contains one or more tab characters, or worse, a newline? That's going to throw the whole process out as we rely on those characters to indicate column- and line-breaks.
The solution is to 'escape' the tab characters. In this case we're going to replace tabs with a literal \t and line breaks with a literal \n so they don't affect the formatting:
<?php
function cleanData( &$str ) {
$str = preg_replace( "/\t/", "\\t", $str );
$str = preg_replace("/\r?\n/", "\\n", $str);
}
header("Content-Type: text/plain");
$flag = false;
foreach( $data as $row ) {
if( !$flag ) {
// display field/column names as first row
echo implode( "\t", array_keys( $row ) ) . "\r\n";
$flag = true;
}
array_walk( $row, 'cleanData' );
echo implode( "\t", array_values( $row ) ) . "\r\n";
}
exit;
?>
Now, before each row is echoed any tab characters are replaced "\t" so that our columns aren't broken up. Also any line breaks within the data are replaced with "\n".
