PHP uses PHPExcel to export data to Excel

PHP Use PHPExcel to transfer data to Excel; there is also a solution for exporting garbled data.

Related recommendations: "PHPExcel-1.8 class library (worth collecting!)"

Post the code directly!

● PHP version 5.6.38

● mysql version 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 > &#39;2019-09-13&#39; AND inserttime <&#39;2019-0916&#39;";
$result = $conn->query($sql);
//遍历将数据放入数组中
$data =array();
while ($row = $result->fetch_assoc())
{
    $data[] = $row;
}
//将查询出来的数据导入Excel中
require_once(&#39;./PHPExcel/PHPExcel.php&#39;);
// 创建Excel文件对象
$objPHPExcel = new PHPExcel();
// 设置文档信息，这个文档信息windows系统可以右键文件属性查看
$objPHPExcel->getProperties()->setCreator("wnn")
    ->setLastModifiedBy("wnn")
    ->setTitle("原创文章链接");
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue(&#39;A1&#39;,&#39;文章ID&#39;)
    ->setCellValue(&#39;B1&#39;,&#39;标题&#39;)
    ->setCellValue(&#39;C1&#39;,&#39;链接&#39;)
    ->setCellValue(&#39;D1&#39;,&#39;作者&#39;)
    ->setCellValue(&#39;E1&#39;,&#39;发布时间&#39;);
//设置excel表单根据内容大小变化
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;A&#39;)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setAutoSize(true);
//$i=1是标题行
$i=2;
foreach ($data as $k=>$v){
    $objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue(&#39;A&#39;.$i,$v[&#39;id&#39;])
        ->setCellValue(&#39;B&#39;.$i,$v[&#39;title&#39;])
        ->setCellValue(&#39;C&#39;.$i,$v[&#39;arcurl&#39;])
        ->setCellValue(&#39;D&#39;.$i,$v[&#39;writer&#39;])
        ->setCellValue(&#39;E&#39;.$i,$v[&#39;senddate&#39;]);
　　　//给C列的文字加上链接
    $objPHPExcel->getActiveSheet()->getCell(&#39;C&#39;.$i)->getHyperlink()->setUrl($v[&#39;arcurl&#39;]);
    $i++;
}
//给C列的超链接字体颜色变成蓝色
$length = count($data)+1;
$objPHPExcel->getActiveSheet()->getStyle( &#39;C2:C&#39;.$length)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
//文件名称
$filename=urlencode(&#39;原创作品&#39;).&#39;_&#39;.date(&#39;YmdHis&#39;);
//生成xls文件
ob_end_clean();//清除缓冲区,避免乱码
header(&#39;Content-Type: application/vnd.ms-excel&#39;);
header(&#39;Content-Disposition: attachment;filename="&#39;.$filename.&#39;.xls"&#39;);
header(&#39;Cache-Control: max-age=0&#39;);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;);
$objWriter->save(&#39;php://output&#39;);

If the exported Excel text is garbled, be sure to add ob_end_clean in front of header() ()!

For more php related knowledge, please visit php tutorial!

The above is the detailed content of PHP uses PHPExcel to export data to Excel. For more information, please follow other related articles on the PHP Chinese website!