首先需要去下载excel的文件包,下载下来后只需要classes里面的内容;
把Classes里的文件复制到thinkphp5的extend目录下的excel文件夹内(没有就手动创建);
以下是控制器代码
<?php
namespace app\web\controller;
use think\Loader;
class Excel
{
public function excel_import(){
$file_name=$_SERVER["DOCUMENT_ROOT"]."/testexcel/Book1.xlsx";
return self::import($file_name);
}
public function excel_export(){
$data=db('excel')->select();
return self::export($data,"测试表格导出");
}
//导入excel表,
//$file_name excel文件所在的路径
private function Import($file_name){
Loader::import('excel.PHPExcel');
Loader::import('excel.PHPExcel.PHPExcel_IOFactory');
$phpexcel=new \PHPExcel;
$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
$obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');//加载文件内容,编码utf-8
echo "<pre>";
$excel_array=$obj_PHPExcel->getsheet(0)->toArray();//转换为数组格式
array_shift($excel_array);//删除第一个数组(标题);
$city = [];
foreach($excel_array as $k=>$v) {
$city[$k]['name'] = $v[0];
$city[$k]['price'] = $v[1];
$city[$k]['style'] = $v[2];
$city[$k]['capacity'] = $v[3];
$city[$k]['type'] = $v[4];
}
if(db('excel')->insertAll($city)){//批量插入数据
return "导入数据成功";
}else{
return "导入失败";
}
}
//导出excel表
//$data 二维数组
//$name 导出的表格命名
private function export($data,$name){
Loader::import('excel.PHPExcel');//引入PHPExcel.php
$PHPExcel = new \PHPExcel();//实例化
$PHPExcel->getActiveSheet()->setTitle("demo"); //给当前活动sheet设置名称
foreach($data as $k => $v){
$num=$k+1;
//定义标题
/*$PHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'ID')
->setCellValue('B1', '名字')
->setCellValue('C1', '价格')
->setCellValue('D1', '款式')
->setCellValue('E1', '电池容量')
->setCellValue('F1', '电池类型');*/
$PHPExcel->setActiveSheetIndex(0)
//Excel的第A列,id是你查出数组的键值,下面以此类推
->setCellValue('A'.$num, $v['id'])
->setCellValue('B'.$num, $v['name'])
->setCellValue('C'.$num, $v['price'])
->setCellValue('D'.$num, $v['style'])
->setCellValue('E'.$num, $v['capacity'])
->setCellValue('F'.$num, $v['type']);
}
$PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");//创建生成的格式
header('Content-Disposition: attachment;filename='.$name.'.xlsx');//下载下来的表格名
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$PHPWriter->save("php://output");//输出文件
exit;
}
}