转自：https://qii404.me/2018/10/31/php-excel.html
本文为基础教程，主要用PHP实现对EXCEL文件的读取和写入。PHPExcel已经被官方弃用，并推荐换用PhpSpreadsheet来代替。我们的顺序是先读取，然后写入，最后下载EXCEL文件。
环境依赖
- PHP >= 5.6
- PHP_ZIP拓展
- PHP_XML拓展
- PHP_GD拓展
composer require phpoffice/phpspreadsheet
"require": {
"phpoffice/phpspreadsheet": "^1.3"
}
# 然后执行
composer install
EXCEL文件读取
excel文件形式如下表：| 姓名 | 性别 | 年龄 |
|---|---|---|
| 张三 | 男 | 21 |
| 李四 | 女 | 22 |
| 王五 | 男 | 20 |
Spreadsheet对象，即整个excel文件对象
# 待读取的excel文件，全路径
$filename = '/home/qii/test.xlsx';
# 根据文件名自动创建 适用于不知道文件后缀时xls还是xlsx的情况
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filename);
# 或者如果确定文件后缀，直接创建，性能会略优于上面方法
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader(“Xlsx”);
# $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader(“Xls”);
$spreadsheet = $reader->load($filename);
# 甚至可以直接指定reader实现创建 性能又会优于上面一丢丢
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
# 可以额外设定只读模式，上面也试用，让工具只读取数据，不处理样式，性能会更好
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($filename);
Worksheet表格对象，即当前工作表格
# 获取当前活动的sheet
$sheet = $spreadsheet->getActiveSheet();
# 或者直接指定序号获得第一个sheet
$sheet = $spreadsheet->getSheet(0);
3、开始读取sheet中的单元格数据
# 读取excel中A1数据，即第一行第一列，返回“姓名”
$sheet->getCell('A1')->getValue();
# B1 返回“性别”
$sheet->getCell('B1')->getValue();
# 获取当前总行数
$rows = $sheet->getHighestRow();
$users = [];
# 一般excel中第一行为标题，所以实际数据从第二行开始 循环读取
for($i = 2; $i <= $rows; $i++) {
$temp = [];
$temp[‘name’] = $sheet->getCell(‘A’ . $i)->getValue();
$temp[‘sex’] = $sheet->getCell(‘B’ . $i)->getValue();
$temp[‘age’] = $sheet->getCell(‘C’ . $i)->getValue();
<span class="c1"># 防止空行情况</span>
<span class="k">if</span> <span class="p">(</span><span class="o">!</span><span class="nv">$temp</span><span class="p">[</span><span class="s1">'name'</span><span class="p">])</span> <span class="p">{</span>
<span class="k">continue</span><span class="p">;</span>
<span class="p">}</span>
<span class="nv">$users</span><span class="p">[]</span> <span class="o">=</span> <span class="nv">$temp</span><span class="p">;</span>
}
var_dump($users);
EXCEL文件写入
1、创建表格对象下面方法为新创建表格对象，当然也可以用已经读取完成的对象，如上面的$spreadsheet，然后实现对原有单元格的覆写，生成新文件
# 新创建Spreadsheet对象
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
# 获取活动的sheet
$sheet = $spreadsheet->getActiveSheet();
# 第一行写入标题数据
$sheet->setCellValue('A1', '姓名1');
$sheet->setCellValue('B1', '性别1');
$sheet->setCellValue('C1', '年龄1');
# 模拟写入的数据
$users = [
[‘name’ => ‘张三’, ‘sex’ => ‘男’, ‘age’ => 21,],
[‘name’ => ‘李四’, ‘sex’ => ‘女’, ‘age’ => 22,],
[‘name’ => ‘王五’, ‘sex’ => ‘男’, ‘age’ => 20,],
];
# 要写入的总行数
$count = count($users);
# 逐行写入
for ($i = 0; $i < $count; $i++) {
# 要写入的行号 从第二行开始
$index = $i + 2;
# 用户数据
$line = $users[$i];
<span class="nv">$sheet</span><span class="o">-></span><span class="na">setCellValue</span><span class="p">(</span><span class="s1">'A'</span> <span class="o">.</span> <span class="nv">$index</span><span class="p">,</span> <span class="nv">$line</span><span class="p">[</span><span class="s1">'name'</span><span class="p">]);</span>
<span class="nv">$sheet</span><span class="o">-></span><span class="na">setCellValue</span><span class="p">(</span><span class="s1">'B'</span> <span class="o">.</span> <span class="nv">$index</span><span class="p">,</span> <span class="nv">$line</span><span class="p">[</span><span class="s1">'sex'</span><span class="p">]);</span>
<span class="nv">$sheet</span><span class="o">-></span><span class="na">setCellValue</span><span class="p">(</span><span class="s1">'C'</span> <span class="o">.</span> <span class="nv">$index</span><span class="p">,</span> <span class="nv">$line</span><span class="p">[</span><span class="s1">'age'</span><span class="p">]);</span>
}
# 也可以保存为其他格式
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
# $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet);
# 写入文件
$writer->save(’/tmp/xxx.xlsx’);
实现EXCEL文件下载
和上面唯一的区别是，最终保存的文件不是到具体文件，而是写到标准输出中，再结合对应的header让浏览器实现下载
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
# $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet);
# 添加对应的header头部 其中xxxxx.xls为下载时的文件名
header(‘Content-Type:application/vnd.ms-excel’);
header(‘Content-Disposition:attachment;filename=“xxxxx.xls”’);
header(‘Cache-Control:max-age=0’);
# 写到标准输出中
$writer->save(‘php://output’);
评论 (0)