PHP create Excel spreadsheet and then email it as an attachment

Question

I'm using the following code:

<?php
$data = $_REQUEST['datatodisplay'];
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Data.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $data;

?>

This is what i call when the user hits a submit button. But what i'm interested in doing is sending an Excel spreadsheet as an email attachment. So in this file below I would connect to a DB, select results and create the spreadsheet, then mail it as an attachment. Is that possible by tweaking the code below (i can do the mysql but just not excel creation)

Answer 1

You'd need a library to create an actual Excel document, unless straight CSV is acceptable. CSV will open as a spreadsheet in Excel, but you cannot do any of the advanced stuff like formatting or formulas.

I use the library PHPExcel (http://phpexcel.codeplex.com/). It allows for complete Excel functionality, including charts and formulas. It takes a bit to get it going, and the code is pretty verbose. BUT, once you get it all set up, it works like a charm.

Here's a snippet of the code involved, this is from my implementation of PHPExcel. I am creating a summary of Paypal payments received through a site's API. I include this merely to give you an idea of the amount and nature of the code involved. As you can see, it is all OO. This is just the first PART of the code, where I am setting up column labels and the like. It goes on like this through loops to put the data in place, then another section for the footer. It makes for a V E R Y long file!

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("----- Web Server");
$objPHPExcel->getProperties()->setLastModifiedBy("-----Web Server");
$objPHPExcel->getProperties()->setTitle("Paypal payment reconciliation report");
$objPHPExcel->getProperties()->setSubject("Paypal payment reconciliation report");
$objPHPExcel->getProperties()->setDescription("Paypal payment reconciliation report");
$objPHPExcel->getProperties()->setKeywords("paypal reconcile");
$objPHPExcel->getProperties()->setCategory("Reconciliation report");

// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);    

// format the heading
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Paypal Reconciliation - ran on '.date('m/d/y', time()));
$objPHPExcel->getActiveSheet()->mergeCells('A1:C1');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Date Range: '.date('m/d/Y', $oldest_transaction).' to '.date('m/d/Y', $newest_transaction));
$objPHPExcel->getActiveSheet()->mergeCells('E1:J1');
$objPHPExcel->getActiveSheet()->duplicateStyleArray(
        array(
            'font'    => array(
                'size'      => '12',
                'bold'      => true
            )
        ),
        'A1:I1'
);

// add column labels
$objPHPExcel->getActiveSheet()->setCellValue('A2', '#');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'Date');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 'Name');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'Gross');
$objPHPExcel->getActiveSheet()->setCellValue('E2', 'Fee');
$objPHPExcel->getActiveSheet()->setCellValue('F2', 'Net');
$objPHPExcel->getActiveSheet()->setCellValue('G2', 'Balance');
$objPHPExcel->getActiveSheet()->setCellValue('H2', 'Class');
$objPHPExcel->getActiveSheet()->setCellValue('I2', 'Item Title');
$objPHPExcel->getActiveSheet()->setCellValue('J2', '');
$objPHPExcel->getActiveSheet()->setCellValue('K2', '#');
$objPHPExcel->getActiveSheet()->setCellValue('L2', 'Time');
$objPHPExcel->getActiveSheet()->setCellValue('M2', 'Type');
$objPHPExcel->getActiveSheet()->setCellValue('N2', 'Status');
$objPHPExcel->getActiveSheet()->setCellValue('O2', 'Transaction ID');
$objPHPExcel->getActiveSheet()->setCellValue('P2', 'Paypal Receipt ID');
$objPHPExcel->getActiveSheet()->setCellValue('Q2', '--- #');
$objPHPExcel->getActiveSheet()->setCellValue('R2', 'Counterparty');
$objPHPExcel->getActiveSheet()->setCellValue('S2', 'Reference Txn ID');
$objPHPExcel->getActiveSheet()->setCellValue('T2', 'Inv #');

---

EDIT

By request, here is the code to actually output the Excel document I created above:

    include 'PHPExcel/IOFactory.php';
    $file_name = date('m-d-Y', $oldest_transaction).'_THRU_'.date('m-d-Y', $newest_transaction);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('/usr/web/cache/temp/'.$file_name.'.xls');
    header ("location:http://www.domain.com/cache/temp/".$file_name.".xls");