Exporting data from php to excel

Question

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

Answer 1

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)

2019:

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.

Answer 2

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

Answer 3

You can use phpexcel library. It allow you to write to and read from different spreadsheet file formats

for Integration with zend, you can take help from following link.

Answer 4

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.