一、前言
慕课网:PHPExcel探索之旅
链接:https://www.imooc.com/learn/433
PHPExcel是PHP语言操作Excel文件最强大的一个工具,也是身为一个PHPer的必备技能。本课程将带领大家全面解析PHPexcel的导入导出、样式控制、图形报表操作,帮助大家在今后工作中玩转任何文件操作需求。
二、下载使用
下载插件:https://github.com/PHPOffice/PHPExcel
复制classes目录重命名为PHPExcel(自己决定)
引入该文件夹下的PHPExcel.php即可使用
include_once 'PHPExcel.php';
$PHPExcelObj=new PHPExcel();
var_dump($PHPExcelObj);
三、导出文件
<?php
include_once 'PHPExcel.php';
$dir=dirname(__FILE__);//找到当前脚本所在路径
#1.新建excel表格
$objPHPExcel=new PHPExcel();//默认生成一个sheet
#2.创建sheet(内置表)
/*
* createSheet()创建sheet
* setActiveSheetIndex()设置某个sheet为当前活动sheet
* getActiveSheet()获得当前活动sheet的操作对象
*/
$objSheet=$objPHPExcel->getActiveSheet();
#3.填充数据
/*
* setCellValue()
*/
$objSheet->setTitle('demo');//给当前活动sheet设置名称
$objSheet->setCellValue('A1','姓名')->setCellValue('B1','分数');
$objSheet->setCellValue('A2','张三')->setCellValue('B2','50');
#4.保存文件
/*
* PHPExcel_IOFactory::createWriter()
* save()
*/
$objWrite=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
$objWrite->save($dir.'/demo.xlsx');
另一种方法:
$data=array(
array('姓名','分数'),
array('李四','60'),
array('王五','70')
);
$objSheet->fromArray($data);
数据量大的话,内存消耗大,不推荐!
三、从数据库读取数据生成excel文件
自备:mysqldb类、数据库、数据
表数据:
include_once 'config.php';
include_once 'db.php';
include_once 'PHPExcel/PHPExcel.php';
$db=new db($configs);
$dir=dirname(__FILE__);
$objPHPExcel=new PHPExcel();
for ($i=0;$i<3;$i++){
if ($i>=1){
$objPHPExcel->createSheet();
}
$objPHPExcel->setActiveSheetIndex($i);
$objSheet=$objPHPExcel->getActiveSheet();
$objSheet->setTitle(($i+1).'年级');
$objSheet->setCellValue('A1','姓名')
->setCellValue('B1','年级')
->setCellValue('C1','成绩');
$res=$db->query('select * from student where class='.($i+1));
$data=$db->fetch_assoc($res);
$j=2;
foreach ($data as $k=>$v){
$objSheet->setCellValue('A'.$j,$v['name'])
->setCellValue('B'.$j,$v['class'])
->setCellValue('C'.$j,$v['grade']);
$j++;
}
}
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
$objWriter->save($dir.'/demo.xls');
四、输出到浏览器(下载打开)
参考文件位置(一开始下载的PHPExcel文件):\PHPExcel-1.8\PHPExcel-1.8\Examples
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
//$objWriter->save($dir.'/demo.xls');
browser_export('Excel5','demo.xls');
$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');//禁止缓存
}
五、PHPExcel控制文件样式
参考文档:
表数据:
效果:
<?php
include_once 'config.php';
include_once 'db.php';
include_once 'PHPExcel/PHPExcel.php';
$db=new db($configs);
$dir=dirname(__FILE__);
$objPHPExcel=new PHPExcel();
$objSheet=$objPHPExcel->getActiveSheet();
//文字居中
$objSheet->getDefaultStyle()//默认
->getAlignment()//对齐
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER)//垂直方向居中
->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);//水平方向居中
//设置字体
$objSheet->getDefaultStyle()->getFont()->setName('微软雅黑');//字体
$objSheet->getStyle('A2:Z2')//范围
->getFont()->setSize(20)->setBold(true);
$objSheet->getStyle('A2:Z2')//范围
->getFont()->setSize(16)->setBold(true);
//查询所有的年级
$sql='select distinct(grade) from student'.' order by grade asc';
$res=$db->query($sql);
$grade=$db->fetch_assoc($res);
//根据年级数查询所有的班级
/*
* 填充数据
*/
$index=0;//年级序号
foreach ($grade as $v){
$gradeIndex=getCells($index*2);//获得年级信息所在列
$objSheet->setCellValue($gradeIndex.'2','高'.$v['grade']);
$sql='select distinct(class) from student where grade='.$v['grade'].' order by class asc';
$res=$db->query($sql);
$class=$db->fetch_assoc($res);
//查询每个班级信息
foreach ($class as $n){
$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('00ff99');
//设置边框
$objSheet->getStyle($nameIndex.'3:'.$scoreIndex.'3')
->applyFromArray(getBorderStyle('ffffcc'));
$sql='select name,score from student where class='.$n['class'].' order by score desc';
$res=$db->query($sql);
$info=$db->fetch_assoc($res);
$objSheet->setCellValue($nameIndex.'3',$n['class'].'班');
$objSheet->setCellValue($nameIndex.'4','姓名')
->setCellValue($scoreIndex.'4','分数');
$row=5;//第五行开始
foreach ($info as $key=>$val){
$objSheet->setCellValue($nameIndex.$row,$val['name'])
->setCellValue($scoreIndex.$row,$val['score']);
$row++;
}
$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('e36951');
//设置边框
$objSheet->getStyle($gradeIndex.'2:'.$endGradeIndex.'2')
->applyFromArray(getBorderStyle('ffff66'));
}
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
$objWriter->save($dir.'/demo.xls');
//browser_export('Excel5','demo.xls');
//$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');//禁止缓存
}
//根据下标获得单元格所在列数
function getCells($index){
$arr=range('A','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;
}
自动换行
格式控制
六、插入图片
默认坐标、默认图片大小
include_once 'PHPExcel/PHPExcel.php';
$dir=dirname(__FILE__);
$objPHPExcel=new PHPExcel();
$objSheet=$objPHPExcel->getActiveSheet();
//获得一个图片的操作对象
$objDrawing=new PHPExcel_Worksheet_Drawing();
//加载图片路径
$objDrawing->setPath('1.jpg');
//插入图片
$objDrawing->setWorksheet($objSheet);
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
$objWriter->save($dir.'/demo.xls');
设置坐标、设置大小、设定单元格内偏移量
//获得一个图片的操作对象
$objDrawing=new PHPExcel_Worksheet_Drawing();
//加载图片路径
$objDrawing->setPath('1.jpg');
//设置坐标
$objDrawing->setCoordinates('F6');
//设置大小
$objDrawing->setWidth(500);//等比缩放,setHeight同
//设定单元格内偏移量
$objDrawing->setOffsetX(20);
//$objDrawing->setOffsetY(20);
//插入图片
$objDrawing->setWorksheet($objSheet);
七、添加丰富的文字块
效果:
//获得一个蚊子块操作对象
$objRichText=new PHPExcel_RichText();
//生成普通的文字,不能操作样式
$objRichText->createText('操作对象');
//生成可以添加样式的文字块
$objStyleFont=$objRichText->createTextRun('balabala');
$objStyleFont->getFont()->setSize(16)->setBold(true)
->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_GREEN));
$objRichText->createText('bilibili');
//插入到sheet中
$objSheet->getCell('G4')->setValue($objRichText);
八、添加批注/超链接
标注上面的文字块
//标注
$objSheet->mergeCells('G4:I4');
$objSheet->getComment('G4')
->getText()->createTextRun('这是批注内容\n\n这里换行了');//图片错了
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel2007');
$objWriter->save($dir.'/demo.xlsx');
//超链接
$objSheet->setCellValue('A1','链接');
//调样式
$objSheet->getStyle('A1')->getFont()->setSize(16)
->setBold(true)->setUnderline(true)->setColor(new PHPExcel_Style_Color(PHPExcel_Style_Color::COLOR_BLUE));
$objSheet->getCell('A1')->getHyperlink()
->setUrl('http://www.baidu.com');
九、导入文件
读取前面数据库导出的文件
header("Content-Type:text/html;charset=utf-8");
include_once 'PHPExcel/PHPExcel.php';
$dir=dirname(__FILE__);
$filename=$dir.'/demo.xls';
$objPHPExcel=PHPExcel_IOFactory::load($filename);//加载文件
$sheetCount=$objPHPExcel->getSheetCount();//获取文件里有多少个sheet
//for ($i=0;$i<$sheetCount;$i++){
// $data=$objPHPExcel->getSheet($i)->toArray();//读取每个sheet里的数据 全部放入数组中
// var_dump($data);
//}
foreach ($objPHPExcel->getWorksheetIterator() as $sheet){//循环去sheet
foreach ($sheet->getRowIterator() as $row){//逐行处理
if ($row->getRowIndex()<4){
continue;
}
foreach ($row->getCellIterator() as $cell){//逐列读取
$data=$cell->getValue();
if ($data!=null){
echo $data.' ';
}
}
echo '<br/>';
}
echo '<br/>';
}
十、部分加载技术
header("Content-Type:text/html;charset=utf-8");
include_once 'PHPExcel/PHPExcel.php';
$dir=dirname(__FILE__);
$filename=$dir.'/demo.xls';
$fileType=PHPExcel_IOFactory::identify($filename);//自动获取文件的类型
$objReader=PHPExcel_IOFactory::createReader($fileType);//获取文件读取操作对象
$sheetName=array('2年级');
$objReader->setLoderSheetsOnly($sheetName);//只加载指定的sheet
$objPHPExcel=$objReader->load($filename);//加载文件