use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\IOFactory;
使用类
"phpoffice/phpspreadsheet": "v1.8.0" 或 composer require phpoffice/phpspreadsheet v1.8.0
<a class="layui-btn up_file" >批量修改</a> <script> layui.use([ 'element','layer','upload'], function(){ var $ = layui.$ ,layer = layui.layer ,upload = layui.upload;upload.render({ elem: '.up_file' ,url: "{:url('admin/category/import_pro')}?cid={$cid}" ,accept: 'file' ,done: function(res){ console.log(res); if(res.result==1){ layer.msg(res.msg, {icon: 1}); setTimeout(function() { window.location.reload(); }, 2000); }else{ layer.msg(res.msg, {icon: 2}); } } ,error: function(){ layer.msg(res.msg, {icon: 2}); } }); });
方法
public function import_pro(){ $upload_file = $_FILES['file']['tmp_name']; //文件类型 //$ext = strtolower(pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION)); $upload_file = $_FILES['file']['tmp_name']; //自动获取文件类型 (xlsx,xls,csv) $spreadsheet = IOFactory::load($upload_file); //直接转为数组,避免上一种方法取值会出现表格内数据格式错误的问题 $data = $spreadsheet->getSheet(0)->toArray(); $num_all= 0; $num_success= 0; foreach($data as $k=>$vo){ if($k>0){ $num_all++; $is = db('post')->where(['id'=>$vo[0]])->update(['price_market'=>$vo[3],'price'=>$vo[4],'price2'=>$vo[5],'price3'=>$vo[6],'price4'=>$vo[7], 'stock'=>intval($vo[8])]); if(!$is){ $num_success++; } } } $num_cf = $num_all-$num_success; return jssuccess('总数:'.$num_all.',成功:'.$num_success.',重复:'.$num_cf); }
public function export(){ admin_role_check($this->z_role_list,$this->mca); if (!is_dir('./vendor/phpoffice/phpspreadsheet')) { $this->error('PhpSpreadsheet扩展(phpoffice/phpspreadsheet)未安装,请先安装后使用'); } $name='用户表'.date("Y-m-d H-i-s",time()); // $data=[['aa','aa','cc','dd','ee'],['bb','bb','cc','dd','ee']]; $data = ZFTB('user')->where(['status'=>1])->select(); //设置表头: $head = ['用户ID', '用户名', '性别', '地址', '注册日期']; //数据中对应的字段,用于读取相应数据: $keys = ['id','name', 'sex', 'address', 'ctime']; zf_excel_export($head,$keys,$data,$name) ; }
方法
if (!function_exists('zf_excel_export')) { function zf_excel_export($head,$keys,$data,$name){ ob_end_clean(); $count = count($head); //计算表头数量 $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); for ($i = 65; $i < $count + 65; $i++) { //数字转字母从65开始,循环设置表头: $sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]); } /--------------开始从数据库提取信息插入Excel表中------------------/ foreach ($data as $key => $item) { //循环设置单元格: //$key+2,因为第一行是表头,所以写到表格时 从第二行开始写 for ($i = 65; $i < $count + 65; $i++) { //数字转字母从65开始: $z_value = str_replace(['+','\','/','='],'*',$item[$keys[$i - 65]]); $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $z_value); $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(40); //固定列宽 } } // header('Content-Type: application/vnd.ms-excel'); // header('Content-Disposition: attachment;filename="' . $name . '.xlsx"'); // header('Cache-Control: max-age=0'); // $writer = new Xlsx($spreadsheet); // $writer->save('php://output'); // //删除清空: // $spreadsheet->disconnectWorksheets(); // unset($spreadsheet); // exit;$writer = IOFactory::createWriter($spreadsheet,'Csv'); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename='.$name.'.csv'); header('Cache-Control: max-age=0'); $writer->setUseBOM(true); $writer->save('php://output'); } }