[PHP知识库]php 实现excel打包下载

场景说明：
举例说明：一个表单下面有多个提交的留言，导出这些留言，并且一个表单是一个excel

生成zip压缩包

$msgList是数据，这里我将要导出的列表、列标题、名称都放在了一个变量中传递过来了，具体的数据结构如下

$msgList = [
	[
		"formTitle" => "我将作为压缩包中excel的名字",
		"fields" => [
			"name" => "姓名",
			"phone" => "手机号"
		],
		"msgList" => [
			[
				"name" => "小明",
				"phone" => "18888888888",
			],
			[
				"name" => "小红",
				"phone" => "19999999999",
			]
		]
	]
]

上面的$msgList只是为了帮助理解下面方法的用法，结构不重要，重要的是实现的原理：

1. 先将每个表单的数据生成各自的excel，临时存放在某个文件夹里面，这个文件夹必须要有写入的权限。我这里调用了exportSingleExcel，这一步就像是常规生成excel，只是没有将其输出到浏览器。
2. 将刚才生成的excel放到压缩包中，即使用ZIPARCHIVE扩展，然后删除临时文件，最后将压缩包输出到浏览器下载

	/**
     * 导出压缩包
     * @param $msgList
     */
    public function getMsgZip($msgList)
    {
        $fileNameArr = [];
        foreach ($msgList as $one) {
            // 列标题
            $expCellName = $one["fields"];
            // 表单名称
            $formTitle = $one["formTitle"];
            // 生成excel
            $this->exportSingleExcel($formTitle . "--留言列表", $expCellName, $one["msgList"], $fileNameArr);
        }
        //进行多个文件压缩
        $zip = new ZipArchive();
        $zipName = "简历--" . date("YmdHis") . ".zip";
        $filename = "../runtime/" . $zipName;
//        $filename = iconv('utf-8','gbk//ignore',$filename);
        $zip->open($filename,  ZIPARCHIVE::CREATE);   //打开压缩包

        //向压缩包中添加文件
        foreach ($fileNameArr as $file) {
            $a = $zip->addFromString($file["title"],file_get_contents($file["file"])); //向压缩包中添加文件
        }
        $zip->close();  //关闭压缩包
        foreach ($fileNameArr as $file) {
            unlink($file["file"]); //删除csv临时文件
        }
        //输出压缩文件提供下载
        header("Cache-Control: max-age=0");
        header("Content-Description: File Transfer");
        header('Content-disposition: attachment; filename='.$zipName); // 文件名
        header("Content-Type: application/zip"); // zip格式的
        header("Content-Transfer-Encoding: binary"); //
        header('Content-Length: '.filesize($filename)); //
        ob_clean();
        flush();
        readfile($filename);//输出文件;
        unlink($filename); //删除压缩包临时文件
    }

压缩包中的单个excel

public function exportSingleExcel($expTitle, $expCellName, $expTableData, &$fileNameArr)
    {
        $expTitle = $expTitle . "-" . time();
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        // excel第一行：数据标题
        $titleColumnIndex = 1;
        foreach ($expCellName as $key => $value) {
            $worksheet->setCellValueByColumnAndRow($titleColumnIndex, 1, $value);
            $titleColumnIndex++;
        }
        foreach ($expTableData as $key => $value) {
            $j = $key + 2;
            $columnIndex = 1;
            foreach ($expCellName as $k => $v) {
                $columnIndexData = isset($value[$k]) ? $value[$k] : "";
                $worksheet->setCellValueByColumnAndRow($columnIndex, $j, $columnIndexData);
                $columnIndex++;
            }
        }
        $expName = "../runtime/" . $expTitle . '.xls';
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name=' . $expName);
        header("Content-Disposition:attachment;filename=$expName");//attachment新窗口打印inline本窗口打印
        $fileNameArr[] = [
            "title" => $expTitle . '.xls',
            "file" => $expName,
        ];
        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $objWriter->save($expName);
    }

扩展：常规excel下载

	/**
     * 导出 数组转换成excel内容
     * @param string $expTitle 文件名
     * @param array $expCellName 列名
     * @param array $expTableData 数据，二维数组，格式：[["name"=>"小明","age"=>"18"],["name"=>"小红","age"=>"17"]]
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function exportExcel($expTitle, $expCellName, $expTableData)
    {
        $expTitle = $expTitle . "-" . date("YmdHis");
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        // excel第一行：数据标题
        $titleColumnIndex = 1;
        foreach ($expCellName as $key => $value) {
            $worksheet->setCellValueByColumnAndRow($titleColumnIndex, 1, $value);
            $titleColumnIndex++;
        }
        // excel数据
        if (!empty($expTableData)) {
            foreach ($expTableData as $key => $value) {
                $j = $key + 2;
                $columnIndex = 1;
                foreach ($expCellName as $k => $v) {
                    $columnIndexData = isset($value[$k]) ? $value[$k] : "";
                    $worksheet->setCellValueByColumnAndRow($columnIndex, $j, $columnIndexData);
                    $columnIndex++;
                }
            }
        }
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $expTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$expTitle.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $objWriter->save('php://output');
//        exit;
    }