PhpSpreadsheet 导出excel 找不到setCellValueByColumnAndRow

<?php
namespace app\admin\controller;
use think\facade\Db;
use think\facade\Request;
use app\admin\controller\BaseController;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Product extends BaseController
{
    public function userorder_list_export(){
        
        $list = Db::name('userorder')->order('id desc')->select()->each(function($item, $key){
            
            $lists = explode(',',$item['product_list']);
            
            $plist = array();
            
            foreach ($lists as $ks => $v){
                
                $id = explode('_',$v)[0];
                
                $pinfo = Db::name('product')->where('id',$id)->find();
                $pinfo['cate_name'] = Db::name('product_cate')->where('id','=',$pinfo['cate_id'])->value('title');
                $pinfo['apps_name'] = Db::name('product_apps')->where('id','=',$pinfo['apps_id'])->value('title');
                $pinfo['diseases_name'] = Db::name('product_diseases')->where('id','in',$pinfo['diseases_id'])->select()->toArray();
                $pinfo['diseases_names'] = Db::name('product_diseasess')->where('id','=',$pinfo['diseases_ids'])->value('title');
                $pinfo['source_name'] = Db::name('product_source')->where('id','=',$pinfo['source'])->value('title');
                $pinfo['format_name'] = Db::name('product_format')->where('id','in',$pinfo['format'])->select()->toArray();
                
                $pinfo['add_num'] = intval(explode('_',$v)[1]);
                array_push($plist,$pinfo);
                
            }
            
            $item['product_lists'] = $plist;
            
            //$item['uinfo'] = Db::name('users')->where('id',$item['user_id'])->find();
            
            $item['create_time'] = date('m-d-Y',$item['create_time']);
            return $item;
        });
        
        $fileName = '会员订单 '.date("Y-m-d",time()) .'导出';
        //实例化spreadsheet对象
        $spreadsheet = new Spreadsheet();
    
        //获取活动工作簿
        $sheet = $spreadsheet->getActiveSheet();
        //设置单元格表头
        $sheet->setCellValue('A1', '序号');
        $sheet->setCellValue('B1', 'Order Number');
        $sheet->setCellValue('C1', 'First name');
        $sheet->setCellValue('D1', 'Last name');
        $sheet->setCellValue('E1', 'Email');
        $sheet->setCellValue('F1', 'Country');
        $sheet->setCellValue('G1', 'Organization Name');
        $sheet->setCellValue('H1', 'Street Address');
        $sheet->setCellValue('I1', 'City');
        $sheet->setCellValue('J1', 'State');
        $sheet->setCellValue('K1', 'Zip Code');
        $sheet->setCellValue('L1', 'Customer Number');
        $sheet->setCellValue('M1', 'mobile');
        $sheet->setCellValue('N1', 'Message');
        $sheet->setCellValue('O1', 'Product List（Product Name / TYPE / Catalog / Number）');
        $sheet->setCellValue('P1', 'Time');
        

        $i = 2;
        foreach($list as $key => $val){
            
            $sheet->setCellValueByColumnAndRow('1',$i,$i-1);
            $sheet->setCellValueByColumnAndRow('2',$i,$val['order_id']);
            $sheet->setCellValueByColumnAndRow('3',$i,$val['first_name']);
            $sheet->setCellValueByColumnAndRow('4',$i,$val['last_name']);
            $sheet->setCellValueByColumnAndRow('5',$i,$val['email']);
            $sheet->setCellValueByColumnAndRow('6',$i,$val['country']);
            $sheet->setCellValueByColumnAndRow('7',$i,$val['organization_name']);
            $sheet->setCellValueByColumnAndRow('8',$i,$val['street_address']);
            $sheet->setCellValueByColumnAndRow('9',$i,$val['city']);
            $sheet->setCellValueByColumnAndRow('10',$i,$val['state']);
            $sheet->setCellValueByColumnAndRow('11',$i,$val['zip_code']);
            $sheet->setCellValueByColumnAndRow('12',$i,$val['customer_number']);
            $sheet->setCellValueByColumnAndRow('13',$i,$val['mobile']);
            $sheet->setCellValueByColumnAndRow('14',$i,$val['message']);
            
            $text = '';
            
            foreach ($val['product_lists'] as $k => $v){
                
                $text .= $v['product_name'].' / ' .$v['cate_name'].' / '.$v['catalog'].' / '.$v['add_num']."\n";
                
            }
            $sheet->setCellValueByColumnAndRow('15',$i,trim($text,"\n"));
            
            $sheet->getStyle('P'.$i)->getAlignment()->setWrapText(true);
            
            $sheet->setCellValueByColumnAndRow('16',$i,$val['create_time']);
            
            $i++;
        
        }
        
        //设置自动列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(15);
        
        // //MIME协议，文件的类型，不设置描绘默认html
        // header('Content-Type:application/vnd.openxmlformats-officedoument.spreadsheetml.sheet');
        // //MIME 协议的扩展
        // header("Content-Disposition:attachment;filename={$fileName}.xlsx");
        // //缓存控制
        // header('Cache-Control:max-age=0');
        
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet,'Xlsx');
        
        $writer->save('./uploads/'.$fileName.'.Xlsx');
        
        $url = Request::domain().'/uploads/'.$fileName.'.Xlsx';
        
        echo apireturn(200,200,'success',$url);
        die;
    }
?>

运行报错

Call to undefined method PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::setCellValueByColumnAndRow
 

    public function getCellByColumnAndRow($columnIndex, $row, $createIfNotExists = true){
        $columnLetter = Coordinate::stringFromColumnIndex($columnIndex);
        $coordinate = $columnLetter . $row;

        if ($this->cellCollection->has($coordinate)) {
            return $this->cellCollection->get($coordinate);
        }

        // Create new cell object, if required
        return $createIfNotExists ? $this->createNewCell($coordinate) : null;
    }
    
    /**
     * Set a cell value by using numeric cell coordinates.
     *
     * @param int $columnIndex Numeric column coordinate of the cell
     * @param int $row Numeric row coordinate of the cell
     * @param mixed $value Value of the cell
     *
     * @return Worksheet
     */
    public function setCellValueByColumnAndRow($columnIndex, $row, $value)
    {
        $this->getCellByColumnAndRow($columnIndex, $row)->setValue($value);

        return $this;
    }

尝试复制以上方法到Worksheet.php
问题解决

原文地址：https://blog.csdn.net/qq_30986969/article/details/144690242

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！