TP6|TP5.1 PHPoffice导出|导入

插件合集

点击跳转

安装

composer require phpoffice/phpspreadsheet

TP6基类

<?php
/**
 * Created by PhpStorm.
 * User: Zhangyongfeng
 * Date: 2020/12/1
 * Time: 11:31
 *
 * ━━━━━━━━━神兽出没━━━━━━━━━
 *
 *        ┏┓   ┏┓+ +
 *       ┏┛┻━━━┛┻┓ + +
 *       ┃       ┃  
 *       ┃   ━   ┃ ++ + + +
 *       ████━████ ┃+
 *       ┃       ┃ +
 *       ┃   ┻   ┃
 *       ┃       ┃ + +
 *       ┗━┓   ┏━┛
 *         ┃   ┃           
 *         ┃   ┃ + + + +
 *         ┃   ┃    Code is far away from bug with the animal protecting       
 *         ┃   ┃ +     神兽保佑,代码无bug  
 *         ┃   ┃
 *         ┃   ┃  +         
 *         ┃    ┗━━━┓ + +
 *         ┃        ┣┓
 *         ┃        ┏┛
 *         ┗┓┓┏━┳┓┏┛ + + + +
 *          ┃┫┫ ┃┫┫
 *          ┗┻┛ ┗┻┛+ + + +
 *
 * ━━━━━━━━━感觉萌萌哒━━━━━━━━━
 */

namespace zyf;

use think\facade\Filesystem;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Excel
{

    static public function returnData($code = '', $msg = '', $data = []){
        return \app\common\controller\Base::returnData($code, $msg, $data);
    }

    /**
     * [exportExcel 导出表格]
     * @param string $fileName excel表的表名
     * @param array  $arr 要导出excel表的数据,接受一个二维数组
     * @param array  $headAr excel表的表头,接受一个一维数组
     * @param array  $keyAr $arr中对应表头的键的数组,接受一个一维数组
     * @return file 到uploads目录下
     * @author zyf <1322816443@qq.com>
     */
    public function exportExcel($fileName = '表格', $arr = [], $headAr = [], $keyAr = [])
    {
        // 计算所需表头数量
        $count = count($headAr)-1;

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置列
        $rowAr = self::setRowAr($count);
        foreach ($rowAr as $k => $v) {
            if($k > $count) break;
            $sheet->setCellValue($v.'1', $headAr[$k]);
        }

        // 写入值
        foreach ($arr as $k => $v) {
            foreach ($rowAr as $ke => $ve){
                if($ke > $count) break;
                $sheet->setCellValue($ve.($k+2), $v[$keyAr[$ke]]);      // 给单元格设置值
                $spreadsheet->getActiveSheet()->getColumnDimension($ve)->setWidth(10); // 固定列宽,看着更整齐
            }
        }
        // 冻结首行
        $spreadsheet->getActiveSheet()->freezePaneByColumnAndRow(0,2);

        // 在输出Excel前,缓冲区中处理BOM头
        ob_end_clean();
        ob_start();

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$fileName.date('_Ymd_Hi',time()).'.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        // 删除清空:
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    /**
     * [importExcel 导入数据]
     * @return array
     * @author zyf <1322816443@qq.com>
     * */
    public function importExcel()
    {
        set_time_limit(0);

        // 文件上传导入
        $res = self::uploadFileImport();
        if($res['code'] == 999){ return $res; }

        // 进行读取
        $spreadsheet = IOFactory::load($res['data']);
        $sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        return $sheetData;
    }

    /**
     * [uploadFileImport 导入文件(数据不入库)]
     * @param file file 文件
     * @return array
     * @author zyf <1322816443@qq.com>
     * */
    public function uploadFileImport()
    {
        $param = request()->param();
        $file = request()->file('file');
        if ($file == null) return $this::returnData(999, '请上传文件');
        // 上传路径
        $disk = !empty($param['disk']) ? $param['disk'] : 'excel';
        // 文件名称
        $saveName = Filesystem::disk($disk)->putFile('', $file);
        $savePath = str_replace(date('Ymd', time()) .'\\', '', '/uploads/' . $disk . "/" . date('Ymd', time()) . "/" . $saveName);
        return $this::returnData(200, '上传成功', $savePath);
    }

    /**
     * [setRowAr 设置列]
     * @param int $count 列总数
     * @return array
     * @author zyf <1322816443@qq.com>
     * */
    public function setRowAr($count = 26)
    {
        $indData = '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';
        $indData = explode(',',$indData);
        $curCount = 26;
        for ($i = 0; $i <26 ; $i++) {
            for ($j = 0; $j < 26 ; $j++) {
                if($curCount >= $count) return $indData;
                $indData[] = $indData[$i].$indData[$j];
                $curCount++;
            }
        }
        return $indData;
    }

}

TP5.1基类

<?php
/**
 * Created by PhpStorm.
 * User: Zhangyongfeng
 * Date: 2020/12/1
 * Time: 11:31
 *
 * ━━━━━━━━━神兽出没━━━━━━━━━
 *
 *        ┏┓   ┏┓+ +
 *       ┏┛┻━━━┛┻┓ + +
 *       ┃       ┃  
 *       ┃   ━   ┃ ++ + + +
 *       ████━████ ┃+
 *       ┃       ┃ +
 *       ┃   ┻   ┃
 *       ┃       ┃ + +
 *       ┗━┓   ┏━┛
 *         ┃   ┃           
 *         ┃   ┃ + + + +
 *         ┃   ┃    Code is far away from bug with the animal protecting       
 *         ┃   ┃ +     神兽保佑,代码无bug  
 *         ┃   ┃
 *         ┃   ┃  +         
 *         ┃    ┗━━━┓ + +
 *         ┃        ┣┓
 *         ┃        ┏┛
 *         ┗┓┓┏━┳┓┏┛ + + + +
 *          ┃┫┫ ┃┫┫
 *          ┗┻┛ ┗┻┛+ + + +
 *
 * ━━━━━━━━━感觉萌萌哒━━━━━━━━━
 */

namespace zyf;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Excel
{

    /**
     * [exportExcel 导出表格]
     * @param string $fileName excel表的表名
     * @param array  $arr 要导出excel表的数据,接受一个二维数组
     * @param array  $headAr excel表的表头,接受一个一维数组
     * @param array  $keyAr $arr中对应表头的键的数组,接受一个一维数组
     * @return mixed 到uploads目录下
     * @author zyf <1322816443@qq.com>
     */
    public function exportExcel($fileName = '表格', $arr = [], $headAr = [], $keyAr = [])
    {
        // 计算所需表头数量
        $count = count($headAr)-1;

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置列
        $rowAr = self::setRowAr($count);
        foreach ($rowAr as $k => $v) {
            if($k > $count) break;
            $sheet->setCellValue($v.'1', $headAr[$k]);
        }

        // 写入值
        foreach ($arr as $k => $v) {
            foreach ($rowAr as $ke => $ve){
                if($ke > $count) break;
                $sheet->setCellValue($ve.($k+2), $v[$keyAr[$ke]]);      // 给单元格设置值
                $spreadsheet->getActiveSheet()->getColumnDimension($ve)->setWidth(10); // 固定列宽,看着更整齐
            }
        }
        // 冻结首行
//        $spreadsheet->getActiveSheet()->freezePaneByColumnAndRow(0,2);

        // 在输出Excel前,缓冲区中处理BOM头
        ob_end_clean();
        ob_start();

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$fileName.date('_Ymd_Hi',time()).'.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        // 删除清空:
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    /**
     * [setRowAr 设置列]
     * @param int $count 列总数
     * @return array
     * @author zyf <1322816443@qq.com>
     * */
    public function setRowAr($count = 26)
    {
        $indData = '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';
        $indData = explode(',',$indData);
        $curCount = 26;
        for ($i = 0; $i <26 ; $i++) {
            for ($j = 0; $j < 26 ; $j++) {
                if($curCount >= $count) return $indData;
                $indData[] = $indData[$i].$indData[$j];
                $curCount++;
            }
        }
        return $indData;
    }

}

导出后台

// 导出excel-实现
    public function export()
    {
        // 获取数据
        $systemMod = new \app\common\model\System();
        $data = $systemMod->getAll('');
        // 设置表头
        $headAr = [
            '姓名',
            '邮箱',
            '电话',
        ];
        $keyAr = [
            'name',
            'email',
            'tel',
        ];
        $phpOffice = new Excel();
        $phpOffice->exportExcel('数据', $data, $headAr, $keyAr);
    }

导入后台

    // 导入excel-实现
    public function import()
    {
        if(IS_POST){
            $phpOffice = new Excel();
            $data = $phpOffice->importExcel();
            halt($data);
        }
        return $this->render();
    }

导入前端

导入前台用的layui

    <div class="layui-form-item">
        <label class="layui-form-label">上传Excel:</label>
        <div class="layui-input-block">
            <div class="layui-upload">
                <button type="button" class="layui-btn upload_excel">Excel</button>
            </div>
        </div>
    </div>

导入JS

// 导入上传
upload.render({
    elem: '.upload_excel'
    , url: 'import'
    , multiple: false
    , accept: 'file'
    , before: function(obj){ layer.load(1); }
    , done: function (res) {
        console.log(res)
    }, error: function(index, upload){
        layer.closeAll('loading');
    }
})

 

上一篇:TP6解决跨域问题


下一篇:thinkPHP6(TP6)的安装及使用