# 一、`PhpSpreadsheet` 介绍
*****
### 1、`PhpSpreadsheet` 是什么
>[info] PhpSpreadsheet是一个用纯PHP编写的库，提供了一组类，使您可以读取和写入不同的电子表格文件格式
> PhpSpreadsheet提供了丰富的API接口，可以设置诸多单元格以及文档属性，包括样式、图片、日期、函数等等诸多应用，总之你想要什么样的Excel表格，PhpSpreadsheet都能做到
* 使用 `PhpSpreadsheet` 开发的PHP要求 7.1或更高版本
* `PhpSpreadsheet` 支持链式操作
### 2、`PhpSpreadsheet` 支持的文件格式
**格式**|**读**|**写**
---|---|---
Open Document Format/OASIS (.ods)|✓|✓
Office Open XML (.xlsx) Excel 2007 and above|✓|✓
BIFF 8 (.xls) Excel 97 and above|✓|✓
BIFF 5 (.xls) Excel 95|✓|
SpreadsheetML (.xml) Excel 2003|✓|
Gnumeric|✓|
HTML|✓|✓
SYLK|✓
CSV|✓|✓
PDF||✓
### 3、`PhpSpreadsheet` 官方网址
* https://phpspreadsheet.readthedocs.io
### 4、`PhpSpreadsheet` 安装
* composer require phpoffice/phpspreadsheet
*****
# 二、基础知识
### 1、载入
```php
<?php
# 载入composer自动加载文件 require 瑞块儿
require 'vendor/autoload.php'; autoload 奥特老特
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
```
### 2、获取工作簿
* getActiveSheet
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
```
### 3、获取单元格
* 两种获取单元格方式
* getCell
* getCellByColumnAndRow
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
# 获取单元格
$cell = $sheet->getCell('A1');
$cell = $sheet->getCellByColumnAndRow(1,1);
```
### 4、设置单元格
* setValue
* 参数：单元格的值
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
# 获取单元格
$cellA = $sheet->getCell('A1');
# 设置单元格值
$cellA->setValue('欧阳克');
# 获取单元格
$cellB = $sheet->getCellByColumnAndRow(1,2);
# 设置单元格值
$cellB->setValue('黄蓉');
# 获取设置单元格，链式操作
$sheet->getCell('A3')->setValue('郭靖');
$sheet->getCellByColumnAndRow(1,4)->setValue('杨康');
```
### 5、获取单元格值
* getValue 获取单元格值
* getCoordinate 获取单元格坐标 可窝的奶特
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
# 获取单元格
$cellA = $sheet->getCell('A1');
# 设置单元格值
$cellA->setValue('欧阳克');
echo '值: ', $cellA->getValue(),PHP_EOL;
echo '坐标: ', $cellA->getCoordinate();
```
### 6、保存表格
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
# 获取单元格
$cellA = $sheet->getCell('A1');
# 设置单元格值
$cellA->setValue('欧阳克');
# 获取单元格
$cellB = $sheet->getCellByColumnAndRow(1,2);
# 设置单元格值
$cellB->setValue('黄蓉');
# 获取设置单元格，链式操作
$sheet->getCell('A3')->setValue('郭靖');
$sheet->getCellByColumnAndRow(1,4)->setValue('杨康');
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
*****
# 三、强化单元格
### 1、设置单元格
* setCellValue
* 参数1：单元格位置
* 参数2：单元格的值
* setCellValueByColumnAndRow
* 参数1：列位置
* 参数2：行位置
* 参数3：单元格的值
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','身高');
$sheet->setCellValueByColumnAndRow(1, 2, 1);
$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
$sheet->setCellValueByColumnAndRow(4, 2, '188cm');
$sheet->setCellValueByColumnAndRow(1, 3, 2);
$sheet->setCellValueByColumnAndRow(2, 3, '黄蓉');
$sheet->setCellValueByColumnAndRow(3, 3, '17岁');
$sheet->setCellValueByColumnAndRow(4, 3, '165cm');
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
### 2、单元格文字样式
* getStyle 获取单元格样式
* getFont 获取单元格文字样式
* setBold 设置文字粗细
* setName 设置文字字体
* setSize 设置文字大小
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','身高');
$sheet->setCellValueByColumnAndRow(1, 2, 1);
$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
$sheet->setCellValueByColumnAndRow(4, 2, '188cm');
$sheet->setCellValueByColumnAndRow(1, 3, 2);
$sheet->setCellValueByColumnAndRow(2, 3, '黄蓉');
$sheet->setCellValueByColumnAndRow(3, 3, '17岁');
$sheet->setCellValueByColumnAndRow(4, 3, '165cm');
$sheet->getStyle('B2')->getFont()->setBold(true)->setName('宋体')->setSize(20);
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
### 3、单元格文字颜色
* getColor() 获取坐标颜色
* setRGB() 设置字体颜色
* getRGB() 获取字体颜色
* setARGB() 设置字体颜色
* getARGB() 获取字体颜色
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','身高');
$sheet->setCellValueByColumnAndRow(1, 2, 1);
$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
$sheet->setCellValueByColumnAndRow(4, 2, '188cm');
$sheet->setCellValueByColumnAndRow(1, 3, 2);
$sheet->setCellValueByColumnAndRow(2, 3, '黄蓉');
$sheet->setCellValueByColumnAndRow(3, 3, '17岁');
$sheet->setCellValueByColumnAndRow(4, 3, '165cm');
$sheet->getStyle('B2')->getFont()->getColor()->setRGB('#AEEEEE');
echo $sheet->getStyle('B2')->getFont()->getColor()->getRGB(),PHP_EOL;
$sheet->getStyle('B3')->getFont()->getColor()->setARGB('FFFF0000');
echo $sheet->getStyle('B3')->getFont()->getColor()->getARGB();
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
### 4、单元格格式
* getNumberFormat 获取格式
* setFormatCode 设置格式
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','2019-10-10 10:10:10');
$sheet->setCellValue('A2','2019-10-10 10:10:10');
$sheet->getStyle('A2')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2);
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
* setWrapText 设置文本里的\n符合为：换行
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1',"欧阳克\n黄蓉");
$sheet->getStyle('A1')->getAlignment()->setWrapText(true);
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
* getHyperlink 获取单元格链接
* setUrl 设置单元格链接
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','www.php.cn');
$sheet->getCell('A1')->getHyperlink()->setUrl('http://www.php.cn');
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
*****
# 四、批量操作
### 1、使用公式
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','10');
$sheet->setCellValue('B1','15');
$sheet->setCellValue('C1','20');
$sheet->setCellValue('D1','25');
$sheet->setCellValue('E1','30');
$sheet->setCellValue('G1','35');
$sheet->setCellValue('A2', '总数：');
$sheet->setCellValue('B2', '=SUM(A7:H7)');
$sheet->setCellValue('A3', '平均数：');
$sheet->setCellValue('B3', '=AVERAGE(A7:H7)');
$sheet->setCellValue('A4', '最小数：');
$sheet->setCellValue('B4', '=MIN(A7:H7)');
$sheet->setCellValue('A5', '最大数：');
$sheet->setCellValue('B5', '=MAX(A7:H7)');
$sheet->setCellValue('A6', '最大数：');
$sheet->setCellValue('B6', '\=MAX(A7:H7)'); // 使用转义字符
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
### 2、批量赋值
* fromArray 从数组中的值填充工作表
* 参数1：数据(数组)
* 参数2：去除某个值
* 参数3：从哪个位置开始
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','身高');
$sheet->fromArray(
[
[1,'欧阳克','18岁','188cm'],
[2,'黄蓉','17岁','165cm'],
[3,'郭靖','21岁','180cm']
],
3,
'A2'
);
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
### 3、合并单元格
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->mergeCells('A1:B5');
$sheet->getCell('A1')->setValue('欧阳克');
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
>[info] 注：合并后，赋值只能给A1，开始的坐标。
### 4、拆分单元格
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->mergeCells('A1:B5');
$sheet->unmergeCells('A1:B5');
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
### 5、列和行操作
* getColumnDimension 获取一列
* getWidth 获取一列的宽度
* setWidth 设置一列的宽度
* setAutoSize 设置一列的宽度自动调整
* getDefaultColumnDimension 获取一列的默认值
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
echo $sheet->getColumnDimension('A')->getWidth();
$sheet->getColumnDimension('A')->setWidth(100);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getDefaultColumnDimension()->setWidth(1);
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
* getRowDimension 获取一行
* getRowHeight 获取一行的高度
* setRowHeight 设置一行的高度
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
echo $sheet->getRowDimension(1)->getRowHeight();
$sheet->getRowDimension(1)->setRowHeight(100);
$sheet->getDefaultRowDimension()->setRowHeight(1);
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
* getHighestColumn 获取总列数
* getHighestRow 获取总行数
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','身高');
$sheet->setCellValueByColumnAndRow(1, 2, 1);
$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
$sheet->setCellValueByColumnAndRow(4, 2, '188cm');
$sheet->setCellValueByColumnAndRow(1, 3, 2);
$sheet->setCellValueByColumnAndRow(2, 3, '黄蓉');
$sheet->setCellValueByColumnAndRow(3, 3, '17岁');
$sheet->setCellValueByColumnAndRow(4, 3, '165cm');
echo $sheet->getHighestColumn();
echo $sheet->getHighestRow();
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
### 6、单元格样式
* applyFromArray
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','身高');
$sheet->setCellValueByColumnAndRow(1, 2, 1);
$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
$sheet->setCellValueByColumnAndRow(4, 2, '188cm');
$styleArray = [
// use PhpOffice\PhpSpreadsheet\Style\Alignment; 文件里常量，就是参数
// Alignment::HORIZONTAL_CENTER 水平居中
// Alignment::VERTICAL_CENTER 垂直居中
'alignment' => [
// 'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
// 'vertical' => Alignment::VERTICAL_CENTER, //垂直居中
'horizontal' => 'center', //水平居中
'vertical' => 'center', //垂直居中
],
// use PhpOffice\PhpSpreadsheet\Style\Border; 文件里常量，就是参数
// Border::BORDER_THICK 边框样式
'borders' => [
'outline' => [
// 'borderStyle' => '\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK',
'borderStyle' => 'thick',
'color' => ['argb' => 'FFFF0000'],
],
],
'font' => [
'name' => '黑体',
'bold' => true,
'size' => 22
]
];
$sheet->getStyle('A1')->applyFromArray($styleArray);
# Xlsx类 将电子表格保存到文件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$writer = new Xlsx($spreadsheet);
$writer->save('1.xlsx');
```
*****
# 五、工作薄操作
### 1、xlsx 文件下载
* IOFactory::createWriter 写入到文件
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','身高');
$sheet->setCellValueByColumnAndRow(1, 2, 1);
$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
$sheet->setCellValueByColumnAndRow(4, 2, '188cm');
// MIME 协议，文件的类型，不设置，会默认html
header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// MIME 协议的扩展
header('Content-Disposition:attachment;filename=1.xlsx');
// 缓存控制
header('Cache-Control:max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
// php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。
$writer->save('php://output');
```
### 2、xls 文件下载
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','身高');
$sheet->setCellValueByColumnAndRow(1, 2, 1);
$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
$sheet->setCellValueByColumnAndRow(4, 2, '188cm');
$filename = '1.xls';
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename=1.xls');
header('Cache-Control:max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
```
### 3、设置工作簿标题
* setTitle
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','身高');
$sheet->setCellValueByColumnAndRow(1, 2, 1);
$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');
$sheet->setCellValueByColumnAndRow(3, 2, '18岁');
$sheet->setCellValueByColumnAndRow(4, 2, '188cm');
$sheet->setTitle('欧阳克');
// MIME 协议，文件的类型，不设置，会默认html
header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// MIME 协议的扩展
header('Content-Disposition:attachment;filename=1.xlsx');
// 缓存控制
header('Cache-Control:max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
// php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。
$writer->save('php://output');
```
### 4、读取表格
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 创建读操作
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
# 打开文件、载入excel表格
$spreadsheet = $reader->load('1.xlsx');
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
# 获取 单元格值 和 坐标
$cellC1 = $sheet->getCell('B2');
echo '值: ', $cellC1->getValue(),PHP_EOL;
echo '坐标: ', $cellC1->getCoordinate(),PHP_EOL;
$sheet->setCellValue('B2','欧阳锋');
# 获取 单元格值 和 坐标
$cellC2 = $sheet->getCell('B2');
echo '值: ', $cellC2->getValue(),PHP_EOL;
echo '坐标: ', $cellC2->getCoordinate();
```
*****
# 六、office 后缀对应的 content-type
**后缀** | **MIME Type**
--- | ---
.doc|application/msword
.dot|application/msword
.docx|application/vnd.openxmlformats-officedocument.wordprocessingml.document
.dotx|application/vnd.openxmlformats-officedocument.wordprocessingml.template
.docm|application/vnd.ms-word.document.macroEnabled.12
.dotm|application/vnd.ms-word.template.macroEnabled.12
.xls|application/vnd.ms-excel
.xlt|application/vnd.ms-excel
.xla|application/vnd.ms-excel
.xlsx|application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
.xltx|application/vnd.openxmlformats-officedocument.spreadsheetml.template
.xlsm|application/vnd.ms-excel.sheet.macroEnabled.12
.xltm|application/vnd.ms-excel.template.macroEnabled.12
.xlam|application/vnd.ms-excel.addin.macroEnabled.12
.xlsb|application/vnd.ms-excel.sheet.binary.macroEnabled.12
.ppt|application/vnd.ms-powerpoint
.pot|application/vnd.ms-powerpoint
.pps|application/vnd.ms-powerpoint
.ppa|application/vnd.ms-powerpoint
.pptx|application/vnd.openxmlformats-officedocument.presentationml.presentation
.potx|application/vnd.openxmlformats-officedocument.presentationml.template
.ppsx|application/vnd.openxmlformats-officedocument.presentationml.slideshow
.ppam|application/vnd.ms-powerpoint.addin.macroEnabled.12
.pptm|application/vnd.ms-powerpoint.presentation.macroEnabled.12
.potm|application/vnd.ms-powerpoint.presentation.macroEnabled.12
.ppsm|application/vnd.ms-powerpoint.slideshow.macroEnabled.12
*****
# 七、实战
### 1、导出数据
>[info] login_log 登陆日志表
```mysql
CREATE TABLE `login_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL COMMENT '管理员ID',
`client` tinyint(4) unsigned DEFAULT '0' COMMENT '0-PC 1-ios 2-android',
`device_id` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '设备id',
`add_time` int(11) DEFAULT '0' COMMENT '创建时间',
`ip` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '登录IP',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1122 DEFAULT CHARSET=utf8mb4 COMMENT='登录日志';
INSERT INTO `login_log` VALUES (2, 34, 0, '865790020627642', 1493111611, 607755826);
INSERT INTO `login_log` VALUES (3, 34, 0, '865790020627642', 1493111948, 607755826);
INSERT INTO `login_log` VALUES (4, 34, 2, '865790020627642', 1493112929, 607755826);
INSERT INTO `login_log` VALUES (5, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493112943, 607755826);
INSERT INTO `login_log` VALUES (6, 34, 2, '865790020627642', 1493113145, 607755826);
INSERT INTO `login_log` VALUES (7, 30, 0, '865790020627642', 1493113151, 607755826);
INSERT INTO `login_log` VALUES (8, 30, 2, '865790020627642', 1493113272, 607755826);
INSERT INTO `login_log` VALUES (9, 18, 0, '865790020627642', 1493114670, 2130706433);
INSERT INTO `login_log` VALUES (10, 18, 1, '865790020627642', 1493114710, 2130706433);
INSERT INTO `login_log` VALUES (11, 18, 1, '865790020627642', 1493114738, 2130706433);
INSERT INTO `login_log` VALUES (14, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493115220, 604467071);
INSERT INTO `login_log` VALUES (15, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493115646, 604467071);
INSERT INTO `login_log` VALUES (16, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493115779, 604467071);
INSERT INTO `login_log` VALUES (17, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493176671, 604467071);
INSERT INTO `login_log` VALUES (18, 33, 1, '609FB031-2AEE-413C-B6AD-F75EC5A3105C', 1493195861, 604467071);
INSERT INTO `login_log` VALUES (19, 33, 1, '609FB031-2AEE-413C-B6AD-F75EC5A3105C', 1493196004, 604467071);
INSERT INTO `login_log` VALUES (20, 33, 1, '609FB031-2AEE-413C-B6AD-F75EC5A3105C', 1493196216, 604467071);
INSERT INTO `login_log` VALUES (21, 33, 1, '609FB031-2AEE-413C-B6AD-F75EC5A3105C', 1493196567, 604467071);
INSERT INTO `login_log` VALUES (22, 33, 1, '609FB031-2AEE-413C-B6AD-F75EC5A3105C', 1493198294, 604467071);
INSERT INTO `login_log` VALUES (23, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493199215, 604467071);
INSERT INTO `login_log` VALUES (24, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493199759, 604467071);
INSERT INTO `login_log` VALUES (25, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493199885, 604467071);
INSERT INTO `login_log` VALUES (26, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493199889, 604467071);
INSERT INTO `login_log` VALUES (27, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493200220, 604467071);
INSERT INTO `login_log` VALUES (28, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493200714, 604467071);
INSERT INTO `login_log` VALUES (29, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493201317, 604467071);
INSERT INTO `login_log` VALUES (30, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493202156, 604467071);
INSERT INTO `login_log` VALUES (31, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493202229, 604467071);
INSERT INTO `login_log` VALUES (32, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493202489, 604467071);
INSERT INTO `login_log` VALUES (33, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493202561, 604467071);
INSERT INTO `login_log` VALUES (34, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493202770, 604467071);
INSERT INTO `login_log` VALUES (35, 33, 0, '865790020627642 200', 1493210525, 2130706433);
INSERT INTO `login_log` VALUES (36, 33, 0, '865790020627642', 1493225069, 2130706433);
INSERT INTO `login_log` VALUES (37, 1, 0, '865790020627642', 1493256291, 2130706433);
INSERT INTO `login_log` VALUES (39, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493256625, 607755425);
INSERT INTO `login_log` VALUES (40, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493261494, 604467071);
INSERT INTO `login_log` VALUES (41, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493263740, 604467071);
INSERT INTO `login_log` VALUES (42, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493264477, 604467071);
INSERT INTO `login_log` VALUES (43, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493264691, 604467071);
INSERT INTO `login_log` VALUES (44, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493264778, 604467071);
INSERT INTO `login_log` VALUES (45, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493272105, 604467071);
INSERT INTO `login_log` VALUES (46, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493272432, 604467071);
INSERT INTO `login_log` VALUES (47, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493273447, 604467071);
INSERT INTO `login_log` VALUES (48, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493273781, 604467071);
INSERT INTO `login_log` VALUES (49, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493274489, 604467071);
INSERT INTO `login_log` VALUES (50, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493274720, 604467071);
INSERT INTO `login_log` VALUES (51, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493274938, 604467071);
INSERT INTO `login_log` VALUES (52, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493275068, 604467071);
INSERT INTO `login_log` VALUES (53, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493275246, 604467071);
INSERT INTO `login_log` VALUES (54, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493275441, 604467071);
INSERT INTO `login_log` VALUES (55, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493275507, 604467071);
INSERT INTO `login_log` VALUES (56, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493275682, 604467071);
INSERT INTO `login_log` VALUES (57, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493275868, 604467071);
INSERT INTO `login_log` VALUES (58, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493275941, 604467071);
INSERT INTO `login_log` VALUES (59, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493278296, 604467071);
INSERT INTO `login_log` VALUES (60, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493278486, 604467071);
INSERT INTO `login_log` VALUES (61, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493278531, 604467071);
INSERT INTO `login_log` VALUES (62, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493278693, 604467071);
INSERT INTO `login_log` VALUES (63, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493278789, 604467071);
INSERT INTO `login_log` VALUES (64, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493278866, 604467071);
INSERT INTO `login_log` VALUES (65, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493279001, 604467071);
INSERT INTO `login_log` VALUES (66, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493279159, 604467071);
INSERT INTO `login_log` VALUES (67, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493279757, 604467071);
INSERT INTO `login_log` VALUES (68, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493279931, 604467071);
INSERT INTO `login_log` VALUES (69, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493280161, 604467071);
INSERT INTO `login_log` VALUES (70, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493281954, 604467071);
INSERT INTO `login_log` VALUES (71, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493281989, 604467071);
INSERT INTO `login_log` VALUES (72, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493283362, 604467071);
INSERT INTO `login_log` VALUES (73, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493283429, 604467071);
INSERT INTO `login_log` VALUES (74, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493283487, 604467071);
INSERT INTO `login_log` VALUES (75, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493284203, 604467071);
INSERT INTO `login_log` VALUES (76, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493288916, 604467071);
INSERT INTO `login_log` VALUES (77, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493289024, 604467071);
INSERT INTO `login_log` VALUES (78, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493289164, 604467071);
INSERT INTO `login_log` VALUES (79, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493290091, 604467071);
INSERT INTO `login_log` VALUES (80, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493290234, 604467071);
INSERT INTO `login_log` VALUES (81, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493290678, 604467071);
INSERT INTO `login_log` VALUES (82, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493293332, 1971873073);
INSERT INTO `login_log` VALUES (83, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493341588, 607762229);
INSERT INTO `login_log` VALUES (84, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493341730, 607762229);
INSERT INTO `login_log` VALUES (85, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493341798, 607762229);
INSERT INTO `login_log` VALUES (86, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493342588, 607762229);
INSERT INTO `login_log` VALUES (87, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493342649, 607762229);
INSERT INTO `login_log` VALUES (88, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493342697, 607762229);
INSERT INTO `login_log` VALUES (89, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493342931, 607762229);
INSERT INTO `login_log` VALUES (90, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493343374, 607762229);
INSERT INTO `login_log` VALUES (91, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493343500, 607762229);
INSERT INTO `login_log` VALUES (92, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493343688, 607762229);
INSERT INTO `login_log` VALUES (93, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493343885, 607762229);
INSERT INTO `login_log` VALUES (94, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493344043, 1971873073);
INSERT INTO `login_log` VALUES (95, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493344131, 607762229);
INSERT INTO `login_log` VALUES (96, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493344395, 607762229);
INSERT INTO `login_log` VALUES (97, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493344464, 607762229);
INSERT INTO `login_log` VALUES (98, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493344615, 607762229);
INSERT INTO `login_log` VALUES (99, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493344783, 607762229);
INSERT INTO `login_log` VALUES (100, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493344868, 607762229);
INSERT INTO `login_log` VALUES (101, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493344889, 607762229);
INSERT INTO `login_log` VALUES (102, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493345126, 607762229);
INSERT INTO `login_log` VALUES (103, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493345619, 607762229);
INSERT INTO `login_log` VALUES (104, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493345764, 607762229);
INSERT INTO `login_log` VALUES (105, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493345944, 607762229);
INSERT INTO `login_log` VALUES (106, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493346353, 607762229);
INSERT INTO `login_log` VALUES (107, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493346408, 607762229);
INSERT INTO `login_log` VALUES (108, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493346675, 607762229);
INSERT INTO `login_log` VALUES (109, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493346794, 607762229);
INSERT INTO `login_log` VALUES (110, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493347141, 607762229);
INSERT INTO `login_log` VALUES (111, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493347324, 607762229);
INSERT INTO `login_log` VALUES (112, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493347609, 607762229);
INSERT INTO `login_log` VALUES (113, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493347733, 607762229);
INSERT INTO `login_log` VALUES (114, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493347808, 607762229);
INSERT INTO `login_log` VALUES (115, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493347939, 607762229);
INSERT INTO `login_log` VALUES (116, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493348084, 607762229);
INSERT INTO `login_log` VALUES (117, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493348144, 607762229);
INSERT INTO `login_log` VALUES (118, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493348180, 607762229);
INSERT INTO `login_log` VALUES (119, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493348232, 607762229);
INSERT INTO `login_log` VALUES (120, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493348607, 607762229);
INSERT INTO `login_log` VALUES (121, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493348674, 607762229);
INSERT INTO `login_log` VALUES (122, 30, 0, '865790020627642', 1493366210, 604467071);
INSERT INTO `login_log` VALUES (123, 1, 0, '865790020627642', 1493366220, 604467071);
INSERT INTO `login_log` VALUES (124, 30, 2, '861451036893744', 1493366957, 604467071);
INSERT INTO `login_log` VALUES (125, 30, 2, '861451036893744', 1493367977, 604467071);
INSERT INTO `login_log` VALUES (126, 30, 2, '861451036893744', 1493368011, 604467071);
INSERT INTO `login_log` VALUES (127, 30, 2, '861451036893744', 1493368082, 604467071);
INSERT INTO `login_log` VALUES (128, 1, 0, '865790020627642', 1493368457, 2130706433);
INSERT INTO `login_log` VALUES (129, 30, 2, '861451036893744', 1493368410, 604467071);
INSERT INTO `login_log` VALUES (131, 30, 2, '861451036893744', 1493368457, 604467071);
INSERT INTO `login_log` VALUES (132, 30, 2, '861451036893744', 1493369484, 604467071);
INSERT INTO `login_log` VALUES (133, 30, 2, '861451036893744', 1493369641, 604467071);
INSERT INTO `login_log` VALUES (134, 30, 2, '861451036893744', 1493370196, 604467071);
INSERT INTO `login_log` VALUES (135, 30, 2, '865790020627642', 1493370259, 607762229);
INSERT INTO `login_log` VALUES (136, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493370433, 604467071);
INSERT INTO `login_log` VALUES (137, 30, 2, '861451036893744', 1493370448, 604467071);
INSERT INTO `login_log` VALUES (138, 33, 0, '865790020627642', 1493370444, 2130706433);
INSERT INTO `login_log` VALUES (139, 30, 2, '861451036893744', 1493370527, 604467071);
INSERT INTO `login_log` VALUES (140, 30, 2, '861451036893744', 1493370571, 604467071);
INSERT INTO `login_log` VALUES (141, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493370578, 604467071);
INSERT INTO `login_log` VALUES (142, 30, 2, '861451036893744', 1493370579, 604467071);
INSERT INTO `login_log` VALUES (143, 30, 2, '861451036893744', 1493370589, 604467071);
INSERT INTO `login_log` VALUES (144, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493370625, 604467071);
INSERT INTO `login_log` VALUES (145, 30, 2, '861451036893744', 1493370639, 604467071);
INSERT INTO `login_log` VALUES (146, 30, 2, '861451036893744', 1493370646, 604467071);
INSERT INTO `login_log` VALUES (147, 30, 2, '861451036893744', 1493370666, 604467071);
INSERT INTO `login_log` VALUES (148, 30, 2, '861451036893744', 1493370673, 604467071);
INSERT INTO `login_log` VALUES (149, 30, 2, '861451036893744', 1493370680, 604467071);
INSERT INTO `login_log` VALUES (150, 30, 2, '861451036893744', 1493370763, 604467071);
INSERT INTO `login_log` VALUES (151, 30, 2, '861451036893744', 1493370770, 604467071);
INSERT INTO `login_log` VALUES (152, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493370791, 604467071);
INSERT INTO `login_log` VALUES (153, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493370849, 604467071);
INSERT INTO `login_log` VALUES (154, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493370892, 604467071);
INSERT INTO `login_log` VALUES (155, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493371659, 604467071);
INSERT INTO `login_log` VALUES (156, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493371710, 604467071);
INSERT INTO `login_log` VALUES (157, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493371744, 604467071);
INSERT INTO `login_log` VALUES (158, 30, 2, '861451036893744', 1493455701, 3080858358);
INSERT INTO `login_log` VALUES (160, 30, 2, '861451036893744', 1493644614, 1017541124);
INSERT INTO `login_log` VALUES (161, 30, 2, '861451036893744', 1493644674, 1017541124);
INSERT INTO `login_log` VALUES (162, 30, 2, '861451036893744', 1493645113, 1017541124);
INSERT INTO `login_log` VALUES (163, 30, 2, '861451036893744', 1493645186, 1017541124);
INSERT INTO `login_log` VALUES (164, 30, 2, '861451036893744', 1493645200, 1017541124);
INSERT INTO `login_log` VALUES (165, 30, 2, '863791032054371', 1493645233, 3704265075);
INSERT INTO `login_log` VALUES (166, 30, 2, '861451036893744', 1493645234, 1017541124);
INSERT INTO `login_log` VALUES (167, 30, 2, '861451036893744', 1493645249, 1017541124);
INSERT INTO `login_log` VALUES (168, 30, 2, '861451036893744', 1493645289, 1017541124);
INSERT INTO `login_log` VALUES (169, 30, 2, '863791032054371', 1493646493, 3704265075);
INSERT INTO `login_log` VALUES (170, 30, 2, '861451036893744', 1493646658, 1017541124);
INSERT INTO `login_log` VALUES (171, 30, 2, 'A0000059B37186', 1493646887, 1911987528);
INSERT INTO `login_log` VALUES (172, 30, 2, 'A0000059B37186', 1493646921, 1911987528);
INSERT INTO `login_log` VALUES (173, 30, 2, '863791032054371', 1493647086, 3704265075);
INSERT INTO `login_log` VALUES (174, 30, 2, 'A0000059B37186', 1493647237, 1911987528);
INSERT INTO `login_log` VALUES (175, 30, 2, '861451036893744', 1493647449, 1017541124);
INSERT INTO `login_log` VALUES (176, 30, 2, 'A0000059B37186', 1493647634, 1911987528);
INSERT INTO `login_log` VALUES (177, 30, 2, '863791032054371', 1493649884, 3704265075);
INSERT INTO `login_log` VALUES (178, 30, 2, 'A0000059B37186', 1493649926, 1911987528);
INSERT INTO `login_log` VALUES (180, 33, 0, '865790020627642', 1493655188, 2130706433);
INSERT INTO `login_log` VALUES (181, 30, 2, '863791032054371', 1493684514, 737855574);
INSERT INTO `login_log` VALUES (182, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493686893, 604467071);
INSERT INTO `login_log` VALUES (183, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493687268, 604467071);
INSERT INTO `login_log` VALUES (184, 30, 2, '865790020627642', 1493688654, 607755083);
INSERT INTO `login_log` VALUES (185, 30, 2, '865790020627642', 1493688662, 607755083);
INSERT INTO `login_log` VALUES (186, 30, 2, '865790020627642', 1493688773, 607755083);
INSERT INTO `login_log` VALUES (187, 30, 2, '865790020627642', 1493688790, 607755083);
INSERT INTO `login_log` VALUES (188, 30, 2, '865790020627642', 1493688826, 607755083);
INSERT INTO `login_log` VALUES (189, 30, 2, '865790020627642', 1493688962, 607755083);
INSERT INTO `login_log` VALUES (190, 30, 2, '865790020627642', 1493688973, 607755083);
INSERT INTO `login_log` VALUES (191, 30, 2, '865790020627642', 1493688998, 607755083);
INSERT INTO `login_log` VALUES (192, 30, 2, '865790020627642', 1493690092, 607755083);
INSERT INTO `login_log` VALUES (193, 30, 2, '865790020627642', 1493690167, 607755083);
INSERT INTO `login_log` VALUES (194, 30, 2, '865790020627642', 1493690176, 607755083);
INSERT INTO `login_log` VALUES (195, 30, 2, '865790020627642', 1493690258, 607755083);
INSERT INTO `login_log` VALUES (196, 30, 2, '865790020627642', 1493690584, 607755083);
INSERT INTO `login_log` VALUES (197, 30, 2, '865790020627642', 1493690614, 607755083);
INSERT INTO `login_log` VALUES (198, 30, 2, '865790020627642', 1493690750, 607755083);
INSERT INTO `login_log` VALUES (199, 30, 2, '865790020627642', 1493690758, 607755083);
INSERT INTO `login_log` VALUES (200, 30, 2, '865790020627642', 1493690960, 607755083);
INSERT INTO `login_log` VALUES (201, 30, 2, '865790020627642', 1493691020, 607755083);
INSERT INTO `login_log` VALUES (202, 30, 0, '865790020627642', 1493691023, 604467071);
INSERT INTO `login_log` VALUES (203, 30, 2, '865790020627642', 1493691028, 607755083);
INSERT INTO `login_log` VALUES (204, 1, 0, '865790020627642', 1493691052, 604467071);
INSERT INTO `login_log` VALUES (205, 30, 2, '865790020627642', 1493691131, 607755083);
INSERT INTO `login_log` VALUES (206, 30, 2, '865790020627642', 1493691142, 607755083);
INSERT INTO `login_log` VALUES (207, 30, 2, '865790020627642', 1493691168, 607755083);
INSERT INTO `login_log` VALUES (208, 30, 2, '865790020627642', 1493691209, 607755083);
INSERT INTO `login_log` VALUES (209, 30, 2, '865790020627642', 1493691220, 607755083);
INSERT INTO `login_log` VALUES (210, 30, 2, '865790020627642', 1493691327, 607755083);
INSERT INTO `login_log` VALUES (211, 30, 2, '865790020627642', 1493691373, 607755083);
INSERT INTO `login_log` VALUES (212, 30, 2, '865790020627642', 1493691450, 607755083);
INSERT INTO `login_log` VALUES (214, 30, 2, '865790020627642', 1493691508, 607755083);
INSERT INTO `login_log` VALUES (215, 30, 2, '865790020627642', 1493691595, 607755083);
INSERT INTO `login_log` VALUES (219, 30, 2, '865790020627642', 1493691733, 607755083);
INSERT INTO `login_log` VALUES (220, 30, 2, '865790020627642', 1493691788, 607755083);
INSERT INTO `login_log` VALUES (222, 30, 2, '865790020627642', 1493691810, 607755083);
INSERT INTO `login_log` VALUES (224, 30, 2, '865790020627642', 1493691816, 607755083);
INSERT INTO `login_log` VALUES (228, 30, 0, '865790020627642', 1493692933, 604467071);
INSERT INTO `login_log` VALUES (231, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493693128, 607755083);
INSERT INTO `login_log` VALUES (232, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493693211, 607755083);
INSERT INTO `login_log` VALUES (234, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493693416, 607755083);
INSERT INTO `login_log` VALUES (237, 30, 2, '863791032054371', 1493693708, 737855574);
INSERT INTO `login_log` VALUES (240, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493694656, 607755083);
INSERT INTO `login_log` VALUES (241, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493694983, 607755083);
INSERT INTO `login_log` VALUES (242, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493695093, 607755083);
INSERT INTO `login_log` VALUES (243, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493695225, 607755083);
INSERT INTO `login_log` VALUES (244, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493695424, 607755083);
INSERT INTO `login_log` VALUES (245, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493695460, 607755083);
INSERT INTO `login_log` VALUES (247, 33, 1, '284CC1A6-2F3A-4B8B-A553-8D7E1A203618', 1493695620, 607755083);
INSERT INTO `login_log` VALUES (250, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493695776, 607755083);
INSERT INTO `login_log` VALUES (252, 30, 2, '865790020627642', 1493695962, 607755083);
INSERT INTO `login_log` VALUES (253, 30, 2, '865790020627642', 1493696416, 607755083);
INSERT INTO `login_log` VALUES (254, 30, 2, '865790020627642', 1493696489, 607755083);
INSERT INTO `login_log` VALUES (255, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493696505, 607755083);
INSERT INTO `login_log` VALUES (256, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493696540, 607755083);
INSERT INTO `login_log` VALUES (257, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493696574, 607755083);
INSERT INTO `login_log` VALUES (258, 30, 2, '865790020627642', 1493696579, 607755083);
INSERT INTO `login_log` VALUES (259, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493696748, 607755083);
INSERT INTO `login_log` VALUES (260, 30, 2, '865790020627642', 1493696771, 607755083);
INSERT INTO `login_log` VALUES (262, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493696853, 607755083);
INSERT INTO `login_log` VALUES (263, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493696893, 607755083);
INSERT INTO `login_log` VALUES (266, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493697205, 607755083);
INSERT INTO `login_log` VALUES (267, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493701429, 607755083);
INSERT INTO `login_log` VALUES (269, 33, 1, '6A0D9EDE-F064-459A-9D41-8C75A45A432C', 1493701693, 607755083);
INSERT INTO `login_log` VALUES (270, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493701909, 607755083);
INSERT INTO `login_log` VALUES (271, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493702034, 607755083);
INSERT INTO `login_log` VALUES (272, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493702115, 607755083);
INSERT INTO `login_log` VALUES (273, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493702236, 607755083);
INSERT INTO `login_log` VALUES (274, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493702482, 607755083);
INSERT INTO `login_log` VALUES (277, 30, 2, '861451036893744', 1493705434, 604467071);
INSERT INTO `login_log` VALUES (278, 30, 2, '861451036893744', 1493705477, 604467071);
INSERT INTO `login_log` VALUES (279, 30, 2, '861451036893744', 1493705491, 604467071);
INSERT INTO `login_log` VALUES (280, 30, 2, '861451036893744', 1493705499, 604467071);
INSERT INTO `login_log` VALUES (293, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493708516, 604467071);
INSERT INTO `login_log` VALUES (294, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493708588, 604467071);
INSERT INTO `login_log` VALUES (295, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493709682, 604467071);
INSERT INTO `login_log` VALUES (298, 30, 2, '861451036893744', 1493710322, 604467071);
INSERT INTO `login_log` VALUES (301, 30, 0, '865790020627642', 1493711222, 604467071);
INSERT INTO `login_log` VALUES (303, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493711973, 604467071);
INSERT INTO `login_log` VALUES (306, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493712701, 1971849285);
INSERT INTO `login_log` VALUES (309, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493712848, 1971849285);
INSERT INTO `login_log` VALUES (311, 30, 2, '861451036893744', 1493712869, 607924339);
INSERT INTO `login_log` VALUES (312, 30, 2, '861451036893744', 1493713166, 607924339);
INSERT INTO `login_log` VALUES (313, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493713622, 604467071);
INSERT INTO `login_log` VALUES (314, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493713994, 604467071);
INSERT INTO `login_log` VALUES (316, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493794339, 604467071);
INSERT INTO `login_log` VALUES (319, 33, 1, 'C850BD97-473F-4EBE-BD7C-E21BD75E7378', 1493794654, 604467071);
INSERT INTO `login_log` VALUES (321, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493794998, 604467071);
INSERT INTO `login_log` VALUES (324, 30, 2, '863791032054371', 1493815146, 2936756211);
INSERT INTO `login_log` VALUES (325, 1, 0, '865790020627642', 1493819655, 2936756211);
INSERT INTO `login_log` VALUES (326, 1, 0, '865790020627642', 1493819744, 2936756211);
INSERT INTO `login_log` VALUES (327, 30, 2, '863791032054371', 1493819851, 2936756211);
INSERT INTO `login_log` VALUES (328, 30, 2, '863791032054371', 1493819862, 2936756211);
INSERT INTO `login_log` VALUES (329, 30, 2, '863791032054371', 1493820027, 1999108160);
INSERT INTO `login_log` VALUES (330, 30, 2, '861451036893744', 1493820044, 607925342);
INSERT INTO `login_log` VALUES (331, 30, 2, '861451036893744', 1493820051, 607925342);
INSERT INTO `login_log` VALUES (332, 30, 2, '863791032054371', 1493820057, 1999108160);
INSERT INTO `login_log` VALUES (333, 30, 2, '863791032054371', 1493820933, 2936756211);
INSERT INTO `login_log` VALUES (334, 30, 0, '865790020627642', 1493859627, 604467071);
INSERT INTO `login_log` VALUES (335, 1, 0, '865790020627642', 1493860264, 604467071);
INSERT INTO `login_log` VALUES (336, 30, 0, '865790020627642', 1493860450, 604467071);
INSERT INTO `login_log` VALUES (337, 1, 0, '865790020627642', 1493860504, 604467071);
INSERT INTO `login_log` VALUES (338, 30, 2, '861451036893744', 1493860555, 604467071);
INSERT INTO `login_log` VALUES (339, 30, 2, '861451036893744', 1493860726, 604467071);
INSERT INTO `login_log` VALUES (340, 30, 2, '861451036893744', 1493862126, 604467071);
INSERT INTO `login_log` VALUES (341, 30, 2, '861451036893744', 1493862647, 604467071);
INSERT INTO `login_log` VALUES (342, 30, 2, '861451036893744', 1493863776, 604467071);
INSERT INTO `login_log` VALUES (343, 30, 2, '861451036893744', 1493863896, 604467071);
INSERT INTO `login_log` VALUES (344, 30, 2, '861451036893744', 1493863997, 604467071);
INSERT INTO `login_log` VALUES (345, 30, 0, '865790020627642', 1493864027, 604467071);
INSERT INTO `login_log` VALUES (346, 30, 2, '861451036893744', 1493864051, 604467071);
INSERT INTO `login_log` VALUES (347, 30, 2, '861451036893744', 1493864125, 604467071);
INSERT INTO `login_log` VALUES (348, 30, 2, '861451036893744', 1493864239, 604467071);
INSERT INTO `login_log` VALUES (349, 30, 2, '861451036893744', 1493864286, 604467071);
INSERT INTO `login_log` VALUES (350, 30, 2, '861451036893744', 1493864373, 604467071);
INSERT INTO `login_log` VALUES (351, 30, 2, '861451036893744', 1493864423, 604467071);
INSERT INTO `login_log` VALUES (352, 30, 2, '861451036893744', 1493864736, 604467071);
INSERT INTO `login_log` VALUES (353, 30, 2, '861451036893744', 1493864781, 604467071);
INSERT INTO `login_log` VALUES (354, 30, 2, '861451036893744', 1493864935, 604467071);
INSERT INTO `login_log` VALUES (355, 30, 2, '861451036893744', 1493864962, 604467071);
INSERT INTO `login_log` VALUES (356, 33, 1, '609FB031-2AEE-413C-B6AD-F75EC5A3105C', 1493865063, 604467071);
INSERT INTO `login_log` VALUES (357, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493865479, 604467071);
INSERT INTO `login_log` VALUES (358, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493865953, 604467071);
INSERT INTO `login_log` VALUES (359, 30, 2, '861451036893744', 1493866699, 604467071);
INSERT INTO `login_log` VALUES (360, 30, 2, '861451036893744', 1493867112, 604467071);
INSERT INTO `login_log` VALUES (361, 1, 0, '865790020627642', 1493867124, 2130706433);
INSERT INTO `login_log` VALUES (362, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493867367, 604467071);
INSERT INTO `login_log` VALUES (363, 30, 2, '861451036893744', 1493867438, 604467071);
INSERT INTO `login_log` VALUES (364, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493867680, 604467071);
INSERT INTO `login_log` VALUES (365, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493867713, 604467071);
INSERT INTO `login_log` VALUES (366, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493867806, 604467071);
INSERT INTO `login_log` VALUES (367, 30, 2, '861451036893744', 1493867892, 604467071);
INSERT INTO `login_log` VALUES (368, 30, 2, '861451036893744', 1493867915, 604467071);
INSERT INTO `login_log` VALUES (369, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493868064, 604467071);
INSERT INTO `login_log` VALUES (370, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493868171, 604467071);
INSERT INTO `login_log` VALUES (371, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493868335, 604467071);
INSERT INTO `login_log` VALUES (372, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493868347, 604467071);
INSERT INTO `login_log` VALUES (373, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493868369, 604467071);
INSERT INTO `login_log` VALUES (374, 30, 2, '861451036893744', 1493868373, 604467071);
INSERT INTO `login_log` VALUES (375, 30, 2, '861451036893744', 1493868493, 604467071);
INSERT INTO `login_log` VALUES (376, 30, 2, '861451036893744', 1493868893, 604467071);
INSERT INTO `login_log` VALUES (377, 30, 2, '861451036893744', 1493868988, 604467071);
INSERT INTO `login_log` VALUES (378, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493869082, 604467071);
INSERT INTO `login_log` VALUES (379, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493869175, 604467071);
INSERT INTO `login_log` VALUES (380, 30, 2, '861451036893744', 1493869183, 604467071);
INSERT INTO `login_log` VALUES (381, 30, 2, '861451036893744', 1493869526, 604467071);
INSERT INTO `login_log` VALUES (382, 30, 2, '861451036893744', 1493869751, 604467071);
INSERT INTO `login_log` VALUES (383, 30, 2, '861451036893744', 1493869830, 604467071);
INSERT INTO `login_log` VALUES (384, 30, 2, '861451036893744', 1493869894, 604467071);
INSERT INTO `login_log` VALUES (385, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493869899, 604467071);
INSERT INTO `login_log` VALUES (386, 30, 2, '861451036893744', 1493870011, 604467071);
INSERT INTO `login_log` VALUES (387, 30, 2, '861451036893744', 1493871556, 604467071);
INSERT INTO `login_log` VALUES (388, 30, 2, '861451036893744', 1493871724, 604467071);
INSERT INTO `login_log` VALUES (389, 30, 2, 'A0000059B37186', 1493872203, 1779614957);
INSERT INTO `login_log` VALUES (390, 30, 2, '861451036893744', 1493874041, 604467071);
INSERT INTO `login_log` VALUES (391, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493874342, 604467071);
INSERT INTO `login_log` VALUES (392, 30, 2, '861451036893744', 1493875009, 604467071);
INSERT INTO `login_log` VALUES (393, 33, 1, 'C13451DB-80F3-4EA0-8DFB-9A81249D132B', 1493875141, 604467071);
INSERT INTO `login_log` VALUES (394, 30, 2, '861451036893744', 1493875498, 604467071);
INSERT INTO `login_log` VALUES (395, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493875703, 607762217);
INSERT INTO `login_log` VALUES (396, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493875961, 607762217);
INSERT INTO `login_log` VALUES (397, 30, 2, '861451036893744', 1493876044, 604467071);
INSERT INTO `login_log` VALUES (398, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493876136, 607762217);
INSERT INTO `login_log` VALUES (399, 30, 2, '861451036893744', 1493876219, 604467071);
INSERT INTO `login_log` VALUES (400, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493876258, 607762217);
INSERT INTO `login_log` VALUES (401, 30, 2, '861451036893744', 1493876318, 604467071);
INSERT INTO `login_log` VALUES (402, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493876410, 607762217);
INSERT INTO `login_log` VALUES (403, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493876485, 607762217);
INSERT INTO `login_log` VALUES (404, 30, 2, '861451036893744', 1493876549, 604467071);
INSERT INTO `login_log` VALUES (405, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493876571, 607762217);
INSERT INTO `login_log` VALUES (406, 30, 2, '861451036893744', 1493876814, 604467071);
INSERT INTO `login_log` VALUES (407, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493876911, 607762217);
INSERT INTO `login_log` VALUES (408, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493876984, 607762217);
INSERT INTO `login_log` VALUES (410, 1, 0, '865790020627642', 1493877063, 2130706433);
INSERT INTO `login_log` VALUES (411, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493877103, 607762217);
INSERT INTO `login_log` VALUES (412, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493877197, 607762217);
INSERT INTO `login_log` VALUES (413, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493877410, 607762217);
INSERT INTO `login_log` VALUES (414, 30, 2, '861451036893744', 1493877433, 604467071);
INSERT INTO `login_log` VALUES (415, 30, 2, '861451036893744', 1493877496, 604467071);
INSERT INTO `login_log` VALUES (416, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493877533, 607762217);
INSERT INTO `login_log` VALUES (417, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493877608, 607762217);
INSERT INTO `login_log` VALUES (418, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493877754, 607762217);
INSERT INTO `login_log` VALUES (419, 30, 2, '861451036893744', 1493877781, 604467071);
INSERT INTO `login_log` VALUES (420, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493877860, 607762217);
INSERT INTO `login_log` VALUES (421, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493877926, 607762217);
INSERT INTO `login_log` VALUES (422, 30, 2, '861451036893744', 1493878185, 604467071);
INSERT INTO `login_log` VALUES (423, 30, 2, '861451036893744', 1493878627, 604467071);
INSERT INTO `login_log` VALUES (424, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493878704, 607762217);
INSERT INTO `login_log` VALUES (425, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493879549, 607762217);
INSERT INTO `login_log` VALUES (426, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493879619, 607762217);
INSERT INTO `login_log` VALUES (427, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493879813, 607762217);
INSERT INTO `login_log` VALUES (428, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493880041, 607762217);
INSERT INTO `login_log` VALUES (429, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493880173, 607762217);
INSERT INTO `login_log` VALUES (430, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493880402, 607762217);
INSERT INTO `login_log` VALUES (431, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493880877, 607762217);
INSERT INTO `login_log` VALUES (432, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493880951, 607762217);
INSERT INTO `login_log` VALUES (433, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493881214, 607762217);
INSERT INTO `login_log` VALUES (434, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493881578, 607762217);
INSERT INTO `login_log` VALUES (435, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493881692, 607762217);
INSERT INTO `login_log` VALUES (436, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493881807, 607762217);
INSERT INTO `login_log` VALUES (437, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493881864, 607762217);
INSERT INTO `login_log` VALUES (438, 30, 2, '861451036893744', 1493882007, 604467071);
INSERT INTO `login_log` VALUES (439, 30, 2, '861451036893744', 1493882128, 604467071);
INSERT INTO `login_log` VALUES (440, 30, 2, '861451036893744', 1493882362, 604467071);
INSERT INTO `login_log` VALUES (441, 30, 2, '861451036893744', 1493882374, 604467071);
INSERT INTO `login_log` VALUES (442, 30, 2, '861451036893744', 1493882413, 604467071);
INSERT INTO `login_log` VALUES (448, 30, 2, '861451036893744', 1493882902, 604467071);
INSERT INTO `login_log` VALUES (449, 30, 2, '861451036893744', 1493882934, 604467071);
INSERT INTO `login_log` VALUES (455, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493883064, 607762217);
INSERT INTO `login_log` VALUES (457, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493883135, 607762217);
INSERT INTO `login_log` VALUES (458, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493883267, 607762217);
INSERT INTO `login_log` VALUES (461, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493883492, 607762217);
INSERT INTO `login_log` VALUES (462, 33, 1, '1D561B7A-041F-4920-B244-E7ED556581C7', 1493883520, 607762217);
INSERT INTO `login_log` VALUES (467, 1, 0, '865790020627642', 1493883898, 604467071);
INSERT INTO `login_log` VALUES (476, 33, 1, 'F6B1D2ED-56FA-409F-AB6D-F606D651AAA6', 1493884253, 1971849285);
INSERT INTO `login_log` VALUES (477, 18, 1, '865790020627642', 1493884270, 987409906);
INSERT INTO `login_log` VALUES (483, 30, 2, '865790020627642', 1493884976, 607762217);
INSERT INTO `login_log` VALUES (484, 30, 1, '865790020627642', 1493884983, 987409906);
INSERT INTO `login_log` VALUES (486, 30, 2, '861451036893744', 1493885062, 604467071);
INSERT INTO `login_log` VALUES (487, 33, 1, 'F6B1D2ED-56FA-409F-AB6D-F606D651AAA6', 1493885161, 607762217);
INSERT INTO `login_log` VALUES (488, 33, 1, 'F6B1D2ED-56FA-409F-AB6D-F606D651AAA6', 1493885255, 607762217);
INSERT INTO `login_log` VALUES (490, 33, 1, 'F6B1D2ED-56FA-409F-AB6D-F606D651AAA6', 1493885272, 607762217);
INSERT INTO `login_log` VALUES (492, 33, 1, 'F6B1D2ED-56FA-409F-AB6D-F606D651AAA6', 1493885279, 607762217);
INSERT INTO `login_log` VALUES (494, 30, 2, '861451036893744', 1493885303, 604467071);
INSERT INTO `login_log` VALUES (495, 30, 1, 'F6B1D2ED-56FA-409F-AB6D-F606D651AAA6', 1493885327, 607762217);
INSERT INTO `login_log` VALUES (496, 33, 1, 'F6B1D2ED-56FA-409F-AB6D-F606D651AAA6', 1493885344, 607762217);
INSERT INTO `login_log` VALUES (497, 30, 2, '861451036893744', 1493885421, 604467071);
INSERT INTO `login_log` VALUES (498, 33, 1, 'F6B1D2ED-56FA-409F-AB6D-F606D651AAA6', 1493885475, 607762217);
INSERT INTO `login_log` VALUES (499, 33, 1, 'F6B1D2ED-56FA-409F-AB6D-F606D651AAA6', 1493885672, 607762217);
INSERT INTO `login_log` VALUES (500, 30, 2, '861451036893744', 1493885739, 604467071);
```
>[info] index.php 数据列表页面
```php
<?php
# 载入方法库
require 'function.php';
$select = select('login_log','*');
if(empty($select)){
exit;
}else{
foreach($select as &$v){
switch ($v['client']) {
case 0:
$v['client'] = 'PC电脑';
break;
case 1:
$v['client'] = '苹果手机';
break;
case 2:
$v['client'] = '安卓手机';
break;
}
$v['add_time'] = date('Y-m-d H:i:s',$v['add_time']);
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>导出数据</title>
<link rel="stylesheet" href="layui/css/layui.css">
</head>
<body>
<div style="text-align:center;">
<a href="download.php" class="layui-btn layui-btn-radius layui-btn-danger">导出数据</a>
</div>
<table class="layui-table">
<thead>
<tr>
<th>ID</th>
<th>用户ID</th>
<th>登陆设备</th>
<th>设备号</th>
<th>登陆时间</th>
<th>登陆ip</th>
</tr>
</thead>
<tbody>
<?php
foreach($select as $v){
?>
<tr>
<td><?php echo $v['id'] ?></td>
<td><?php echo $v['uid'] ?></td>
<td><?php echo $v['client'] ?></td>
<td><?php echo $v['device_id'] ?></td>
<td><?php echo $v['add_time'] ?></td>
<td><?php echo $v['ip'] ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</body>
</html>
```
>[info] download.php 导出操作
```php
<?php
# 载入方法库
require 'function.php';
$select = select('login_log','*');
if(empty($select)){
exit;
}else{
foreach($select as &$v){
switch ($v['client']) {
case 0:
$v['client'] = 'PC电脑';
break;
case 1:
$v['client'] = '苹果手机';
break;
case 2:
$v['client'] = '安卓手机';
break;
}
$v['add_time'] = date('Y-m-d H:i:s',$v['add_time']);
}
}
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','用户ID');
$sheet->setCellValue('C1','登陆设备');
$sheet->setCellValue('D1','设备号');
$sheet->setCellValue('E1','登陆时间');
$sheet->setCellValue('F1','登陆ip');
$sheet->fromArray(
$select,
null,
'A2'
);
// MIME 协议，文件的类型，不设置，会默认html
header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// MIME 协议的扩展
header('Content-Disposition:attachment;filename=1.xlsx');
// 缓存控制
header('Cache-Control:max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
// php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。
$writer->save('php://output');
?>
```
### 2、导入数据
* 下载表格数据：链接:https://pan.baidu.com/s/19SLlTHWlc-7ADI3ibqmyrw 密码:diwq
>[info] 数据库
```mysql
# 商品分类表
CREATE TABLE `shop_cat` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`pid` int(10) unsigned DEFAULT '0' COMMENT '父ID',
`name` varchar(50) DEFAULT NULL COMMENT '分类名',
`status` tinyint(1) unsigned DEFAULT '1' COMMENT '状态 1开启 0关闭',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COMMENT='分类表';
INSERT INTO `shop_cat` VALUES (1, 0, '女装', 1);
INSERT INTO `shop_cat` VALUES (2, 0, '男装', 1);
INSERT INTO `shop_cat` VALUES (3, 0, '孕产', 1);
INSERT INTO `shop_cat` VALUES (4, 1, '连衣裙', 1);
INSERT INTO `shop_cat` VALUES (5, 1, '牛仔裤', 1);
INSERT INTO `shop_cat` VALUES (6, 2, '衬衫', 1);
INSERT INTO `shop_cat` VALUES (7, 3, '睡衣', 1);
# 商品表
CREATE TABLE `shop_list` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cat_id` int(10) unsigned DEFAULT NULL COMMENT '分类ID',
`cat_fid` int(10) unsigned DEFAULT NULL COMMENT '分类父ID',
`title` varchar(200) NOT NULL COMMENT '商品标题',
`price` double(10,2) unsigned NOT NULL COMMENT '价格',
`img` varchar(200) NOT NULL COMMENT '商品图片',
`add_time` int(10) unsigned NOT NULL COMMENT '添加时间',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
```
>[info] index.php 导入页面
```php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>导出数据</title>
<link rel="stylesheet" href="layui/css/layui.css">
</head>
<body>
<div style="text-align:center;">
<button type="button" class="layui-btn" id="up"><i class="layui-icon"></i>上传文件</button>
<a href="download.php" class="layui-btn layui-btn-danger"><i class="layui-icon"></i>示例下载</a>
</div>
<div id="log" style="text-align:center;">
</div>
</body>
</html>
<script src="layui/layui.js" charset="utf-8"></script>
<script>
layui.use('upload', function(){
var $ = layui.jquery
,upload = layui.upload;
upload.render({
elem: '#up'
,url: 'index10.php'
,accept: 'file' //普通文件
,done: function(res){
if(res.code == 0){
for(var i=0;i<res.data.length;i++){
$("#log").append('<div>'+res.data[i]+'</div>');
}
}
}
});
})
</script>
```
>[info] 导入功能
```php
<?php
$file = $_FILES['file']['tmp_name'];
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 载入方法库
require 'function.php';
# 创建读操作
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
# 打开文件、载入excel表格
$spreadsheet = $reader->load($file);
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
# 获取总列数
$highestColumn = $sheet->getHighestColumn();
# 获取总行数
$highestRow = $sheet->getHighestRow();
# 列数 改为数字显示
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
$log = [];
for($a=2;$a<$highestRow;$a++){
$title = $sheet->getCellByColumnAndRow(1,$a)->getValue();
$cat_fname = $sheet->getCellByColumnAndRow(2,$a)->getValue();
$cat_name = $sheet->getCellByColumnAndRow(3,$a)->getValue();
$price = $sheet->getCellByColumnAndRow(4,$a)->getValue();
$img = $sheet->getCellByColumnAndRow(5,$a)->getValue();
$cat_fid = find('shop_cat','id','name="'.$cat_fname.'"');
$cat_id = find('shop_cat','id','name="'.$cat_name.'"');
$data = [
'title' => $title,
'cat_fid' => $cat_fid['id'],
'cat_id' => $cat_id['id'],
'price' => $price,
'img' => $img,
'add_time' => time(),
];
$ins = insert('shop_list',$data);
if($ins){
$log[] = '第'.$a.'条，插入成功';
}else{
$log[] = '第'.$a.'条，插入失败';
}
}
echo json_encode(['code'=>0,'msg'=>'成功','data'=>$log]);
```
>[info] 下载示例(范文)
```php
<?php
# 载入composer自动加载文件
require 'vendor/autoload.php';
# 给类文件的命名空间起个别名
use \PhpOffice\PhpSpreadsheet\Spreadsheet;
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','商品标题');
$sheet->setCellValue('B1','一级分类');
$sheet->setCellValue('C1','二级分类');
$sheet->setCellValue('D1','进货价');
$sheet->setCellValue('E1','图片');
$data = [
'云朵般轻盈的仙女裙 高级钉珠收腰长裙 气质无袖连衣裙',
'女装',
'连衣裙',
279.99,
'https://gd3.alicdn.com/imgextra/i3/266969832/O1CN01PWUBBB2MV6ekBKtb6_!!266969832.jpg_400x400.jpg',
];
$sheet->fromArray(
$data,
null,
'A2'
);
// MIME 协议，文件的类型，不设置，会默认html
header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// MIME 协议的扩展
header('Content-Disposition:attachment;filename=商品列表示例.xlsx');
// 缓存控制
header('Cache-Control:max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
// php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。
$writer->save('php://output');
```
- 序言
- PHP基础
- 认识PHP
- 环境安装
- PHP语法
- 流程控制
- PHP数组
- PHP函数
- PHP类与对象
- PHP命名空间
- PHP7新特性
- PHP方法库
- PHP交互
- 前后端交互
- 项目常规开发流程
- MySQL数据库
- 会话控制
- Ajax分页技术
- 细说函数
- 类与对象
- 对象进阶
- 类与对象进阶
- OOP面向对象
- 设计模式
- 路由与模板引擎
- 异常类
- PHP爬虫
- PHP抓取函数
- PHP匹配函数
- 正则表达式
- PHP字符串函数
- 抓取实战
- PHP接口
- 了解接口
- PHP插件
- PHPSpreadsheet
- ThinkPHP6
- 安装
- 架构
- 数据库
- 数据库操作
- 视图
- 模版
- 模型
- 杂项
- 命令行
- 交互
- 微信小程序
- 介绍
- 配置
- 组件
- 交互
- API
- 其他知识
- 百度小程序
- 介绍
- 配置
- 组件
- 交互
- API
- 其他知识
- Linux
- 服务器上线流程
- 安装svn
- MySQL
- 认识MySQL
- MySQL函数
- 杂项
- composer依赖管理工具