• PHP Spreadsheet 导出 excel 服务封装


    PHP Spreadsheet 导出 excel 服务封装

    概述

    SpreadSheet Packagist 仓库地址
    PHPSpreadsheet - Read, Create and Write Spreadsheet documents in PHP - Spreadsheet engine

    1. 文件代码如下:

    点击查看代码
    	<?php
    
    	use PhpOfficePhpSpreadsheetSpreadsheet;
    	use PhpOfficePhpSpreadsheetWriterXlsx;
    
    	class ExcelExportService
    	{
    	    /**
    	     * @var Spreadsheet
    	     */
    	    public $excel;
    	    public $fileName;
    	    public $rootPath;
    	    public $storeDir = '/Files/Export/Tmp/';
    	    public $rowNum;
    	    public $header = array();
    	    public $body = array();
    
    	    public $fields = array();
    	    public $values = array();
    
    	    public function __construct($fileName)
    	    {
    		$this->excel = new Spreadsheet();
    
    		$arrFile = explode('.', $fileName);
    		$ext = $arrFile[1];
    		$baseName = $arrFile[0];
    
    		$this->fileName = $baseName . '_' . time() . random_int(1000, 9999) . '.' . $ext;
    		$this->rowNum = 1;
    	    }
    
    	    /**
    	     * 设置根目录
    	     * @param null $path
    	     */
    	    public function setRootPath($path = null)
    	    {
    		if (!empty($path)) {
    		    $this->rootPath = rtrim($path, '/');
    		} else {
    		    $this->rootPath = rtrim(fcommon::FilePath(), '/');
    		}
    	    }
    
    	    /**
    	     * 设置文件存放目, 相对于根目录
    	     * @param $pathName
    	     */
    	    public function setStorePath($pathName)
    	    {
    		if (!empty($pathName)) {
    		    $this->storeDir = rtrim($pathName, '/') . '/';
    		} else {
    		    $this->storeDir = $this->storeDir . date('Y') . '/' . date('m') . '/';
    		}
    	    }
    
    	    /**
    	     * 设置导出excel 头部
    	     * @param $header
    	     */
    	    public function setHeader($header)
    	    {
    		$this->header = $header;
    	    }
    
    	    /**
    	     * 设置导出excel 数据
    	     * @param $body
    	     */
    	    public function setBody($body)
    	    {
    		$this->body = $body;
    	    }
    
    	    /**
    	     * excel 写行数据
    	     * @param $row
    	     * @throws PhpOfficePhpSpreadsheetException
    	     */
    	    public function writeRow($row)
    	    {
    		$sheet = $this->excel->getActiveSheet();
    		$column = 0;
    		foreach ($row as $k => $value) {
    		    $sheet->setCellValueByColumnAndRow(++$column, $this->rowNum, $value);
    		}
    
    		$this->rowNum++;
    	    }
    
    	    /**
    	     * excel 写表头
    	     * @throws PhpOfficePhpSpreadsheetException
    	     */
    	    public function writeHeader()
    	    {
    		$this->writeRow($this->header);
    	    }
    
    	    /**
    	     * excel 写表数据
    	     * @throws PhpOfficePhpSpreadsheetException
    	     */
    	    public function writeBody()
    	    {
    		foreach ($this->body as $k => $row) {
    
    		    $filterRow = [];
    		    foreach($this->fields as $field)
    		    {
    			$value = $row[$field];
    			if(isset($this->values[$field]) && is_callable($this->values[$field]))
    			{
    			    $value = call_user_func($this->values[$field], $value, $row);
    			}
    			$filterRow[] = $value;
    		    }
    
    		    $this->writeRow($filterRow);
    		}
    	    }
    
    	    /**
    	     * 解析表头, 和 导出字段值得自定义设置
    	     * columns 格式 :
    	     * 匿名函数的参数: $value, 导出字段对应的原始值, $row, 导出原始字段所在的行数组
    	     * $columns = [
    	     *      'id'=>'序号',
    	     *      'name'=>[
    	     *          'label'=>'姓名',
    	     *          'value'=>function($value, $row){
    	     *              return $value
    	     *          }
    	     *      ]
    	     * ]
    	     * @param $columns array
    	     */
    	    public function parseColumns($columns)
    	    {
    		$header = [];
    		$fields = [];
    		$values = [];
    		foreach ($columns as $k => $item) {
    		    $fields[] = $k;
    
    		    if (is_string($item)) {
    			$header[] = $item;
    
    		    } elseif (is_array($item)) {
    			$header[] = $item['label'];
    			if(isset($item['value']) && is_callable($item['value']))
    			{
    			    $values[$k] = $item['value'];
    			}
    		    }
    		}
    
    		$this->header = $header;
    		$this->fields = $fields;
    		$this->values = $values;
    	    }
    
    	    /**
    	     * excel 设置列宽
    	     * @param $widths
    	     * @throws PhpOfficePhpSpreadsheetException
    	     */
    	    public function setWidths($widths)
    	    {
    		if(count($widths) == count($this->header))
    		{
    		    for($i=1; $i<=count($this->header); $i++)
    		    {
    			$this->excel->getActiveSheet()->getColumnDimensionByColumn($i)->setWidth($widths[$i-1]);
    		    }
    		}
    	    }
    
    	    /**
    	     * 设置导出文件的保存目录
    	     * @param $path string 相对于根目录的路径
    	     * @return string
    	     */
    	    public function setFilePath($path)
    	    {
    		$this->setRootPath();
    		$this->setStorePath($path);
    		$absPath = $this->rootPath . $this->storeDir . $this->fileName;
    
    		$arrPath = pathinfo($absPath);
    		if (!is_dir($arrPath['dirname'])) {
    		    mkdir($arrPath['dirname'], 0777, true);
    		}
    
    		return $absPath;
    	    }
    
    	    /**
    	     * 导出excel文件
    	     * @param null $path string 保存文件的目录, 相对于根目录
    	     * @throws PhpOfficePhpSpreadsheetException
    	     * @throws PhpOfficePhpSpreadsheetWriterException
    	     */
    	    public function save($path = null)
    	    {
    		if (empty($this->body)) {
    		    throw new Exception('没有待导出的数据', 501);
    		}
    
    		//设置文件保存目录
    		$absPath = $this->setFilePath($path);
    
    		//write header
    		$this->writeHeader();
    
    		//write body
    		$this->writeBody();
    
    		$write = new Xlsx($this->excel);
    		$write->save(fcommon::linuxStringUtf($absPath));
    
    		$this->excel->disconnectWorksheets();
    		unset($this->excel);
    	    }
    	    //自定义文件下载路径
    	    public function getFullFileUrl()
    	    {
    		$baseUrl = fcommon::FileUrl();
    
    		$fileUrl = $baseUrl . $this->storeDir . $this->fileName;
    
    		return $fileUrl;
    	    }
    	}
    

    2. 使用方式如下:

    ...
    
    $fileName = '教师信息导入错误记录.xlsx';
        $columns = [
            'name'=>'姓名',
            'idNumber'=>'身份证号',
            'error'=>'错误信息'
        ];
        $excel = new ExcelExportService($fileName);
        $widths = [10, 20, 60];
    
        $excel->parseColumns($columns);
        $excel->setBody($data);
        $excel->setWidths($widths);
    
        $excel->save();
    
        return $excel->getFullFileUrl();
    
    ...
  • 相关阅读:
    2013第五周上机任务【项目1 三角形类(构造函数)】
    Google搜索小技巧
    项目总结:文件上传(MVC uploadify)
    Oracle 触发器 Update 不能操作本表的疑问
    【笔试题STL】求两个vector的交集
    Zookeeper(六)数据模型
    是的,我们真的在遭遇有史以来最大的DDoS攻击,并且还在加剧
    在MFC框架下使用osg报内存泄露的解决办法
    NetBeans 时事通讯(刊号 # 107 Jun 25, 2010)
    VS 2008中的C/C++静态代码分析工具Prefast
  • 原文地址:https://www.cnblogs.com/zakun/p/php-spreadsheet.html
Copyright © 2020-2023  润新知