phpexcel 实现将数据保存到excel 并导出

不念不忘少年蓝@ 2022-05-23 12:26 263阅读 0赞

**我的 :`$res`**

array (size=17)
      0 => 
        array (size=21)
          0 => string '平台编号' (length=12)
          1 => string '内部商户号' (length=15)
          2 => string '商户名称' (length=12)
          3 => string '终端号' (length=9)
          4 => string '终端序列号' (length=15)
          5 => string '商户订单号' (length=15)
          6 => string '民生支付订单号' (length=21)
          7 => string '通道支付订单号' (length=21)
          8 => string '参考号' (length=9)
          9 => string '交易类型' (length=12)
          10 => string '交易日期' (length=12)
          11 => string '交易时间' (length=12)
          12 => string '入账账户' (length=12)
          13 => string '交易银行信息' (length=18)
          14 => string '交易金额' (length=12)
          15 => string '交易银行信息' (length=18)
          16 => string '交易金额' (length=12)
          17 => string '应收手续费(元)' (length=20)
          18 => string '实扣手续费(元)' (length=20)
          19 => string '减免手续费(元)' (length=20)
          20 => string '支付状态(元)' (length=17)
      1 => 
        array (size=19)
          0 => string 'A00012017050000000545' (length=21)
          1 => string 'M29002017090000029160' (length=21)
          2 => string '�̻�HGUAT20170907�޸�' (length=21)
          3 => string 'AAAAAAAA' (length=8)
          4 => string 'K20170900091404' (length=15)
          5 => string '2018060510042' (length=13)
          6 => string '32500201806051020493666325000001' (length=32)
          7 => string '4200000114201806057492546228' (length=28)
          8 => string '325810528605' (length=12)
          9 => string '΢����ɨ' (length=8)
          10 => string '20180605' (length=8)
          11 => string '102014' (length=6)
          12 => string '6226223380006109' (length=16)
          13 => string 'cft' (length=3)
          14 => string '0.01' (length=4)
          15 => string '0.00' (length=4)
          16 => string '0.00' (length=4)
          17 => string '0.00' (length=4)
          18 => string '�ɹ�' (length=4)

public function getExcel($res){
    
        //引入类
        include 'shared/libraries/PHPExcel/PHPExcel.php';
        include 'shared/libraries/PHPExcel/PHPExcel/Writer/Excel5.php';
        $objPHPExcel = new PHPExcel();
    
        // 设置文件属性
    
        //标题
        $objPHPExcel->getProperties()->setTitle("交易明细下载".date('YmdHis'));
        //描述
        $objPHPExcel->getProperties()->setDescription("交易明细对账使用");
    
    
        //设置当前的sheet
        $objPHPExcel->setActiveSheetIndex(0);
    
        $resCount = count($res);
    
    
        //循环 一行一行的插入
        $i = 1;
        foreach($res as $value){
          $j = 'A';
          foreach($value as $value2){
            if ($i != 1) {
              $value2 = mb_convert_encoding($value2,"UTF-8","GB2312"); //因为标题的编码和数据的编码不一样,要进行转码
            }
            $objPHPExcel->getActiveSheet()->setCellValue($j.$i,$value2);
            $j++;
          }
          $i++;
    
        }
        ob_end_clean();/清除缓存防止乱码
        //Save Excel 5 file 保存 
        header('Content-Type: applicationnd.ms-excel');  
        header('Content-Disposition: attachment;filename=交易明细('.date('YmdHis').').xls');  
        header('Cache-Control: max-age=0');  
    
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
        $objWriter->save('php://output');  
    
        //$objWriter->save(iconv("utf-8","gb2312",$fielpath));
        echo '保存成功';die;  
    
      }