第二版 使用PHPoffice实现普通的导出功能
<?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{
private $sheet_filename;
private $sheet_name;
private $sheet_firstline = [];
private $sheet_info = [];
private $imgSwitch;
private $switch;
private $path = "uploads/excel/";
/**
* Importsheet constructor.
* @param $filename 文件名
* @param $name sheet名
* @param $firstline 表头
* @param $info 表内容
* @param $imgSwitch 图片开关
* @param $switch 返回下载地址还是直接返回文件
*/
public function __construct($filename='Default',$name='sheet1',$firstline = [],$info = [],$imgSwitch = false, $switch = false)
{
parent::__construct();
$this->sheet_filename = $filename;
$this->sheet_name = $name;
$this->sheet_firstline = $firstline;
$this->sheet_info = $info;
$this->imgSwitch = $imgSwitch;
$this->switch = $switch;
}
/**
* @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){
if($column_num > $info_field_num){
$better_column_info = '表头多';
}else{
$better_column_info = '数据列多';
}
throw new Exception('结果集列数和表头列数不一致~'.$better_column_info);
}
//生成表头上方的字母（最大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);
if($this->imgSwitch){
/*写入图片*/
$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)){
$str_thumb = str_replace($this->request->domain(),'',$getvalbykey[$b]);
$thumb = '/home/wwwroot/xphbk/public'.$str_thumb;
if($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++;
}
}else{
$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');
if($this->switch == false){
$path = self::createPath($this->path);
$writer->save($path.$file_name);
// $url = 'http://'.$_SERVER['SERVER_NAME'].'/public/'.$path.$filename;
$url = 'http://'.$_SERVER['SERVER_NAME'].'/'.$path.$file_name;
}else{
return $writer->save('php://output');
}
return $url;
}catch (Exception $e){
returnResponse(100,$e->getMessage());
}
}
/**
* @Author: 小破孩嫩
* @Email: 3584685883@qq.com
* @Time: 2021/4/12 13:36
* @param $path
* @Description：设置路径判断是否存在，不存在创建
*/
private function createPath($path){
$Month = date('Ym',time());
$Day = date('d',time());
$path = $this->path.$Month.'/'.$Day.'/';
if(!is_dir($path)){
header("Content-type:text/html;charset=utf-8");
$res = mkdir(iconv("UTF-8", "GBK", $path),0777,true);
if(!$res){
throw new Exception("创建目录失败");
}
}
return $path;
}
}使用图片导出的时候注意限制条数，图片导出比较费时间，在我自己的项目中，每次导出大概有五百条数据，是没有问题，具体的还要各位使用者再测
使用方法：复制粘贴+稍微改改，就能用了
评论