YII2 프레임워크에서 Excel 테이블을 내보내는 방법에 대한 그래픽 코드 튜토리얼

최근에 PHP의 Yii 프레임워크를 공부하고 있는데 Excel 내보내기 문제가 발생했을 때 공부했는데 이 기사에서는 내보내기에 대한 관련 정보를 주로 소개했습니다. YII2 프레임워크의 엑셀 테이블에 대해 샘플 코드를 통해 자세히 소개하고 있으니, 필요하신 분들은 참고해보세요.

머리말

테이블 가져오기 및 내보내기는 일상적인 개발에서 자주 접하는 기능입니다. 최근 프로젝트에서 테이블 출력 기능을 구현한 적이 있는데, 이전에 TP를 사용할 때 해본 적이 있습니다. , 그래서 이번 기회에 좀 더 다양한 기능으로 정리해서 나중에 필요할 때 참고할 수 있도록, 혹은 필요로 하는 친구들을 위해 더 이상 말하지 않겠습니다. 자세한 소개를 살펴보겠습니다.

이 문서는 YII2 프레임워크를 기반으로 개발되었으므로 다른 프레임워크를 변경해야 할 수도 있습니다.

1. 일반 Excel 형식 테이블 출력

첫째, 가장 일반적인 내보내기 .xls 형식의 테이블입니다. 먼저 웹사이트에서 테이블의 표시 효과를 살펴보겠습니다

여기서 전체 테이블은 총 7개의 열로 구성되어 있는 것을 볼 수 있습니다. 코드 구현을 살펴보겠습니다.

1.controller file

//导出统计

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 file

 <?php
 namespace app\models;//model层的命名空间
 //注意要引用yii的arrayhelper
 use yii\helpers\ArrayHelper;
 use Yii;
 class Recharge extends \yii\db\ActiveRecord
 {
 //excel一次导出条数
 const EXCEL_SIZE = 10000;
 
 //统计导出
 public static function statistics($params){

 //导出时间条件
 if(empty($params[&#39;min&#39;])){
 $date_max = date("Y-m-d",strtotime("-1 day"));
 $date_min = date("Y-m-d",strtotime("-31 day"));
 }else{
 $date_min = $params[&#39;min&#39;];
 $date_max = $params[&#39;max&#39;];
 }
 $where = &#39;&#39;;
 $where .= &#39;(`issue_date` BETWEEN &#39;.&#39;\&#39;&#39;.$date_min.&#39;\&#39;&#39;.&#39; AND &#39;.&#39;\&#39;&#39;.$date_max.&#39;\&#39;)&#39;;

 //查找指定数据
 $sql = &#39;select
 article.company_id,
 article.cost,
 article.company_cost
 from article WHERE article.status=2 AND &#39;.$where;
 $article = Article::findBySql($sql)->asArray()->all();
 $article = ArrayHelper::index($article,null,&#39;company_id&#39;);
 $companys = [];

 foreach ($article as $key=>$v){
 if(empty($key)){
 continue;
 }else{
 $number = count($v);
 $company = Company::find()->where([&#39;id&#39;=>$key])->select(&#39;name&#39;)->one();
 $company_name = $company[&#39;name&#39;];
 $cost = 0;
 $company_cost = 0;
 foreach ($v as $n){
 $cost += $n[&#39;cost&#39;];
 $company_cost += $n[&#39;company_cost&#39;];
 }
 if($company_cost == 0){
 $company_cost =1;
 }

 //这里注意，数据的存储顺序要和输出的表格里的顺序一样
 $companys[] = [
 //公司名
 &#39;company_name&#39; => $company_name,

 //收入
 &#39;company_cost&#39; => $company_cost,

 //成本
 &#39;cost&#39; => $cost,

 //稿件数
 &#39;num&#39; => $number,

 //毛利
 &#39;gross_margin&#39; => $company_cost-$cost,

 //毛利率
 &#39;gross_profit_rate&#39; => round(($company_cost-$cost)/$company_cost*100,2).&#39;%&#39;,

 //ARPU值
 &#39;arpu&#39; => round($company_cost/$number,2),
 ];
 }
 }
 return $companys;
 }
}

최종 내보낸 효과(내보낸 후 셀 크기가 조정됨)는 기본적으로 동일하다고 볼 수 있습니다. 웹 페이지에 표시됩니다.

2. 빅데이터 테이블 내보내기

이때 사장님께서는 전체 데이터만 볼 수는 없고 세부 데이터도 함께 내보내는 것이 좋다고 하셨습니다. 이제 상사가 말했으니 그렇게 하세요. 나는 여전히 첫 번째 방법을 따랐지만 결과적으로 PHP가 충돌했다는 메시지가 나타났습니다. 다시 시도했을 때 작성된 바이트 수가 초과되었음을 발견했습니다. PHP 구성 파일 php.ini

memory_limit = 128M

을 열고 기본 메모리가 128M으로 지정되어 있음을 확인하면 충분합니다. 그래서 데이터베이스를 열어서 살펴보니, 아!

약 830,000개에 달하는 데이터를 쿼리하고 내보내면 문제가 발생합니다! 어떡하지 해서 구글링 해보니 빅데이터(20,000개 이상의 항목)를 내보내려면 .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초 내에 완료되도록 보장할 수 있습니다.

3. 셀 병합
사장님께서 잘 보신 것 같은데, 충전 통계도 내보내셨네요. 몇 분 안에 끝나나요? 어서 프로토타입 사진 보여주세요

윽, 다 말했으니 해보자. 하면서 이 내보내기가 주로 셀 병합 문제를 해결하기 위한 것임을 발견했습니다. 정보를 확인해보니 PHP 자체에서는 셀 병합이 불가능해서 phpexcel을 통해서 하려고 합니다

PHPExcel을 사용한다면 기본적인 동작은 이렇습니다(A1에서 E1으로 병합)

$objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
// 表格填充内容
$objPHPExcel->getActiveSheet()->setCellValue('A1','The quick brown fox.');

Result

또는 이렇게(A1을 E4로 병합)

$objPHPExcel->getActiveSheet()->mergeCells('A1:E4');
$objPHPExcel->getActiveSheet()->setCellValue('A1','The quick brown fox.');

결과

이것은 내 요구 사항을 충족하지 않습니다. 우선 하나씩 병합됩니다. 다음 유형의 충전량이 변경됩니다. 하드 코딩하여 매번 변경하는 것은 불가능합니다. 그래서 이 방법은 포기했습니다.

나중에 친구들의 도움으로 html을 이용해 엑셀 전송을 시도했습니다1. 메소드 파일 (매일 정기적으로 실행해야 하기 때문에 컨트롤러 레이어에는 기록되지 않습니다)

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 = &#39; created < \&#39; &#39;.$time.&#39;\&#39;&#39;;
 }else{
 $time = $params[&#39;min&#39;]+3600*24;
 $time_end = $params[&#39;max&#39;]+3600*24;
 $where = &#39; created <= \&#39; &#39;.$time_end.&#39;\&#39; AND created >= \&#39;&#39;.$time.&#39;\&#39; &#39;;
 }
 //充值类型列表
 $recharge_type = Recharge::find()->asArray()->all();
 if(empty($recharge_type)){
 $rechargelist[0]= &#39;&#39;;
 }else{
 $rechargelist = ArrayHelper::map($recharge_type,&#39;id&#39;,&#39;recharge_name&#39;);
 }
 $rechargelist1 = $rechargelist;
 $count = count($rechargelist1);
 //使用html语句生成显示的格式
 $excel_content = &#39;<meta http-equiv="content-type" content="application/ms-excel; charset=utf-8"/>&#39;;
 $excel_content .= &#39;<table border="1" style="font-size:14px;">&#39;;
 $excel_content .= &#39;<thead>
   <tr>
   <th rowspan="2">ID</th>
   <th rowspan="2">公司名称</th>
   <th colspan=&#39;.$count.&#39;>充值金额</th>
   <th rowspan="2">充值大小</th>
   <th rowspan="2">实际消费</th>
   <th rowspan="2">当前余额</th>
   </tr>
   <tr>
  &#39;;
 foreach ($rechargelist1 as $v => $t){
 $excel_content .= &#39;<th colspan="1">&#39;.$t.&#39;</th>&#39;;
 }
 $excel_content .= &#39;</tr>
  </thead>&#39;;
 //查找最新的固化数据
 $search = RechargeStatistics::find()->where($where)->asArray()->all();
 if(!empty($search)){
 foreach ($search as $key => $value){
 $search[$key][&#39;recharge&#39;] = unserialize($value[&#39;recharge&#39;]);
 }
 }
 //html语句填充数据
 if(empty($search)){
 }else{
 foreach ($search as $k) {
 $excel_content .= &#39;<td>&#39;.$k[&#39;company_id&#39;].&#39;</td>&#39;;
 $excel_content .= &#39;<td>&#39;.$k[&#39;company_name&#39;].&#39;</td>&#39;;
 foreach ($rechargelist1 as $v=>$t){
 $price = 0;
 foreach ($k[&#39;recharge&#39;] as $q=>$w){
  if($w[&#39;recharge_id&#39;] == $v){
  $price = $w[&#39;price&#39;];
  break;
  }
 }
 $excel_content .= &#39;<td>&#39;.$price.&#39;</td>&#39;;
 }
 $excel_content .= &#39;<td>&#39;.$k[&#39;total&#39;].&#39;</td>&#39;;
 $excel_content .= &#39;<td>&#39;.$k[&#39;consume&#39;].&#39;</td>&#39;;
 $excel_content .= &#39;<td>&#39;.($k[&#39;total&#39;]-$k[&#39;consume&#39;]).&#39;</td></tr>&#39;;
 }
 }
 $excel_content .= &#39;</table>&#39;;
 echo $excel_content;
 die;
}

2.结果

到这里基本就完成所有的任务了！

总结

위 내용은 YII2 프레임워크에서 Excel 테이블을 내보내는 방법에 대한 그래픽 코드 튜토리얼의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!