本文主要是介绍PHP中使用PhpSpreadsheet 读取mysql ,生成excel下载，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
安装phpoffice/phpspreadsheet
composer require phpoffice/phpspreadsheetgithub地址: 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');}
示例二
<?phpnamespace 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");}}
这篇关于PHP中使用PhpSpreadsheet 读取mysql ,生成excel下载的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
