liche/common/libraries/Excel.php

<?php
/**
 * Created by PhpStorm.
 * User: linfan
 * Date: 2018-11-28
 * Time: 16:49
 */

defined('BASEPATH') OR exit('No direct script access allowed');

require_once COMMPATH.'third_party/PHPExcel.php';

class Excel extends  PHPExcel {

    /*
     * 导出Excel
     * @param array $data 数据数组
     * @param array $indexs 设置要导出的字段 array('key1' => '中文名称1', 'key2' => '中文名称2', ...)
     * @param string $filename 导出文件名称
     */
    public function out($data, $indexs = array(), $filename = '')
    {
        if($data)
        {
            $excel = new PHPExcel();
            $sheet = $excel->getActiveSheet();
            $cells = array('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');
            $startRow = 1;

            $indexs_key = $indexs ? array_keys($indexs) : array_keys($data);
            $indexs_name = $indexs ? array_values($indexs) : array_keys($data);

            foreach ($data as $row)
            {
                foreach ($indexs_key as $key => $value)
                {
                    $startRow == 1 ? $sheet->setCellValue($cells[$key].$startRow, $indexs_name[$key]) : $sheet->setCellValue($cells[$key].$startRow, $row[$value]);
                }

                $startRow++;
            }

            !$filename && $filename = date('YmdHis');

            header("Pragma: public");
            header("Expires: 0");
            header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
            header("Content-Type:application/force-download");
            header("Content-Type:application/vnd.ms-execl");
            header("Content-Type:application/octet-stream");
            header("Content-Type:application/download");;
            header('Content-Disposition:attachment;filename='. $filename .'.xlsx');
            header("Content-Transfer-Encoding:binary");

            $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
            $writer->save('php://output');
        }
    }

    /**
     * cvs格式导出
     * @param $data
     * @param array $indexs
     * @param string $filename
     * @return bool
     */
    public function out_csv($data, $indexs = array(), $filename = ''){
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename='. $filename .'.csv');
        header("Content-Transfer-Encoding:binary");


        $fp = fopen('php://output','a');

        $indexs_key = $indexs ? array_keys($indexs) : array_keys($data);

        //每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
        $limit = 100000;
        $num = 0;
        foreach($data as $k => $v){
            $num++;
            //刷新一下输出buffer，防止由于数据过多造成问题
            if ($limit == $num) {
                ob_flush();
                flush();
                $num = 0;
            }

            $row = array();
            foreach($indexs_key as $col){
                if(in_array($col, array('sid', 'refund_no')) && is_numeric($v[$col])){
                    $row[] = iconv('utf-8', 'GBK//IGNORE', "=\"{$v[$col]}\"");
                } else {
                    $row[] = iconv('utf-8', 'GBK//IGNORE', $v[$col]);
                }
            }
            //将数据通过fputcsv写到文件句柄
            fputcsv($fp, $row);
        }

        return fclose($fp);
    }

    /**
     * @param $data "数据数组"
     * @param $indexs "数据字段对应的列"
     * @param $file "文件路径"
     * @param $mode "a追加"
     * @return bool
     */
    public function save($data, $indexs, $file, $mode = ''){
        $dir = substr($file, 0, strrpos($file, '/'));
        if (!file_exists($dir) && !mkdir($dir, 0777, true)) {//创建文件夹失败
            return false;
        }
        if('a' == $mode && file_exists($file)){
            $excel = PHPExcel_IOFactory::load($file);
            $sheet = $excel->getActiveSheet();
            $startRow = $sheet->getHighestRow()+1;
        } else {
            $excel = new PHPExcel();
            $sheet = $excel->getActiveSheet();
            $startRow = 1;
        }
        $cells = array('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');

        foreach ($data as $row)
        {
            foreach ($indexs as $key => $value)
            {
                $sheet->setCellValue($cells[$key].$startRow, $row[$value]);
            }

            $startRow++;
        }

        $writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $writer->save($file);
        return true;
    }

    /**
     * 使用csv方式保存
     * @param $data
     * @param $indexs
     * @param $file
     * @param string $mode
     * @return bool
     */
    function csv($data, $indexs, $file, $mode = 'a'){
        $dir = substr($file, 0, strrpos($file, '/'));
        if (!file_exists($dir) && !mkdir($dir, 0777, true)) {//创建文件夹失败
            return false;
        }

        $fp = fopen($file, $mode);

        //每隔$limit行，刷新一下输出buffer，不要太大，也不要太小
        $limit = 100000;
        $num = 0;
        foreach($data as $k => $v){
            $num++;
            //刷新一下输出buffer，防止由于数据过多造成问题
            if ($limit == $num) {
                ob_flush();
                flush();
                $num = 0;
            }

            $row = array();
            foreach($indexs as $col){
                if(in_array($col, array('sid', 'refund_no')) && is_numeric($v[$col])){
                    $row[] = iconv('utf-8', 'GBK//IGNORE', "=\"{$v[$col]}\"");
                } else {
                    $row[] = iconv('utf-8', 'GBK//IGNORE', $v[$col]);
                }
            }
            //将数据通过fputcsv写到文件句柄
            fputcsv($fp, $row);
        }

        return fclose($fp);
    }
}