摘要：最近的项目做到关于表格输出的功能，之前用的时候也做过，趁着这次功能比较多样的机会整理一下本文是基于框架进行开发的，不同框架可能会需要更改一普通格式表格输出先是最普通的导出格式的表格。在做的时候我发现，这次的导出主要是要解决单元格合并的问题。
最近的项目做到关于表格输出的功能，之前用TP的时候也做过，趁着这次功能比较多样的机会整理一下
本文是基于YII2框架进行开发的，不同框架可能会需要更改
一.普通excel格式表格输出先是最普通的导出.xls格式的表格。首先先看一下表格在网站的显示效果
这里可以看到整个表格一共是7列。下面来看代码的实现。
1.controller文件
//导出统计
public function actionStatistics(){
//设置内存
ini_set("memory_limit", "2048M");
set_time_limit(0);
//获取用户ID
$id = Yii::$app->user->identity->getId();
//去用户表获取用户信息
$user = Employee::find()->where(["id"=>$id])->one();
//获取传过来的信息（时间，公司ID之类的，根据需要查询资料生成表格）
$params = Yii::$app->request->get();
$objectPHPExcel = new PHPExcel();
//设置表格头的输出
$objectPHPExcel->setActiveSheetIndex()->setCellValue("A1", "代理公司");
$objectPHPExcel->setActiveSheetIndex()->setCellValue("B1", "收入");
$objectPHPExcel->setActiveSheetIndex()->setCellValue("C1", "成本");
$objectPHPExcel->setActiveSheetIndex()->setCellValue("D1", "稿件数");
$objectPHPExcel->setActiveSheetIndex()->setCellValue("E1", "毛利（收入-成本）");
$objectPHPExcel->setActiveSheetIndex()->setCellValue("F1", "毛利率（毛利/收入）*100%");
$objectPHPExcel->setActiveSheetIndex()->setCellValue("G1", "ARPU值");
//跳转到recharge这个model文件的statistics方法去处理数据
$data = Recharge::statistics($params);
//指定开始输出数据的行数
$n = 2;
foreach ($data as $v){
$objectPHPExcel->getActiveSheet()->setCellValue("A".($n) ,$v["company_name"]);
$objectPHPExcel->getActiveSheet()->setCellValue("B".($n) ,$v["company_cost"]);
$objectPHPExcel->getActiveSheet()->setCellValue("C".($n) ,$v["cost"]);
$objectPHPExcel->getActiveSheet()->setCellValue("D".($n) ,$v["num"]);
$objectPHPExcel->getActiveSheet()->setCellValue("E".($n) ,$v["gross_margin"]);
$objectPHPExcel->getActiveSheet()->setCellValue("F".($n) ,$v["gross_profit_rate"]);
$objectPHPExcel->getActiveSheet()->setCellValue("G".($n) ,$v["arpu"]);
$n = $n +1;
}
ob_end_clean();
ob_start();
header("Content-Type : application/vnd.ms-excel");
//设置输出文件名及格式
header("Content-Disposition:attachment;filename="代理公司统计".date("YmdHis").".xls"");
//导出.xls格式的话使用Excel5,若是想导出.xlsx需要使用Excel2007
$objWriter= PHPExcel_IOFactory::createWriter($objectPHPExcel,"Excel5");
$objWriter->save("php://output");
ob_end_flush();
//清空数据缓存
unset($data);
}
2.model文件
asArray()->all();
$article = ArrayHelper::index($article,null,"company_id");
$companys = [];
foreach ($article as $key=>$v){
if(empty($key)){
continue;
}else{
$number = count($v);
$company = Company::find()->where(["id"=>$key])->select("name")->one();
$company_name = $company["name"];
$cost = 0;
$company_cost = 0;
foreach ($v as $n){
$cost += $n["cost"];
$company_cost += $n["company_cost"];
}
if($company_cost == 0){
$company_cost =1;
}
//这里注意，数据的存储顺序要和输出的表格里的顺序一样
$companys[] = [
//公司名
"company_name" => $company_name,
//收入
"company_cost" => $company_cost,
//成本
"cost" => $cost,
//稿件数
"num" => $number,
//毛利
"gross_margin" => $company_cost-$cost,
//毛利率
"gross_profit_rate" => round(($company_cost-$cost)/$company_cost*100,2)."%",
//ARPU值
"arpu" => round($company_cost/$number,2),
];
}
}
return $companys;
}
}
最终导出的效果（单元格大小导出后调整过）可以看到和网页显示的基本一样。
二.大数据表格导出这时老板说了，我们不能只看总和的数据，最好是把详细数据也给导出来。既然老板发话了，那就做吧。还是按照第一种的方法去做，结果提示我php崩溃了，再试一次发现提示写入字节超出。打开php的配置文件php.ini
memory_limit = 128M
发现默认内存已经给到128M，应该是足够的了。于是我打开数据库一看，嚯！
接近83万条的数据进行查询并导出，可不是会出问题嘛！怎么办呢，于是我Google了一下，发现对于大数据(2万条以上)的导出，最好是以.csv的形式。不说废话，直接上代码
1.controller文件
//导出清单
public function actionInventory(){
ini_set("memory_limit", "2048M");
set_time_limit(0);
$id = Yii::$app->user->identity->getId();
$user = Employee::find()->where(["id"=>$id])->one();
$params = Yii::$app->request->get();
//类似的，跳转到recharge这个model文件里的inventory方法去处理数据
$data = Recharge::inventory($params);
//设置导出的文件名
$fileName = iconv("utf-8", "gbk", "代理商统计清单".date("Y-m-d"));
//设置表头
$headlist = array("代理商","文章ID","文章标题","媒体","统计时间范围","状态","创建时间","审核时间","发稿时间","退稿时间","财务状态","成本","销售额","是否是预收款媒体类型","订单类别");
header("Content-Type: application/vnd.ms-excel");
//指明导出的格式
header("Content-Disposition: attachment;filename="".$fileName.".csv"");
header("Cache-Control: max-age=0");
//打开PHP文件句柄,php://output 表示直接输出到浏览器
$fp = fopen("php://output", "a");
//输出Excel列名信息
foreach ($headlist as $key => $value) {
//CSV的Excel支持GBK编码，一定要转换，否则乱码
$headlist[$key] = iconv("utf-8", "gbk", $value);
}
//将数据通过fputcsv写到文件句柄
fputcsv($fp, $headlist);
//每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
$limit = 100000;
//逐行取出数据，不浪费内存
foreach ($data as $k => $v) {
//刷新一下输出buffer，防止由于数据过多造成问题
if ($k % $limit == 0 && $k!=0) {
ob_flush();
flush();
}
$row = $data[$k];
foreach ($row as $key => $value) {
$row[$key] = iconv("utf-8", "gbk", $value);
}
fputcsv($fp, $row);
}
}
2.model文件（因为这部分我要处理的过多，所以只选择了部分代码），在查询数据那部分，因为要查的数据较多，所以可以结合我之前写的关于Mysql大数据查询处理的文章看一下
//清单导出
public static function inventory($params){
//统计时间范围
if(!empty($params["min"]) && !empty($params["max"])){
$ti = strtotime($params["max"])+3600*24;
$max = date("Y-m-d",$ti);
$time = $params["min"]."-".$params["max"];
$date_min = $params["min"];
$date_max = $max;
}else{
$date_max = date("Y-m-d");
$date_min = date("Y-m-d",strtotime("-31 day"));
$time = $date_min."-".$date_max;
}
//查询数据
if($params["state"] == 1){
$where = "";
$where .= " AND (`issue_date` BETWEEN ".""".$date_min."""." AND ".""".$date_max."")";
$map = "select
company.name,
article.id,
article.title,
media.media_name,
article.status,
article.created,
article.audit_at,
article.issue_date,
article.back_date,
article.finance_status,
article.cost,
article.company_cost,
media.is_advance
from article
LEFT JOIN custom_package ON custom_package.id = article.custom_package_id
LEFT JOIN `order` ON custom_package.order_id = `order`.`id`
LEFT JOIN company ON company.id = article.company_id
LEFT JOIN media ON media.id = article.media_id
where article.status=2 and `order`.package=0".$where;
//查找的第一部分数据，使用asArray方法可以使我们查找的结果直接形成数组的形式，没有其他多余的数据占空间（注意：我这里查找分三部分是因为我要查三种不同的数据）
$list1 = Article::findBySql($map)->asArray()->all();
$where2 = "";
$where2 .= " AND (`issue_date` BETWEEN ".""".$date_min."""." AND ".""".$date_max."")";
$where2 .= " AND (`back_date` > "".$date_max."")";
$map2 = "select
company.name,
article.id,
article.title,
media.media_name,
article.status,
article.created,
article.audit_at,
article.issue_date,
article.back_date,
article.finance_status,
article.cost,
article.company_cost,
media.is_advance
from article
LEFT JOIN custom_package ON custom_package.id = article.custom_package_id
LEFT JOIN `order` ON custom_package.order_id = `order`.`id`
LEFT JOIN company ON company.id = article.company_id
LEFT JOIN media ON media.id = article.media_id
where article.status=3 and `order`.package=0 ".$where2;
//查找的第二部分数据
$list2 = Article::findBySql($map2)->asArray()->all();
$where3 = "";
$where3 .= " AND (`issue_date` BETWEEN ".""".$date_min."""." AND ".""".$date_max."")";
$map3 = "select
company.name,
article.id,
article.title,
media.media_name,
article.status,
article.created,
article.audit_at,
article.issue_date,
article.back_date,
article.finance_status,
article.cost,
article.company_cost,
media.is_advance
from article
LEFT JOIN custom_package ON custom_package.id = article.custom_package_id
LEFT JOIN `order` ON custom_package.order_id = `order`.`id`
LEFT JOIN company ON company.id = article.company_id
LEFT JOIN media ON media.id = article.media_id
where article.status=5 ".$where3;
//查找的第三部分数据
$list3 = Article::findBySql($map3)->asArray()->all();
$list4 = ArrayHelper::merge($list1,$list2);
$list = ArrayHelper::merge($list4,$list3);
}
//把结果按照显示顺序存到返回的数组中
if(!empty($list)){
foreach ($list as $key => $value){
//代理公司
$inventory[$key]["company_name"] = $value["name"];
//文章ID
$inventory[$key]["id"] = $value["id"];
//文章标题
$inventory[$key]["title"] = $value["title"];
//媒体
$inventory[$key]["media"] = $value["media_name"];
//统计时间
$inventory[$key]["time"] = $time;
//状态
switch($value["status"]){
case 2:
$inventory[$key]["status"] = "已发布";
break;
case 3:
$inventory[$key]["status"] = "已退稿";
break;
case 5:
$inventory[$key]["status"] = "异常稿件";
break;
}
//创建时间
$inventory[$key]["created"] = $value["created"];
//审核时间
$inventory[$key]["audit"] = $value["audit_at"];
//发稿时间
$inventory[$key]["issue_date"] = $value["issue_date"];
//退稿时间
$inventory[$key]["back_date"] = $value["back_date"];
//财务状态
switch($value["finance_status"]){
case 0:
$inventory[$key]["finance_status"] = "未到结算期";
break;
case 1:
$inventory[$key]["finance_status"] = "可结算";
break;
case 2:
$inventory[$key]["finance_status"] = "资源审批中";
break;
case 3:
$inventory[$key]["finance_status"] = "财务审批中";
break;
case 4:
$inventory[$key]["finance_status"] = "已结款";
break;
case 5:
$inventory[$key]["finance_status"] = "未通过";
break;
case 6:
$inventory[$key]["finance_status"] = "财务已审批";
break;
}
//成本
$inventory[$key]["cost"] = $value["cost"];
//销售额
$inventory[$key]["company_cost"] = $value["company_cost"];
//是否是预售
switch($value["is_advance"]){
case 0:
$inventory[$key]["is_advance"] = "否";
break;
case 1:
$inventory[$key]["is_advance"] = "是";
break;
case 2:
$inventory[$key]["is_advance"] = "合同";
break;
}
//订单类别
switch($params["state"]){
case 1:
$inventory[$key]["order_type"] = "时间区间无退稿完成订单";
break;
case 2:
$inventory[$key]["order_type"] = "时间区间发布前退稿订单";
break;
case 3:
$inventory[$key]["order_type"] = "时间区间发布后时间区间退稿订单";
break;
case 4:
$inventory[$key]["order_type"] = "时间区间之前发布时间区间内退稿订单";
break;
case 5:
$inventory[$key]["order_type"] = "异常订单";
break;
}
}
}else{
$inventory[0]["company_name"] = "无数据导出";
}
return $inventory;
}
3.导出结果
导出数量
导出的文件
基本上可以保证整个过程在2~4秒内处理完成
三.合并单元格老板一看做的不错，说你顺便把充值统计的导出也做了把，想想我都是处理过这么多数据的人了，还不是分分钟搞定的事？来，上原型图
噗，一口老血，话都说了，搞吧。在做的时候我发现，这次的导出主要是要解决单元格合并的问题。经过查资料发现，PHP本身是实现不了单元格合并的，于是我打算通过phpexcel来实现
如果是使用PHPExcel的话，基本操作是这样的（合并A1到E1）
$objPHPExcel->getActiveSheet()->mergeCells("A1:E1");
// 表格填充内容
$objPHPExcel->getActiveSheet()->setCellValue("A1","The quick brown fox.");
结果
或者这样的（合并A1到E4）
$objPHPExcel->getActiveSheet()->mergeCells("A1:E4");
$objPHPExcel->getActiveSheet()->setCellValue("A1","The quick brown fox.");
结果
这样并不能满足我的要求，首先它是一个一个合并的，其次我要显示的充值金额下面的类型是会变化的，不可能固定写死，然后每次都更改。所以放弃了这种方法。
后来在小伙伴的帮助下尝试用html转存excel的方法
1.方法文件（因为我要每天定时执行，所以并没有写到controller层）
public function actionExcelRechargeStatistics(){
//先定义一个excel文件
$filename = date("【充值统计表】(".date("Y-m-d")."导出)").".xls";
header("Content-Type: application/vnd.ms-execl");
header("Content-Type: application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=$filename");
header("Pragma: no-cache");
header("Expires: 0");
//时间条件
if(empty($params["min"])){
$time = date("Y-m-d",strtotime("+1 day"));
$where = " created < " ".$time.""";
}else{
$time = $params["min"]+3600*24;
$time_end = $params["max"]+3600*24;
$where = " created <= " ".$time_end."" AND created >= "".$time."" ";
}
//充值类型列表
$recharge_type = Recharge::find()->asArray()->all();
if(empty($recharge_type)){
$rechargelist[0]= "";
}else{
$rechargelist = ArrayHelper::map($recharge_type,"id","recharge_name");
}
$rechargelist1 = $rechargelist;
$count = count($rechargelist1);
//使用html语句生成显示的格式
$excel_content = "";
$excel_content .= "| ID | 公司名称 | 充值金额 | 充值大小 | 实际消费 | 当前余额 |
|---|---|---|---|---|---|
| ".$t." | "; } $excel_content .= "".$k["company_id"]." | "; $excel_content .= "".$k["company_name"]." | "; foreach ($rechargelist1 as $v=>$t){ $price = 0; foreach ($k["recharge"] as $q=>$w){ if($w["recharge_id"] == $v){ $price = $w["price"]; break; } } $excel_content .= "".$price." | "; } $excel_content .= "".$k["total"]." | "; $excel_content .= "".$k["consume"]." | "; $excel_content .= "".($k["total"]-$k["consume"])." | "; } } $excel_content .= "
}
2.结果
到这里基本就完成所有的任务了！
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/23288.html
摘要：一普遍导出方法在或是系统中导出是常有的事，做过的此功能人都知道，其主要操作其实是循环数据列表，然后一格一格地添加数据到固定的单元格中。 一.普遍导出方法 在crm或是oa系统中导出excel是常有的事，做过的此功能人都知道，其主要操作其实是循环数据列表，然后一格一格地添加数据到固定的单元格中。只要做好了一次，其后只要复制相关代码修改修改，其他地方导出功能也就完成了。 但是这样会有两个问...
摘要：使用时，前端可以将后端返回的数据拼接成自己需要导出的格式，下载到电脑中，完全不依赖后端。 前言 github: https://github.com/stardew516... 以往做excel表格下载功能的时候，都是后端生成好表格后，存储在某个地方，然后给前端一个链接，前端使用a标签加download下载，或者使用node。其实纯前端也是可以做表格下载的，有一个很好用的javascr...
摘要：而作为一款深受用户喜爱的电子表格工具，借助其直观的界面出色的计算性能和图表工具，已经成为数据统计领域不可或缺的软件之一。使用实现的导入和导出通过纯，您完全可以实现导入和导出文件功能，并为最终用户提供与这些文件进行交互的界面。 JavaScript是一个涵盖多种框架、直译式、可以轻松自定义客户端的脚本语言，在 Web 应用程序中，更加易于编码和维护。而Excel 作为一款深受用户喜爱的电...
摘要：效果预览导出文件效果点击下载弹出框效果代码总览为公司业务代码，大多为从缓存或者数据库中获取导出数据，不影响导出功能。导出导出导出所有在线离线用户成功导出所有在线离线用户失败引用导出表格 需求 将每个xmpp机房的在线/离线用户信息导出到Excel表格中（定时任务+网页按钮），并在网页上提供下载按钮进行下载。 效果预览 showImg(https://segmentfault.com/i...
摘要：本文经授权转自社区说明是一款强大的文件处理扩展包能够快速完成文件的的导出解析等功能本项目由团队成员整理发布首发地为社区文章的项目截图运行代码请见请参照此文档运行文章概览安装基础用法更多功能接下来是详细解说安装使用安装该扩展包安装完成后， 本文经授权转自 PHPHub 社区 说明 maatwebsite/excel 是一款强大的 Excel 文件处理扩展包, 能够快速完成 Excel 文...
阅读 1522·2021-09-22 15:35
阅读 2003·2021-09-14 18:04
阅读 875·2019-08-30 15:55
阅读 2447·2019-08-30 15:53
阅读 2678·2019-08-30 12:45
阅读 1202·2019-08-29 17:01
阅读 2576·2019-08-29 15:30
阅读 3512·2019-08-29 15:09
