station-test-api/catch/hydraulic/server/ExcelServer.php

<?php
/*
 * @Descripttion: 
 * @version: 1.0.0
 * @Author: likang
 * @Date: 2022-08-05 10:48:47
 * @LastEditors: likang
 * @LastEditTime: 2022-08-08 16:17:18
 */

namespace catchAdmin\hydraulic\server;

use catcher\base\CatchRequest;
use catcher\CatchResponse;
use catcher\library\excel\ExcelContract;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Db;

class ExcelServer implements ExcelContract
{


    //私有对象
    private function __construct()
    {
    }

    public static function getInterface()
    {

        return new ExcelServer();
    }

    /**
     * @Descripttion: 
     * @name: likang
     * @param {CatchRequest} $request
     * @param {*} $map [[key=>'e','value'=>'属性']]
     * @param  $model 数据库模型
     * @return {*}
     */
    function importExcel(CatchRequest $request, $map, $func, $install)
    {
        $url = $request->post('url');
        if (!$url) {
            return CatchResponse::fail('请上传文件');
        }

        $creator_id = $request->post('creator_id');
        //解析地址
        $parse_url = parse_url($url)['path'];
        //载入excel表格 
        $objPHPExcel = IOFactory::load(public_path() . $parse_url);
        $sheetCount = $objPHPExcel->getSheetCount();
        $fail = 0; //失败条数
        $success = 0; //成功条数
        $total = 0; //总导入数
        $excel_data = [];
        //循环读取每一张表
        for ($index = 0; $index < $sheetCount; $index++) {
            //设置当前要读取的表
            $sheet = $objPHPExcel->getSheet($index);   //excel中的第一张sheet
            // var_dump($sheet);exit;
            $highestRow  = $sheet->getHighestRow();       // 取得总行数
            // var_dump($highestRow);
            if ($highestRow <= 2) {
                continue;
            }
            $total += $highestRow - 3;
            for ($j = 4; $j <= $highestRow - 1; $j++) {

                $data = array(); //每条门店信息

                foreach ($map as $item) {

                    $data[$item['value']] = trim($sheet->getCell($item['key'] . $j)->getFormattedValue());
                }
                $data = $func($data);
                //校验数据是否重复
                if (!$data) {
                    continue;
                }
                array_push($excel_data, $data);
            }
        }

        // var_dump($excel_data);
        // return CatchResponse::success();
        //防止Excel有重复，去重
        array_unique($excel_data, SORT_REGULAR);
        $count = 0;
        Db::startTrans();
        try {
            foreach ($excel_data as $item) {
                $install($item);
                $count++;
            }
        } catch (\Exception $e) {
            Db::rollback();
            return CatchResponse::success(['error' => true, 'msg' => $e->getMessage()]);
        }
        //提交事务
        Db::commit();
        return CatchResponse::success('共' . $total . '条数据,成功' . $count . '条，失败' . $fail . '条');
    }


    //导出
    public function sheets()
    {
        // TODO: Implement sheets() method.
        return Db::name('user')->field(['username', 'email'])->limit(100)->cursor();
    }
    //设置头部
    public function headers(): array
    {
        // TODO: Implement headers() method.
        return [
            '物料号', '液压工具', '设备类别', '工具名称', '设备型号（智能）', '发放单位', '固定资产编号', '类固资产编号', '序列号', '出厂编号', '上次检验日期', '下次检验日期', '状态
（已校验、待校验、已报废）', '状态'
        ];
    }
}