PhpSpreadsheet解析Excel文件
安装 PhpSpreadsheet
通过 Composer 安装了 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()]);
}
}
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
路由配置
前端
如果这篇文章对你有用,可以关注本人微信公众号获取更多ヽ(^ω^)ノ ~