首页 > 代码库 > PHP 从mysql库里取出大量数据写入excel压缩下载

PHP 从mysql库里取出大量数据写入excel压缩下载

$zip = new ZipArchive;
if ($zip->open($filePath . date(‘Ymd‘) . ‘.zip‘,ZipArchive::OVERWRITE) === TRUE) {
  $count = $this->mod->getCount($cond);
  $num = ceil($count/5000);    //取整5000 余数进1
  $z = 0;
  for ($j=0;$j<$num;$j++) {
  $limit = $j*5000; //每次查询5000条
    $query = array(
    ‘pri‘ => ‘id‘,
    ‘fields‘ => ‘*‘,
    ‘cond‘ => $cond,
    ‘order_by‘ => ‘id‘,
    ‘limit‘ => "$limit,5000",
    );
    $result = $this->mod->getData($query);
    if ($result) {
      $i = 2;
      $objPHPExcel = new PHPExcel();
      $objPHPExcel->setActiveSheetIndex(0);
      $objPHPExcel->getProperties()->setCreator("Meteoric_cry")
      ->setLastModifiedBy("Meteoric002")
      ->setTitle("Php_excel_demo1");
      $objPHPExcel->getActiveSheet()->setTitle("Simple{}");
      $objPHPExcel->getActiveSheet()->setCellValue("A1", ‘标题‘);
      $objPHPExcel->getActiveSheet()->setCellValue("B1", ‘链接‘);
      $objPHPExcel->getActiveSheet()->setCellValue("C1", ‘内容‘);
      foreach($result as $key=> $val) {
        $objPHPExcel->getActiveSheet()->setCellValue("A{$i}", $val[‘title‘]);
        $objPHPExcel->getActiveSheet()->setCellValue("B{$i}", $val[‘url‘]);
        $objPHPExcel->getActiveSheet()->setCellValue("E{$i}", $val[‘content‘]);
        $i++;
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
      }
      //写操作
      $fileName = $key.".xlsx"; //文件名称
      $objWriter->save( $fileName); //保存文件
      $file_path = $fileName;
      if(file_exists($file_path)) { //判断文件是否存在此路径下
        $zip->addFile($file_path);
      }
    }
  }
}
$zip->close(); //关闭
//远程下载压缩包
$path = $filePath . date(‘Ymd‘) . ‘.zip‘;
$file = fopen($path,"r"); // 打开文件
// 输入文件标签
Header("Content-type: application/octet-stream");
Header("Accept-Ranges: bytes");
Header("Accept-Length: ".filesize($path));
Header("Content-Disposition: attachment; filename=" .date(‘Ymd‘) . ".zip");
// 输出文件内容
echo fread($file,filesize($path));
fclose($file); //关闭文件
unlink($path); //下载完之后删除压缩包

PHP 从mysql库里取出大量数据写入excel压缩下载