场景说明:
举例说明:一个表单下面有多个提交的留言,导出这些留言,并且一个表单是一个excel
生成zip压缩包
$msgList是数据,这里我将要导出的列表、列标题、名称都放在了一个变量中传递过来了,具体的数据结构如下
$msgList = [
[
"formTitle" => "我将作为压缩包中excel的名字",
"fields" => [
"name" => "姓名",
"phone" => "手机号"
],
"msgList" => [
[
"name" => "小明",
"phone" => "18888888888",
],
[
"name" => "小红",
"phone" => "19999999999",
]
]
]
]
上面的$msgList只是为了帮助理解下面方法的用法,结构不重要,重要的是实现的原理:
- 先将每个表单的数据生成各自的excel,临时存放在某个文件夹里面,这个文件夹必须要有写入的权限。我这里调用了exportSingleExcel,这一步就像是常规生成excel,只是没有将其输出到浏览器。
- 将刚才生成的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;
}