大神请绕道~
使用composer 安装 PHPExcel
composer require phpoffice/phpexcel
这样就安装好了
-
导入
1-1 准备一个Excel表格
1-2 编写代码,引入类库
use think\Controller;
use think\Request;
use PHPExcel; //引入刚刚PHPExcel类库
use app\index\model\User;
use think\facade\Env;
1-3 编写导入的PHP代码
public function postExcel(Request $request)
{
if ($request->isPost()) {
$ext_arr = ['xlsx', 'xls'];
//接收文件
$file = $request->file('file');
$file_type = $file->getInfo()['name'];
//获取后缀
$ext = explode('.', $file_type);
$ext = end($ext);
//判断时候是限制的文件格式
if (!in_array($ext, $ext_arr)) {
$this->error('文件格式有误');
}
if ($ext == 'xls') {
$type = 'Excel5';
} elseif ($ext == 'xlsx') {
$type = 'Excel2007';
}
$tmp_name = $file->getInfo()['tmp_name'];
//实例化类库
$phpExcel = new PHPExcel;
$reader = \PHPExcel_IOFactory::createReader($type);
$objData = $reader->load($tmp_name, $encode = 'utf-8');
$excel_array = $objData->getsheet(0)->toArray();
//删除表格第一行
unset($excel_array[0]);
$i = 0;
$insertData = [];
foreach ($excel_array as $key => $value) {
$insertData[$i]['id'] = $value[0];
$insertData[$i]['name'] = $value[1];
$insertData[$i]['pass'] = $value[2];
$insertData[$i]['create_time'] = time();
$insertData[$i]['update_time'] = time();
$insertData[$i]['delete_time'] = $value[5];
$insertData[$i]['status'] = $value[6];
$i++;
}
$info = new User;
$result = $info->saveExcel($insertData);
if ($result) {
$this->success('导入成功');
} else {
$this->error('导入失败');
}
}
}
1-4 最后
2.导出
2-1 编写导出的PHP代码
public function index()
{
$file_name = date('Ymd_His') . '_' . rand(1111, 9999);
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle("demo"); //给当前活动sheet设置名称
$res = new Users();
$result = $res->getExcecl(); //这里是查数据,就不一一写了。[懒惰]
// 设置头信息
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1','ID')
->setCellValue('B1','名称')
->setCellValue('C1','密码')
->setCellValue('D1','创建时间')
->setCellValue('E1','修改时间')
->setCellValue('F1','删除时间')
->setCellValue('G1','状态');
$num = 2;
foreach ($result as $k => $v) {
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A' . $num, $v['id']) //这里是对应数据库字段名
->setCellValue('B' . $num, $v['name'])
->setCellValue('C' . $num, $v['pass'])
->setCellValue('D' . $num, $v['create_time'])
->setCellValue('E' . $num, $v['update_time'])
->setCellValue('F' . $num, $v['delete_time'])
->setCellValue('G' . $num, $v['status']);
$num++;
}
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=".$file_name.".xls");
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter->save("php://output");//输出文件
}