• 注册
  • thinkphp6 关注:2 内容:16

    ThinkPHP5.1扩展phpspreadsheet实现数据导出Excel

  • 查看作者
  • 打赏作者
  • 拉黑名单
    • thinkphp通过composer扩展phpspreadsheet,最终实现数据导出到excel.

      研究了一下phpspreadsheet官方例子来直接输出数据到浏览器实现下载。但在windows本地测试一直没有成功, 发现是由于$rewriter->save('php://output')对目录的写入权限问题,在linux服务器正常。最后更换为以下方式实现输出:

      以下是代码:环境 thinkphp 5.1.23 +Apache + PHP7.1

      use PhpOffice\PhpSpreadsheet\Spreadsheet;

      //从数据库查询需要的数据

      $data = model('admin/Loginlog')->where($where)->order('id','desc')->select();

      // Create new Spreadsheet object

      $spreadsheet = new Spreadsheet();

      // Add title

      $spreadsheet->setActiveSheetIndex(0)

          ->setCellValue('A1', 'ID')

          ->setCellValue('B1', '用户')

          ->setCellValue('C1', '详情')

          ->setCellValue('D1', '结果')

          ->setCellValue('E1', '时间')

          ->setCellValue('F1', 'IP');

      // Rename worksheet

      $spreadsheet->getActiveSheet()->setTitle('登陆日志');

      $i = 2;

      foreach ($data as $rs) {

          // Add data

          $spreadsheet->getActiveSheet()

              ->setCellValue('A'.$i, $rs['id'])

              ->setCellValue('B'.$i, $rs['username'])

              ->setCellValue('C'.$i, $rs['info'])

              ->setCellValue('D'.$i, $rs['status'] ? '成功' : '失败')

              ->setCellValue('E'.$i, date('Y-m-d H:i:s',$rs['logintime']))

              ->setCellValue('F'.$i, $rs['loginip']);

          $i++;

      }

      //Set width

      $spreadsheet->getActiveSheet()

          ->getColumnDimension('A')

          ->setWidth(15);

      $spreadsheet->getActiveSheet()

          ->getColumnDimension('B')

          ->setWidth(15);

      $spreadsheet->getActiveSheet()

          ->getColumnDimension('C')

          ->setWidth(60);

      $spreadsheet->getActiveSheet()

          ->getColumnDimension('D')

          ->setWidth(15);

      $spreadsheet->getActiveSheet()

          ->getColumnDimension('E')

          ->setWidth(20);

      $spreadsheet->getActiveSheet()

          ->getColumnDimension('F')

          ->setWidth(20);

      // Set alignment

      $spreadsheet->getActiveSheet()->getStyle('A1:F'.$i)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

      $spreadsheet->getActiveSheet()->getStyle('C2:C'.$i)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);

      // Set active sheet index to the first sheet, so Excel opens this as the first sheet

      $spreadsheet->setActiveSheetIndex(0);

      return exportExcel($spreadsheet, 'xls', '登陆日志');

      复制代码

      函数exportExcel写在公共函数common.php里了

      /**

      * 导出Excel

      * @param  object $spreadsheet  数据

      * @param  string $format       格式:excel2003 = xls, excel2007 = xlsx

      * @param  string $savename     保存的文件名

      * @return filedownload         浏览器下载

      */

      function exportExcel($spreadsheet, $format = 'xls', $savename = 'export') {

          if (!$spreadsheet) return false;

          if ($format == 'xls') {

              //输出Excel03版本

              header('Content-Type:application/vnd.ms-excel'); 

              $class = “\PhpOffice\PhpSpreadsheet\Writer\Xls”;       

          } elseif ($format == 'xlsx') {

              //输出07Excel版本

              header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

              $class = “\PhpOffice\PhpSpreadsheet\Writer\Xlsx”; 

          }

          //输出名称

          header('Content-Disposition: attachment;filename=”'.$savename.'.'.$format.'”');

          //禁止缓存

          header('Cache-Control: max-age=0');

          $writer = new $class($spreadsheet);

          $filePath = env('runtime_path').”temp/”.time().microtime(true).”.tmp”;

          $writer->save($filePath);

          readfile($filePath);

          unlink($filePath);

      }

      你需要登录,才能进行发帖操作
    • 单栏布局 帖子间隔 侧栏位置: