php uses phpoffice/phpspreadsheet to import and export excel tables

phpoffice/phpspreadsheet引入

composer require phpoffice/phpspreadsheet

导出

can be exported directly,The code in the comments is for looping cell assignments

<?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();//将sheet1The data in the table is converted to array type data
// $keys=array_shift($data);//Delete the first item of data from the array,The return value is the deleted 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()];
        }
    }