技术饭
使用phpoffice/phpspreadsheet，导入导出数据
1、composer安装：
composer require phpoffice/phpspreadsheet
2、导出类Export，放在服务层：
<?php
namespace app\backend\service;
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory; //use \PHPExcel_Style_NumberFormat; //设置列的格式==>>设置文本格式
class Export
{
public function exportAdmin($startTime, $endTime)
{
$adminList = db('admin')
->where('create_time', 'between', [$startTime, $endTime])
->order(['admin_id' => 'desc'])
->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['admin_id'])
->setCellValue('B' . $k, $val['admin_username'])
->setCellValue('C' . $k, $val['admin_password'])
->setCellValue('D' . $k, date('Y-m-d H:i:s', $val['create_time']));
}
$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');
//通过php保存在本地的时候需要用到
//$objWriter->save($dir.'/demo.xlsx');
//以下为需要用到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;
}
}
3、导入类Import，放在服务层：
<?php
namespace app\backend\service;
use think\Controller;
use \PhpOffice\PhpSpreadsheet\IOFactory;
class Import extends Controller
{
public function importAdmin()
{
//获取表格的大小，限制上传表格的大小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] = [
'admin_username' => $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue(),
'admin_password' => $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue(),
'create_time' => time()
];
//看下用户名是否存在。将存在的用户名保存在数组里。
$userExist = db('admin')->where('admin_username', $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('admin')->insertAll($data);
if ($res) {
$this->success('上传成功！', '/backend/admin', '', 1);
}
}
}
}
4、导出类Export，放在服务层：
/**
* [importoptions 读取数据]
*/
public function importoptions()
{
if($this->request->isPost()){
//获取参数
$params = $this->request->param();
$inputFileName = '';
//上传文件
$file = request()->file('file');
$info = $file->validate(['ext' => 'xls,xlsx'])->move('./uploads/xslx/');
if($info){
// 成功上传后 获取上传信息
$inputFileName = './uploads/xslx/' . $info->getSaveName();
} else {
$this->error('文件上传失败：'.$file->getError());
}
//验证文件
if(!file_exists($inputFileName)){
$this->error('文件异常');
}
//获取excel文件
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$spreadsheet = $reader->load($inputFileName);
$data = $spreadsheet->getActiveSheet()->toArray();
if(!empty($data)){
unset($data[0]);
}
//获取所有题目编号
$number = \Db::name('wechat_guess_options')->column('number');
$add_data = [];
if(!empty($data)){
foreach ($data as $key => $value) {
if(in_array($value[0], $number)){
continue;
}
//去除重复编号记录
$add_data[$key]['number'] = $value[0];
$add_data[$key]['name'] = $value[1];
$add_data[$key]['answer'] = $value[2];
$add_data[$key]['tips'] = $value[3];
$add_data[$key]['update_time'] = date("Y-m-d H:i:s");
}
}
//重新索引
$add_data = array_values($add_data);
if(empty($add_data)){
$this->error('导入数据已经存在');
}
//写入数据
$res = \Db::name('wechat_guess_options')->insertAll($add_data);
if($res){
$this->success('导入成功');
} else {
$this->error('导入失败');
}
}
}
5、导出类Export，放在服务层：
/**
* [exportSignup 导出数据]
*/
public function exportSignup()
{
//查询数据
$data = $this->model_signup->field('id,name,mobile,address,ip,create_time,thumb')->order('create_time desc')->select();
//处理数据
if(!empty($data)){
foreach ($data as $key => $value) {
//ip
if(isset($value['ip']) && !empty($value['ip'])){
$data[$key]['ip'] = getIpInfo($value['ip']);
}
}
}
//创建一个新的excel文档
$newExcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
//获取当前操作sheet的对象
$objSheet = $newExcel->getActiveSheet();
//设置当前sheet的标题
$objSheet->setTitle('报名数据');
//设置宽度为true,不然太窄了
$newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$newExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$newExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);
$newExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
$newExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
$newExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
//设置默认行宽度
//$newExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(150);
//设置默认行高度
//$newExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(50);
//设置第一栏的标题
$objSheet->setCellValue('A1', 'ID')
->setCellValue('B1', '姓名')
->setCellValue('C1', '电话')
->setCellValue('D1', '地址')
->setCellValue('E1', 'IP信息')
->setCellValue('F1', '时间')
->setCellValue('G1', '图片');
//第二行起，每一行的值,setCellValueExplicit是用来导出文本格式的。
//->setCellValueExplicit('C' . $key, $val['admin_password']PHPExcel_Cell_DataType::TYPE_STRING),可以用来导出数字不变格式
if(!empty($data)){
foreach ($data as $key => $val) {
//数据写入
$key = $key + 2;
$objSheet->setCellValue('A' . $key, $val['id'])
->setCellValue('B' . $key, $val['name'])
->setCellValue('C' . $key, $val['mobile'])
->setCellValue('D' . $key, $val['address'])
->setCellValue('E' . $key, $val['ip']['area'] . " " . $val['ip']['country'])
->setCellValue('F' . $key, $val['create_time']);
//处理图片
$file_path = '';
if(!empty($val['thumb'])){
$thumb = unserialize($val['thumb']);
$file_path = ".".$thumb[0]['photo'];
}
//图片设置
if(!empty($file_path) && file_exists($file_path)){
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName($val['name']);
$drawing->setDescription($val['name']);
$drawing->setPath($file_path);
$drawing->setHeight(36);
//$drawing->setWidth(50);
$drawing->setCoordinates('G' . $key);
$drawing->setWorksheet($newExcel->getActiveSheet());
//设置行高
$newExcel->getActiveSheet()->getRowDimension($key)->setRowHeight(30);
}
}
} else {
$this->error('暂无数据');
}
//导出
$filename = '报名数据';
$format = 'Xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format));
header('Cache-Control: max-age=0');
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($newExcel, $format);
$objWriter->save('php://output');
//通过php保存在本地的时候需要用到
//$objWriter->save($dir.'/demo.xlsx');
//以下为需要用到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;
}
文明上网理性发言！