PHP Export EXCEL Quick Development Guide

php excel has a proprietary development document. For detailed operations, please refer to the php excel development document. This article only optimizes and integrates its use to facilitate new projects. Rapid development. If you are interested, you can also look at the php excel development documentation.

There are also two ways for phpexcel to generate files, one is direct output, and the other is to generate static files.

Direct output:
The main file is (the file in the same directory of the class directory):

<?php
    include("./class/class.php"); // 包含class的基本头文件
    include("./class/phpexcel/PHPExcel.php"); // 生成excel的基本类定义(注意文件名的大小写)
    // 如果直接输出excel文件，则要包含此文件
    include("./class/phpexcel/PHPExcel/IOFactory.php");
    // 创建phpexcel对象，此对象包含输出的内容及格式
    $m_objPHPExcel = new PHPExcel();
    // 模板文件，为了实现格式与内容分离，有关输出文件具体内容实现在模板文件中
    // 模板文件将对象$m_objPHPExcel进行操作
    include("./include/excel.php");
    // 输出文件的类型，excel或pdf
    $m_exportType = "excel";
    $m_strOutputExcelFileName = date(&#39;Y-m-j_H_i_s&#39;).".xls"; // 输出EXCEL文件名
    $m_strOutputPdfFileName = date(&#39;Y-m-j_H_i_s&#39;).".pdf"; // 输出PDF文件名
    // PHPExcel_IOFactory, 输出excel
    //require_once dirname(__FILE__).&#39;/Classes/PHPExcel/IOFactory.php&#39;;
    // 如果需要输出EXCEL格式
    if($m_exportType=="excel"){
    $objWriter = PHPExcel_IOFactory::createWriter($m_objPHPExcel, &#39;Excel5&#39;);
    // 从浏览器直接输出$m_strOutputExcelFileName
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type: application/vnd.ms-excel;");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header("Content-Disposition:attachment;filename=".$m_strOutputExcelFileName);
    header("Content-Transfer-Encoding:binary");
    $objWriter->save("php://output");
    }
    // 如果需要输出PDF格式
    if($m_exportType=="pdf"){
    $objWriter = PHPExcel_IOFactory::createWriter($m_objPHPExcel, &#39;PDF&#39;);
    $objWriter->setSheetIndex(0);
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type: application/pdf");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header("Content-Disposition:attachment;filename=".$m_strOutputPdfFileName);
    header("Content-Transfer-Encoding:binary");
    $objWriter->save("php://output");
    }
?>

Template file content (with additional common operations)

<?php
global $m_objPHPExcel; // 由外部文件定义
// 设置基本属性
$m_objPHPExcel->getProperties()->setCreator("Sun Star Data Center")
->setLastModifiedBy("Sun Star Data Center")
->setTitle("Microsoft Office Excel Document")
->setSubject("Test Data Report -- From Sunstar Data Center")
->setDescription("LD Test Data Report, Generate by Sunstar Data Center")
->setKeywords("sunstar ld report")
->setCategory("Test result file");
// 创建多个工作薄
$sheet1 = $m_objPHPExcel->createSheet();
$sheet2 = $m_objPHPExcel->createSheet();
// 通过操作索引即可操作对应的工作薄
// 只需设置要操作的工作簿索引为当前活动工作簿，如
// $m_objPHPExcel->setActiveSheetIndex(0);
// 设置第一个工作簿为活动工作簿
$m_objPHPExcel->setActiveSheetIndex(0);
// 设置活动工作簿名称
// 如果是中文一定要使用iconv函数转换编码
$m_objPHPExcel->getActiveSheet()->setTitle(iconv(&#39;gbk&#39;, &#39;utf-8&#39;, &#39;测试工作簿&#39;));
// 设置默认字体和大小
$m_objPHPExcel->getDefaultStyle()->getFont()->setName(iconv(&#39;gbk&#39;, &#39;utf-8&#39;, &#39;宋体&#39;));
$m_objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
// 设置一列的宽度
$m_objPHPExcel->getActiveSheet()->getColumnDimension(&#39;A&#39;)->setWidth(15);
// 设置一行的高度
$m_objPHPExcel->getActiveSheet()->getRowDimension(&#39;6&#39;)->setRowHeight(30);
// 合并单元格
$m_objPHPExcel->getActiveSheet()->mergeCells(&#39;A1:P1&#39;);
// 定义一个样式，加粗，居中
$styleArray1 = array(
&#39;font&#39; => array(
&#39;bold&#39; => true,
&#39;color&#39;=>array(
&#39;argb&#39; => &#39;00000000&#39;,
),
),
&#39;alignment&#39; => array(
&#39;horizontal&#39; => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
),
);
// 将样式应用于A1单元格
$m_objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->applyFromArray($styleArray1);
// 设置单元格样式（黑色字体）
$m_objPHPExcel->getActiveSheet()->getStyle(&#39;H5&#39;)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK); // 黑色
// 设置单元格格式（背景）
$m_objPHPExcel->getActiveSheet()->getStyle(&#39;H5&#39;)->getFill()->getStartColor()->setARGB(&#39;00ff99cc&#39;); // 将背景设置为浅粉色
// 设置单元格格式（数字格式）
$m_objPHPExcel->getActiveSheet()->getStyle(&#39;F1&#39;)->getNumberFormat()->setFormatCode(&#39;0.000&#39;);
// 给特定单元格中写入内容
$m_objPHPExcel->getActiveSheet()->setCellValue(&#39;A1&#39;, &#39;Hello Baby&#39;);
// 设置单元格样式（居中）
$m_objPHPExcel->getActiveSheet()->getStyle(&#39;H5&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 给单元格中放入图片, 将数据图片放在J1单元格内
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName(&#39;Logo&#39;);
$objDrawing->setDescription(&#39;Logo&#39;);
$objDrawing->setPath("../logo.jpg"); // 图片路径，只能是相对路径
$objDrawing->setWidth(400); // 图片宽度
$objDrawing->setHeight(123); // 图片高度
$objDrawing->setCoordinates(&#39;J1&#39;);
$objDrawing->setWorksheet($m_objPHPExcel->getActiveSheet());
// 设置A5单元格内容并增加超链接
$m_objPHPExcel->getActiveSheet()->setCellValue(&#39;A5&#39;, iconv(&#39;gbk&#39;, &#39;utf-8&#39;, &#39;超链接keiyi.com&#39;));
$m_objPHPExcel->getActiveSheet()->getCell(&#39;A5&#39;)->getHyperlink()->setUrl(&#39;http://www.keiyi.com/&#39;);
?>

Generate static files on the server side

Compared with direct generation, the main difference between these two methods is the generation format. The template file is completely The same, the following is a modified version based on the above example, pay attention to the difference from the above example.

<?php
// 包含class的基本头文件
include("./class/class.php");
// 生成excel的基本类定义(注意文件名的大小写)
include("./class/phpexcel/PHPExcel.php");
// 包含写Excel5格式的文件，如果需要生成excel2007的文件，包含对应的Writer即可
include("./class/phpexcel/PHPExcel/Writer/Excel5.php");
// 包含写PDF格式文件
include("./class/phpexcel/PHPExcel/Writer/PDF.php");
// 创建phpexcel对象，此对象包含输出的内容及格式
$m_objPHPExcel = new PHPExcel();
// 模板文件，为了实现格式与内容分离，有关输出文件具体内容实现在模板文件中
// 模板文件将对象$m_objPHPExcel进行操作
include("./include/excel.php");
// 输出文件的类型，excel或pdf
$m_exportType = "pdf";
$m_strOutputExcelFileName = date(&#39;Y-m-j_H_i_s&#39;).".xls"; // 输出EXCEL文件名
$m_strOutputPdfFileName = date(&#39;Y-m-j_H_i_s&#39;).".pdf"; // 输出PDF文件名
// 输出文件保存路径，此路径必须可写
$m_strOutputPath = "./output/";
// 如果需要输出EXCEL格式
if($m_exportType=="excel"){
$objWriter = new PHPExcel_Writer_Excel5($m_objPHPExcel);
$objWriter->save($m_strOutputPath.$m_strOutputExcelFileName);
}
// 如果需要输出PDF格式
if($m_exportType=="pdf"){
$objWriter = new PHPExcel_Writer_PDF($m_objPHPExcel);
$objWriter->save($m_strOutputPath.$m_strOutputPdfFileName);
}
?>

The above is all the contents of the PHP Export EXCEL Rapid Development Guide. If you are interested, you can search for more on the PHP Chinese website~

##Related recommendations:

Introduction to the method of freezing and locking table headers in PHPExcel

Example of how phpexcel imports excel to process big data code

Use PHPExcel to upload data in batches

Introduction to the use process of PHPExcel

The above is the detailed content of PHP Export EXCEL Quick Development Guide. For more information, please follow other related articles on the PHP Chinese website!