PhpOffice\PhpSpreadsheet导出excel列表

基本使用调用

$keys = ['姓名', '年龄'];
$fields = ['name', 'age'];
$list = [
	['name' => 'clown', 'age' => 18],
	['name' => '小苏', 'age' => 25]
];
$result = (new ExportDataTool())->exportExecl($keys, $fields, $list, 'test');

ExportDataTool工具类

<?php

namespace app\common\tool;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

include_once "./../vendor/autoload.php";
/**
 * 数据导出
 * Class Aes
 * @package app\common\tool
 */
class ExportDataTool
{

    /**
     * @var string 导出数据根目录
     */
    public $export_path = './export';

    /**
     * 导出excel列表
     * @param $keys array 对应列名称, 要和list里面的一一对应 举例: ['姓名', '年龄']
     * @param array $fields 对应字段值 要和$keys里面一致    举例: ['name', 'age']
     * @param $list array 列表数据 举例: [['name' => 'clown', 'age' => 18]]
     * @param $file_name string 文件名称
     * @param $title string sheet标题
     * @return bool|string
     */
    public function exportExecl($keys = [], $fields = [], $list = [], $file_name = '', $title = '')
    {
        if(empty($keys)) return false;
        if(empty($list)) return false;
        if(empty($fields)) return false;

        $columns = $this->getColumnArr($keys);

        $date = date('Y-m-d H:i:s');

        if(empty($file_name)){
            $file_name = $date . '.xlsx';
        }else{
            $file_name = $file_name . '-' . $date . '.xlsx';
        }

        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);

        if(!empty($title)){
            $objActSheet->setTitle($title);//设置excel的标题
        }

        //写入列抬头
        foreach ($columns as $key => $column){
            $objActSheet->setCellValue($column . '1', $keys[$key]);
        }

        $i = 2;//因为从第二行开始

        foreach ($list as $k => $v){
            foreach ($fields as $kk => $vv){
                $objActSheet->setCellValueByColumnAndRow($kk + 1, $i, $v[$vv]);
            }
            ++$i;
        }

        $dir_name = $this->export_path . '/' . date('Ymd') . '/';

        $this->createDir($dir_name);

        $new_dir_name = str_replace('.', '', $dir_name);

        $objWriter->save($dir_name . $file_name);

        return '你的域名:' . $new_dir_name;
    }

    /**
     * 获取列数组
     * @param $keys 要获取列名的数组
     * @return void
     */
    public function getColumnArr($keys = [])
    {
        $length = count($keys);

        $column = [];

        for ($i = 0; $i < $length; $i++){
            $column[] = $this->getColumnName($i);
        }

        return $column;
    }

    /**
     * 获取生成列名
     * @param $keys 对应列名称
     * @return void
     */
    public function getColumnName($pColumnIndex = 0)
    {
        static $_indexCache = array();

        if (!isset($_indexCache[$pColumnIndex])) {

            if ($pColumnIndex < 26) {

                $_indexCache[$pColumnIndex] = chr(65 + $pColumnIndex);

            } elseif ($pColumnIndex < 702) {

                $_indexCache[$pColumnIndex] = chr(64 + ($pColumnIndex / 26)) .

                    chr(65 + $pColumnIndex % 26);

            } else {

                $_indexCache[$pColumnIndex] = chr(64 + (($pColumnIndex - 26) / 676)) .

                    chr(65 + ((($pColumnIndex - 26) % 676) / 26)) .

                    chr(65 + $pColumnIndex % 26);

            }

        }

        return $_indexCache[$pColumnIndex];

    }

    /**
     * 创建文件夹
     * @param $dir_name 文件夹名称
     * @return void
     */
    public function createDir($dir_name = '')
    {
        if(!is_dir($this->export_path)){
            mkdir($this->export_path, 0777);
        }

        if(!is_dir($dir_name)){
            mkdir($dir_name, 0777);
        }
    }
}