php使用phpoffice/phpspreadsheet导入导出excel表格

phpoffice/phpspreadsheet引入

composer require phpoffice/phpspreadsheet

导出

可以直接数组导出，注释里代码为循环进行单元格赋值房是

<?php

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;
class ErpOrder extends Model
{
   
    public function export($adminId)
    {
        try {
            $list = $this->field('order_number,good_title,sku,good_number,user_name,user_mobile,user_address,express_number')
                ->where(['status' => 2,'send_status' => 1,'partner_admin_id' => $adminId])
                ->select()
                ->toArray();

            //导出表格
            $objExcel = new Spreadsheet();
            $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
            $objActSheet = $objExcel->getActiveSheet(0);
            $objActSheet->setTitle('发货单');//设置excel的标题



            $keys= ['订单号','商品名称','sku','数量','收件人姓名','联系电话','收货地址','物流单号'];
            array_unshift($list,$keys);
            $objActSheet->fromArray($list);


//            $objActSheet->setCellValue('A1', '订单号');
//            $objActSheet->setCellValue('B1', '商品名称');
//            $objActSheet->setCellValue('C1', 'sku');
//            $objActSheet->setCellValue('D1', '数量');
//            $objActSheet->setCellValue('E1', '收件人姓名');
//            $objActSheet->setCellValue('F1', '联系电话');
//            $objActSheet->setCellValue('G1', '收货地址');
//            $objActSheet->setCellValue('H1', '物流单号');



//            $baseRow = 3; //数据从N-1行开始往下输出
//            foreach ($list as $r => $d) {
//                $i = $baseRow + $r;
//                $objExcel->getActiveSheet()->setCellValue('A' . $i, $d['date']);
//                $objExcel->getActiveSheet()->setCellValue('B' . $i, $d['num']);
//                $objExcel->getActiveSheet()->setCellValue('C' . $i, $d['recharge']);
//                $objExcel->getActiveSheet()->setCellValue('D' . $i, $d['rate']);
//                $objExcel->getActiveSheet()->setCellValue('E' . $i, $d['invest_money']);
//                $objExcel->getActiveSheet()->setCellValue('F' . $i, $d['refund_money']);
//                $objExcel->getActiveSheet()->setCellValue('G' . $i, $d['money']);
//                $objExcel->getActiveSheet()->setCellValue('H' . $i, $d['status']);
//            }
            $objWriter->save(__DIR__.'/a12.xlsx');

            return ['data' => '', 'code' => 0, 'msg' => 'success'];
        } catch (Throwable $e) {
            return ['data' => '', 'code' => $e->getCode(), 'msg' => $e->getMessage()];
        }


    }


}

导入

 /**
     *导入
     * @param $url
     * @param $adminId
     * @return array
     * @throws \think\exception\PDOException
     * @date 2022-08-04 14:40
     */
    public function import($url,$adminId)
    {
        try {
            $this->startTrans();
            $filename = tempnam(sys_get_temp_dir(), "xlsx");
            $handle = fopen($filename, "wb");
            fwrite($handle, file_get_contents($url));
            $objReader = IOFactory::createReader('Xlsx');
            $objPHPExcel = $objReader->load($filename);  //$filename可以是上传的表格
            $data = $objPHPExcel->getSheet(0)->toArray();//将sheet1表中的数据转换为数组类型数据
//            $keys=array_shift($data);//从数组中删除第一条数据，返回值为删除的数据
            $keys = ['order_number', 'good_title', 'sku', 'good_number', 'user_name', 'user_mobile', 'user_address', 'express_number'];
            array_shift($data);
            foreach ($data as $key => $value)
                $list[] = array_combine($keys, $value);
            foreach ($list as $k => $v) {
                if (!empty($v['order_number'])) {
                    $info = $this->where(['order_number' => $v['order_number'], 'partner_admin_id' => $adminId])->find();
                    $info->express_number = $v['express_number'];
                    $info->send_status = 2;
                    $info->send_time = time();
                    $info->save();
                }
            }
            $this->commit();
            return ['data' => '', 'code' => 0, 'msg' => 'success'];
        } catch (Throwable $e) {
            $this->rollback();
            return ['data' => '', 'code' => $e->getCode(), 'msg' => $e->getMessage()];
        }
    }