首先 7.1以上php环境 安装扩展
composer config -g repo.packagist composer https://mirrors.aliyun.com/composer/ // 下载快一点 composer require phpoffice/phpspreadsheet // 推荐使用此扩展 phpExecl 已经不在维护作者已弃用
导出代码
<?php
/**
* 导出
* User: zsq
* Date: 2019/9/9
* Time: 16:39
*/
namespace app\index\controller;
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;
use think\Controller;
use think\facade\Request;
class Export extends Controller
{
public function index()
{
if(Request::isGet()){
return $this->fetch();
}
$adminList = db('user')
->field(['id','name','age','sex'])
->select();
//return $adminList;
$newExcel = new Spreadsheet(); //创建一个新的excel文档
$objSheet = $newExcel->getActiveSheet(); //获取当前操作sheet的对象
$objSheet->setTitle('用户表'); //设置当前sheet的标题
//设置宽度为true,不然太窄了
$newExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$newExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$newExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$newExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
//设置第一栏的标题
$objSheet->setCellValue('A1', 'id')
->setCellValue('B1', '用户名')
->setCellValue('C1', '年龄')
->setCellValue('D1', '性别');
//第二行起，每一行的值,setCellValueExplicit是用来导出文本格式的。
//->setCellValueExplicit('C' . $k, $val['admin_password']PHPExcel_Cell_DataType::TYPE_STRING),可以用来导出数字不变格式
foreach ($adminList as $k => $val) {
$k = $k + 2;
$objSheet->setCellValue('A' . $k, $val['id'])
->setCellValue('B' . $k, $val['name'])
->setCellValue('C' . $k, $val['age'])
->setCellValue('D' . $k, $val['sex']);
}
return $this->downloadExcel($newExcel, '用户表', 'Xls');
}
//公共文件，用来传入xls并下载
function downloadExcel($newExcel, $filename, $format)
{
// $format只能为 Xlsx 或 Xls
if ($format == 'Xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
} elseif ($format == 'Xls') {
header('Content-Type: application/vnd.ms-excel');
}
header("Content-Disposition: attachment;filename="
. $filename . date('Y-m-d') . '.' . strtolower($format));
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($newExcel, $format);
// $objWriter->save('php://output');
$dir=".\upload\/execl\/";
$url=$dir.$filename . date('Y-m-d') . '.' . strtolower($format);
//通过php保存在本地的时候需要用到
$objWriter->save($url);
return $this->downloadDemo($url,$filename);
//以下为需要用到IE时候设置
// If you're serving to IE 9, then the following may be needed
//header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
//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
// exit;
}
/*
* 下载excel的demo
* $download_url 文件下载地址
* $file_name 文件名
*/
public function downloadDemo($download_url, $file_name) {
//下载地址
$url ="http://" . $_SERVER['HTTP_HOST'] . "/new_tp51/" . "public" .$download_url;
//下载html样式
$data['info'] = '<div style="width:200px;margin:17% auto 0 auto;text-align:center;"><a style="color:#5a98de;font-size:18px;" href="' . $url . '">点击下载</a></div>';
$data['url'] = $download_url;
$data['status'] = '1';
return $data;
}
}html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form action="" method="post">
<button type="button" id="submit">导出</button>
</form>
<div id="demo"></div>
</body>
</html>
<link href="https://cdn.bootcss.com/layer/2.3/skin/layer.css" rel="stylesheet">
<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdn.bootcss.com/layer/2.3/layer.js"></script>
<script>
$("#submit").on('click', function() {
$.ajax({
type: 'post',
url: '{:url("Export/index")}',
// 需要几个字段 需要自己传递一下 token 的值
dataType: 'json',
success: function(data) {
if (data.status == 1) {
layer.open({
type: 1,
title: false,
closeBtn: 0,
shadeClose: true,
area: ['300px', '130px'],
skin: 'yourclass',
content: data.info,
end: function(layero, index) {
}
});
} else {
layer.msg('数据发生错误，请稍后再试', {
time: 1500,
icon: 5
});
console.log(data);
}
}
});
});
</script>效果图 自行美化
![37T4}@A)]DRY]3$4G2`~Y}0.png](https://img.php.cn/upload/image/565/639/239/1568110228362943.png "1568110228362943.png")
导入
<?php
/**
* 导入
* User: zsq
* Date: 2019/9/9
* Time: 16:42
*/
namespace app\index\controller;
use think\Controller;
use \PhpOffice\PhpSpreadsheet\IOFactory;
class Import extends Controller
{
public function index()
{
halt(\think\facade\Request::file('myfile'));
//获取表格的大小，限制上传表格的大小5M
$file_size = $_FILES['myfile']['size'];
if ($file_size > 5 * 1024 * 1024) {
$this->error('文件大小不能超过5M');
exit();
}
//限制上传表格类型
$fileExtendName = substr(strrchr($_FILES['myfile']["name"], '.'), 1);
//application/vnd.ms-excel 为xls文件类型
if ($fileExtendName != 'xls') {
$this->error('必须为excel表格，且必须为xls格式！');
exit();
}
if (is_uploaded_file($_FILES['myfile']['tmp_name'])) {
// 有Xls和Xlsx格式两种
$objReader = IOFactory::createReader('Xls');
$filename = $_FILES['myfile']['tmp_name'];
$objPHPExcel = $objReader->load($filename); //$filename可以是上传的表格，或者是指定的表格
$sheet = $objPHPExcel->getSheet(0); //excel中的第一张sheet
$highestRow = $sheet->getHighestRow(); // 取得总行数
// $highestColumn = $sheet->getHighestColumn(); // 取得总列数
//定义$usersExits，循环表格的时候，找出已存在的用户。
$usersExits = [];
//循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
for ($j = 2; $j <= $highestRow; $j++) {
$data[$j - 2] = [
'name' => $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue(),
'age' => $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue(),
'sex' =>$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue(),
];
//看下用户名是否存在。将存在的用户名保存在数组里。
// $userExist = db('user')->where('name', $data[$j - 2]['admin_username'])->find();
// if ($userExist) {
// array_push($usersExits, $data[$j - 2]['admin_username']);
// }
}
//halt($usersExits);
// //如果有已存在的用户名，就不插入数据库了。
// if ($usersExits != []) {
// //把数组变成字符串，向前端输出。
// $c = implode(" / ", $usersExits);
// $this->error('Excel中以下用户名已存在:' . $c, "/backend/admin/create", '', 20);
// exit();
// }
//halt($data);
//插入数据库
$res = db('user')->insertAll($data);
if ($res) {
$this->success('上传成功！', '/index/index', '', 1);
}
}
}
}