phpexcel导出数据库数据生成excel文件

由于项目的需要把数据库的数据导出生成excel文件，发了点时间学习里下PHPexcel导出excel文档

1.查询服务器数据库，数据库查询返回json格式数据。

2.json数据转换为数组，生成的数组写到excel里。

3.导出的excel保存在服务器端，前端网页通过链接的方式下载excel附件。

PHP文件 datagrid_getusertestdata_excel.php

<?php
  include_once('../Classes/PHPExcel.php');
  require_once '../Classes/PHPExcel/IOFactory.php';
  include '../Classes/PHPExcel/Writer/Excel5.php' ;
  include '../Classes/PHPExcel/Writer/Excel2007.php' ;

  $con=mysqli_connect("localhost","root","123456","optimaltangbao_database");

  $from_time = $_POST['from_time'];
  $end_time = $_POST['end_time'];
  $user_name = $_POST['user_name'];
  $table_name=$user_name."_user_test_record_table";

  $result = mysqli_query($con,"select * from $table_name");
  {
   while($row = mysqli_fetch_assoc($result))
   {
    $output[]=$row;
   }
   //print(json_encode($output));
  }

  $json=json_encode($output);

  $data=json_decode($json, true);

  $objPHPExcel = new PHPExcel();

  $name='Excelfile';

  /*以下是一些设置，什么作者标题啊之类的*/
  $objPHPExcel->getProperties()->setCreator("chenwuchang")
     ->setLastModifiedBy("chenwuchang")
     ->setTitle("数据EXCEL导出")
     ->setSubject("数据EXCEL导出")
     ->setDescription("备份数据")
     ->setKeywords("excel")
    ->setCategory("result file");
   /*以下就是对处理Excel里的数据，横着取数据，主要是这一步，其他基本都不要改*/
    // 表头
  $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
     $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

  $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
  $objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getFont()->setBold(true);
  //$objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
   // 合并
  $objPHPExcel->getActiveSheet()->mergeCells('A1:I1');


  $num=2;

     $objPHPExcel->setActiveSheetIndex(0)
      ->setCellValue('A1', "用户血糖测试记录")
     ->setCellValue('A'.$num, "记录ID")
     ->setCellValue('B'.$num, "用户名")
     ->setCellValue('C'.$num, "测试时间")
     ->setCellValue('D'.$num, "测试时间段")
     ->setCellValue('E'.$num, "测试值")
     ->setCellValue('F'.$num, "设备名称")
     ->setCellValue('G'.$num, "运动记录")
     ->setCellValue('H'.$num, "用药记录")
     ->setCellValue('I'.$num, "饮食记录");

  foreach($data as $k => $v){

    $num=$k+3;
    $objPHPExcel->setActiveSheetIndex(0)

    //Excel的第A列，uid是你查出数组的键值，下面以此类推
     ->setCellValue('A'.$num, $v['record_id'])
     ->setCellValue('B'.$num, $v['user_id'])
     ->setCellValue('C'.$num, $v['test_time'])
     ->setCellValue('D'.$num, $v['test_time_quantum'])
     ->setCellValue('E'.$num, $v['test_value'])
     ->setCellValue('F'.$num, $v['device_name'])
     ->setCellValue('G'.$num, $v['sport'])
     ->setCellValue('H'.$num, $v['drug'])
     ->setCellValue('I'.$num, $v['food']);
  }

  $con->close();

  $objPHPExcel->getActiveSheet()->setTitle('User');
  $objPHPExcel->setActiveSheetIndex(0);

  $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);

  header("Content-Type:application/force-download");

  header("Content-Type:application/octet-stream");

  header("Content-Type:application/download");

  header('Content-Type:application/vnd.ms-excel');
  header('Content-Disposition:attachment;filename="'.$table_name.'.xls"');
  header('Cache-Control:max-age=0');
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
  //$objWriter->save('php://output'); //浏览器输出
  $objWriter->save("$table_name.xls"); //保存服务器

  exit;

?>

前端网页调用

网络说不能使用ajax的方式下载excel文件，所有把导出的excel文件保存在服务器端，post请求成功服务器端生成excel文件，通过链接的方式下载excel文件。

下面是前端网页和生成的excel文档

phpexcel导出数据库数据生成excel文件

秒客网

phpexcel导出数据库数据生成excel文件

相关文章