PhpSpreadsheet - 导出-入

 

 

    /**
     * 导出
     * @param SearchRequest $searchRequest
     * @param ResponseInterface $response
     * @return Psr7ResponseInterface
     */
    public function GoodsExp(SearchRequest $searchRequest, ResponseInterface $response): Psr7ResponseInterface
    {
        $mobile     = $searchRequest->post('mobile', '');
        $name       = $searchRequest->post('name', '');
        $goodsName  = $searchRequest->post('goods_name', '');
        [$startDate, $endDate] = $searchRequest->post('trial_at', ['', '']);
        $data       = $this->PhysicalAuditLogic->GoodsExp(
            $mobile,
            $name,
            $goodsName,
            $startDate,
            $endDate
        );
        return $response->download(Upload::PHARMACY_PHYSICAL_PATH.$data);
    }

    /**
     * @param RequestInterface $request
     * @return array
     */
    public function GoodsImp(RequestInterface $request)
    {
        if (!$request->hasFile('file')) {
            throw new ImportException(ErrImport::FILE_NOT_EXISTS);
        }
        $file = $request->file('file');

        $this->PhysicalAuditLogic->import($file);

        return $this->success();
    }

　　

 

    /**
     * @param $id
     * @return string
     */
    public function GoodsExp(
        $mobile,
        $name,
        $goodsName,
        $startDate,
        $endDate
    )
    {

        $uids = $goodsIds = [];

        if (($mobile || $name) && ! $uids = $this->PointExchangeOrderRepository->GetIdsByMobileName($mobile, $name)) {
            return [0, []];
        }

        if ($goodsName && ! $goodsIds = $this->PointExchangeOrderRepository->GetIdsByName($goodsName)) {
            return [0, []];
        }

        $data =  $this->PointExchangeOrderRepository->ExpList(
            $uids,
            $goodsIds,
            $startDate,
            $endDate
        );

        $ls = $data->toArray();

        $list = $this->FormatPharmacyGoodsAudit($ls);

        if(empty($list)) {
            throw new ImportException(ErrImport::NO_DATA_EXPORT);
        }

        $heat = [['id'=>'ID','name'=>'NAME','mobile'=>'MOBILE','created_at'=>'CREATED_AT','goods_name'=>'GOODS_NAME','num'=>'NUM','service_point'=>'SERVICE_POINT','money_point'=>'MONEY_POINT','state'=>'STATE']];

        $exportData['item']     = array_merge($heat, $list);

        $exportData['key_head'] = ['ID','用户名','手机号','申请日期','商品名称','购买数量','服务积分','消费积分','状态'];

        // 生成文件并下载
        $fileName = self::createAgentExportExcel($exportData);

        return $fileName;
    }

    public static function createAgentExportExcel($data)
    {

        $spreadsheet = new Spreadsheet();


        # 设置第4行 A到I列背景和字体颜色
        $spreadsheet->getActiveSheet()->getStyle('A4:I4')->getFont()->getColor()->setARGB(Color::COLOR_RED);
        $spreadsheet->getActiveSheet()->getStyle('A4:I4')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('ffff00');

        $count = count($data['item']) + 3;  # 总行数

        # 设置第一列背景和字体颜色
        $spreadsheet->getActiveSheet()->getStyle('A5:A'.$count)->getFont()->getColor()->setARGB(Color::COLOR_RED);
        $spreadsheet->getActiveSheet()->getStyle('A5:A'.$count)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('ffff00');

        # 设置第"I"列到最后一行背景和字体颜色
        $spreadsheet->getActiveSheet()->getStyle('I5:I'.$count)->getFont()->getColor()->setARGB(Color::COLOR_DARKGREEN);
        $spreadsheet->getActiveSheet()->getStyle('I5:I'.$count)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('ffff00');


        $sheet = $spreadsheet->getActiveSheet();

        # 顶层
        $sheet->mergeCells('A1:I2')->setCellValue('A1','备注：红色字体栏不可修改/删除，当发放成功后 只需修改对应的"状态栏值【绿色】为4即可"');
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(Color::COLOR_RED);

        $sheet->setTitle('sheet1');

        foreach ($data['key_head'] as $key => $value) {
            $sheet->setCellValueByColumnAndRow($key + 1, 3, $value);
        }

        $row = 4;

        foreach ($data['item'] as $k=>$item) {

            $st = 2;

            if($k == 0) $st = 'STATE';

            $sheet->setCellValue('A'.$row, $item['id']);
            $sheet->setCellValue('B'.$row, $item['name']);
            $sheet->setCellValue('C'.$row, $item['mobile']);
            $sheet->setCellValue('D'.$row, $item['created_at']);
            $sheet->setCellValue('E'.$row, $item['goods_name']);
            $sheet->setCellValue('F'.$row, $item['num']);
            $sheet->setCellValue('G'.$row, $item['service_point']);
            $sheet->setCellValue('H'.$row, $item['money_point']);
            $sheet->setCellValue('I'.$row, $st);
            $row++;
        }

        $fileName = date('YmdHis', time()) . rand(100000, 999999);

        $writer = new Xlsx($spreadsheet);

        $fileUri = Upload::PHARMACY_PHYSICAL_PATH . $fileName . ".xlsx";

        $writer->save($fileUri);

        return $fileName . ".xlsx";
    }



    /**
     * 提交更新状态
     * @param $file
     * @return array
     */
    public function import($file)
    {
        $importLogic = new ImportLogic();

        //存储临时文件
        $file = $importLogic->storeFile($file, Upload::PHARMACY_PHYSICAL_PATH);

        return $this->readWhiteList($file['file']);

    }

    /**
     * @param $file_name
     * @param $liveId
     */
    public function readWhiteList($file_name)
    {
        $importLogic = new ImportLogic();

        if($file_name){
            //提取excel数据
            $sheetDatac = $importLogic->getSheetDataCsv(Upload::PHARMACY_PHYSICAL_PATH . $file_name);
            //数据去空
            $sheetData = $importLogic->array_filter_recursive($sheetDatac);

            //校验模板格式
            $importLogic->checkExcelModule($sheetData[3], Field::PHARMACY_PHYSICAL_EXPORT);
            //判断是否有数据
            $importLogic->checkExcelEmpty($sheetData);
            //处理数据
            $UpdateIds = self::handleSheetData($sheetData);

            if($UpdateIds) $this->PointExchangeOrderRepository->UpadteExchangeGoodsStatus($UpdateIds);
        }


//            Db::beginTransaction();
//            try {
//                $exam->addExamsUsers($insertData['normal_data'], $users, $examId);
//                Db::commit();
//                return true;
//            } catch (\Exception $e) {
//                Db::rollBack();
//                throw new ImportException(ErrImport::FAIL_TO_STORE_TEMPORARY_DATA);
//            }
    }

    /**
     * @param $sheetData
     * @param $companyYyid
     * @return array
     */
    public static function handleSheetData($sheetData)
    {

        $UpdateIds = [];

        foreach ($sheetData as $key => $val) {

            if ($key==3) {

                $nk1 = array_search('ID', $val);

                $nk2 = array_search('STATE', $val);
            }


            if ($key>3){

                if($val[$nk2] != 4) continue;

                $UpdateIds[] = $val[$nk1];
            }
        }

        return $UpdateIds;
    }

　

 

VUE：

  // 实物导出
    GoodsExport() {
      axios.defaults.headers.common['Authorization'] = getToken()
      axios.post(
        RsetApi.GoodsExport,this.listQuery,
        { responseType: 'arraybuffer' }
      ).then(function(response) {
        const fileName = Date.parse(new Date()) + '.xlsx'
        const link = document.createElement('a')
        const blob = new Blob([response.data], { type: 'application/vnd.ms-excel;charset=UTF-8' })
        link.style.display = 'none'
        link.href = URL.createObjectURL(blob)
        link.download = fileName
        document.body.appendChild(link)
        link.click()
        document.body.removeChild(link)
        URL.revokeObjectURL(link.href)
      }).catch(function(error) {
        console.log(error)
      })
    },

　　

结果：