##### 安装phpspreadsheet
`composer require phpoffice/phpspreadsheet `
##### 导出（php）
```
~~~
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
~~~
```
```
~~~
public function index(){
if ($this->request->isAjax()){
$this->check_isPost();
$agency_sell_log = Db::name('agency_sell_log');
$start = $this->request->post("start", '');
$end = $this->request->post("end", '');
$where = [];
$where['agency_user_id'] = $this->auth->id;
$where['status'] = 3;
if ($start > $end) {
$where['trade_time'] = ['between time', [strtotime($end . ' 00:00:00'), strtotime($start . ' 23:59:59')]];
} else {
$where['trade_time'] = ['between time', [strtotime($start . ' 00:00:00'), strtotime($end . ' 23:59:59')]];
}
$adminList = $agency_sell_log
->field('id,money,trade_number,type,trade_time,agency_id')
->where($where)->select();
if (empty($adminList)) {
$this->error(__('搜索的时间暂无成功的订单'));
}
$newExcel = new Spreadsheet(); //创建一个新的excel文档
$objSheet = $newExcel->getActiveSheet(); //获取当前操作sheet的对象
$objSheet->setTitle($this->userInfo['username'].'_打款记录'); //设置当前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);
$newExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
$newExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
$newExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
//设置第一栏的标题
$objSheet->setCellValue('A1', '商户会员名')
->setCellValue('B1', '购买积分')
->setCellValue('C1', '订单状态')
->setCellValue('D1', '单类型')
->setCellValue('E1', '交易号')
->setCellValue('F1', '交易时间')
->setCellValue('G1', '收款账号');
//第二行起，每一行的值,setCellValueExplicit是用来导出文本格式的。
//->setCellValueExplicit('C' . $k, $val['admin_password']PHPExcel_Cell_DataType::TYPE_STRING),可以用来导出数字不变格式
$agency = Db::name('agency');
foreach ($adminList as $k => $val) {
$k = $k + 2;
switch ($val['type']){
case 0:$type='会员单';break;
case 1:$type='代付单';break;
case 2:$type='商户会员单';break;
case 3:$type='商户前两笔充值';break;
default:$type='渠道代付';break;
}
$acc_no=$agency->where('id',$val['agency_id'])->value('acc_no');
$objSheet->setCellValue('A' . $k, $this->auth->username)
->setCellValue('B' . $k, $val['money'])
->setCellValue('C' . $k, '支付成功')
->setCellValue('D' . $k, $type)
->setCellValue('E' . $k, $val['trade_number'])
->setCellValue('F' . $k, date('Y-m-d H:i:s',$val['trade_time']))
->setCellValueExplicit('G' . $k, $acc_no,DataType::TYPE_STRING);
}
$filename = $this->auth->username.'_'.$start.'__'.$end;
$format = 'Xls';
// $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);
$f=ROOT_PATH.'public/excel/'.$this->auth->username."/";
if (!is_dir($f)){
@mkdir($f,0777,true);
}
$save_name='excel/'. $this->auth->username . '/' . $filename . '.xlsx';
//保存本地
$objWriter->save(ROOT_PATH . 'public/' . $save_name);
$data = [
'down_url' => 'http://www.euback.com/'. $save_name
];
$this->success(__('Operation completed'), $data, 102);
}
}
~~~
```
##### 导入
```
~~~
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
~~~
```
```
~~~
public function import(){
$file = $this->request->request('file');
if (!$file) {
$this->error(__('Parameter %s can not be empty', 'file'));
}
$filePath = ROOT_PATH . DS . 'public' . DS . $file;
if (!is_file($filePath)) {
$this->error(__('No results were found'));
}
//实例化reader
$ext = pathinfo($filePath, PATHINFO_EXTENSION);
if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
$this->error(__('Unknown data format'));
}
$type = pathinfo($filePath);
$type = strtolower($type["extension"]);
$types = "";
if ($type == 'xlsx') {
$types = 'Xlsx';
} elseif ($type == 'xls') {
$types = 'Xls';
}
try {
$objReader = IOFactory::createReader($types);
$PHPExcel = $objReader->load($filePath);
$currentSheet = $PHPExcel->getSheet(0); //读取excel文件中的第一个工作表
$highestColumnIndex = Coordinate::columnIndexFromString("C");//总列数
$allRow = $currentSheet->getHighestRow(); //取得一共有多少行
$dataList = []; //声明数组
/**从第三行开始输出*/
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
/**从第A列开始输出*/
for ($col = 1; $col < $highestColumnIndex; $col++) {
if ($col)
$dataList[] = $currentSheet->getCellByColumnAndRow($col, $currentRow)->getValue();
}
}
$arr=[];
foreach ($dataList as $k=>$v){
if(!empty($v)){
$arr[]=[
'NickName'=>$v,
'IsUsed'=>0
];
}
}
// writeLog($dataList,'test');
if (empty($dataList)){
$this->error('没有获取到昵称');
}
writeLog($dataList,'test');
//用完就删除 文件
@unlink($filePath);
$this->success('上传成功','');
} catch (Exception $exception) {
$this->error($exception->getMessage());
}
}
~~~
```
##### html (javascript)
```
$('.data-export').click(function(){
var startDate = $('#startDate').val();
var endDate = $('#endDate').val();
if(startDate=="" || endDate==""){
layer.msg('请选择开始和结束时间');
return ;
}
$.ajax({
url: '{:url("export_data")}',
type: 'post',
data: {
start:startDate,
end:endDate,
},
dataType: 'json',
beforeSend: function(data) {
layer.load(1);
},success: function(data) {
if(data.code==1){
layer.open({
type: 1
,title: false //不显示标题栏
,closeBtn: false
,area: ['80%','20%']
,shade: 0.5
,id: 'LAY_layuipro' //设定一个id，防止重复弹出
,btn: ['下载', '取消']
,btnAlign: 'c'
,moveType: 0 //拖拽模式，0或者1
,content: '<div style="padding: 30px; line-height: 22px; background-color: #393D49; color: #fff; font-weight: 300;">数据已导出,点击下载！</div>'
,success: function(layero){
var btn = layero.find('.layui-layer-btn');
btn.find('.layui-layer-btn0').attr({
href: data.data
,target: '_blank'
});
}
});
}else{
layer.msg(data.msg,{time:1500,icon:15});
}
},
complete:function(){
layer.closeAll("loading");
},
error:function (){
layer.msg('未知错误');
layer.closeAll("load");
}
})
});
```
- 空白目录
- thinkphp5
- tools-常用类库
- redis类库
- Excel类库
- File文件操作类库
- Http请求类库
- Maile邮件发送
- Hooks行为钩子
- 七牛云
- 随机数和字符串生成
- 字符串处理
- 时间类处理
- tree型转换
- 工具类库
- 文件打包下载
- 常用功能
- 文件上传
- php生成word文档
- elasticsearch 基本搜索
- 使用jwt开发API接口
- 安装模及搭建
- ApiCheck.php
- ApiCheckLogin.php
- common.php
- Login.php
- Comment.php
- 汉字转拼音
- 安装使用
- Pinyin类
- elasticsearch操作
- 常用方法
- 数据源生成layui-select
- 获取自定义配置项
- 百度编辑器
- 格式化文件大小
- 多语言设置
- hook监听
- 域名绑定到模块
- thinkphp6
- 文件上传
- tp5totp6
- 创建路径
- 获取类所有方法
- password_hash加密验证
- 生成 qrcode
- 邮件发送
- 获取QQ信息
- GoogleAuthenticator
- redis限流
- redis 加锁
- 百度翻译
- QueryList爬取数据
- 获取时间类
- 命令
- Git常用命令
- easyswoole
- pix_qrcode
- 验证 cpf,cnpj
- php常用方法
- 日志
- 卡通头像
- 两位小数
- 图片转base64
- auth加密解密
- phpoffice/phpspreadsheet导入导出
- fastadmin
- 树结构
- 单选框
- 复选框
- 二级搜索
- select选择框
- selectpage选中回调事件
- 标签添加
- 修改where条件
- 表格列表中添加input框
- selectpage事件
- fieldlist
- js操作
- test_js
- 多表格
- template模板