引入phpexcel包,建议使用composer导入。
$ composer require phpoffice/phpspreadsheet
以下为代码示例:
注意:生成的中文excel文件在代码读取时是否乱码:
<?php
namespace app\controller;
use app\BaseController;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Index extends BaseController
{
public function index()
{
//查询所有的省份数据
$list = Db::table('region')->where('level', 1)->select();
foreach ($list as $key => $val) {
//查询省份下的城市数据
$all_city = Db::table('region')->where('pid', $val["id"])->select();
$objPHPExcel = new Spreadsheet();//创建Excel文件对象
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'ID')
->setCellValue('B1', '城市名称')
->setCellValue('C1', '中文全拼');
//插入表格数据
$i = 2;
foreach ($all_city as $k => $vo) {
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A' . $i, $k+1)
->setCellValue('B' . $i, $vo['name'])
->setCellValue('C' . $i, $vo['pinyin']);
$i++;
}
$objPHPExcel->setActiveSheetIndex(0);
//保存Excel格式文件,保存路径为当前路径
$filename = $val["name"] . '.xlsx';
$fileNameArr[] = $filename;
$objWriter = new Xlsx($objPHPExcel);
$objWriter->save($filename);
}
//进行多个文件压缩
$zip = new \ZipArchive();
$filename = "各省城市数据.zip";
$zip->open($filename, \ZipArchive::CREATE | \ZipArchive::OVERWRITE);//打开压缩包
//向压缩包中添加文件
foreach ($fileNameArr as $file) {
$a = $zip->addFromString($file,file_get_contents($file));
}
$zip->close();//关闭压缩包
foreach ($fileNameArr as $file) {
unlink($file); //删除csv临时文件
}
//输出压缩文件提供下载
header("Cache-Control: max-age=0");
header("Content-Description: File Transfer");
header('Content-disposition: attachment; filename='.$filename); // 文件名
header("Content-Type: application/zip"); // zip格式的
header("Content-Transfer-Encoding: binary");
header('Content-Length: '.filesize($filename));
ob_clean();
flush();
readfile($filename);//输出文件;
unlink($filename); //删除压缩包临时文件
}
}
实测有效。
以下为导出的压缩包截图
|