PHPExcel 使用简单说明
- 解压后把Classes文件夹复制到项目目录并重新命名为PHPExcel。
- 新建一个excel表格 ------------实例化PHPExcel
- 创建sheet(内置表) -----------createSheet()方法 获取当前活动对象 getActiveSheet方法
- 填充数据 ------------setCellValue方法
- 保存文件 -------------PHPExcel_IOFactory::createWriter()方法 save()方法
首先看一下目录结构:
一个入门简单的小实例代码：
创建index.php
<?php
/**
* Created by ZhengNiu.
* User: admin
* Date: 2019/8/28
* Time: 14:59
*/
$dir = dirname(__FILE__);
require_once $dir . '/PHPExcel/PHPExcel.php';
$objPHPExcel = new PHPExcel();//实例化PHPExcel类 等同于在桌面上新建一个excel。
$objSheet = $objPHPExcel->getActiveSheet();//获取当前活动sheet的操作对象
$objSheet->setTitle('demo');//给当前活动sheet设置名称
$objSheet->setCellValue("A1", "姓名")->setCellValue("B1", "分数");//给当前活动sheet填充数据
$objSheet->setCellValue("A2", "张三")->setCellValue("B2", 50);
/*$arr = [
['姓名','分数'],
['李四','60'],
['王五','70']
];
$objSheet->fromArray($arr);//直接加载数据块添加数据*/
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//按照指定格式生成excel文件
$objWriter->save($dir . '/demo.xlsx');
效果：
结合数据库查询写一个简单的小例子：场景有这样一个订单表如图，我们把1-20号订单，21-40号订单，41-60号订单分开放在不同的sheet里面。
dbconfig.php--数据库配置文件
<?php
/**
* Created by ZhengNiu.
* User: admin
* Date: 2019/8/28
* Time: 15:37
*/
$dbconfig = [
'host' => '127.0.0.1',
'username' => 'root',
'password' => '123456',
'database' => 'test',
'charset' => 'utf8'
];
db.php----数据库操作类
<?php
/**
* Created by ZhengNiu.
* User: admin
* Date: 2019/8/28
* Time: 15:39
*/
require_once dirname(__FILE__) . '/dbconfig.php';
class db
{
public $conn = null;
static $_instance;
/**
* db constructor.
* @param $config
*/
private function __construct($config)
{
$this->conn = mysqli_connect($config['host'], $config['username'], $config['password'], $config['database']);
if ($this->conn->connect_errno) {
printf("Connect failed: %s\n", $this->conn->connect_error);
exit();
}
mysqli_query($this->conn, "set names " . $config['charset']) or die(mysqli_error($this->conn));
}
/**
* @param $sql
* @return array
*/
public function getResult($sql)
{
$resource = mysqli_query($this->conn, $sql) or die(mysqli_error($this->conn));
$res = [];
while (($row = mysqli_fetch_assoc($resource)) != false) {
$res[] = $row;
}
return $res;
}
private function __clone(){}
/**
* @param $config
* @return db
*/
public static function getInstance($config){
if(FALSE == (self::$_instance instanceof self)){
self::$_instance = new self($config);
}
return self::$_instance;
}
/**
* @param $sql
* @return bool|mysqli_result
*/
public function query($sql)
{
return $this->conn->query($sql);
}
/**
** 查询所有的年级
**/
public function getAllGrade(){
$sql="select distinct(grade) from user order by grade asc";
$res=$this->getResult($sql);
return $res;
}
/**
**根据年级数查询所有的班级
**/
public function getClassByGrade($grade){
$sql="select distinct(class) from user where grade=".$grade." order by class asc";
$res=$this->getResult($sql);
return $res;
}
/**
**根据年级数班级数查询学生信息
**/
public function getDataByClassGrade($class,$grade){
$sql="select username,score from user where class=".$class." and grade=".$grade." order by score desc";
$res=$this->getResult($sql);
return $res;
}
}
helpers.php---调试输出文件
<?php
/**
* Created by ZhengNiu.
* User: admin
* Date: 2019/8/29
* Time: 10:15
*/
/**
* @param $val
* @param bool $dump
* @param bool $exit
*/
function debug($val, $dump = false, $exit = true)
{
if ($dump) {
$func = 'var_dump';
} else {
$func = (is_array($val) || is_object($val)) ? 'print_r' : 'printf';
}
header("Content-type:text/html;charset=utf-8");
echo '<pre>debug output:<hr />';
$func($val);
echo "</pre>";
if ($exit) exit;
}
export.php----逻辑代码文件
<?php
/**
* Created by ZhengNiu.
* User: admin
* Date: 2019/8/28
* Time: 15:59
*/
$dir = dirname(__FILE__);
require_once $dir . '/db.php';
require_once $dir . '/PHPExcel/PHPExcel.php';
$db = db::getInstance($dbconfig);
$objPHPExcel = new PHPExcel();//实例化PHPExcel类 等同于在桌面上新建一个excel。
for ($i = 1; $i <= 3; $i++) {
if ($i > 1) {
$objPHPExcel->createSheet();//创建sheet
}
$objPHPExcel->setActiveSheetIndex($i - 1);//把新创建的sheet设定为当前sheet
$objSheet = $objPHPExcel->getActiveSheet();//获取当前活动sheet
$objSheet->setTitle('订单号：' . (($i - 1) * 20 + 1) . '~' . $i * 20);
$where = " id>=".(($i-1)*20 + 1).' AND id<='.($i*20);
$data = $db->getResult("SELECT * FROM `order` WHERE {$where}");//查询每个年级的学生数据
$objSheet->setCellValue('A1', '订单ID')
->setCellValue('B1', '是否删除')
->setCellValue("C1", '发货时间')
->setCellValue("D1", '是否取消')
->setCellValue("E1", '是否发货')
->setCellValue("F1", '是否验证')
->setCellValue("G1", '验证时间');
//从第二行开始写入
$j = 2;
foreach ($data as $k => $v) {
$objSheet->setCellValue("A" . $j, $v['id'])
->setCellValue("B" . $j, $v['is_del'])
->setCellValue("c" . $j, $v['send_time'])
->setCellValue("D" . $j, $v['is_cancel'])
->setCellValue("E" . $j, $v['is_send'])
->setCellValue("F" . $j, $v['is_token'])
->setCellValue("G" . $j, $v['token_time']);
$j++;
}
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//按照指定格式生成excel文件
//保存在服务器
//$objWriter->save($dir . '/export.xlsx');
//输出到浏览器
browser_export('Excel5','browser_export03.xls');
$objWriter->save('php://output');
exit;
/**
* 输出到浏览器
*
* @param $type
* @param $filename
*/
function browser_export($type, $filename)
{
if ($type == "Excel5") {
header('Content-Type: application/vnd.ms-excel');//输出Excel03
} else {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//输出Excel07
}
header('Content-Disposition: attachment;filename="'.$filename.'"');//输出文件名称
header('Cache-Control: max-age=0');//禁止缓存
}
效果：
下面我们以年级、班级、学生成绩为例介绍一些常用的样式（设置字体、设置边框、合并单元格、设置、设置背景色、以及单元格内换行、单元格不以科学记数法显示等）
首先我们建一个数据表并插入一些数据如图：
insert.php----插入测试数据文件
<?php
/**
* Created by ZhengNiu.
* User: admin
* Date: 2019/8/29
* Time: 9:23
*/
$dir = dirname(__FILE__);
require_once $dir . '/db.php';
require_once $dir.'/helpers.php';
$conn = db::getInstance($dbconfig);
//$conn->query("truncate table `user`");die;
$sqlTpl = "INSERT INTO `user` (`id`,`class`,`grade`,`score`,`username`) VALUES ";
$val = "";
for ($i = 1; $i <= 1000; $i++) {
$username = getUsername();
$value = [$i, rand(1, 4), rand(1, 3), rand(20, 100), "'{$username}'"];
$val .= " ( " . implode(',', $value) . " ) ,";
}
$insertStr = $sqlTpl.rtrim($val, ',');
$conn->query($insertStr);
/**
* @return string
*/
function getUsername()
{
$name = ["张飞", "李奎", "赵三", "赵云", "王昭君", "孙策", "孙尚香", "哪吒", "刘婵", "后裔", "妲己"];
$randge = range("A", "Z");
$randgeNum = range(0, 1000);
return $name[rand(0, count($name) - 1)] . $randge[rand(0, count($randge) - 1)] . $randgeNum[rand(0, count($randgeNum) - 1)];
}
style.php-----逻辑代码
<?php
/**
* Created by ZhengNiu.
* User: admin
* Date: 2019/8/28
* Time: 15:59
*/
$dir = dirname(__FILE__);
require_once $dir . '/db.php';
require_once $dir . '/PHPExcel/PHPExcel.php';
require_once $dir.'/helpers.php';
$db = db::getInstance($dbconfig);
$objPHPExcel = new PHPExcel();//实例化PHPExcel类 等同于在桌面上新建一个excel。
$objSheet = $objPHPExcel->getActiveSheet();//获得当前活动单元格
$objSheet->setTitle("学生成绩统计");
$objSheet->getDefaultStyle()->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中
$objSheet->getDefaultStyle()->getFont()->setSize(14)->setName("微软雅黑");//设置默认字体大小和格式
$objSheet->getStyle("A2:Z2")->getFont()->setSize(20)->setBold(true);//设置第二行字体大小和加粗
$objSheet->getStyle("A3:Z3")->getFont()->setSize(16)->setBold(true);//设置第三行字体大小和加粗
$objSheet->getDefaultRowDimension()->setRowHeight(30);//设置默认行高
$objSheet->getRowDimension(2)->setRowHeight(50);//设置第二行行高
$objSheet->getRowDimension(3)->setRowHeight(40);//设置第三行行高
$gradeInfo = $db->getAllGrade();//查询所有的年级
$index = 0;
foreach ($gradeInfo as $g_k => $g_v) {
$gradeIndex = getCells($index * 2);//获取年级信息所在列
$objSheet->setCellValue($gradeIndex . "2", "高" . $g_v['grade']);
$classInfo = $db->getClassByGrade($g_v['grade']);//查询每个年级所有的班级
foreach ($classInfo as $c_k => $c_v) {
$nameIndex = getCells($index * 2);//获得每个班级学生姓名所在列位置
$scoreIndex = getCells($index * 2 + 1);//获得每个班级学生分数所在列位置
$objSheet->mergeCells($nameIndex . "3:" . $scoreIndex . "3");//合并每个班级的单元格
$objSheet->getStyle($nameIndex . "3:" . $scoreIndex . "3")
->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()->setRGB('6fc144');//填充班级背景颜色
$classBorder = getBorderStyle("445cc1");//获取班级边框样式代码
$objSheet->getStyle($nameIndex . "3:" . $scoreIndex . "3")->applyFromArray($classBorder);//设置每个班级的边框
$info = $db->getDataByClassGrade($c_v['class'], $g_v['grade']);//查询每个班级的学生信息
$objSheet->setCellValue($nameIndex . "3", $c_v['class'] . "班");//填充班级信息
$objSheet->getStyle($nameIndex)->getAlignment()->setWrapText(true);//设置文字自动换行
$objSheet->setCellValue($nameIndex . "4", "姓名\n换行")->setCellValue($scoreIndex . "4", "分数");
$objSheet->getStyle($scoreIndex)->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);//设置某列单元格格式为文本格式
$j = 5;
foreach ($info as $key => $val) {
$objSheet->setCellValue($nameIndex . $j, $val['username'])->setCellValue($scoreIndex . $j, $val['score']);//填充学生信息
$j++;
}
$index++;
}
$endGradeIndex = getCells($index * 2 - 1);//获得每个年级的终止单元格
$objSheet->mergeCells($gradeIndex . "2:" . $endGradeIndex . "2");//合并每个年级的单元格
$objSheet->getStyle($gradeIndex . "2:" . $endGradeIndex . "2")->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()->setRGB('c1b644');//填充年级背景颜色
$gradeBorder = getBorderStyle("c144b1");//获取年级边框样式代码
$objSheet->getStyle($gradeIndex . "2:" . $endGradeIndex . "2")->applyFromArray($gradeBorder);//设置每个年级的边框
}
//die;
//debug($index);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//按照指定格式生成excel文件
//保存在服务器
//$objWriter->save($dir . '/export.xlsx');
//输出到浏览器
browser_export('Excel5','browser_export03.xls');
$objWriter->save('php://output');
exit;
/**
* 输出到浏览器
*
* @param $type
* @param $filename
*/
function browser_export($type, $filename)
{
if ($type == "Excel5") {
header('Content-Type: application/vnd.ms-excel');//输出Excel03
} else {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//输出Excel07
}
header('Content-Disposition: attachment;filename="'.$filename.'"');//输出文件名称
header('Cache-Control: max-age=0');//禁止缓存
}
function getCells($index)
{
$arr = range('A', 'Z');
//$arr=array(A,B,C,D,E,F,G,H,I,J,K,L,M,N,....Z);
return $arr[$index];
}
/**
**获取边框样式代码
**/
function getBorderStyle($color)
{
$styleArray = array(
'borders' => array(
'outline' => array(
'style' => PHPExcel_Style_Border::BORDER_THICK,
'color' => array('rgb' => $color),
),
),
);
return $styleArray;
}
演示结果：
插入图片、添加丰富的文字块、添加超链接、添加批注等
<?php
$dir=dirname(__FILE__);//查找当前脚本所在路径
require $dir."/db.php";//引入mysql操作类文件
require $dir."./../PHPExcel/PHPExcel.php";//引入PHPExcel
$objPHPExcel=new PHPExcel();//实例化PHPExcel类， 等同于在桌面上新建一个excel
$objSheet=$objPHPExcel->getActiveSheet();//获得当前活动sheet
/**插入图片代码开始**/
$objDrawing=new PHPExcel_WorkSheet_Drawing();//获得一个图片的操作对象
$objDrawing->setPath($dir."./../1.png");//加载图片路径
$objDrawing->setCoordinates("F6");//设置图片插入位置的左上角坐标
$objDrawing->setWidth(500);//设置插入图片的大小 等比
//$objDrawing->setHeight(100);//会覆盖上面的setWidth
//$objDrawing->setOffsetX(20)->setOffsetY(40);//设定单元格内偏移量
$objDrawing->setWorkSheet($objSheet);//将图片插入到sheet
/**代码结束**/
/**添加丰富的文字块**/
$objRichText=new PHPExcel_RichText();//获得一个文字块操作对象
$objRichText->createText("我的测试");//添加普通的文字 不能操作样式
$objStyleFont=$objRichText->createTextRun("我的测试");//生成可以添加样式的文字块
$objStyleFont->getFont()->setSize(16)->setBold(True)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_GREEN));//加一些样式
$objRichText->createText(",我的测试");
$objSheet->getCell("F4")->setValue($objRichText);//将文字块插入sheet里
/**代码结束**/
/**添加批注代码开始**/
$objSheet->mergeCells("F4:N4");//合并单元格
$objSheet->getComment("F4")->getText()->createTextRun("Van:\r\n我的测试\n\n时尚时尚最时尚");//添加批注
/**代码结束**/
/**超链接代码开始**/
$objSheet->setCellValue("I3","我的测试");//添加文字
$objSheet->getStyle("I3")->getFont()->setSize(16)->setBold(true)->setUnderline(true)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLUE));//添加样式
$objSheet->getCell("I3")->getHyperlink()->setUrl("http://www.baidu.com");//给文字加上链接地址
/**代码结束**/
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');//生成excel文件
//$objWriter->save($dir."/export_1.xls");//保存文件
browser_export('Excel2007','browser_excel03.xlsx');//输出到浏览器
$objWriter->save("php://output");
function browser_export($type,$filename){
if($type=="Excel5"){
header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
}else{
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
}
header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称
header('Cache-Control: max-age=0');//禁止缓存
}
?>演示：
图表绘制
<?php
$dir=dirname(__FILE__);//查找当前脚本所在路径
require $dir."/db.php";//引入mysql操作类文件
require $dir."/PHPExcel/PHPExcel.php";//引入PHPExcel
$db = db::getInstance($dbconfig);//实例化db类 连接数据库
$objPHPExcel=new PHPExcel();//实例化PHPExcel类， 等同于在桌面上新建一个excel
$objSheet=$objPHPExcel->getActiveSheet();//获得当前活动sheet
/**本节课程代码编写开始**/
$array=array(
array("","一班","二班","三班"),
array("不及格",20,30,40),
array("良好",30,50,55),
array("优秀",15,17,20)
);//准备数据
$objSheet->fromArray($array);//直接加载数组填充进单元格内
//开始图表代码编写
$labels=array(
new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$B$1',null,1),//一班
new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$C$1',null,1),//二班
new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$D$1',null,1),//三班
);//先取得绘制图表的标签
$xLabels=array(
new PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$2:$A$4',null,3)//取得图表X轴的刻度
);
$datas=array(
new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$B$2:$B$4',null,3),//取一班的数据
new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$C$2:$C$4',null,3),//取二班的数据
new PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$D$2:$D$4',null,3)//取三班的数据
);//取得绘图所需的数据
$series=array(
new PHPExcel_Chart_DataSeries(
PHPExcel_Chart_DataSeries::TYPE_LINECHART,//线状图
PHPExcel_Chart_DataSeries::GROUPING_STANDARD,//类别
range(0,count($labels)-1),
$labels,
$xLabels,
$datas
)
);//根据取得的东西做出一个图表的框架
//设置所在位置的值
$layout=new PHPExcel_Chart_Layout();
$layout->setShowVal(true);
$areas=new PHPExcel_Chart_PlotArea($layout,$series);
//设置靠右位置
$legend=new PHPExcel_Chart_Legend(PHPExcel_Chart_Legend::POSITION_RIGHT,$layout,false);
$title=new PHPExcel_Chart_Title("高一学生成绩分布");
$ytitle=new PHPExcel_Chart_Title("value(人数)");
$chart=new PHPExcel_Chart(
'line_chart',
$title,
$legend,
$areas,
true,
false,
null,
$ytitle
);//生成一个图标
// 左上和右下角位置确定
$chart->setTopLeftPosition("A7")->setBottomRightPosition("K25");//给定图表所在表格中的位置
$objSheet->addChart($chart);//将chart添加到表格中
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');//生成excel文件
$objWriter->setIncludeCharts(true);
//$objWriter->save($dir."/export_1.xls");//保存文件
browser_export('Excel2007','browser_chart.xlsx');//输出到浏览器
$objWriter->save("php://output");
function browser_export($type,$filename){
if($type=="Excel5"){
header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
}else{
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器数据excel07文件
}
header('Content-Disposition: attachment;filename="'.$filename.'"');//告诉浏览器将输出文件的名称
header('Cache-Control: max-age=0');//禁止缓存
}
?>
演示：
读取excel:
<?php
header("Content-Type:text/html;charset=utf-8");
$dir = dirname(__FILE__);//找到当前脚本所在路径
require $dir . "/PHPExcel/PHPExcel/IOFactory.php";//引入读取excel的类文件
$filename = $dir . "/demo.xlsx";
$fileType = PHPExcel_IOFactory::identify($filename);//自动获取文件的类型提供给phpexcel用
$objReader = PHPExcel_IOFactory::createReader($fileType);//获取文件读取操作对象
$sheetName = array("demo");
$objReader->setLoadSheetsOnly($sheetName);//只加载指定的sheet
$objPHPExcel = $objReader->load($filename);//加载文件
//$sheetCount=$objPHPExcel->getSheetCount();//获取excel文件里有多少个sheet
// for($i=0;$i<$sheetCount;$i++){
// $data=$objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据 全部放入到数组中
// print_r($data);
//}
foreach ($objPHPExcel->getWorksheetIterator() as $sheet) {//循环取sheet
foreach ($sheet->getRowIterator() as $row) {//逐行处理
if ($row->getRowIndex() < 2) {
continue;
}
foreach ($row->getCellIterator() as $cell) {//逐列读取
$data = $cell->getValue();//获取单元格数据
echo $data . " ";
}
echo '<br/>';
}
echo '<br/>';
}
exit;
?>**拓展：
PHP启用缓存
- 默认 -----------按cell占用PHP内存
- cache_ in memory serialized ------------ 序列化存放PHP内存
- cache in memory gzip ----------进一 步压缩
- cache_ in memory_ disclSAM ------------ 存放于硬盘
- cache_ in memory .phpTemp -----------存放于临时文件夹
- cache in memory_ memcache ----------存放于memcache里
以memcache为例子简单的说明一下、具体使用请参考文档
<?php
$dir = dirname(__FILE__);
require_once $dir . '/PHPExcel/PHPExcel.php';
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache;
$cacheSettings = array(
'memcacheServer' => 'localhost',
'memcachePort' => 11211,
'cache Time' => 600
);
PHPExcel_Settings:setCacheStorageMethod($cacheMethod, $cacheSettings);
?>
PHPExcel相关文章、大文件导出数据相关文章
