tp6 thinkphp6 Excel导入导出,主要是使用了PHPExcel类库。
php导出excel数字太长尾数变000解决方法 导出时为什么数字字段要加“\t”。是因为，由于数字超过15位，会被显示成0或者加小数点处理。造成这种情况是由于Excel内置的数值有效范围是15位。超过15位，如果要显示的话，就需要转换成非数字格式。比如文本格式！！！那么我们用php如何解决呢？？就需要在导出数字的字段前后加上“\t”即可解决。导出数据正常，末尾变0或者小数点的问题成功解决
利用PHPExcel导入导出excel
1、安装类库下载
composer require phpoffice/phpexcel
2、加载
use PHPExcel_IOFactory;
3、excel导入数据库
/*** 学生导入* */public function students_import(){if(request()->isPost()){// 获取表单上传文件$file = request()->file('file');if(empty($file)){return json(['info'=>'请选择上传文件！','status'=>0]);}// 移动到框架应用根目录/public/upload/ 目录下，并修改文件名为时间戳$savename = \think\facade\Filesystem::disk('public')->putFile('files', $file, 'time');// 文件名称$info = explode('/', $savename);$file = public_path().'upload/files/'.$info['1'];$file_extension = strtolower(pathinfo($file, PATHINFO_EXTENSION));//获取文件扩展名//实例化PHPExcel类if ($file_extension == 'xlsx'){$objReader =\PHPExcel_IOFactory::createReader('Excel');} else if ($file_extension == 'xls') {$objReader =\PHPExcel_IOFactory::createReader('Excel5');}// $objReader =\PHPExcel_IOFactory::createReader('Excel');$obj_PHPExcel =$objReader->load($file, $encode = 'utf-8'); // 加载文件内容,编码utf-8$excel_array=$obj_PHPExcel->getsheet(0)->toArray(); // 转换为数组格式$highestRow = $obj_PHPExcel->getSheet(0)->getHighestRow() - 1; //取得总行数array_shift($excel_array); // 删除第一个数组(标题);$data = []; // 数据库需要的二维数组foreach ($excel_array as $key => $value) {// 正则去除多余空白字符$data[$key]['id'] = preg_replace('/\s+/', '', $value['0']);// $data[$key]['password'] = md5(preg_replace('/\s+/', '', $value['1']));$data[$key]['number'] = date('ym',time()).rand(10000,99999);$data[$key]['id_card'] = preg_replace('/\s+/', '', $value['1']);$data[$key]['name'] = preg_replace('/\s+/', '', $value['2']);$data[$key]['mobile'] = preg_replace('/\s+/', '', $value['3']);$data[$key]['sex'] = preg_replace('/\s+/', '', $value['4']);$data[$key]['province'] = preg_replace('/\s+/', '', $value['5']);$data[$key]['campus'] = preg_replace('/\s+/', '', $value['6']);$data[$key]['class_grade'] = preg_replace('/\s+/', '', $value['7']);$data[$key]['class_type'] = preg_replace('/\s+/', '', $value['8']);$data[$key]['create_time'] = time();$data[$key]['update_time'] = time();}// 启动事务Db::startTrans();try {$moneyinfo = new Students();$result = $moneyinfo->insertAll($data);if (!$result) {throw new \Exception('插入数据失败!');}// 提交事务Db::commit();unlink($file);return json(['msg'=>'文件上传成功，已经导入'.$result.'条数据','status'=>200]);} catch (\Exception $e) {// 回滚事务Db::rollback();unlink($file);return json(['msg'=>'导入数据失败，'.$e->getMessage(),'status'=>400]);}}return view();}
4、导出Excel（注意：主要使用了export_excel封装自定义函数，后边会附上）
/*** 学生导出Excel* */public function export_excel($value=''){$model = Students::where(['status'=>1])->field('id,id_card,number,name,mobile,sex,province,campus,class_grade,class_type,note,create_time')->select()->toArray();foreach ($model as $key => $value) {$model[$key]['id_card'] = "\t".$value['id_card']."\t";$model[$key]['number'] = "\t".$value['number']."\t";$model[$key]['mobile'] = "\t".$value['mobile']."\t";$model[$key]['campus'] = Campus::where(['id'=>$value['campus']])->value('title');$model[$key]['class_grade'] = ClassGrade::where(['id'=>$value['class_grade']])->value('title');$model[$key]['class_type'] = ClassType::where(['id'=>$value['class_type']])->value('title');}return export_excel('学生_'.time().'.xls',['id','身份证','学号','姓名','手机号','性别','省份','校区','班级','班型','备注','登记时间'],$model);}
注意，上方“id_card、number、mobile”就是数字字段，所以需要在字段值前后加上“\t”，即可解决导出excel后数字不能正常显示问题。
4.1封装的export_excel自定义函数
if (!function_exists('export_excel')) {/*** 数据导出Excel(csv文件)* @param string $file_name 文件名称* @param array $tile 标题* @param array $data 数据源*/function export_excel($file_name, $tile = [], $data = []){ini_set('memory_limit', '512M');ini_set('max_execution_time', 0);ob_end_clean();ob_start();header("Content-Type: text/csv");header("Content-Disposition:filename=" . $file_name);$fp = fopen('php://output', 'w');// 转码 防止乱码(比如微信昵称)fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));fputcsv($fp, $tile);$index = 0;foreach ($data as $item) {if ($index == 1000) {$index = 0;ob_flush();flush();}$index++;fputcsv($fp, $item);}ob_flush();flush();ob_end_clean();}}
以上就是tp6 thinkphp6用PHPExcel导入导出excel
