EXCEL分页导出压缩包

EXCEL分页导出压缩包

   //导出商品档案
   public function goods()
   {
        $store_id = $this->store_id;
        if (IS_POST) {
            $page = I('post.page') ? I('post.page') : 1;
            $limit = 100;
            $directory_name = I('post.directory_name');
            $param = array(
                'store_id' => $store_id,
                'showpurch_price' => session('global_store_jurisdiction')['showpurch_price'],//进价查看权限
                'identity' => I('session.identity'),//寄售类型
                'page' => $page,//页
                'limit' => $limit,//每页数量
                'fields' => array(
                    'id',
                    'goods_class_id',
                    'goods_name',
                    'goods_number',
                    'goods_code',
                    'purchase_price',
                    'retail_price',
                    'online_price',
                    'store_mem_status',
                    'store_mem_price',
                    'plus_mem_status',
                    'plus_price',
                    'online_state',
                    'shop_mem_status',
                    'shop_mem_price',
                    'vip_online_state',
                    'vip_retail_state',
                    'integral_mem_status',
                    'integral',
                    'guide_royalty_state',
                    'guide_royalty_custom_state',
                    'guide_royalty_price',
                    'guide_royalty_online_price',
                    'goods_num',
                    'more_num',
                    'less_num',
                    'sell_num',
                    'fictitious_num',
                    'company',
                    'specifications',
                    'pricing_method',
                    'production_date',
                    'shelf_life',
                    'status',
                ),
            );
            $SkuGoodsViewModel = new SkuGoodsViewModel();
            $ret = $SkuGoodsViewModel->getGoodsList($param);
            if (empty($ret['data'])) {
                $this->ajaxReturn(array(
                    'status' => 0,
                    'msg' => '暂无商品可导出'
                ));
            }
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            import("Vendor.PHPExcel.PHPExcel");
            import("Vendor.PHPExcel.Writer.Excel5");
            import("Vendor.PHPExcel.IOFactory.php");
            $filename = "storeGoods_NO.{$page}";
            $headArr = array(
                array('id', 'ID'),
                array('class_name', '分类'),
                array('goods_name', '商品名称'),
                array('goods_number', '货号'),
                array('goods_code', '条码'),
                array('purchase_price', '成本价'),
                array('retail_price', '零售价'),
                array('online_price', '线上价'),
                array('store_mem_name', '是否开启店内会员价'),
                array('store_mem_price', '店内会员价格'),
                array('plus_mem_name', ' plus会员价开关'),
                array('plus_price', 'Plus员价'),
                array('online_state_name', '是否在商城出售'),
                array('shop_mem_name', '是否开启商城会员价'),
                array('shop_mem_price', '线上会员价'),
                array('vip_online_name', '线上折扣'),
                array('vip_retail_name', '线下折扣'),
                array('integral_mem_name', '会员购买获得积分'),
                array('integral', '会员消费送积分'),
                array('guide_royalty_name', ' 购买该商品获得提成的状态'),
                array('guide_royalty_custom_name', ' 导购员获得提成自定义状态'),
                array('guide_royalty_price', ' 导购员线下固定金提成'),
                array('guide_royalty_online_price', ' 导购员线上固定金提成'),
                array('goods_num', '商品系统库存'),
                array('more_num', '库存上限(超量值)'),
                array('less_num', '库存下限(预警值)'),
                array('sell_num', '已售数量'),
                array('fictitious_num', '虚拟销量'),
                array('company', '单位'),
                array('specifications', '规格'),
                array('pricing_method_name', '称重'),
                array('production_date', '生产日期'),
                array('shelf_life', '到期时间'),
                array('status_name', '状态'),
            );
            $str = $filename . ' 表格生成时间:' . date('Y-m-d H:i:s');
            if (!$directory_name) {
                $num = createKey();   //随机字符串
                $directory_name = "./Uploads/storeGoodsExcel/{$store_id}/{$num}";
            }
            $this->setExcel($filename, $headArr, $ret['data'], $str, $directory_name);
            $res = array(
                'status' => 1,
                'type' => 1,
                'directory_name' => $directory_name,
                'msg' => '请继续生成文件'
            );
            if (count($ret['data']) < $limit) {
                $res['type'] = 2;
                $res['msg'] = '数据生成完毕';
            }
            $this->ajaxReturn($res);
        } else if (IS_GET) {
            $directory_name = I('get.directory_name');
            if (is_dir($directory_name)) {
                $zip = new \ZipArchive();
                $dir = ltrim($directory_name, './');
                $arr = explode('/', $dir);
                unset($arr[count($arr) - 1]);
                $zipName = implode('/', $arr);
                $zip_url = './' . $zipName . '/商品档案导出' . date('Y-m-d_H:i:s') . '.zip';

                if ($zip->open($zip_url, \ZipArchive::CREATE) === TRUE) {
                    var_dump(123);
                    $this->addFileToZip($dir, $zip, '商品档案导出'); //调用方法，对要打包的根目录进行操作，并将ZipArchive的对象传递给方法
                    $zip->close(); //关闭处理的zip文件

                    if (!file_exists($zip_url)) {  //判断文件是否存在
                        echo "文件不存在";
                        exit();
                    }
                    $file = fopen($zip_url, "r");
                    Header("Content-type:application/octet-stream");
                    header("Accept-Ranges: bytes");
                    header("Accept-Length: " . filesize($zip_url));
                    header("Content-Disposition: attachment; filename=商品档案导出" . date('Y-m-d_H:i:s') . ".zip");
                    echo fread($file, filesize($zip_url));
                    fclose($file);

                    $this->delDirFile($directory_name);//删除文件夹下的所有文件
                    rmdir($directory_name);//删除当前文件夹
                    unlink($zip_url);//删除压缩包
                }
            } else {
                echo '文件不存在';
            }
        }
    }
        /**
     * 添加Excel表格到压缩包
     * @param $path
     * @param $zip
     * @param string $name
     */
    private function addFileToZip($path, $zip, $name = '商品档案导出')
    {
        $handler = opendir($path); //打开当前文件夹由$path指定。
        while (($filename = readdir($handler)) !== false) {
            if ($filename != "." && $filename != "..") {//文件夹文件名字为'.'和‘..'，不要对他们进行操作
                if (is_dir($path . "/" . $filename)) {// 如果读取的某个对象是文件夹，则递归
                    $this->addFileToZip($path . "/" . $filename, $zip);
                } else { //将文件加入zip对象
                    $name = $name . "-第" . explode('.', $filename)[1] . "页.xls";
                    $zip->addFile($path . "/" . $filename, $name);
                }
            }
        }
        @closedir($path);
    }
   /**
     * 写入表格 把表格保存在服务器
     * @param $expTitle
     * @param $expCellName
     * @param $expTableData
     * @param string $common
     * @param string $directory_name
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    private function setExcel($expTitle, $expCellName, $expTableData, $common = "", $directory_name = "./Public")
    {
        $fileName = $expTitle;//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        vendor("PHPExcel.PHPExcel");
        ob_clean();
        $objPHPExcel = new \PHPExcel();
        $cellName = 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', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');

        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $common);
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
        }

        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), ' ' . $expTableData[$i][$expCellName[$j][0]]);

            }
        }
        foreach ($cellName as $value) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($value)->setWidth(20);

        }
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $this->createDir($directory_name);//生成文件目录
        $path = "{$directory_name}/{$fileName}.xls";
        $objWriter->save($path);
    }
      /**
     * 生成文件目录
     * @param $directory_name 目录地址
     */
    private function createDir($directory_name)
    {
        if (!is_dir($directory_name)) {
            $temp = ltrim($directory_name, './');
            $arr = explode('/', $temp);
            $str = './';
            foreach ($arr as $key => $sv) {
                if (!is_dir("{$str}/{$sv}")) {
                    mkdir("{$str}/{$sv}", 0777);
                }
                $str = "{$str}/{$sv}";
            }
        }
    }
      /**
     * 删除目录下的所有文件
     * @param $dir  目录地址
     */
    private function delDirFile($dir)
    {
        $dh = opendir($dir);
        while ($file = readdir($dh)) {
            if ($file != "." && $file != "..") {
                $full_path = $dir . "/" . $file;
                if (!is_dir($full_path)) {
                    unlink($full_path);
                } else {
                    $this->delDirFile($full_path);
                }
            }
        }

        closedir($dh);
    }