使用PHPExcel導(dǎo)出數(shù)據(jù)到Excel的方法

● PHP版本5.6.38

● mysql版本5.0

//連接數(shù)據(jù)庫(kù)
$mysql_server_name = "*.*.*.*";
$mysql_username="user";
$mysql_password="dbpasswd";
$mysql_database="test";
$conn = mysqli_connect($mysql_server_name,$mysql_username,$mysql_password,$mysql_database);
// 檢測(cè)連接
if ($conn->connect_error) {
    die("連接失敗: " . $conn->connect_error);
}
//設(shè)置字符集，如果不設(shè)置的話中文可能會(huì)亂碼
$conn->query("set names utf8");
//查詢數(shù)據(jù)
$sql = "SELECT * FROM testdata WHERE  inserttime > '2019-09-13' AND inserttime <'2019-0916'";
$result = $conn->query($sql);
//遍歷將數(shù)據(jù)放入數(shù)組中
$data =array();
while ($row = $result->fetch_assoc())
{
    $data[] = $row;
}
//將查詢出來(lái)的數(shù)據(jù)導(dǎo)入Excel中
require_once('./PHPExcel/PHPExcel.php');
// 創(chuàng)建Excel文件對(duì)象
$objPHPExcel = new PHPExcel();
// 設(shè)置文檔信息，這個(gè)文檔信息windows系統(tǒng)可以右鍵文件屬性查看
$objPHPExcel->getProperties()->setCreator("wnn")
    ->setLastModifiedBy("wnn")
    ->setTitle("原創(chuàng)文章鏈接");
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1','文章ID')
    ->setCellValue('B1','標(biāo)題')
    ->setCellValue('C1','鏈接')
    ->setCellValue('D1','作者')
    ->setCellValue('E1','發(fā)布時(shí)間');
//設(shè)置excel表單根據(jù)內(nèi)容大小變化
$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是標(biāo)題行
$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列的超鏈接字體顏色變成藍(lán)色
$length = count($data)+1;
$objPHPExcel->getActiveSheet()->getStyle( 'C2:C'.$length)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE);
//文件名稱
$filename=urlencode('原創(chuàng)作品').'_'.date('YmdHis');
//生成xls文件
ob_end_clean();//清除緩沖區(qū),避免亂碼
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');

如果導(dǎo)出來(lái)的Excel文字都是亂碼的話，一定要在header()前面加上ob_end_clean()！

以上就是PHP利用PHPExcel導(dǎo)出數(shù)據(jù)到Excel的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注億速云其它相關(guān)文章！