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");

Answer 2

I really wanted to do this without using a library, so I found a good reference on this page PHP Send email with PDF attachment without creating the file? and then tweaked it to create an Excel file using a tab-delimited (\t) string.

So you could grab the data from the DB to create the string and then use this method below to send an email with the Excel attachment

<?php
$attachment  = "testdata1 \t testdata2 \t testdata3 \t \n testdata1 \t testdata2 \t testdata3 \t ";

$to = '[email protected]'; 
$subject = 'Test email with attachment'; 

//create a boundary string. It must be unique 
//so we use the MD5 algorithm to generate a random hash 
$random_hash = md5(date('r', time())); 

$headers = "From: [email protected]"; 
//add boundary string and mime type specification 
$headers .= "\r\nContent-Type: multipart/mixed; boundary=\"PHP-mixed-".$random_hash."\""; 

//define the body of the message. 
ob_start(); //Turn on output buffering 
?> 
--PHP-mixed-<?php echo $random_hash; ?>  
Content-Type: multipart/alternative; boundary="PHP-alt-<?php echo $random_hash; ?>" 

--PHP-alt-<?php echo $random_hash; ?>  
Content-Type: text/plain; charset="iso-8859-1" 
Content-Transfer-Encoding: 7bit

Email Text here

--PHP-mixed-<?php echo $random_hash; ?>  
Content-Type: application/ms-excel; name="test.xls"  
Content-Disposition: attachment  

<?php echo $attachment; 
//copy current buffer contents into $message variable and delete current output buffer 
$message = ob_get_clean(); 
//send the email 
$mail_sent = @mail( $to, $subject, $message, $headers ); 
//if the message is sent successfully print "Mail sent". Otherwise print "Mail failed" 
echo $mail_sent ? "Mail sent" : "Mail failed"; 
?>

Answer 3

**Code to create excel in php:**
$dtime=date('Y-m-d H-i-s');
$dtimeFile=date('Y-m-d-H-i-s');
$headerTab ="Col1 \t Col2\t Col3\n";
$rowRecords='';
$rowRecords .=preg_replace("/\r|\n|\t/"," ",$Col1)."\t".preg_replace("/\r|\n|\t/", " ",$Col2)."\t".preg_replace("/\r|\n|\t/", " ",Col3). "\t\n";
date_default_timezone_set('America/Los_Angeles');
$filename="Your File Name-".$dtimeFile.".xls";
$path='/pathOfFile/';
$csv_handler = fopen ($path.$filename,'w');
fwrite ($csv_handler,$headerTab);
fwrite ($csv_handler,$rowRecords);
fclose ($csv_handler);

**Code to send html email with attached excel in php:**
$file = $path.$filename;
$file_size = filesize($file);
$handle = fopen($file, "r");
$content = fread($handle, $file_size);
fclose($handle);
$content = chunk_split(base64_encode($content));
$uid = md5(uniqid(time()));
$headers = "From: [email protected]"."\r\n";
$headers.= "Bcc: [email protected]"."\r\n";
$headers.= "MIME-Version: 1.0\r\n";
$headers.= "Content-Type: multipart/mixed; boundary=\"".$uid."\"\r\n\r\n";
$headers .= "This is a multi-part message in MIME format.\r\n";
$headers .= "--".$uid."\r\n";
$headers .= "Content-type:text/html; charset=iso-8859-1\r\n";
$headers .= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$headers .= $msg."\r\n\r\n";
$headers .= "--".$uid."\r\n";
$headers .= "Content-Type: application/octet-stream; name=\"".$filename."\"\r\n";
$headers .= "Content-Transfer-Encoding: base64\r\n";
$headers .= "Content-Disposition: attachment; filename=\"".$filename."\"\r\n\r\n";
$headers .= $content."\r\n\r\n";
$headers .= "--".$uid."--"; 

$date=date("Y-m-d");
if(mail($to,"Mail heading--".$date,$msg,$headers)){
    echo "Mailed successfully";
}
else
{
    echo "Mailed couldn't be sent"; 
}