excel

导出csv

  1. 代码参考
    <?php
    //CSV表格导出(同时去除不可见字符)
    $result = [
        ['姓名', '年龄', '性别'],
        ['张三', '22', '男'],
        ['李四', '20', '女']
    ];
    ob_implicit_flush(1);
    header("Content-Disposition: attachment;filename=表格导出.csv");
    foreach ($result as &$value) {
        foreach ($value as &$v) {
            $v = preg_replace('/\s/', '', $v);
            $v = str_replace('"', '""', $v);
            $v = '"' . $v . '"';
        }
        $value = implode(',', $value) . "\r\n";
        echo $value;
    }
    exit;
    

导出xls

  1. 中文参考

  2. 项目地址

  3. 安装

     composer require liuggio/excelbundle
    
  4. 如果是Symfony3.x,修改[项目路径]/app/AppKernel.php

     new Liuggio\ExcelBundle\LiuggioExcelBundle(),
    
  5. 代码参考

     use Symfony\Bundle\FrameworkBundle\Controller\Controller;
     use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
     use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
    
     class DefaultController extends Controller
     {
         /**
          * 生成excel
          * 如果是Symfony3.x,则定义路由和请求
          * @Route("/php_excel")
          * @Method({"GET"})
          */
         public function phpExcel()
         {
             //数据处理
             $filename = '文件名.xls';
             $withdrawal_data = [
                 [
                     'date' => '日期',
                     'total' => '数量',
                 ],
                 [
                     'date' => '2017-01-01',
                     'total' => 1234,
                 ],
                 [
                     'date' => '2017-01-02',
                     'total' => 5678,
                 ],
             ];
             $count = count($arr[0]);
             $excel_col = $this->getArrayColume($count);//该方法允许count最大为26*26
    
             //创建Excel writer
             $phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();
             $phpExcelObject->getProperties()
                 ->setCreator("创建者")
                 ->setLastModifiedBy("最后编辑")
                 ->setTitle("标题")
                 ->setSubject("主题")
                 ->setCategory("分类")
                 ->setKeywords("关键字")
                 ->setDescription("描述");
             foreach ($excel_col as $value){
                 $phpExcelObject->getActiveSheet()->getColumnDimension($value)->setWidth(20);
             }
             $row = 0;
             foreach ($withdrawal_data as $value) {
                 $col = 0;
                 $row++;
                 foreach ($value as $body) {
                     $phpExcelObject->setActiveSheetIndex(0)->setCellValue($excel_col[$col++] . $row, $body);
                 }
             }
             $phpExcelObject->getActiveSheet()->setTitle('标题');
             $phpExcelObject->setActiveSheetIndex(0);
             //"Excel5","Excel2007","Excel2003XML","OOCalc","SYLK","Gnumeric","HTML","CSV"
             $writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel5');
    
             //返回数据
             //如果是Symfony3.x,则按如下方式返回响应
             $response = $this->get('phpexcel')->createStreamedResponse($writer);
             $response->headers->set('Pragma', 'public');
             $response->headers->set('Content-Type', 'text/vnd.ms-excel; charset=utf-8');
             $response->headers->set("Content-Disposition", "attachment;filename={$filename}");
             return $response;
         }
     }
    
     /**
      * 根据列数量生成列标号数组
      */
     private function getArrayColume($count)
     {
         $count = abs((int)$count);
         $map = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
         if ($count >= 0 && $count <= 26) {
             $res = [];
             for ($i = 0; $i < $count; $i++) {
                 $res[] = $map[$i];
             }
             return $res;
         } elseif ($count > 26 && $count <= 256) {
             $res = $map;
             $l = floor($count / 26);
             $m = $count % 26;
             for ($i = 0; $i < $l; $i++) {
                 $n = ($i == ($l - 1)) ? $m : 26;
                 for ($j = 0; $j < $n; $j++) {
                     $res[] = $map[$i] . $map[$j];
                 }
             }
             return $res;
         } else {
             return [];
         }
     }
    
@耿志环 2012-∞ 冀ICP备17033181号, powered by Gitbook修订: 2019-07-29 11:43:55

results matching ""

    No results matching ""