文章中心
文章中心
我们以信息质量为主,为客户展现真实可靠的数据
易懂SEO应用系统 > 文章中心 > 网站建设 > php导出excel表方法

php导出excel表方法

网站专员网站建设562人看过2021-6-24 08:00:32

我们使用PhpSpreadsheet将学生成绩数据导出为Excel表。注意:PhpSpreadsheet要求PHP版本7.1以上。


php导出excel表方法源码如下:


一、数据库连接(conn.php)


<?php 
//header("Content-Type: text/html;charset=utf-8");
//error_reporting(E_ALL); 
//ini_set('display_errors', true);

define('DBHOST', 'localhost');
define('DBNAME', 'demo_execl');//数据库名
define('DBUSER', 'root');//账号
define('DBPWD', '');//密码
define('DBPREFIX', 'hw_');
define('DBCHARSET', 'utf8');
define('CONN', '');
define('TIMEZONE', 'Asia/Shanghai');

try{
    $db = new PDO('mysql:host='.DBHOST.';dbname='.DBNAME, DBUSER, DBPWD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->query('SET NAMES utf8;');
}catch(PDOException  $e ){
    echo '{"result":"failed", "msg":"连接数据库失败!"}';
    exit;
}
?>
二、mysql数据库导出excel表(export.php)



<?php 
require 'vendor/autoload.php';

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

include('conn.php');

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setTitle('学生成绩表');

//表头
//设置单元格内容
$worksheet->setCellValueByColumnAndRow(1, 1, '学生成绩表');
$worksheet->setCellValueByColumnAndRow(1, 2, '姓名');
    $worksheet->setCellValueByColumnAndRow(2, 2, '语文');
    $worksheet->setCellValueByColumnAndRow(3, 2, '数学');
    $worksheet->setCellValueByColumnAndRow(4, 2, '外语');
    $worksheet->setCellValueByColumnAndRow(5, 2, '总分');

    //合并单元格
    $worksheet->mergeCells('A1:E1');

    $styleArray = [
        'font' => [
            'bold' => true
        ],
        'alignment' => [
            'horizontal' => PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER,
        ],
    ];
    //设置单元格样式
    $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);

    $worksheet->getStyle('A2:E2')->applyFromArray($styleArray)->getFont()->setSize(14);
    //$worksheet->getStyle('A2:D2')->getFont()->setSize(14);


    //读取数据
    $sql = "SELECT id,name,chinese,maths,english FROM `t_student`";
    $stmt = $db->query($sql);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $len = count($rows);
    $j = 0;
    for ($i=0; $i < $len; $i++) { 
        $j = $i + 3;
        $worksheet->setCellValueByColumnAndRow(1, $j, $rows[$i]['name']);
        $worksheet->setCellValueByColumnAndRow(2, $j, $rows[$i]['chinese']);
        $worksheet->setCellValueByColumnAndRow(3, $j, $rows[$i]['maths']);
        $worksheet->setCellValueByColumnAndRow(4, $j, $rows[$i]['english']);
        $worksheet->setCellValueByColumnAndRow(5, $j, $rows[$i]['chinese'] + $rows[$i]['maths'] + $rows[$i]['english']);
    }


    $styleArrayBody = [
        'borders' => [
            'allBorders' => [
                'borderStyle' => PhpOfficePhpSpreadsheetStyleBorder::BORDER_THIN,
                'color' => ['argb' => '666666'],
            ],
        ],
        'alignment' => [
            'horizontal' => PhpOfficePhpSpreadsheetStyleAlignment::HORIZONTAL_CENTER,
        ],
    ];
    $total_rows = $len + 2;
    //添加所有边框/居中
    $worksheet->getStyle('A1:E'.$total_rows)->applyFromArray($styleArrayBody);


// 下载
$filename = '成绩表.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');

$writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
?>



本站注重原创,部分内容来源于网络整理,如有侵权,请联系QQ 570803231 进行删改,谢谢。

易懂SEO专注网站建设、SEO优化和网站安全,十多年来通过我们不懈努力获得了众多客户认可和尊重。

我们的目标:让所有用户都能用上一款好的网站系统!

我们的优势