第一步：安装，使用composer安装扩展。
composer require phpoffice/phpexcel
第二步：引入 Spread.php 插件
<?php
/*
* 这个是PhpSpreadsheet的工具
*/
namespace Tools;
// --- 生成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 Spread
{
/**
* 样例
* @access public
* @return null
*/
public function putdemo(){
$fileName = "test";
$Excel['fileName']=$fileName.date('Y年m月d日-His',time());//or $xlsTitle
$Excel['cellName']=['A','B','C'];
$Excel['H'] = ['A'=>22,'B'=>12,'C'=>30];//横向水平宽度
$Excel['V'] = ['1'=>40,'2'=>26];//纵向垂直高度
$Excel['sheetTitle']=$fileName;//大标题，自定义
$Excel['xlsCell']=[['majorid','专业号'],['majorshort','专业简称'],
['majorname','专业名称']];
$expTableData = db("major")->select();
$this::excelPut($Excel,$expTableData);
}
/**
* Excel输出
* @access public
* @param array $Excel Excel相关配置信息
* @param array $expTableData 表格里面的数据
* @return file
*/
public static function excelPut($Excel,$expTableData){
// ------------- 文件参数 -------------
$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',$Excel['sheetTitle']);
$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','0C8080','EFE4B0','8db4e2','00b0f0','0fb746'];//设置总颜色
$selectcolor=$colors[mt_rand(0,count($colors)-1)];//获取随机颜色
$setcolor->getStartColor()->setRGB($selectcolor);
// ------------- 根据表格数据设置列名称 -------------
// for($i=0;$i<$cellNum;$i++){
// $sheet0->setCellValue($cellName[$i].'2', $xlsCell[$i][1])
// ->getStyle($cellName[$i].'2')
// ->applyFromArray(['font' => ['bold' => true],'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
// }
foreach ($xlsCell as $key => $value) {
$sheet0->setCellValue($cellName[$key].'2', $value[1])
->getStyle($cellName[$key].'2')
->applyFromArray(['font' => ['bold' => true],'alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
}
// ------------- 渲染表中数据内容部分 -------------
// for($i=0;$i<$dataNum;$i++){
// for($j=0;$j<$cellNum;$j++){
// $sheet0->getStyle($cellName[$j].($i+3))->applyFromArray(['alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'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("@");
// }
// }
foreach ($expTableData as $keyi => $valuei) {
foreach ($xlsCell as $keyj => $valuej) {
$sheet0->getStyle($cellName[$keyj].($keyi+3))->applyFromArray(['alignment' => ['horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_CENTER]]);
$sheet0->setCellValueExplicit($cellName[$keyj].($keyi+3),$valuei[$valuej[0]],PHPExcel_Cell_DataType::TYPE_STRING);
$sheet0->getStyle($cellName[$keyj].($keyi+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].($keyi+3))->applyFromArray($styleArray);
//$sheet0->setCellValue("A".($dataNum+10)," ");//多设置一些行
// 冻结表格
if(isset($Excel['freeze'])){
$sheet0->freezePane($Excel['freeze']);
}
// ------------- 输出 -------------
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读取
* @access public
* @param string $filePath Excel文件存放的路径
* @return array
*/
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; // --- 直接返回数组数据
}
}
引用插件
use Tools\Spread;
导出excel
public function export_info(){
$expTableData = Db::name('list')->alias('a')
->join('department b','a.department_id = b.department_id')
->field('a.*,b.department_name')
->select();
$manager_id = Session::get('user_id');
$manager_name = Db::name('list')->where('userid',$manager_id)->value('username');
$Excel['fileName']=$manager_name.date('Y年m月d日-His',time());//or $xlsTitle
$Excel['cellName']=['A','B','C','D','E','F','G'];
$Excel['H'] = ['A'=>22,'B'=>22,'C'=>28,'D'=>22,'E'=>22,'F'=>22,'G'=>22];//横向水平宽度
$Excel['V'] = ['1'=>40,'2'=>26];//纵向垂直高度
$Excel['sheetTitle']="员工积分信息";//大标题，自定义
$Excel['xlsCell']=[
['username','姓名'],
['mobile','手机号'],
['points','当前积分'],
['finance_points','高级积分'],
['total_points','总积分'],
['department_name','部门'],
['position','岗位'],
];
insert_logs("下载员工积分数据");
Spread::excelPut($Excel,$expTableData);
}OK完成！
评论 (0)