首页 Web 后端 PHP

# 前言

在早期的实习中，我所在的项目中需要将 Excel 文件导入到数据库中，并将数据库中的数据导出到 Excel 文件中。由于项目中使用的数据库为 MySQL，因此需要使用 PHP 对 Excel 文件进行导入和导出。

# 准备工作

# 初识 PhpSpreadsheet

PhpSpreadsheet 是一个开源的 PHP 库，可以用来处理各种电子表格文件，包括 Excel、CSV、ODS 等。
官网：https://phpspreadsheet.readthedocs.io/en/stable/

# 安装 PhpSpreadsheet

使用 Composer 安装 PhpSpreadsheet：

composer require phpoffice/phpspreadsheet

# demo 目录

├── vendor
│   ...
│   └── autoload.php
├── index.php
├── index2.php
├── db.php
└── excel.xlsx

# 使用

# 导入 Excel 文件到 MySQL 数据库

index.php
<?php
require 'vendor/autoload.php';
include "db.php";   // 连接数据库
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load('excel.xlsx'); // 载入 excel 表格
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // 总列数转为数字
$lines = $highestRow - 1; 
if ($lines <= 0) {
    exit('Excel表格中没有数据');
}
$sql = "INSERT INTO `t_student` (`name`, `chinese`, `maths`, `english`) VALUES ";
for ($row = 2; $row <= $highestRow; ++$row) {
    $name = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); // 姓名
    $chinese = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); // 语文
    $maths = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); // 数学
    $english = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); // 外语
    $sql .= "('$name','$chinese','$maths','$english'),";
}
$sql = rtrim($sql, ","); // 去掉最后一个，号
try {
    $db->query($sql);
} catch (Exception $e) {
    echo $e->getMessage();
}
?>
worksheet->getCellByColumnAndRow (col, row)->getValue () 可以获取表格中任意单元格数据内容，col 表示单元格所在的列，以数字表示，A 列表示第一列，$row 表示所在的行。
# 导出 MySQL 数据库到 Excel 文件
# 一、设置表头
首先我们引入自动加载 PhpSpreadsheet 库，然后实例化，设置工作表标题名称为：学生成绩表，接着设置表头内容。表头分为两行，第一行是表格的名称，第二行数表格列名称。
index2.php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
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' => \PhpOffice\PhpSpreadsheet\Style\Alignment::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 行是表头占用了。。
index2.php
$sql = "SELECT id,name,chinese,maths,english FROM `t_student`";
$stmt = $db->query($sql);
$rows = [];
while ($row = $stmt->fetch_assoc()) {
    $rows[] = $row; // 按行添加到数组中
}
$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' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '666666'],
        ],
    ],
    'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
    ],
];
$total_rows = $len + 2;
// 添加所有边框 / 居中
$worksheet->getStyle('A1:E'.$total_rows)->applyFromArray($styleArrayBody);
# 三、下载保存
强制浏览器下载数据并保存为 Excel 文件
index2.php
ob_end_clean();        // 清除缓冲区 如果导出文件打不开加上
$filename = '成绩表.xlsx';
header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Disposition:attachment;filename='.$filename);
header('Cache-Control:max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');