php使用phpoffice/phpspreadsheet导入导出excel表格 |
您所在的位置：网站首页 › php导入导出excel › php使用phpoffice/phpspreadsheet导入导出excel表格 |
|
php使用phpoffice/phpspreadsheet导入导出excel表格
原创
flysnownet 2023-05-08 13:56:00 博主文章分类：php ©著作权 文章标签 php excel 数据 数组 其他 文章分类 Hadoop 大数据 ©著作权归作者所有：来自51CTO博客作者flysnownet的原创作品，请联系作者获取转载授权，否则将追究法律责任 phpoffice/phpspreadsheet引入 composer require phpoffice/phpspreadsheet 导出可以直接数组导出，注释里代码为循环进行单元格赋值房是 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()]; } } 赞 收藏 评论 分享 举报上一篇：phpstorm配置phpunit单元测试及PHPunit断言函数 下一篇：php将在线远程文件写入临时文件 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |