安装phpoffice/phpspreadsheet
composer require phpoffice/phpspreadsheet
github地址: https://github.com/PHPOffice/PhpSpreadsheet
PhpSpreadsheet's documentation相关文档:https://phpspreadsheet.readthedocs.io/en/develop/
用法示例:https://blog.csdn.net/gc258_2767_qq/article/details/81003656
https://www.wj0511.com/site/detail.html?id=347
使用范例(范例使用ThinkPHP5.0框架)
/**
* 表格数据导出
*/
public function export_excel()
{
//获取任务对应的表名,以及采集数据id
$param = $this->request->param();
$id = $param['id'];
$info = Db::name('crawler_task')->where('id', $id)->find();
$table_name = $info['table_name'];
$crawler_id = json_decode($info['crawler_id'], true);
//获取数据
$data = Db::table($info['table_name'])->whereIn('id', $crawler_id)->select()->toArray();
//获取表信息
$sql = "SHOW FULL COLUMNS FROM $table_name";
$table_structure = Db::table($table_name)->query($sql);
$table_field = array_column($table_structure, 'Field');
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$field_len = count($table_field);
$end_alpha = chr(63 + $field_len);
// 设置表标题名称
$worksheet->setTitle($info['task_name']);
//循环填充第一行数据
foreach ($table_structure as $key => $value) {
if ($value['Field'] != 'id') { //去除id表头
$worksheet->setCellValueByColumnAndRow($key, 1, $value['Field']);
}
}
//设置第一行样式
$styleArray = [
'font' => [
'bold' => true
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
$worksheet->getStyle('A1:' . $end_alpha . '1')->applyFromArray($styleArray)->getFont()->setSize(14);
//填充表数据
$len = count($data);
for ($i = 0; $i < $len; $i++) {
$j = $i + 2;
foreach ($table_field as $key => $value) {
if ($key != 0) { //去除id数据
$worksheet->setCellValueByColumnAndRow($key, $j, $data[$i][$value]);
}
}
}
//设置数据表格样式
$styleArrayBody = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '666666'],
],
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
$total_rows = $len + 1;
//添加所有边框/居中
$worksheet->getStyle('A1:' . $end_alpha . $total_rows)->applyFromArray($styleArrayBody);
$file_name = $info['task_name'] . ".xls";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . $file_name );
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
}
mysql-table里面的数据与导出变化比较大时
public function exportExcel()
{
//获取任务对应的表名,以及采集数据id
$id = input('post.id');
$info = Db::name('task')->where('id', $id)->find();
$table_info = Db::name('templates')->where('id', $info['temp_id'])->find();
$table_name = $table_info['table_name'];
//获取数据
$data = Db::table($table_name )->where('col_task_id', $id)->where('is_delete', 0)->select();
//获取表信息
$sql = "SHOW FULL COLUMNS FROM $table_name";
$table_structure = Db::table($table_name)->query($sql);
$table_field = array_column($table_structure, 'Field');
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$field_len = count($table_field);
$end_alpha = chr(63 + $field_len - 2);
// 设置表标题名称
$worksheet->setTitle($info['name']);
//循环填充第一行数据
foreach ($table_structure as $key => $value) {
// print_r(11111);
if ($value['Field'] != 'id' && $value['Field'] != 'is_delete' && $value['Field'] != 'col_task_id' ) { //去除id表头, is_delete col_task_id
$worksheet->setCellValueByColumnAndRow($key-1, 1, $value['Field']);
}
}
//设置第一行样式
$styleArray = [
'font' => [
'bold' => true
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
$worksheet->getStyle('A1:' . $end_alpha . '1')->applyFromArray($styleArray)->getFont()->setSize(14);
//填充表数据
$len = count($data);
for ($i = 0; $i < $len; $i++) {
$j = $i + 2;
foreach ($table_field as $key => $value) {
if ($key != 0 && $key != 1 && $key != 7) { //去除id, is_delete, col_task_id数据
$worksheet->setCellValueByColumnAndRow($key - 1, $j, $data[$i][$value]);
}
}
}
//设置数据表格样式
$styleArrayBody = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '666666'],
],
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
$total_rows = $len + 1;
//添加所有边框/居中
$worksheet->getStyle('A1:' . $end_alpha . $total_rows)->applyFromArray($styleArrayBody);
$file_name = $info['name'] . ".xls";
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . urlencode($file_name) );
header('Cache-Control: max-age=0');
header('Access-Control-Expose-Headers: Content-Disposition');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
}
示例二
<?php
namespace app\index\controller;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use think\Db;
class Index
{
//统计表存放地址
private $shangzhibo_Excel = __DIR__ . "/2020.2.12数据.xlsx";
//生成的统计表存放位置
private $statistics_excel_save_path = __DIR__;
//读取Excel数据
public function readExcel($file)
{
$sheetname = ['地域分布'];
/* 转码 */
$file = iconv("utf-8", "gb2312", $file);
$reader = new Xlsx();
$reader->setLoadSheetsOnly($sheetname);
$spreadsheet = $reader->load($file);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
// 获取单元行列信息 例如第一行 A列 数据
return $sheetData;
}
/**
* @param $video_id string 视频id
* @param $redis array redis 配置信息
* @return array
*/
public function getShareData($video_id, $redis)
{
$redis->hgetall('empno_key__video_' . $video_id . '__share_code_list');
//获取非白名单进入工号生成的分享码 ['工号'=>'分享码','工号'=>'分享码']
$job_numbers_key_share_code_list = $redis->hgetall('empno_key__video_' . $video_id . '__share_code_list');
if (!empty($job_numbers_key_share_code_list)) {
//非白名单进入的游客['分享码'=>'次数','分享码'=>'次数']
$share_count_list = $redis->hgetall('share_key__video_' . $video_id . '__count_list');
return array($job_numbers_key_share_code_list, $share_count_list);
} else {
//获取白名单进 ['工号'=>'分享码','工号'=>'分享码']
$job_numbers_key_share_code_list = $redis->hgetall('empno_key__video_' . $video_id . '__white_list');
//白名单进入的游客['分享码'=>'次数','分享码'=>'次数']
$share_count_list = $redis->hgetall('share_key__video_' . $video_id . '__white_list');
return array($job_numbers_key_share_code_list, $share_count_list);
}
}
/**
* 获取在线时长信息
* @param $video_id string 视频id
* @param $redis
* @return mixed
*/
public function getOnlineTime($video_id, $redis)
{
$redis->hgetall('empno_key__video_' . $video_id . '__share_code_list');
//业务员获取观看时长
$online_time_data = $redis->hgetall("empno_key__video_" . $video_id . "__online_duration_count");
return $online_time_data;
}
/**
* @param $redis_config array redis配置信息
* @param $video_id string 视频id
* @return array
*/
private function getSingleRedisServiceData($redis_config, $video_id)
{
$redis = new \Redis();
$redis->connect($redis_config["ip"], $redis_config["port"]);
$redis->auth($redis_config["password"]);
//获取在线观看时长数据 工号=>分钟
$online_time_data = $this->getOnlineTime($video_id, $redis);
//获取分享数据
list($job_numbers_key_share_code_list, $share_count_list) = $this->getShareData($video_id, $redis);
$redis->close();
return array($online_time_data, $job_numbers_key_share_code_list, $share_count_list);
}
public function index()
{
$video_id = 36;
//根据工号 video_id 获取工号 公司信息
$users_info = Db::table("snake_record")->distinct(true)->field("empno, company")->where("room_id", $video_id)->where("empno", "<>", "unknow")->select();
$users_info = array_combine(array_column($users_info, "empno"), $users_info);
$redis_configs = array(
array("ip" => "127.0.1", "port" => 6379, "password" => "123456"),
);
$district = array("合计", "安徽", "北京", "江苏", "常州", "无锡", "苏州", "福建", "厦门", "甘肃", "广东", "深圳", "广西", "贵州", "海南", "河北", "黑龙江", "吉林", "江西", "辽宁", "大连", "内蒙古", "宁夏", "山东", "青岛", "青海", "山西", "陕西", "上海", "上海自贸区", "四川", "天津", "新疆", "豫北", "豫东", "豫南", "豫西", "浙江", "宁波", "重庆", "湖北", "湖南", "云南");
$province_area = array("江苏" => array("常州", "无锡", "苏州"), "福建" => array("厦门"), "广东" => array("深圳"), "辽宁" => array("大连"), "山东" => array("青岛"), "上海" => array("上海自贸区"), "浙江" => array("宁波"), "豫北" => array("豫东", "豫南", "豫西"));
$cities = array("常州", "无锡", "苏州", "厦门", "深圳", "大连", "青岛", "上海自贸区", "宁波", "豫北", "豫东", "豫南", "豫西");
//获取空白的地区数据,以及地区需要合并单元格的起始位置列表
list($district_data, $merage_position) = $this->getEmptyDistrictData($district, $province_area);
//填充的统计数据
$district_data = $this->setTotalData($district_data, $district);
$users_data = array();
//遍历redis服务器,统计观看时长,分享数据
foreach ($redis_configs as $redis_config) {
list($online_time_data, $job_numbers_key_share_code_list, $share_count_list) = $this->getSingleRedisServiceData($redis_config, $video_id);
统计各地区业务员观看人数,总有效分享次数,同时统计业务员观看及分享数据
list($users_data, $district_data) = $this->handleData($users_info, $job_numbers_key_share_code_list, $share_count_list, $online_time_data, $users_data, $district_data, $district);
}
//计算各地区游客观看人数
$district_data = $this->ComputeTheVisitorsCount($district_data, $province_area, $cities);
//创建Excel表格
$this->createExcel($district_data, $this->arraySort(array_values($users_data), "company"), $merage_position);
return json_encode(array_slice($users_data, 0, 12));
}
/**
* @param $district_data array 分公司统计信息
* @param $district array 分公司信息
* @return mixed
*/
private function setTotalData($district_data, $district)
{
//读取统计数据
$shangzhibo_statistical_data = $this->readExcel($this->shangzhibo_Excel);
//计算各地中总观看人数
$total = 0;
foreach ($shangzhibo_statistical_data as $key => $value) {
if (in_array($value["A"], $district)) {
$num = (int)$value["B"];
$district_data[$value["A"]]["viewers_total_count"] = $num;
$total += $num;
} elseif ($value["A"] == "河南") {
$num = (int)$value["B"];
$district_data["豫北"]["viewers_total_count"] = $num;
$total += $num;
}
}
$district_data["合计"]["viewers_total_count"] = $total;
return $district_data;
}
/**
* @param $district array 分公司列表
* @param $province_area array 包含有地市级分公司的省份
* @return array
*/
private function getEmptyDistrictData($district, $province_area)
{
//地区空白统计数据
$district_data = array();
//需要合并的单元格起始行号
$merage_position = array();
//组装地区数据
foreach ($district as $key => $value) {
$district_data[$value] = array("company" => $value, "staff_viewers_count" => 0, "visitors_count" => 0, "viewers_total_count" => 0, "share_count" => 0);
if (in_array($value, array_keys($province_area))) {
array_push($merage_position, array("start" => $key + 2, "end" => count($province_area[$value]) + $key + 2));
}
}
return [$district_data, $merage_position];
}
/**
* @param $users_info array 用户数据信息
* @param $job_numbers_key_share_code_list array 工号与分享码对应列表
* @param $share_count_list array 工号与分享次数对应列表
* @param $online_time_data array 工号与在线时长对应列表
* @param $users_data array 统计的职员信息
* @param $district_data array 统计的地区信息
* @param $district array 分公司列表
* @return array array 地市级分公司列表
*/
private function handleData($users_info, $job_numbers_key_share_code_list, $share_count_list, $online_time_data, $users_data, $district_data, $district)
{
//涉及到分享数据的员工工号列表
$job_numbers_in_share = array_keys($job_numbers_key_share_code_list);
$job_numbers_online = array_unique(array_keys($online_time_data));
//统计各地区业务员观看人数,总有效分享次数,同时统计业务员观看及分享数据
foreach ($users_info as $user) {
$company = $user["company"];
$job_number = $user["empno"];
if (array_key_exists($job_number, $job_numbers_key_share_code_list)) {
//分享数据有该工号
if (in_array($job_number, $job_numbers_in_share)) {
$share_code = $job_numbers_key_share_code_list[$job_number];
$share_count = (int)$share_count_list[$share_code] - 1;
} else {
$share_count = 0;
}
if (in_array($job_number, $job_numbers_online)) {
$online_time = (int)$online_time_data[$job_number];
if (!in_array($job_number, array_keys($users_data))) {
$users_data[$job_number] = array("job_number" => $job_number, "company" => $company, "online_time" => $online_time, "share_count" => $share_count);
} else {
$users_data[$job_number]["online_time"] += $online_time;
}
}
if (in_array($company, $district)) {
$district_data[$company]["staff_viewers_count"] = $district_data[$company]["staff_viewers_count"] + 1;
$district_data[$company]["share_count"] = $district_data[$company]["share_count"] + $share_count;
$district_data["合计"]["staff_viewers_count"] = $district_data["合计"]["staff_viewers_count"] + 1;
$district_data["合计"]["share_count"] = $district_data["合计"]["share_count"] + $share_count;
}
}
}
return [$users_data, $district_data];
}
/**
* 计算各分公司游客观看人数
* @param $district_data array 分公司统计信息
* @param $province_area array 包含有地市级分公司的省份
* @param $cities array 地市级分公司
* @return mixed array 分公司统计信息
*/
private function ComputeTheVisitorsCount($district_data, $province_area, $cities)
{
//计算各地区游客观看人数
foreach ($district_data as $key => &$value) {
if (in_array($key, array_keys($province_area))) {
$staff_viewers_count = $value["staff_viewers_count"];
foreach ($province_area[$key] as $city) {
$staff_viewers_count += $district_data[$city]["staff_viewers_count"];
}
$value["visitors_count"] = $value["viewers_total_count"] - $staff_viewers_count;
} elseif (in_array($key, $cities)) {
$value["visitors_count"] = 0;
} else {
$value["visitors_count"] = $value["viewers_total_count"] - $value["staff_viewers_count"];
}
}
return $district_data;
}
/**
* 二维数组根据某个字段排序
* @param array $array 要排序的数组
* @param string $keys 要排序的键字段
* @param string $sort 排序类型 SORT_ASC SORT_ASC
* @return array 排序后的数组
*/
public function arraySort($array, $keys, $sort = SORT_ASC)
{
$keysValue = [];
foreach ($array as $k => $v) {
$keysValue[$k] = $v[$keys];
}
array_multisort($keysValue, $sort, $array);
return $array;
}
/**
* 数字转大写字母
* @param $index int 数字
* @param int $start
* @return string 字母
*/
private function numberToLetter($index, $start = 65)
{
$str = '';
if (floor($index / 26) > 0) {
$str .= $this->numberToLetter(floor($index / 26) - 1);
}
return $str . chr($index % 26 + $start);
}
/**
* @param $spreadSheet
* @param $worksheet
* @param $header array 头部列表
* @param $data array 统计数据
* @param array $merge_positons 合并单元格起始列
*/
private function writeAndSetSheet($spreadSheet, $worksheet, $header, $data, $merge_positons = array())
{
//循环填充第一行数据
foreach ($header as $key => $value) {
$spreadSheet->getActiveSheet()->getColumnDimension($this->numberToLetter($key))->setWidth(30);
$worksheet->setCellValueByColumnAndRow($key + 1, 1, $value);
}
//填充统计数据
$row = 2;
foreach ($data as $key => $value) {
$worksheet->setCellValueByColumnAndRow(1, $row, $key);
$column = 1;
foreach ($value as $item) {
$worksheet->setCellValueByColumnAndRow($column, $row, $item);
$column += 1;
}
$row += 1;
}
//设置第一行样式
$styleHead = [
'font' => [
'bold' => true,
"name" => "宋体",
"size" => 12
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
"vertical" => Alignment::VERTICAL_CENTER
],
];
//设置数据样式
$stylebody = [
'font' => [
'bold' => false,
"name" => "宋体",
"size" => 12
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
"vertical" => Alignment::VERTICAL_CENTER
],
];
if (!empty($merge_positons)) {
foreach ($merge_positons as $merge_positon) {
$worksheet->mergeCells('C' . $merge_positon["start"] . ':C' . $merge_positon["end"]);
$worksheet->mergeCells('D' . $merge_positon["start"] . ':D' . $merge_positon["end"]);
}
}
$column_len = count($header);
$row_len = (count($data) + 1);
$end_alpha = $this->numberToLetter($column_len);
$worksheet->getStyle('A1:' . $end_alpha . '1')->applyFromArray($styleHead);
$worksheet->getStyle('A2:' . $end_alpha . $row_len)->applyFromArray($stylebody);
}
/**
* @param $district_data array 分公司统计数据
* @param $users_data 职员统计数据
* @param $merge_positon array 合并单元格起始列
*/
private function createExcel($district_data, $users_data, $merge_positon)
{
$spreadSheet = new Spreadsheet();
//写入分公司统计数据
$spreadSheet->setActiveSheetIndex(0);
$worksheet = $spreadSheet->getActiveSheet();
$worksheet->setTitle("地区观看人数统计");
$header = ["分总司", "观看人数(工号)", "观看人数(游客)", "总人数", "总有效分享次数"];
$this->writeAndSetSheet($spreadSheet, $worksheet, $header, $district_data, $merge_positon);
//写入职员统计数据
$spreadSheet->createSheet();
$spreadSheet->setActiveSheetIndex(1);
$worksheet = $spreadSheet->getActiveSheet();
$worksheet->setTitle("业务员观看及分享统计");
$header2 = ["工号", "公司名称", "观看时长/分钟", "有效分享次数"];
$this->writeAndSetSheet($spreadSheet, $worksheet, $header2, $users_data);
//保存文件
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadSheet);
$writer->save($this->statistics_excel_save_path."/".date('YmdHis', time())."直播统计信息".".xlsx");
}
}