<?php
namespace app\api\controller\revit;
use app\api\controller\Common;
use app\admin\model\revit\Field;
use app\admin\model\revit\Revit as RevitModel;
use think\Db;
use think\Exception;
class Revit extends Common
{
protected $noNeedLogin = '*';
protected $noNeedRight = '*';
//导入
public function add_excel(){
vendor("phpexecl.PHPExcel");
vendor("phpexecl.PHPExcel.IOFactory");
$file_name = $this->upload();
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');//创建读取实例
$objPHPExcel = $objReader->load($file_name['path'], $encode = 'utf-8');
$sheet = $objPHPExcel->getSheet(0);
$highestColumn = $sheet->getHighestColumn();
$highestColumn++;
$highestRow = $sheet->getHighestRow();
$guidfield ="";
$field=[];
$data = [];
$guid = [];
$revalue = -1;
$user_id = $this->getUserId();
// 获取 $guidfield 以及 $field
for ($i = 'A', $j = 0; $i != $highestColumn; $i++) {
$field[$j] = $objPHPExcel->getActiveSheet()->getCell($i.'1')->getValue();
if ($field[$j] == "Family Instance Unique ID"){
$guidfield = $i;
}
$j++;
}
// 获取 $guid
if (!empty($guidfield)){
for ($i = 4, $j = 0; $i <= $highestRow; $i++) {
$guid[$j] = $objPHPExcel->getActiveSheet()->getCell($guidfield.$i)->getValue();
$j++;
}
}else{
$this->error('缺少guid列',$revalue);
}
Db::startTrans();
try{
$this->updateField($field);
$res = RevitModel::whereIn('guid',$guid)->column('guid');
for ($z = 4; $z <= $highestRow; $z++) {
for ($i = 'A',$j = 0; $i != $highestColumn; $i++,$j++) {
$data[$z][$field[$j]] = $objPHPExcel->getActiveSheet()->getCell($i.$z)->getValue();
}
}
if (empty($res)){
$this->addfield();
$allfield = Field::wherein('name',$field)->column('field','name');
foreach ($data as $key => $value){
$newdata = [];
$newdata['user_id'] = $user_id;
$newdata['createtime'] = time();
foreach ($value as $k => $v){
$newdata[$allfield[$k]] = $v;
}
Db::name('revit')->insert($newdata);
}
$revalue = 0;
}else{
$this->createTemp($field);
$allfield = Field::wherein('name',$field)->column('field','name');
foreach ($data as $key => $value){
$newdata = [];
foreach ($value as $k => $v){
$newdata[$allfield[$k]] = $v;
}
Db::name('revit_temp')->insert($newdata);
}
Db::name('revit_temp')->whereIn('guid',$res)->update(['status'=>2]);
$revalue =RevitModel::whereIn('guid',$guid)->count();
}
Db::commit();
}catch (Exception $e){
Db::rollback();
$this->error($e->getMessage());
}
if ($revalue == 0){
$this->success('导入成功',$revalue);
}elseif($revalue > 0){
$this->success('导入成功,有重复数据',$revalue);
}else{
$this->error('导入失败',$revalue);
}
}
//更新字段表中的数据
public function updateField($field){
Field::where('status',0)->delete();
$oldfield = Field::column('name');
$newfield = array_diff($field,$oldfield);
foreach ($newfield as $value)
{
$data = [ 'name' => $value];
$res = Field::insertGetid($data);
Field::where('id',$res)->update(['field'=> 'field'.$res]);
}
}
//创建temp
private static function createTemp($field){
$isTable = db()->query("SHOW TABLES LIKE 'revit_temp'" ); //查询temp表是否存在
$newfield = Field::wherein('name',$field)->column('field','name');
if ($isTable) {
Db::execute("DROP TABLE `revit_temp`"); //删除temp表
}
Db::execute("
CREATE TABLE `revit_temp` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`status` enum('1','2') NOT NULL DEFAULT '1' COMMENT '状态:1=正常,2=重复',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='revit_temp';");
foreach ($newfield as $key => $value){
Db::execute("
ALTER TABLE `revit_temp` ADD `{$value}` varchar(255) COMMENT '{$key}';
");
}
}
//创建字段
public function addfield(){
$field = Field::where('status',0)->column('field','name');
foreach ($field as $key => $value){
Db::execute("
ALTER TABLE `revit` ADD `{$value}` varchar(255) COMMENT '{$key}';
");
}
Field::where('status',0)->update(['status'=>1]);
}
//添加重复数据
public function add_duplicate(){
$type = input("type");
$this->addfield();
$user_id = $this->getUserId();
if ($type == '1'){
$res = Db::name('revit_temp')->select(); //替换已有的
}else{
$res = Db::name('revit_temp')->where('status',1)->select(); //保留已有的
}
foreach ($res as $key => $value){
unset($value['id']);
$value['createtime'] = time();
$value['user_id'] = $user_id;
if ($value['status'] == 2){
unset($value['status']);
RevitModel::where('guid',$value['guid'])->update($value);
}else{
unset($value['status']);
RevitModel::insert($value);
}
}
$this->success('成功');
}
public function upload()
{
$file = request()->file('file');
// 移动到框架应用根目录/public/uploads/ 目录下
if($file){
$info = $file->move(ROOT_PATH . 'public' . DS . 'uploads\revit');
if($info){
$return['status'] =1;
$return['msg'] = "上传成功";
// $return['path'] = 'C:\phpstudy_pro\WWW\mdpadmin\public\uploads\revit\\'.$info->getSaveName();
$return['path'] = 'C:\phpstudy_pro\WWW\mdpadmin\public\uploads\revit\\'.$info->getSaveName();
return $return;
}else{
// 上传失败获取错误信息
throw new ParameterException(['msg'=>$file->getError()]);
}
}else{
throw new ParameterException(['msg'=>'请上传xlsx文件']);
}
}
}
php 导入excel 并且创建temp表,修改原表字段名
最新推荐文章于 2024-08-22 09:44:18 发布