##### 安裝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模板