PHP利用PHPExcel导出数据到Excel
PHP 利用PHPExcel到处数据到Excel；还有导出数据乱码的解决方案。
直接贴代码吧！
● PHP版本5.6.38
● mysql版本5.0
- //连接数据库
- $mysql_server_name = "*.*.*.*";
- $mysql_username="user";
- $mysql_password="dbpasswd";
- $mysql_database="test";
- $conn = mysqli_connect($mysql_server_name,$mysql_username,$mysql_password,$mysql_database);
- // 检测连接
- if ($conn->connect_error) {
- die("连接失败: " . $conn->connect_error);
- }
- //设置字符集，如果不设置的话中文可能会乱码
- $conn->query("set names utf8");
- //查询数据
- $sql = "SELECT * FROM testdata WHERE inserttime > '2019-09-13' AND inserttime <'2019-0916'";
- $result = $conn->query($sql);
- //遍历将数据放入数组中
- $data =array();
- while ($row = $result->fetch_assoc())
- {
- $data[] = $row;
- }
- //将查询出来的数据导入Excel中
- require_once('./PHPExcel/PHPExcel.php');
- // 创建Excel文件对象
- $objPHPExcel = new PHPExcel();
- // 设置文档信息，这个文档信息windows系统可以右键文件属性查看
- $objPHPExcel->getProperties()->setCreator("wnn")
- ->setLastModifiedBy("wnn")
- ->setTitle("原创文章链接");
- $objPHPExcel->setActiveSheetIndex(0)
- ->setCellValue('A1','文章ID')
- ->setCellValue('B1','标题')
- ->setCellValue('C1','链接')
- ->setCellValue('D1','作者')
- ->setCellValue('E1','发布时间');
- //设置excel表单根据内容大小变化
- $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
- $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
- $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
- $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
- $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
- //$i=1是标题行
- $i=2;
- foreach ($data as $k=>$v){
- $objPHPExcel->setActiveSheetIndex(0)
- ->setCellValue('A'.$i,$v['id'])
- ->setCellValue('B'.$i,$v['title'])
- ->setCellValue('C'.$i,$v['arcurl'])
- ->setCellValue('D'.$i,$v['writer'])
- ->setCellValue('E'.$i,$v['senddate']);
- //给C列的文字加上链接
- $objPHPExcel->getActiveSheet()->getCell('C'.$i)->getHyperlink()->setUrl($v['arcurl']);
- $i++;
- }
- //给C列的超链接字体颜色变成蓝色
- $length = count($data)+1;
- $objPHPExcel->getActiveSheet()->getStyle( 'C2:C'.$length)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
- //文件名称
- $filename=urlencode('原创作品').'_'.date('YmdHis');
- //生成xls文件
- ob_end_clean();//清除缓冲区,避免乱码
- header('Content-Type: application/vnd.ms-excel');
- header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
- header('Cache-Control: max-age=0');
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
- $objWriter->save('php://output');
如果导出来的Excel文字都是乱码的话，一定要在header()前面加上ob_end_clean()！