本文介绍: 如果你尚未安装Composer,请先安装 Composer。Composer是PHP的依赖管理工具,它可以方便地安装和管理项目中的第三方库。

Composer安装

如果你尚未安装Composer,请先安装 Composer。Composer是PHP的依赖管理工具,它可以方便地安装和管理项目中的第三方库。

安装phpoffice/phpspreadsheet在这里插入图片描述 触发控制器里面方法 wdjzdc()

控制引入

use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;

在这里插入图片描述

//wdjzdc数据导出
    public function wdjzdc()
    {
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
//设置工作标题名称
        $worksheet->setTitle('wdjz客户表');

//表头
//设置单元格内容
        $worksheet->setCellValueByColumnAndRow(1, 1, 'wdjz无毒样板客户表');
        $worksheet->setCellValueByColumnAndRow(1, 2, 'name');
        $worksheet->setCellValueByColumnAndRow(2, 2, 'phone');
        $worksheet->setCellValueByColumnAndRow(3, 2, 'radio1');
        $worksheet->setCellValueByColumnAndRow(4, 2, 'style');
        $worksheet->setCellValueByColumnAndRow(5, 2, 'time');

//合并单元格
        $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);
        ;
        $jzInfo = db('wdjz')->select();
        $len = count($jzInfo);
        $j = 0;
        for ($i=0; $i < $len; $i++) {
            $j = $i + 3; //从表格第3行开始

            $worksheet->setCellValueByColumnAndRow(1, $j, $jzInfo[$i]['name']);
            $worksheet->setCellValueByColumnAndRow(2, $j, $jzInfo[$i]['phone']);
            $worksheet->setCellValueByColumnAndRow(3, $j, $jzInfo[$i]['radio1']);
            $worksheet->setCellValueByColumnAndRow(4, $j, $jzInfo[$i]['style']);
            $worksheet->setCellValueByColumnAndRow(5, $j, $jzInfo[$i]['time']);
        }

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

        $filename = 'wdjz无毒样板客户表.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');
    }

设置表头

首先我们引入自动加载PhpSpreadsheet库,然后实例化,设置工作标题名称为:学生成绩表,接着设置表头内容表头分为两行第一行表格名称第二行表格列名称。最后我们第一行单元格进行合并,并设置表头内容样式:字体对齐方式

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);

读取数据

我们连接数据库后,直接读取学生成绩表t_student然后for循环,设置每个单元格对应内容计算成绩注意的是表格中的数据是从第3行开始,因为第1,2行是表头占用了。

然后我们设置整个表格样式,给表格加上边框,并且居中对齐

$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; //从表格第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);

如果仅是为了满足文章开头说的老板的需求我们这个时候可以将数据保存为Excel文件,当然这个Excel文件保存服务器上,然后使用邮件方式将Excel发送给老板就结了。

下载保存

最后我们强制浏览器下载数据并保存为Excel文件

$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');

如果你想要保存为.xls文件格式的话,可以改下header代码

$filename = '成绩表.xlsx';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');

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

PhpSpreadsheet提供了很多选项设置,接下来文章我会专门介绍有关生成Excel的设置,如样式:字体对齐颜色、行高于列宽合并拆分图片日期时间换行函数使用等等。敬请关注

原文地址:https://blog.csdn.net/wangxuanyang_zer/article/details/134812052

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任

如若转载,请注明出处:http://www.7code.cn/show_47796.html

如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱suwngjj01@126.com进行投诉反馈,一经查实,立即删除

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注