> 使用composer安装： composer require phpoffice/phpspreadsheet
<?php
namespace app\index\controller;
use app\index\controller\Comm;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Exception;
use think\Request;
class Importsheet extends comm{
/**
* @var Excel文件名字
*/
private $sheet_filename;
/**
* @var Excel中的sheet的名字
*/
private $sheet_name;
/**
* @var array 输出Excel的表头
*/
private $sheet_firstline = [];
/**
* @var array 表的内容
*/
private $sheet_info = [];
/**
* Importsheet constructor.
* @param $filename 文件名
* @param $name sheet名
* @param $firstline 表头
* @param $info 表内容
*/
public function __construct($filename,$name,$firstline = [],$info = [])
{
parent::__construct();
$this->sheet_filename = $filename;
$this->sheet_name = $name;
$this->sheet_firstline = $firstline;
$this->sheet_info = $info;
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2020/12/23 16:08
* @param int $column_num
* @return mixed
* @Description：获取表格列数的字母
*/
public function getMaxColumn(int $column_num)
{
try{
if(empty($column_num)){
throw new Exception('column_num:列数为空~');
}
if(!is_int($column_num)){
throw new Exception('column_num:参数类型错误~');
}
if($column_num > 26*26 || $column_num < 0){
throw new Exception('最大列数：676列,最小列数：1列');
}
$column_word = ['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'];
//生成循环次数
$num = ceil($column_num/26);
for($c = 0; $c < $num; $c++) {
$first_word = $column_word[$c-1];
foreach($column_word as $key => $val){
if($c >= 1){
$word = $first_word.$column_word[$key];
}else{
$word = $column_word[$key];
}
$column[] = $word;
}
}
for($a = 0; $a < $column_num; $a++){
$new_column[] = $column[$a];
}
return $new_column;
}catch (Exception $e){
returnResponse(100,$e->getMessage());
}
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2020/12/23 17:54
* @Description：输出表
*/
public function outputSheet()
{
try{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
//设置sheet的名字
$sheet->setTitle($this->sheet_name);
//默认表头第一行
$k = 1;
//生成列的个数，根据表头个数来定
$column_num = count($this->sheet_firstline);
$info_field_num = count($this->sheet_info[0]);
if($column_num != $info_field_num){
throw new Exception('结果集列数和表头列数不一致~');
}
//生成表头上方的字母（最大676，最小1）
$column_word = $this->getMaxColumn($column_num);
//设置表头
for($i=0;$i<$column_num;$i++){
$sheet->setCellValue($column_word[$i].$k, $this->sheet_firstline[$i]);
}
//第二行开始插入数据
$k = 2;
//插入表格数据
foreach ($this->sheet_info as $key => $value) {
$b = 0;
for($a = 0; $a < $column_num; $a++){
$getvalbykey = array_values($value);
$sheet->setCellValue($column_word[$a].$k, $getvalbykey[$b]);
$b++;
}
$k++;
}
//文件名
$file_name = date('Y-m-d H:i:s', time()).'-'.rand(1000, 9999).'_'. $this->sheet_filename . ".xlsx";
//下载
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$file_name.'"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
}catch (Exception $e){
returnResponse(100,$e->getMessage());
}
}
}
使用方法TP框架为例：
$res = Db('company')->field('id,companys,business_remark,img1,img2,img3')->select();//查询的数据
$first_line = ['ID','公司','备注','图片一','图片二','图片三'];
$sheet = new Importsheet('下载的excel名','excel里面sheet名','表头','查询的结果集');
$sheet->outputSheet();
> 改变部分地方的语法，可以适用于任意框架；
> phpoffice使用介绍：http://www.honeyrh.com/index.php/archives/100/
$writer->save('文件路径');//设置文件保存路径，自己拼接路由地址
如果有更好的解决方法，欢迎留言指导。
补充：导出需要导出图片的处理方法
for($a = 0; $a < $column_num; $a++){
$getvalbykey = array_values($value);
/*写入图片*/
$files_arr = explode('.', $getvalbykey[$b]);
if(!empty($files_arr)){
$file_suffix = array_pop($files_arr);
strtolower($file_suffix);
$suffix = ['jpg', 'jpeg', 'gif', 'bmp', 'png','pdf','doc','docx','xlsx','xls'];
if(in_array($file_suffix,$suffix)){
$thumb_str = str_replace(request()->domain(),'',$getvalbykey[$b]);
$thumb = '/home/wwwroot/crm.sddjieshui.com/public'.$thumb_str;
if(file_exists($thumb)){
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing ->setName('图片');
$drawing ->setDescription('图片');
$drawing ->setPath($thumb);
$drawing ->setWidth(80);
$drawing ->setHeight(80);
$drawing ->setCoordinates($column_word[$a].$k);
$drawing ->setOffsetX(0);
$drawing ->setOffsetY(0);
$drawing ->setWorksheet($spreadsheet->getActiveSheet());
}
}else{
$sheet->setCellValue($column_word[$a].$k, $getvalbykey[$b]);
}
$b++;
}// $sheet->setCellValue($column_word[$a].$k, $getvalbykey[$b]);
// $b++;
导出图片替换上面方法，也可以做成开关的形式或者自动选择的形式，根据业务场景来定，后期有时间再重新更新一版可以灵活控制应用的，开箱即用，无需修改，相当nice
评论 (0)