PHP 创建 Excel 电子表格，然后将其作为附件通过电子邮件发送 [英] PHP create Excel spreadsheet and then email it as an attachment

问题描述

我正在使用以下代码:

<?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;

?>

当用户点击提交按钮时，这就是我所说的.但我感兴趣的是将 Excel 电子表格作为电子邮件附件发送.因此，在下面的这个文件中，我将连接到数据库，选择结果并创建电子表格，然后将其作为附件邮寄.是否可以通过调整下面的代码来实现(我可以创建 mysql，但不能创建 excel)

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)

推荐答案

除非可以接受直接的 CSV，否则您需要一个库来创建实际的 Excel 文档.CSV 将在 Excel 中作为电子表格打开，但您无法执行任何高级操作，例如格式设置或公式.

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.

我使用 PHPExcel 库 (http://phpexcel.codeplex.com/).它允许完整的 Excel 功能，包括图表和公式.它需要一点时间才能运行，而且代码非常冗长.但是，一旦你完成所有设置，它就像一个魅力.

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.

这是所涉及的代码片段，这是我对 PHPExcel 的实现.我正在创建通过网站 API 收到的 Paypal 付款的摘要.我包含这只是为了让您了解所涉及代码的数量和性质.如您所见，一切都是面向对象的.这只是代码的第一部分，我在其中设置列标签等.它通过循环继续将数据放置到位，然后是页脚的另一部分.它是一个非常长的文件！

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 #');

<小时>

编辑

根据要求，这里是实际输出我上面创建的 Excel 文档的代码:

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

这篇关于PHP 创建 Excel 电子表格，然后将其作为附件通过电子邮件发送的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！