Laravel框架Excel数据导入导出

一、laravel框架

(1)Excel数据导出为数组

$path_all1 = '/data/user_data.xlsx';

if (!file_exists($path_all1)) {

return $this->fail('文件不存在');

}

$now = date('Y-m-d H:i:s');

$data1 = [];

Excel::load($path_all1, function ($reader) use (&$data1) {

$reader = $reader->getSheet(0);

$data1 = $reader->toArray();

});

（2）Excel数据导出为txt文件

if (count($result) > 0) {

$result = json_encode($result, JSON_UNESCAPED_UNICODE);

file_put_contents('/mnt/vdc/groupInfo.txt', $result);

}

（3）将数据导出为Excel文件

$excel_name = 'deposits_05_1';

 $info = Excel::create($excel_name, function ($excel) use ($orders) {

 $excel->sheet('deposits', function ($sheet) use ($orders) {

 $sheet->rows($orders);

 });

 })->store('xlsx');

二、tp5的Excel导入

public function load(){

$controller = $this->request->controller();

if ($this->request->isAjax()){

$file = $this->request->post(false);

$file = $this->request->file('excel');

if(!$file){

//return '<script>parent.layer.alert("请上传想要导入的excel文件")</script>';

return ajax_return_adv('请上传想要导入的excel文件');

}

$info = $file->validate(['ext' => 'xlsx'])->move(ROOT_PATH.'public\tmp');

$PHPExcel = new \PHPExcel();

if($info){

$excelPath = $info ->getSaveName(); //获取文件名

$fileName = ROOT_PATH.'public/tmp/'.$excelPath;

unset($info);

unset($excelPath);

$objReader = \PHPExcel_IOFactory::createReader('Excel2007');

$obj_PHPExcel = $objReader->load($fileName,$encode='utf-8');

$excel_array = $obj_PHPExcel->getsheet(0)->toArray();

array_shift($excel_array);

$questionLevel = Config::get('questionLevel');

$answers = ['A','B','C','D'];

$question = [];

// 验证

if (class_exists($validateClass = Loader::parseClass(Config::get('app.validate_path'), 'validate', $controller))) {

$validate = new $validateClass();

if (!$validate->check($excel_array)) {

return ajax_return_adv_error($validate->getError());

}

}

foreach($excel_array as $k => $v){

if(empty($v[0]) || empty($v[1]) || empty($v[2]) || empty($v[3]) || empty($v[4]) || empty($v[5]) || empty($v[6]) || empty($v[7]) || empty($v[8]) || empty($v[9])){

$this->delDir($fileName);

return ajax_return_adv_error("缺少必要信息");

}

if(!in_array($v[3],$answers)){

$this->delDir($fileName);

return ajax_return_adv_error('请核对文件中问题的正确答案选项是否正确');

}

if(!in_array($v[8],$questionLevel)){

$this->delDir($fileName);

return ajax_return_adv_error('请核对文件中问题的等级是否正确');

}

$questionTitle = Db::name('question')->field('question_title')->where(array('question_title' => $v[0],'isdelete' => 0))->value('question_title');

if($questionTitle){

$this->delDir($fileName);

return ajax_return_adv_error("问题重复");

}

$question[$k]['question_title'] = $v[0];

$question[$k]['money'] = $v[1];

$question[$k]['cate_id'] = Db::name('question_cate')->field('id')->where(array('name' => $v[2]))->value('id');

if(empty($question[$k]['cate_id'])){

$this->delDir($fileName);

return ajax_return_adv_error("分类有误");

}

$question[$k]['level'] = $v[3];

$data['answers'] = array(

array('letter' => 'A' , 'question_txt' => $v[4]),

array('letter' => 'B' , 'question_txt' => $v[5]),

array('letter' => 'C' , 'question_txt' => $v[6]),

array('letter' => 'D' , 'question_txt' => $v[7]),

);

$question[$k]['answers'] = json_encode($data['answers']);

$question[$k]['true_answer'] = $v[8];

$question[$k]['orderid'] = $v[9];

}

 

// 写入数据

if (

class_exists($modelClass = Loader::parseClass(Config::get('app.model_path'), 'model', $this->parseCamelCase($controller)))

|| class_exists($modelClass = Loader::parseClass(Config::get('app.model_path'), 'model', $controller))

) {

//使用模型写入，可以在模型中定义更高级的操作

$model = new $modelClass();

$ret = $model->isUpdate(false)->saveAll($question);

} else {

// 简单的直接使用db写入

Db::startTrans();

try {

$model = Db::name($this->parseTable($controller));

$ret = $model->insertAll($question);

// 提交事务

Db::commit();

} catch (\Exception $e) {

// 回滚事务

Db::rollback();

return ajax_return_adv_error($e->getMessage());

}

}

$this->delDir($fileName);

//return '<script>parent.layer.alert("添加成功");layer.close();</script>';

return ajax_return_adv('添加成功');

}else{

$this->delDir($fileName);

return ajax_return_adv_error('请再次上传想要导入的excel文件');

}

}else{

return $this->view->fetch(isset($this->template) ? $this->template : 'load');

}

}

 

public function delDir($fileName){

if (file_exists($fileName)) {

$filePath = substr($fileName,0,strrpos($fileName,"\\"));

unlink($fileName);//删除文件

rmdir($filePath);//删除文件夹

}

}