ThinkPHP6之Excel解析

composer require phpoffice/phpspreadsheet

<?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>