lachang/app/common/service/Export.php

<?php
// +----------------------------------------------------------------------
// | 萤火商城系统 [ 致力于通过产品和服务，帮助商家高效化开拓市场 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2017~2021 https://www.yiovo.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed 这不是一个自由软件，不允许对程序代码以任何形式任何目的的再发行
// +----------------------------------------------------------------------
// | Author: 萤火科技 <admin@yiovo.com>
// +----------------------------------------------------------------------
declare (strict_types = 1);

namespace app\common\service;

use app\common\model\Express;
use app\common\model\member\MemberCooperationCoupon;
use app\store\model\OrderGoods as OrderGoodsModel;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * 短信服务类
 * Class Order
 * @package app\common\service
 */
class Export extends BaseService {

    /**
     * 导出示例
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function testExport(){
        $data = [
            ['title'=>'张三', 'content' => '张三的内容', 'prices' => '100', 'create_time' => '2017.10.12', 'end_time' =>'2020.12.12'],
            ['title'=>'李四', 'content' => '李四的内容', 'prices' => '222', 'create_time' => '2017.10.12', 'end_time' =>'2020.12.12'],
            ['title'=>'王莽', 'content' => '王莽的内容', 'prices' => '343', 'create_time' => '2017.10.12', 'end_time' =>'2022.03.12'],
        ];
        $header = ['名称', '内容', '金额', '开始时间', '结束时间'];

        self::export($data, $header, $title = 'test', $filename = 'test', $file_type = 'Csv');
    }

    /**
     * 下载导出文件
     * @param array $data 数据二维数组
     * @param array $header 表头
     * @param string $filename 文件名称
     * @param string $title excel标题
     * @param string $format
     * @param bool $isDelivery 发货导出
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function export(array $data, array $header, $filename = '导出订单', $title = 'test', $format = 'Xls',$isDelivery=false)
    {
        $expressOptions = '';
        if ($isDelivery == true){
            $expressOptions = Express::getExpressNames();
        }

        set_time_limit(0);
        $title_key = [];
        foreach ($data[0] as $key=>$val){
            $title_key[] = $key;
        }
        $unit = array(
            '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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN',
            'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
        );
        $line_count = count($header);

        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle($title);  //设置当前sheet的标题

        $n = 1;
        for ($i = 0; $i < $line_count; ++$i){
            $objSheet->setCellValue($unit[$i].$n, $header[$i]);
            foreach ($data as $key=>$val){
                $objSheet->getColumnDimension($unit[$i])->setAutoSize(true);//自动计算列宽--有问题不会自动
//                $objSheet->getColumnDimension($unit[$i])->setWidth(20);//固定列宽
                $m = $key + 2;
                if ($isDelivery == true && $unit[$i] == "K"){//表示是快递公司列
                    self::setValidation($objSheet,$unit[$i] . $m,$expressOptions);
                }
                if ($unit[$i] == "N" || $unit[$i] == "O" || $unit[$i] == "P"){
                    $objSheet->getStyle($unit[$i].$m)->getAlignment()->setWrapText(true);
                }
                $objSheet->getStyle($unit[$i].$m)->getAlignment()->setWrapText(true);//自动换行 使用换行 PHP_EOL或者\n
                $styleArray = [
                    'alignment' => [
//                        'horizontal' => Alignment::HORIZONTAL_LEFT,//左右位置
                        'vertical' => Alignment::VERTICAL_CENTER,//上下位置
                    ],
                ];
                $objSheet->getStyle($unit[$i].$m)->applyFromArray($styleArray);
                $objSheet->setCellValueExplicit($unit[$i] . $m, $val[$title_key[$i]],DataType::TYPE_STRING);
            }
        }

        ob_end_clean() ;
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }else{
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        }

        $filename = $filename .'-'. date('YmdHis') . '.' . strtolower($format);
        header("Content-Disposition: attachment;filename=" . $filename);
        header('Cache-Control: max-age=0');
        header('Access-Control-Allow-Origin:*');
        $objWriter = IOFactory::createWriter($newExcel, $format);
        ob_start(); //打开缓冲区
        $objWriter->save('php://output');
        $xlsdata =ob_get_contents();
        ob_end_clean();
        $data = [];
        $data['filename'] = $filename;
        $data['file'] = "data:application/vnd.ms-excel;base64,".base64_encode($xlsdata);
        return $data;

        //通过php保存在本地的时候需要用到
//        $objWriter->save($filename);

        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
//        header('Cache-Control: max-age=1');
//        //If you're serving to IE over SSL, then the following may be needed
//        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
//        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
//        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
//        header('Pragma: public'); // HTTP/1.0
//        exit;
    }

    public  static function setValidation(Worksheet $sheet, $cellPoint, $format)
    {
        $validation = $sheet -> getCell($cellPoint) -> getDataValidation();
        $validation -> setType(DataValidation::TYPE_LIST);
        $validation -> setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation -> setAllowBlank(false);
        $validation -> setShowInputMessage(true);
        $validation -> setShowErrorMessage(true);
        $validation -> setShowDropDown(true);
        $validation -> setErrorTitle('输出错误');
        $validation -> setError('值不在列表中');
        $validation -> setPromptTitle('请选择');
        $validation -> setPrompt('');
        $validation -> setFormula1('"'.$format.'"');
        // $validation->setFormula1('=major');//使用某个公式
        //$validation->setFormula1('"顺丰,圆通,韵达"');//可以直接写列表，用逗号分隔,最多255字符
        // $objValidation->setFormula1('=INDIRECT(F5)');//indirect间接的，以某个坐标为依托，联级
    }


    public static function importDemo(){


        $expressOptions = Express::getExpressNames();
        //dd($expressOptions);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        //self::setValidation($sheet,'A1',null);
        $unit = array(
            '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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN',
            'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
        );
        $n = 1;
        for ($i = 0; $i < 10; ++$i){
            $sheet->getColumnDimension($unit[$i])->setAutoSize(true);

            $sheet->setCellValue($unit[$i].$n, 'hearder'.$unit[$i]);
            for ($j=0;$j<5;$j++){
                $m = $j+2;

                if ($unit[$i] == "A"){
                    self::setValidation($sheet,$unit[$i] . $m,$expressOptions);
                }
                $sheet->setCellValueExplicit($unit[$i] . $m, "顺丰",DataType::TYPE_STRING);
            }

        }

        $writer = new Xlsx($spreadsheet);
        $name = 'hello'.rand(10000,99999).'.xlsx';
        $writer->save($name);
    }

    /**
     * 导入批量发货
     * @param $fileName
     * @return int[]
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function import($fileName){
        $reader = new Xls();
        $file = runtime_root_path().'/storage/'.$fileName;
        $excel = $reader->load($file);

        //$sheetCount = $excel->getSheetCount();
        $sheet = $excel->getSheet(0);

        $sheetRows = $sheet->getHighestRow();
        $exp = Express::getAllList();

        $expEs = array_column($exp,'express_name','express_id');

        $failure = $success = 0;
        $model = new OrderGoodsModel();

        for ($i = 2; $i <= $sheetRows; $i++) {
            //order_goods_id
            $orderGoodsId = trim($sheet->getCell('A' . $i)->getValue()??'');
            //快递公司
            $expressName = trim($sheet->getCell('K' . $i)->getValue()??'');
            if (!$expressName){
                $failure += 1;
                continue;
            }

            $expressId = array_search($expressName,$expEs);
            if ($expressId == false){
                $failure += 1;
                continue;
            }

            //快递单号
            $expressNo = trim($sheet->getCell('L' . $i)->getValue()??'');
            if (!$expressNo){
                $failure += 1;
                continue;
            }
            if ($model->delivery(['order_goods_id'=>[$orderGoodsId],'express_id'=>$expressId,'express_no'=>$expressNo,'is_update'=>0])) {
                $success += 1;
            }else{
                $failure += 1;
            }
        }
        return ['failure'=>$failure,'success'=>$success];
    }


    /**
     * 导入批量发货
     * @param $welfareId
     * @param $fileName
     * @return bool
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     */
    public static function importCooperationCodes($welfareId,$fileName): bool
    {
        if (!$fileName)return false;
        //$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
        $reader = new Xls();
        $excel = $reader->load($fileName);

        //$sheetCount = $excel->getSheetCount();
        $sheet = $excel->getSheet(0);
        $sheetRows = $sheet->getHighestRow();
        $datas = [];
        $now = time();
        $model = new MemberCooperationCoupon();

        for ($i = 2; $i <= $sheetRows; $i++) {
            $datas[] = [
                        'coupon_id'=>trim(strval($sheet->getCell('B' . $i)->getValue()??'')),
                        'coupon_name'=>trim(strval($sheet->getCell('C' . $i)->getValue()??'')),
                        'create_time'=>$now,
                        'update_time'=>$now,
                        'welfare_id'=> $welfareId,
                        'code'=>trim(strval($sheet->getCell('D' . $i)->getValue()??'')),
                        'provider'=>trim(strval($sheet->getCell('E' . $i)->getValue()??'')),
            ];
            if (count($datas) >3000){
                $model->insertAll($datas);
                unset($datas);
                $datas = [];
            }
        }
        if (count($datas)){
            $model->insertAll($datas);
        }

        return true;
    }

}