386 lines
14 KiB
C#
386 lines
14 KiB
C#
using NPOI.SS.UserModel;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Web;
|
||
using System.Text.RegularExpressions;
|
||
|
||
namespace 報到系統
|
||
{
|
||
public class FileUploadHandler : IHttpHandler
|
||
{
|
||
public void ProcessRequest(HttpContext context)
|
||
{
|
||
try
|
||
{
|
||
string action = context.Request.QueryString["action"];
|
||
|
||
if (action == "ProcessImportFile")
|
||
{
|
||
ProcessImportFile(context);
|
||
}
|
||
else
|
||
{
|
||
SendJsonResponse(context, new BaseResponse
|
||
{
|
||
Success = false,
|
||
Message = "無效的操作"
|
||
});
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
SendJsonResponse(context, new BaseResponse
|
||
{
|
||
Success = false,
|
||
Message = "處理請求發生錯誤：" + ex.Message
|
||
});
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 處理匯入檔案 - 讀取 Excel 檔案並進行驗證，使用 NPOI
|
||
/// </summary>
|
||
private void ProcessImportFile(HttpContext context)
|
||
{
|
||
try
|
||
{
|
||
string courseIDStr = context.Request.QueryString["courseID"];
|
||
if (!int.TryParse(courseIDStr, out int courseID) || courseID <= 0)
|
||
{
|
||
SendJsonResponse(context, new BaseResponse
|
||
{
|
||
Success = false,
|
||
Message = "活動編號為必填欄位"
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 檢查是否有上傳檔案
|
||
if (context.Request.Files.Count == 0)
|
||
{
|
||
SendJsonResponse(context, new BaseResponse
|
||
{
|
||
Success = false,
|
||
Message = "沒有上傳檔案"
|
||
});
|
||
return;
|
||
}
|
||
|
||
HttpPostedFile file = context.Request.Files[0];
|
||
if (file.ContentLength == 0)
|
||
{
|
||
SendJsonResponse(context, new BaseResponse
|
||
{
|
||
Success = false,
|
||
Message = "檔案為空"
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 驗證檔案格式
|
||
string fileName = Path.GetFileName(file.FileName);
|
||
if (!fileName.ToLower().EndsWith(".xlsx") && !fileName.ToLower().EndsWith(".xls"))
|
||
{
|
||
SendJsonResponse(context, new BaseResponse
|
||
{
|
||
Success = false,
|
||
Message = "只支援 .xlsx 或 .xls 格式的 Excel 檔案"
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 使用 NPOI 讀取 Excel 檔案
|
||
IWorkbook workbook = WorkbookFactory.Create(file.InputStream);
|
||
ISheet sheet = workbook.GetSheetAt(0);
|
||
|
||
List<Dictionary<string, object>> previewData = new List<Dictionary<string, object>>();
|
||
List<string> validationErrors = new List<string>();
|
||
int headerRowIndex = -1;
|
||
|
||
// 偵測表頭並讀取資料
|
||
for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
|
||
{
|
||
IRow row = sheet.GetRow(rowIndex);
|
||
if (row == null) continue;
|
||
|
||
// 欄位順序: 序號(0), 學員姓名(1), 手機號(2), 身分別(5), 身分證字號(6), 餐點(7), 電子郵件(8), 失業給付認定次數(9), 性別(10)
|
||
string indexValue = GetCellValue(row.GetCell(0))?.Trim();
|
||
string nameValue = GetCellValue(row.GetCell(1))?.Trim();
|
||
string mobileValue = GetCellValue(row.GetCell(2))?.Trim();
|
||
string idTypeValue = GetCellValue(row.GetCell(5))?.Trim();
|
||
string idNumberValue = GetCellValue(row.GetCell(6))?.Trim();
|
||
string mealTypeValue = GetCellValue(row.GetCell(7))?.Trim();
|
||
string emailValue = GetCellValue(row.GetCell(8))?.Trim();
|
||
string lossJobTimesValue = GetCellValue(row.GetCell(9))?.Trim();
|
||
string genderValue = GetCellValue(row.GetCell(10))?.Trim();
|
||
|
||
// 偵測表頭 - 檢查第1欄和第2欄
|
||
if (headerRowIndex < 0 && IsHeaderRow(indexValue, nameValue))
|
||
{
|
||
headerRowIndex = rowIndex;
|
||
continue;
|
||
}
|
||
|
||
// 跳過表頭之前的行
|
||
if (headerRowIndex < 0) continue;
|
||
|
||
// 跳過空行
|
||
if (string.IsNullOrEmpty(nameValue) && string.IsNullOrEmpty(mobileValue))
|
||
continue;
|
||
|
||
// 驗證資料
|
||
List<string> rowErrors = new List<string>();
|
||
int dataRowIndex = rowIndex - headerRowIndex;
|
||
|
||
if (string.IsNullOrEmpty(nameValue?.Trim()))
|
||
{
|
||
rowErrors.Add($"第 {dataRowIndex} 列：姓名為必填欄位");
|
||
validationErrors.Add(rowErrors[rowErrors.Count - 1]);
|
||
}
|
||
else if (nameValue.Length > 100)
|
||
{
|
||
rowErrors.Add($"第 {dataRowIndex} 列：姓名最長 100 個字元");
|
||
validationErrors.Add(rowErrors[rowErrors.Count - 1]);
|
||
}
|
||
|
||
if (string.IsNullOrEmpty(mobileValue?.Trim()))
|
||
{
|
||
rowErrors.Add($"第 {dataRowIndex} 列：手機號為必填欄位");
|
||
validationErrors.Add(rowErrors[rowErrors.Count - 1]);
|
||
}
|
||
else
|
||
{
|
||
// 清洗資料，只保留數字
|
||
mobileValue = Regex.Replace(mobileValue, @"\D", "");
|
||
|
||
if (!Regex.IsMatch(mobileValue.Trim(), @"^09\d{8}$"))
|
||
{
|
||
rowErrors.Add($"第 {dataRowIndex} 列：手機號格式必須為 09 開頭的 10 位數字");
|
||
validationErrors.Add(rowErrors[rowErrors.Count - 1]);
|
||
}
|
||
}
|
||
|
||
// 驗證身分證字號（如果有填寫）
|
||
if (!string.IsNullOrEmpty(idNumberValue?.Trim()))
|
||
{
|
||
if (!ValidateIDNumber(idNumberValue.ToUpper().Trim()))
|
||
{
|
||
rowErrors.Add($"第 {dataRowIndex} 列：身分證字號無效（格式應為：大寫英文字母 + 9位數字，且檢查碼需正確）");
|
||
validationErrors.Add(rowErrors[rowErrors.Count - 1]);
|
||
}
|
||
}
|
||
|
||
// 驗證電子郵件（如果有填寫）
|
||
if (!string.IsNullOrEmpty(emailValue?.Trim()))
|
||
{
|
||
if (!ValidateEmail(emailValue.Trim()))
|
||
{
|
||
rowErrors.Add($"第 {dataRowIndex} 列：電子郵件格式不正確（例如：user@example.com）");
|
||
validationErrors.Add(rowErrors[rowErrors.Count - 1]);
|
||
}
|
||
}
|
||
|
||
// 驗證失業給付認定次數（如果有填寫）
|
||
if (!string.IsNullOrEmpty(lossJobTimesValue?.Trim()))
|
||
{
|
||
if (!int.TryParse(lossJobTimesValue, out int i))
|
||
{
|
||
rowErrors.Add($"第 {dataRowIndex} 列：失業給付認定次數必須為數字");
|
||
validationErrors.Add(rowErrors[rowErrors.Count - 1]);
|
||
}
|
||
|
||
if (i < 0)
|
||
{
|
||
rowErrors.Add($"第 {dataRowIndex} 列：失業給付認定次數必須大於或等於 0");
|
||
validationErrors.Add(rowErrors[rowErrors.Count - 1]);
|
||
}
|
||
}
|
||
|
||
var rowData = new Dictionary<string, object>();
|
||
rowData["Name"] = nameValue;
|
||
rowData["Mobile"] = mobileValue;
|
||
rowData["IDType"] = idTypeValue;
|
||
rowData["IDNumber"] = idNumberValue?.ToUpper();
|
||
rowData["MealType"] = mealTypeValue;
|
||
rowData["Email"] = emailValue;
|
||
rowData["LossJobTimes"] = lossJobTimesValue;
|
||
rowData["Gender"] = genderValue;
|
||
rowData["SignUpType"] = "預約";
|
||
rowData["Errors"] = rowErrors;
|
||
rowData["IsValid"] = rowErrors.Count == 0;
|
||
previewData.Add(rowData);
|
||
}
|
||
|
||
workbook.Close();
|
||
|
||
int validRowCount = 0;
|
||
foreach (var item in previewData)
|
||
{
|
||
if ((bool)item["IsValid"])
|
||
{
|
||
validRowCount++;
|
||
}
|
||
}
|
||
|
||
SendJsonResponse(context, new BaseResponse
|
||
{
|
||
Success = true,
|
||
Message = "檔案讀取成功",
|
||
Data = new
|
||
{
|
||
PreviewData = previewData,
|
||
ValidationErrors = validationErrors,
|
||
ValidRowCount = validRowCount,
|
||
TotalRowCount = previewData.Count
|
||
}
|
||
});
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
SendJsonResponse(context, new BaseResponse
|
||
{
|
||
Success = false,
|
||
Message = "處理匯入檔案發生錯誤：" + ex.Message
|
||
});
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 驗證中華民國身分證字號
|
||
/// 規則：第一碼為英文字母（A-Z），後接九位數字
|
||
/// 使用檢查碼算法驗證
|
||
/// </summary>
|
||
private bool ValidateIDNumber(string idNumber)
|
||
{
|
||
if (string.IsNullOrEmpty(idNumber))
|
||
return false;
|
||
|
||
idNumber = idNumber.Trim().ToUpper();
|
||
|
||
// 檢查長度
|
||
if (idNumber.Length != 10)
|
||
return false;
|
||
|
||
// 第一碼必須是英文字母
|
||
if (idNumber[0] < 'A' || idNumber[0] > 'Z')
|
||
return false;
|
||
|
||
// 後九碼必須是數字
|
||
for (int i = 1; i < 10; i++)
|
||
{
|
||
if (idNumber[i] < '0' || idNumber[i] > '9')
|
||
return false;
|
||
}
|
||
|
||
// 驗證檢查碼
|
||
// 字母對應的數字
|
||
int[] letterValues = {
|
||
10, 11, 12, 13, 14, 15, 16, 17, 34, 18, 19, 20, 21,
|
||
22, 35, 23, 24, 25, 26, 27, 28, 29, 32, 30, 31, 33
|
||
};
|
||
|
||
int code = letterValues[idNumber[0] - 'A'];
|
||
|
||
int sum = code / 10 + (code % 10) * 9;
|
||
|
||
int[] weights = { 8, 7, 6, 5, 4, 3, 2, 1 };
|
||
|
||
for (int i = 1; i <= 8; i++)
|
||
{
|
||
sum += (idNumber[i] - '0') * weights[i - 1];
|
||
}
|
||
|
||
sum += idNumber[9] - '0';
|
||
|
||
return sum % 10 == 0;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 驗證電子郵件格式
|
||
/// </summary>
|
||
private bool ValidateEmail(string email)
|
||
{
|
||
if (string.IsNullOrEmpty(email))
|
||
return true;
|
||
|
||
return System.Text.RegularExpressions.Regex.IsMatch(
|
||
email.Trim(),
|
||
@"^[^\s@]+@[^\s@]+\.[^\s@]+$");
|
||
}
|
||
|
||
/// <summary>
|
||
/// 取得單元格的值（文字）
|
||
/// </summary>
|
||
private string GetCellValue(ICell cell)
|
||
{
|
||
if (cell == null) return "";
|
||
|
||
try
|
||
{
|
||
switch (cell.CellType)
|
||
{
|
||
case CellType.String:
|
||
return cell.StringCellValue;
|
||
case CellType.Numeric:
|
||
return cell.NumericCellValue.ToString();
|
||
case CellType.Boolean:
|
||
return cell.BooleanCellValue.ToString();
|
||
default:
|
||
return "";
|
||
}
|
||
}
|
||
catch
|
||
{
|
||
return "";
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 判斷是否為表頭行
|
||
/// </summary>
|
||
private bool IsHeaderRow(string col1, string col2)
|
||
{
|
||
// col1 是序號, col2 是學員姓名
|
||
string[] col1Keywords = { "序號", "編號", "index", "Index", "#" };
|
||
string[] col2Keywords = { "姓名", "名字", "name", "Name", "學員" };
|
||
|
||
bool isCol1Header = col1Keywords.Any(keyword =>
|
||
(col1 ?? "").ToLower().Contains(keyword.ToLower()));
|
||
bool isCol2Header = col2Keywords.Any(keyword =>
|
||
(col2 ?? "").ToLower().Contains(keyword.ToLower()));
|
||
|
||
return isCol1Header || isCol2Header;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 發送 JSON 響應
|
||
/// </summary>
|
||
private void SendJsonResponse(HttpContext context, object data)
|
||
{
|
||
context.Response.ContentType = "application/json; charset=utf-8";
|
||
try
|
||
{
|
||
// 使用 JavaScriptSerializer 序列化為 JSON
|
||
System.Web.Script.Serialization.JavaScriptSerializer serializer =
|
||
new System.Web.Script.Serialization.JavaScriptSerializer();
|
||
// 設定遞迴深度以支援複雜物件
|
||
serializer.MaxJsonLength = int.MaxValue;
|
||
string json = serializer.Serialize(data);
|
||
context.Response.Write(json);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
// 備用的簡單 JSON 序列化
|
||
context.Response.Write("{\"Success\":false,\"Message\":\"序列化錯誤：" + ex.Message.Replace("\"", "\\\"") + "\"}");
|
||
}
|
||
}
|
||
|
||
public bool IsReusable
|
||
{
|
||
get { return false; }
|
||
}
|
||
}
|
||
}
|