tp5导出mysql语句_TP5操作Excel导入导出数据库

导入：

namespace app\index\controller;

use think\Controller;

use think\Db;

use think\Loader;

class Upload extends Controller

{

public function upload(){

return view();

}

//excel导入

public function upload_excel(){

//设置文件上传的最大限制

ini_set('memory_limit','1024M');

//加载第三方类文件

Loader::import("PHPExcel.Classes.PHPExcel");

//防止乱码

header("Content-type:text/html;charset=utf-8");

//实例化主文件

// $model = new \PHPExcel();

//接收前台传过来的execl文件

$file = $_FILES['file'];

//截取文件的后缀名，转化成小写

$extension = strtolower(pathinfo($file['name'],PATHINFO_EXTENSION));

if($extension == "xlsx"){

//2007(相当于是打开接收的这个excel)

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

}else{

//2003(相当于是打开接收的这个excel)

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

}

$objContent = $objReader -> load($file['tmp_name']);

$sheetContent = $objContent -> getSheet(0) -> toArray();

unset($sheetContent[0]);

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

$arr['name'] = $v[0];

$arr['sex'] = $v[1];

$arr['age'] = $v[2];

$arr['class'] = $v[3];

$res[] = $arr;

}

$res = Db::name('user') -> insertAll($res);

if($res){

echo "成功";

}else{

echo "失败";

}

}

}

导出：

//导出

public function out_excel(){

ini_set('memory_limit','1024M');

Loader::import('PHPExcel.Classes.PHPExcel');

header("content-type:text/html;charset=utf8");

$objExcel = new \PHPExcel();

$objSheet = $objExcel -> getActiveSheet();

$objSheet -> setTitle("PHPExcel导出测试");

$data = Db::name('user') -> select();

$objSheet -> setCellValue('A1','ID')

-> setCellValue('B1','姓名')

$j = 2;

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

$objSheet -> setCellValue('A'.$j,$v['id'])

-> setCellValue('B'.$j,$v['name'])

$j++;

}

$file_name = '文件名'.xlsx';

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header("Content-Disposition: attachment;filename=$file_name");

header('Cache-Control: max-age=0');

header('Cache-Control: max-age=1');

header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT');

header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');

header ('Cache-Control: cache, must-revalidate');

header ('Pragma: public');

$objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');

$objWriter->save('php://output');

exit;

}