操作步骤:
1、首页将phpexcel 文件下载放到extend 下面。
PHPExcel 文件下载地址:链接: https://pan.baidu.com/s/1KRZpn1Xlq8xOK7uaYsWGjw 提取码: sry8
2、在根目录index.php定义如下
define('PHPEXCEL_PATH', __DIR__ . '/extend/PHPExcel/');
3、html文件中的写法
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>导入Excel到数据库</title>
</head>
<body>
<button type="button" onclick="document.getElementById('file').click();" class="btn">导入Excel到数据库.xlsx</button>
<form action="{:url('Show/inserExcel')}" method="post" id="excelform" enctype="multipart/form-data" style="display:none;">
<input type="file" name="file" id="file" onchange="addexcel()">
</form>
<script>
function addexcel(){
$("#excelform").submit();
}
</script>
</body>
</html>
4、php控制器文件写法
//导入excel到数据库
public function inserExcel(){
require_once PHPEXCEL_PATH.'PHPExcel.php';
require_once PHPEXCEL_PATH.'PHPExcel/IOFactory.php';
require_once PHPEXCEL_PATH.'PHPExcel/Reader/Excel2007.php';
//注意:这里如果是window主机请用反斜杠。PHPEXCEL_PATH.'PHPExcel\IOFactory.php',我现在是lnunx主机;
$objPHPExcel = new \PHPExcel();
//获取表单上传文件
$file = request()->file('file');
$info = $file->validate(['ext' => 'xls,xlsx'])->move(ROOT_PATH . 'upload');
if($info){
$exclePath = $info->getSaveName(); //获取文件名
$file_name = ROOT_PATH . DS . 'upload' . DS . $exclePath; //上传文件
$ext = explode('.',$file_name);
if ($ext[1] == 'xls') {
$objReader= \PHPExcel_IOFactory::createReader('Excel5');
} else {
$objReader= \PHPExcel_IOFactory::createReader('Excel2007');
}
$obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8'); //加载文件内容,编码utf-8
$excel_array= $obj_PHPExcel->getsheet(0)->toArray(); //转换为数组格式
array_shift($excel_array); //删除第一个数组(标题);
$data = [];
if(count($excel_array)>5000){
exit("一次最多导入5000条数据");
}
echo '<p>正在导入...</p>';
foreach($excel_array as $k=>$v) {
$data[$k]['mid'] = 95;
$data[$k]['code'] = $v[0];
$data[$k]['datetime'] = time();
}
Db::name('code')->insertAll($data); //批量插入数据
echo '<p>导入完成</p>';
$this->success("导入成功");
}else {
//echo $file->getError();
$this->success($file->getError());
}
}
2、导出excel
//设置原始数据表格
function dataSourceField($key=-1){
$arr = [
0=>'ID|id',
1=>'编号|code',
2=>'名称|name',
3=>'创建时间|create_time',
4=>'排序|sort',
];
return $key > -1 ? $arr[$key] : $arr;
}
//导出EXCEL报表
public function excertexcel(){
require_once PHPEXCEL_PATH.'PHPExcel.php';
require_once PHPEXCEL_PATH.'PHPExcel/IOFactory.php';
require_once PHPEXCEL_PATH.'PHPExcel/Reader/Excel2007.php';
//注意:这里如果是window主机请用反斜杠。PHPEXCEL_PATH.'PHPExcel\IOFactory.php',我现在是lnunx主机;
$excel = new \PHPExcel();
$sheet = $excel->setActiveSheetIndex(0);//设定写入的工作簿
//写入数据
$list = db('data')->order("id")->select();
array_unshift($list,$list[0]);
array_unshift($list,$list[0]);
foreach($list as $key=>$v){
for($b = 0; $b < 5; $b++){
$field = explode('|',$this->dataSourceField($b))[1];
$tit = explode('|',$this->dataSourceField($b))[0];
$val = $v[$field];
if($key==1){ $val = $tit; }
$sheet->setCellValueByColumnAndRow($b, $key, $val);
}
}
$excelWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');//设定保存格式
$filename = 'data'.date('YmdHis').'.xlsx';
$files = ROOT_PATH. DS . 'upload' . DS . $filename;
$excelWriter->save($files);//保存文件
if(!file_exists('./upload/'.$filename)){
header('HTTP/1.1 404 NOT FOUND');
}else{
$file = fopen($files ,"r");
Header("Content-type: application/octet-stream");
Header("Accept-Ranges: bytes");
Header("Accept-Length: ".filesize($files));
Header("Content-Disposition: attachment; filename=".$filename);
echo fread($file, filesize($files));
fclose($file);
unlink($files);
}
}
有问题请留言.