介绍
本文会介绍Webman框架中使用PhpSpreadsheet操作Excel，供大家参考。
1、简介
PhpSpreadsheet 是一个用纯PHP编写的库，提供了一组类，使您可以读取和写入不同的电子表格文件格式 PhpSpreadsheet 提供了丰富的API接口，可以设置诸多单元格以及文档属性，包括样式、图片、日期、函数等等诸多应用，总之你想要什么样的Excel表格，PhpSpreadsheet都能做到。
2、安装
使用 composer 安装，在项目根目录下执行下面命令，即可安装。在webman框架中，可以使用composer安装phpoffice/phpspreadsheet库，实现Excel处理功能。在命令行中执行以下命令进行安装：
composer require phpoffice/phpspreadsheet
Excel导入
代码语言：javascript复制<?php
/**
* @desc Excel 导入操作
* @author Tinywan(ShaoBo Wan)
* @email 756684177@qq.com
* @date 2023/11/10 23:27
*/
declare(strict_types=1);
namespace appconsolecontroller;
use PhpOfficePhpSpreadsheetException;
use supportRequest;
class Excel
{
/**
* @desc import
* @param Request $request
* @throws Exception
* @throws Exception
* @author Tinywan(ShaoBo Wan)
*/
public function import(Request $request)
{
$file = $request->file('file');
if (empty($file) || !$file->isValid()) {
throw new Exception('无效的文件');
}
if (!in_array($file->getUploadExtension(), ['xlsx', 'pptx', 'docx', 'pdf'], true)) {
throw new Exception('上传文件格式不支持');
}
$reader = new PhpOfficePhpSpreadsheetReaderXlsx();
if (false === $reader->canRead($file->getRealPath())) {
throw new Exception('非法的Excel文件格式（如：强制转换文件后缀xls为xlsx）');
}
$excel = $reader->load($file->getRealPath());
$sheelList = $excel->getSheet(0)->toArray();
$importSheelList = array_slice($sheelList, 2);
if (empty($importSheelList)) {
throw new Exception('数据为空，请填写后重新上传');
}
$importData = [];
foreach ($importSheelList as $sheel) {
array_push($importData, [
'user_id' => (string)$sheel[0],
'username' => (string)$sheel[1],
'create_time' => time(),
]);
}
var_dump($importData);
}
}
Excel导出
代码语言：javascript复制<?php
/**
* @desc Excel 导出操作
* @author Tinywan(ShaoBo Wan)
* @email 756684177@qq.com
* @date 2023/11/10 23:27
*/
declare(strict_types=1);
namespace appconsolecontroller;
use PhpOfficePhpSpreadsheetCellDataType;
use supportRequest;
use supportResponse;
class Excel
{
/**
* @desc 导出Excel
* @param Request $request
* @return Response
* @throws PhpOfficePhpSpreadsheetWriterException
* @author Tinywan(ShaoBo Wan)
*/
public function import(Request $request)
{
$exportList = UserModel::where(['channel_id' => 10086])->order('id desc')->select();
if ($exportList->isEmpty()) {
throw new Exception('暂无数据');
}
$spreadsheet = new PhpOfficePhpSpreadsheetSpreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 设置默认行高
$sheet->getDefaultRowDimension()->setRowHeight(18);
$sheet->getStyle("A1:J1")
->getFont()
->setBold(true);
$sheet->setCellValue('A1', '用户账号');
$sheet->setCellValue('B1', '姓名');
$sheet->setCellValue('C1', '观看时长');
$sheet->setCellValue('D1', '开始时间');
$sheet->setCellValue('E1', '结束时间');
$sheet->setCellValue('F1', '观看IP');
$sheet->setCellValue('G1', '观看地区');
$sheet->setCellValue('H1', '观看终端（版本号）');
$sheet->setCellValue('I1', '事件');
$sheet->setCellValue('J1', '场次ID');
$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(10);
$sheet->getColumnDimension('C')->setWidth(10);
$sheet->getColumnDimension('D')->setWidth(20);
$sheet->getColumnDimension('E')->setWidth(20);
$sheet->getColumnDimension('F')->setWidth(15);
$sheet->getColumnDimension('G')->setWidth(20);
$sheet->getColumnDimension('H')->setWidth(25);
$sheet->getColumnDimension('I')->setWidth(20);
$sheet->getColumnDimension('J')->setWidth(40);
foreach ($exportList as $key => $user) {
$sheet->setCellValueExplicit("A" . ($key 2), $user['user_id'], DataType::TYPE_STRING)
->setCellValueExplicit('B' . ($key 2), $user['username'], DataType::TYPE_STRING)
->setCellValueExplicit('C' . ($key 2), gmstrftime('%H:%M:%S', $user['duration']), DataType::TYPE_STRING)
->setCellValueExplicit('D' . ($key 2), $user['create_time'], DataType::TYPE_STRING)
->setCellValueExplicit('E' . ($key 2), $user['update_time'], DataType::TYPE_STRING)
->setCellValueExplicit('F' . ($key 2), $user['client_ip'], DataType::TYPE_STRING)
->setCellValueExplicit('G' . ($key 2), $user['region'], DataType::TYPE_STRING)
->setCellValueExplicit('H' . ($key 2), $user['browser'], DataType::TYPE_STRING)
->setCellValueExplicit('I' . ($key 2), $user['event'], DataType::TYPE_STRING)
->setCellValueExplicit('J' . ($key 2), $user['session_id'], DataType::TYPE_STRING);
}
$writer = new PhpOfficePhpSpreadsheetWriterXlsx($spreadsheet);
ob_start();
$writer->save("php://output");
$xlsData = ob_get_contents();
ob_end_clean();
return json([
'filename' => '[开源技术]导出直播观看记录.xlsx',
'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsData),
]);
}
}
