第一步:使用composer安装上扩展
composer require phpoffice/phpspreadsheet
导出
//依赖注入
protected $userModel;
public function __construct(\app\admin\model\User $user)
{
$this->userModel = $user;
}
public function export()
{
// 设置表格的表头数据
$header = ["A1" => "用户编号", "B1" => "用户名称", "C1" => "手机号码","D1" => "邮箱"];
// 假设下面这个数组从数据库查询出的二维数组($this->userModel是依赖注入)
$data = $this->userModel->field(['id','username','tel','email'])->select()->toArray();
//转化成索引数组
$arr = [];
foreach ($data as $v){
$arr[] = array_values($v);
}
// 保存文件的类型
$type= false;
// 设置下载文件保存的名称
$fileName = '1910A'.time();
// 调用方法导出excel
self::getExport($header,$type,$arr,$fileName);
}
// 导出
public static function getExport($header = [], $type =false, $data = [], $fileName = "1910")
{
// 实例化类
$preadsheet = new Spreadsheet();
// 创建sheet
$sheet = $preadsheet->getActiveSheet();
// 循环设置表头数据
foreach ($header as $k => $v) {
$sheet->setCellValue($k, $v);
}
// 生成数据
$sheet->fromArray($data, null, "A2");
// 样式设置
$sheet->getDefaultColumnDimension()->setWidth(12);
// 设置下载与后缀
if ($type) {
header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
$type = "Xlsx";
$suffix = "xlsx";
} else {
header("Content-Type:application/vnd.ms-excel");
$type = "Xls";
$suffix = "xls";
}
ob_end_clean();//清楚缓存区
// 激活浏览器窗口
header("Content-Disposition:attachment;filename=$fileName.$suffix");
//缓存控制
header("Cache-Control:max-age=0");
// 调用方法执行下载
$writer = IOFactory::createWriter($preadsheet, $type);
// 数据流
$writer->save("php://output");
}
*当你引用完,需要根据自身的控制器导出的路径自行更改,默认路径是runtime下
我的路径习惯更改在:public/storage/
如果已经弹出Excel但是报错可以尝试更改保存的文件类型:$type= true时文件后缀是xlsx,$type = false时文件后缀是xls
导入:
public function getImport(){
// 获取表单上传文件
$file[]= request()->file('myfile');
try {
// 验证文件大小,名称等是否正确
validate(['file' => 'filesize:51200|fileExt:xls,xlsx'])
->check($file);
$savename =\think\facade\Filesystem::disk('public')->putFile( 'file', $file[0]);
$fileExtendName = substr(strrchr($savename, '.'), 1);
// 有Xls和Xlsx格式两种
if ($fileExtendName == 'xlsx') {
$objReader = IOFactory::createReader('Xlsx');
} else {
$objReader = IOFactory::createReader('Xls');
}
$objReader->setReadDataOnly(TRUE);
// 读取文件,tp6默认上传的文件,在runtime的相应目录下,可根据实际情况自己更改
$objPHPExcel = $objReader->load('storage/'.$savename);
$sheet = $objPHPExcel->getSheet(0); //excel中的第一张sheet
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
\PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
$lines = $highestRow - 1;
if ($lines <= 0) {
echo('数据不能为空!');
exit();
}
//循环读取excel表格,整合成数组。如果是不指定key的二维,就用$data[i][j]表示。
for ($j = 2; $j <= $highestRow; $j++) {
$data[$j - 2] = [
//'id' => $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue(),
'username' => $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue(),
'name' => $objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue(),
'tel' => $objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue(),
'status' => $objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue(),
];
}
//dd($data);
// 批量添加数据
if ($this->userModel->saveAll($data)) {
return 200;
}
return 500;
} catch (\think\exception\ValidateException $e) {
return $e->getMessage();
}
}
*添加时注意当id为主键时,无需手动添加
以上导入内容转载自:tp6 导入excel表格_小林的博客-CSDN博客_tp6 读取excel然后自行进行了修改;