<!-- 引入必要資源 -->
<script src="/static/js/jquery-1.12.4.min.js"></script>
<script src="/static/uploadify/jquery.uploadify.min.js"></script>
<link rel="stylesheet" href="/static/uploadify/uploadify.css">
<!-- 上傳按鈕 -->
<input type="file" id="student_excel" name="student_excel" accept=".xls,.xlsx">
<!-- 進度模態框 (Amaze UI) -->
<div class="am-modal am-modal-loading am-modal-no-btn" tabindex="-1" id="upload-modal">
<div class="am-modal-dialog">
<div class="am-modal-hd">處理中</div>
<div class="am-modal-bd" id="modal-content">正在上傳檔案...</div>
</div>
</div>
$(function() {
const uploadUrl = "";
const importUrl = "";
const sessionId = "";
const $modal = $('#upload-modal');
const $content = $('#modal-content');
$('#student_excel').uploadify({
'swf' : '/static/uploadify/uploadify.swf',
'uploader' : uploadUrl,
'buttonText' : '選擇 Excel 檔案',
'width' : 150,
'height' : 36,
'fileTypeExts' : '*.xls;*.xlsx',
'formData' : { 'session_id': sessionId },
'onUploadStart' : function(file) {
$content.text('正在上傳檔案...');
$modal.modal('open');
},
'onUploadSuccess' : function(file, data, response) {
$content.text('正在解析匯入資料...');
try {
const result = JSON.parse(data);
if (result.code !== 1) throw new Error(result.msg);
// 發起匯入請求
$.post(importUrl, { file: result.file }, function(res) {
$modal.modal('close');
if (res.code === 1) {
alert('匯入成功：' + res.msg);
location.reload();
} else {
alert('匯入失敗：' + res.msg);
}
}, 'json');
} catch (e) {
$modal.modal('close');
alert('伺服器回應異常：' + e.message);
}
},
'onUploadError' : function(file, errorCode, errorMsg, errorString) {
$modal.modal('close');
alert('上傳失敗：' + errorString);
}
});
});
<?php
namespace app\admin\controller;
use think\Controller;
use think\facade\Filesystem;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;
class Student extends Controller
{
// 上傳處理
public function upload()
{
$file = request()->file('Filedata');
if (empty($file)) {
return json(['code' => 0, 'msg' => '未接收到檔案']);
}
// 驗證：副檔名、MIME、大小
$allowedExt = ['xls', 'xlsx'];
$ext = strtolower($file->getOriginalExtension());
if (!in_array($ext, $allowedExt)) {
return json(['code' => 0, 'msg' => '僅支援 .xls/.xlsx 格式']);
}
// 儲存到非公開目錄
$savePath = 'uploads/excel/' . date('Ymd');
$filename = $file->move(ROOT_PATH . 'public/' . $savePath);
if (!$filename) {
return json(['code' => 0, 'msg' => $file->getError()]);
}
return json([
'code' => 1,
'file' => $savePath . '/' . $filename->getSaveName(),
'msg' => '上傳成功'
]);
}
// 匯入處理
public function import()
{
$relativePath = input('file');
$fullPath = ROOT_PATH . 'public/' . $relativePath;
if (!file_exists($fullPath)) {
return json(['code' => 0, 'msg' => '檔案不存在']);
}
try {
$spreadsheet = IOFactory::load($fullPath);
$sheet = $spreadsheet->getActiveSheet();
$rows = $sheet->toArray(null, true, true, true); // 索引為欄位字母
$success = 0;
$errors = [];
// 跳過標題列 (假設第 1 列)
foreach (array_slice($rows, 1) as $index => $row) {
// 資料驗證
if (empty($row['A']) || empty($row['B'])) {
$errors[] = "第 {$index} 行：學號或姓名為空";
continue;
}
$data = [
'student_no' => trim($row['A']),
'name' => trim($row['B']),
'gender' => (isset($row['C']) && $row['C'] === '男') ? 1 : 0,
'password' => md5($row['D'] ?? '123456'), // 預設密碼
'create_time'=> time(),
];
// 檢查重複
if (\app\model\Student::where('student_no', $data['student_no'])->find()) {
$errors[] = "第 {$index} 行：學號 {$data['student_no']} 已存在";
continue;
}
\app\model\Student::create($data);
$success++;
}
// 清理暫存檔
@unlink($fullPath);
return json([
'code' => 1,
'msg' => "成功匯入 {$success} 筆" . ($errors ? "，失敗 " . count($errors) . " 筆" : "")
]);
} catch (ReaderException $e) {
return json(['code' => 0, 'msg' => 'Excel 解析失敗：' . $e->getMessage()]);
} catch (\Exception $e) {
return json(['code' => 0, 'msg' => '系統錯誤：' . $e->getMessage()]);
}
}
}
// 讀取大檔案優化：使用 ReadFilter 僅讀取必要範圍
class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter
{
private $startRow = 0;
private $endRow = 0;
public function setRows($start, $end) {
$this->startRow = $start;
$this->endRow = $end;
}
public function readCell($column, $row, $worksheetName = '') {
return ($row >= $this->startRow && $row < $this->endRow);
}
}
// 分塊讀取大檔 (每次 1000 列)
$filter = new ChunkReadFilter();
$reader = IOFactory::createReader('Xlsx');
$reader->setReadFilter($filter);
$chunkSize = 1000;
for ($start = 2; $start <= $totalRows; $start += $chunkSize) {
$filter->setRows($start, min($start + $chunkSize, $totalRows + 1));
$spreadsheet = $reader->load($fullPath);
// 處理 $spreadsheet->getActiveSheet()->toArray()
unset($spreadsheet); // 釋放記憶體
}
$file->getMimeType())、魔術位元組、大小限制location ~ \.php$ { deny all; })trim()、htmlspecialchars()、長度限制、格式驗證Db::transaction() 中，失敗回滾Uploadify 基於 Flash，已被瀏覽器淘汰。建議遷移至：
本文展示了 ThinkPHP 6 整合前端上傳組件與 PhpSpreadsheet 實現 Excel 無刷新匯入的完整流程。關鍵點在於前後端分離的非同步協作：上傳 → 回傳檔案路徑 → 後端異步解析入庫 → 前端輪詢/回調顯示結果。實際專案中務必加強檔案驗證、資料清洗、異常處理與記憶體優化。
