ThinkPHP6之Excel解析
PhpSpreadsheet解析Excel文件
安装 PhpSpreadsheet
通过 Composer 安装了 PhpSpreadsheet:
composer require phpoffice/phpspreadsheet
控制器
ExcelController
<?php
namespace app\controller;
use think\facade\Db;
use think\facade\Request;
use think\facade\View;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class ExcelController
{
public function upload()
{
// 获取上传的文件
$file = Request::file('file');
if (!$file) {
return json(['status' => 'fail', 'message' => 'No file uploaded']);
}
/* 验证文件类型和大小
$validate = [
'size' => 10485760, // 10MB
'ext' => 'xls,xlsx',
];
$fileInfo = $file->validate($validate);
if (!$fileInfo) {
return json(['status' => 'fail', 'message' => $file->getError()]);
}*/
// thinkphp6验证文件类型和大小
$validate = Validate::rule([
'file' => 'file|fileExt:xls,xlsx|fileSize:10485760', // 10MB
]);
$data = ['file' => $file];
if (!$validate->check($data)) {
return json(['status' => 'fail', 'message' => $validate->getError()]);
}
// 将文件保存到临时路径
$savePath = $file->getPathname();
try {
// 解析Excel文件
$spreadsheet = IOFactory::load($savePath);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
// 开启数据库事务
Db::startTrans();
try {
// 批量插入数据到数据库
foreach ($sheetData as $index => $row) {
if ($index == 0) {
// 跳过第一行标题行
continue;
}
// 假设有三列需要插入到数据库
Db::table('your_table_name')->insert([
'column1' => $row['A'],
'column2' => $row['B'],
'column3' => $row['C'],
]);
}
// 提交事务
Db::commit();
return json(['status' => 'success', 'message' => 'Data imported successfully']);
} catch (\Exception $e) {
// 回滚事务
Db::rollback();
return json(['status' => 'fail', 'message' => 'Data import failed: ' . $e->getMessage()]);
}
} catch (\PhpOffice\PhpSpreadsheet\Reader\Exception $e) {
return json(['status' => 'fail', 'message' => 'Error reading file: ' . $e->getMessage()]);
}
}
}
路由配置
use think\facade\Route;
Route::post('upload-excel', 'ExcelController/upload');
前端
<form action="/upload-excel" method="post" enctype="multipart/form-data">
<label for="file">选择 Excel 文件:</label>
<input type="file" name="file" id="file" accept=".xlsx,.xls">
<input type="submit" value="上传并解析">
</form>
如果这篇文章对你有用,可以关注本人微信公众号获取更多ヽ(^ω^)ノ ~
![微信公众号二维码](https://images.cnblogs.com/cnblogs_com/aeolian/1679458/o_wechat_gzh_qrcode.jpg)