最近做项目,遇到一个需要excel、word导出的功能案例,所以找到了PhpSpreadsheet进行导出,不要问我为什么不用PHPExcel,因为PHPExcel已经不更新了,对后面的扩展不太好。今天这里就来说一下如何在没有命名空间的PHP程序里面引入PhpSpreadsheet(有命名空间)。
最近做项目,遇到一个需要excel、word导出的功能案例,所以找到了PhpSpreadsheet进行导出,不要问我为什么不用PHPExcel,因为PHPExcel已经不更新了,对后面的扩展不太好。今天这里就来说一下如何在没有命名空间的PHP程序里面引入PhpSpreadsheet(有命名空间)。
我们边听音乐边看如何运用
不废话,直接上菜
1、首先要自己通过仓库git下载/或者安装PhpSpreadsheet包
composer require phpoffice/phpspreadsheet
这里会自动安装1.6版本的,如果是windows请先安装Composer
安装后php.ini中打开
2、引入PhpSpreadsheet到代码中
先把安装好后的vendor整个文件拷贝到你的项目文件中,如果是TP3.1及以下的版本,放到根目录下的公共文件夹中即可,如果是TP3.2以上的,直接将代码安装到扩展vendor里面即可。不用我多说吧。
require .'/Public/common/vendor/autoload.php';//引入公共目录下的excel导入导出类
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment as PHPExcel_Style_Alignment;
use PhpOffice\PhpSpreadsheet\Cell\DataType as PHPExcel_Cell_DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill as PHPExcel_Style_Fill;
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
// --- 读取Excel
use PhpOffice\PhpSpreadsheet\IOFactory;
//声明命名空间关键类
3、开始编写个类
require .'/Public/common/vendor/autoload.php';//引入公共目录下的excel导入导出类
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment as PHPExcel_Style_Alignment;
use PhpOffice\PhpSpreadsheet\Cell\DataType as PHPExcel_Cell_DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill as PHPExcel_Style_Fill;
use PhpOffice\PhpSpreadsheet\Style\Border as Border;
// --- 读取Excel
use PhpOffice\PhpSpreadsheet\IOFactory;
//声明命名空间关键类
class Office {
//Excel输出样式
/*
* $Excel 数组,传入excel导出后的样式或者模板
* $expTableData 一个二维数组
*/
public static function excelPut($Excel,$expTableData){
// $Excel['sheetTitle']=iconv('utf-8', 'gb2312',$Excel['sheetTitle']);
// ------------- 文件参数 -------------
$cellName = $Excel['cellName'];
$xlsCell = $Excel['xlsCell'];
$cellNum = count($xlsCell);//计算总列数
$dataNum = count($expTableData);//计算数据总行数
$spreadsheet = new Spreadsheet();
$sheet0 = $spreadsheet->getActiveSheet();
$sheet0->setTitle("Sheet1");
//设置表格标题A1
$sheet0->mergeCells('A1:'.$cellName[$cellNum-1].'1');//表头合并单元格
// ------------- 表头 -------------
// $sheet0->setCellValue('A1',"测试表头");
$sheet0->setCellValue('A1',$Excel['sheetTitle']);//.date("YmdHis") 不用时间
$sheet0->getStyle('A1')->getFont()->setSize(20);
$sheet0->getStyle('A1')->getFont()->setName('微软雅黑');
//设置行高和列宽
// ------------- 横向水平宽度 -------------
if(isset($Excel['H'])){
foreach ($Excel['H'] as $key => $value) {
$sheet0->getColumnDimension($key)->setWidth($value);
}
}
// ------------- 纵向垂直高度 -------------
if(isset($Excel['V'])){
foreach ($Excel['V'] as $key => $value) {
$sheet0->getRowDimension($key)->setRowHeight($value);
}
}
// ------------- 第二行:表头要加粗和居中,加入颜色 -------------
$sheet0->getStyle('A1')
->applyFromArray(['font' => ['bold' => false],'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
$setcolor = $sheet0->getStyle("A2:".$cellName[$cellNum-1]."2")->getFill();
$setcolor->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$colors=['00a000','53a500','3385FF','00a0d0','D07E0E','c000c0','0C8080','EFE4B0'];//设置总颜色
$selectcolor=$colors[mt_rand(0,count($colors)-1)];//获取随机颜色
$setcolor->getStartColor()->setRGB($selectcolor);
// ------------- 根据表格数据设置列名称 -------------
for($i=0;$i
$sheet0->setCellValue($cellName[$i].'2', $xlsCell[$i][1])
->getStyle($cellName[$i].'2')
->applyFromArray(['font' => ['bold' => true],'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
}
// ------------- 渲染表中数据内容部分 -------------
for($i=0;$i
for($j=0;$j
$sheet0->getStyle($cellName[$j].($i+3))->applyFromArray(['alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
$sheet0->setCellValueExplicit($cellName[$j].($i+3),$expTableData[$i][$xlsCell[$j][0]],PHPExcel_Cell_DataType::TYPE_STRING);
$sheet0->getStyle($cellName[$j].($i+3))->getNumberFormat()->setFormatCode("@");
}
}
// ------------- 设置边框 -------------
// $sheet0->getStyle('A2:'.$cellName[$cellNum-1].($i+2))->applyFromArray(['borders' => ['allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN]]]);
$styleArray = [
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
'color' => ['argb' => 'FF505050'],
],
],
];
$sheet0->getStyle('A2:'.$cellName[$cellNum-1].($i+2))->applyFromArray($styleArray);
//$sheet0->setCellValue("A".($dataNum+10)," ");//多设置一些行
// ------------- 输出 -------------
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
//header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
header("Content-Disposition: attachment;filename=".$Excel['fileName'].".xlsx");//告诉浏览器输出浏览器名称
header('Cache-Control: max-age=0');//禁止缓存
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
}
//Excel读取
public static function excelReader($uploadfile){
$inputFileType = IOFactory::identify($uploadfile);
$excelReader = IOFactory::createReader($inputFileType); //Xlsx
$PHPExcel = $excelReader->load($uploadfile); // 载入excel文件
$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
$sheetdata = $sheet->toArray();
return $sheetdata; // --- 直接返回数组数据
}
}
整个类文件就完成了,包含导出和导入
4、调用(以TP3.1为例)
function export(){//导出角色为Excel
import('ORG.Util.Office');//载入excel插件
$Spread=new Office();//实例化excel插件
$Db=M('role','sys_');
if(I('title')!=null || I('title')!=''){
$map['title']=array('like','%'.I('title').'%');
}
if(I('key')!=null || I('key')!=''){
$map['key']=array('like','%'.I('key').'%');
}
if((I('startTime')!=null || I('startTime')!='') && (I('endTime')!=null || I('endTime')!='')){
$map['createtime']=array('between',I('startTime').' 00:00:00 , '.I('endTime').' 23:59:59');
}elseif((I('startTime')!=null || I('startTime')!='') && (I('endTime')==null || I('endTime')=='')){
$map['createtime']=array('egt',I('startTime').' 00:00:00');
}elseif((I('startTime')==null || I('startTime')=='') && (I('endTime')!=null || I('endTime')!='')){
$map['createtime']=array('elt',I('endTime').' 23:59:59');
}
$list=$Db->field('id,title,key,createtime,sort,desc')->where($map)->select();
//设置excel导出参数
$Excel['fileName']="角色表".date('YmdHis',time());//or $xlsTitle
$Excel['cellName']=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN'];
$Excel['H'] = ['A'=>8,'B'=>20,'C'=>14,'D'=>20,'E'=>8,'F'=>20];//设置列宽度
$Excel['V'] = ['1'=>40,'2'=>23];//纵向行高度
$Excel['sheetTitle']="风之涯cms角色表";//设置大标题,自定义
$Excel['xlsCell']=[['id','序号'],['title','角色名称'],['key','角色识别码'],['createtime','创建时间'],['sort','排序'],['desc','备注']];//设置字段对应名称
$Spread->excelPut($Excel,$list);//传入excel插件执行导出并生成下载
}
如果是TP3.2以上版本
直接写命名空间
newspaces xxx;
use \xx\office;
class sss extends xx{
function xx(){
//直接调用office
}
}
整篇文章完成,如有改进,请多多交流 email 83560309@qq.com