Excel output from PHP script

Let's say you've written a PHP script that queries a data source (say a MySQL database) and displays the output in the browser. Now you want to export this data to an Excel sheet that the user can print or process further. What are the different ways of doing this?

The first way I ever learned was to simply set the PHP content type header, like "Content-type: application/vnd.ms-excel; name='excel'". This prompts the user to open or save an Excel sheet, which would contain our tabular content. But in this method, you would not have any control over the formatting of the Excel sheet, such as borders, fonts, colors, etc.

If you want to generate a well-formatted Excel sheet, you can use the PHP library called PHPExcel, which can be downloaded from here. Just copy part of the zip file contents (see the "install.txt" file) to a sub-directory on your web server document root and you can get going. It is easy to save your tabular content to an Excel file on the server, using PHPExcel.

The code required for creating the Excel file is fairly simple, and you can put it together by looking at the examples given in the PHPExcel zip file. If you get stuck, say on a formatting issue, you can always google the solution. PHPExcel seems to be quite popular and so, more likely than not, your question would have been answered on one of the coding forums.

Here is my code for saving my Music Database List content as an Excel file:

/* Include PHPExcel */

require_once '../../Classes/PHPExcel.php';

// Create new PHPExcel object

$objPHPExcel = new PHPExcel();

// Set document properties

$objPHPExcel->getProperties()->setCreator("Ajay Ramakrishnan")

    ->setLastModifiedBy("Ajay Ramakrishnan")

    ->setTitle("Music Database List")

    ->setSubject("Music Database List")

    ->setDescription("Music Database List, generated using PHPExcel.")

    ->setKeywords("Music Database PHPExcel")

    ->setCategory("Music Database List");

// Add title row to Excel file

$objPHPExcel->setActiveSheetIndex(0)

    ->setCellValue('A1', $a[0])

    ->setCellValue('B1', $b[0])

    ->setCellValue('C1', $c[0])

    ->setCellValue('D1', $d[0]);

// Set the title row font to bold and background color to light slate grey

$header_range = "A1:D1";
$objPHPExcel->getActiveSheet()->getStyle($header_range)->getFont()->setBold(true);
$bgColorArray = array(
                    'fill' => array(
                        'type' => PHPExcel_Style_Fill::FILL_SOLID,
                        'color' => array('rgb' => '6D7B8D')
                    )
                );
$objPHPExcel->getActiveSheet()->getStyle($header_range)->applyFromArray($bgColorArray);

// Loop through the existing data rows

for ($i=2; $i <= $row_count; $i++) {

    $objPHPExcel->setActiveSheetIndex(0)

        ->setCellValue('A' . $i, $a[$i-1])

        ->setCellValue('B' . $i, $b[$i-1])

        ->setCellValue('C' . $i, $c[$i-1])

        ->setCellValue('D' . $i, $d[$i-1]);

    }

// Resize the columns

$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);

// Set the border for all the non-empty cells

$i = $i - 1;

$styleArray = array(

        'borders' => array(

            'left' => array(

                'style' => PHPExcel_Style_Border::BORDER_THIN,

            ),

            'right' => array(

                'style' => PHPExcel_Style_Border::BORDER_THIN,

            ),

            'top' => array(

                'style' => PHPExcel_Style_Border::BORDER_THIN,

            ),

            'bottom' => array(

                'style' => PHPExcel_Style_Border::BORDER_THIN,

            ),

        ),

    );

$objPHPExcel->getActiveSheet()->getStyle('A1:D' . $i)->applyFromArray($styleArray, False);

// Rename worksheet

$objPHPExcel->getActiveSheet()->setTitle('Music_DB_List');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet

$objPHPExcel->setActiveSheetIndex(0);

// Save Excel 2007 file

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$objWriter->save(str_replace('.php', '.xlsx', __FILE__));