484 lines
17 KiB
C#
484 lines
17 KiB
C#
using NPOI.HSSF.UserModel;
|
|
using NPOI.SS.Formula.Functions;
|
|
using NPOI.SS.UserModel;
|
|
using NPOI.XSSF.UserModel;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data.Common;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Web;
|
|
using WebLib;
|
|
|
|
namespace Education
|
|
{
|
|
public class ImportBase
|
|
{
|
|
protected WebLib.Excel excel;
|
|
protected List<ImportColumn> columns;
|
|
private static string importFilePath = "~/DocUpload/Import";
|
|
protected DbConnection connection;
|
|
protected static List<string> trueValues = new List<string>() { "Y", "V", "T", "1", "YES", "TRUE" };
|
|
|
|
public const double MinOADate = 43831.0; // 2020/01/01
|
|
public const double MaxOADate = 2958465.0; // 9999/12/31
|
|
|
|
public ImportBase()
|
|
{
|
|
excel = new WebLib.Excel();
|
|
columns = new List<ImportColumn>();
|
|
}
|
|
|
|
public DbConnection Connection
|
|
{
|
|
get { return connection; }
|
|
set { connection = value; }
|
|
}
|
|
|
|
public static string ImportFilePath
|
|
{
|
|
get
|
|
{
|
|
var fullPath = HttpContext.Current.Server.MapPath(importFilePath);
|
|
if (!Directory.Exists(fullPath))
|
|
{
|
|
Directory.CreateDirectory(fullPath);
|
|
}
|
|
|
|
return fullPath;
|
|
}
|
|
}
|
|
|
|
public MemoryStream ImportExample()
|
|
{
|
|
var workbook = new XSSFWorkbook();
|
|
var sheet = workbook.CreateSheet("匯入範例");
|
|
excel.Sheet = sheet;
|
|
int col = 0;
|
|
foreach (var column in columns)
|
|
{
|
|
excel.SetCellValue(0, col, column.Title);
|
|
excel.SetColumnWidthInChars(col, column.Width);
|
|
col++;
|
|
}
|
|
MemoryStream ms = new MemoryStream();
|
|
workbook.Write(ms);
|
|
return ms;
|
|
}
|
|
|
|
public IWorkbook LoadWorkbook(string filePath)
|
|
{
|
|
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
|
|
{
|
|
if (Path.GetExtension(filePath).ToLower() == ".xls")
|
|
{
|
|
return new HSSFWorkbook(file);
|
|
}
|
|
else if (Path.GetExtension(filePath).ToLower() == ".xlsx")
|
|
{
|
|
return new XSSFWorkbook(file);
|
|
}
|
|
else
|
|
{
|
|
throw new Exception("不支援的檔案格式");
|
|
}
|
|
}
|
|
}
|
|
|
|
public List<string> Validate(string filePath, Type type, out int rowCount)
|
|
{
|
|
var errors = new List<string>();
|
|
rowCount = 0;
|
|
IWorkbook workbook = null;
|
|
try
|
|
{
|
|
// 1. 開啟 Excel 檔案
|
|
workbook = LoadWorkbook(filePath);
|
|
}
|
|
catch (NPOI.POIXMLException)
|
|
{
|
|
errors.Add("無法開啟上傳檔案，請取消隱藏欄位並清除篩選條件");
|
|
}
|
|
catch (Exception)
|
|
{
|
|
errors.Add("無法開啟上傳檔案，請檢查檔案是否無損壞");
|
|
}
|
|
|
|
if (workbook == null)
|
|
{
|
|
return errors;
|
|
}
|
|
|
|
// 2. 讀取第一個工作表
|
|
var sheet = workbook.GetSheetAt(0);
|
|
|
|
// 3. 驗證欄位名稱
|
|
var headerRow = sheet.GetRow(0);
|
|
errors.AddRange(ValidateColumns(headerRow, out var columnMap));
|
|
|
|
// 4. 驗證每一列資料
|
|
for (int rowId = 1; rowId <= sheet.LastRowNum; rowId++)
|
|
{
|
|
var row = sheet.GetRow(rowId);
|
|
if (row == null)
|
|
{
|
|
break;
|
|
|
|
}
|
|
var rowErrors = ValidateRow(row, type, columnMap, out bool stop);
|
|
foreach (var err in rowErrors)
|
|
{
|
|
errors.Add($"第 {rowId} 列：{err}");
|
|
}
|
|
rowCount++;
|
|
if (stop)
|
|
{
|
|
break;
|
|
}
|
|
}
|
|
if (rowCount == 0)
|
|
{
|
|
errors.Add("沒有任何資料列");
|
|
}
|
|
// 5. 回傳錯誤訊息清單
|
|
return errors;
|
|
}
|
|
|
|
public List<string> ValidateColumns(IRow headerRow, out Dictionary<string, int> columnMap)
|
|
{
|
|
List<string> errors = new List<string>();
|
|
columnMap = new Dictionary<string, int>();
|
|
|
|
// 建立標題 → 欄位索引的對照表
|
|
if (headerRow != null)
|
|
{
|
|
for (int i = 0; i <= headerRow.LastCellNum; i++)
|
|
{
|
|
var cell = headerRow.GetCell(i);
|
|
if (cell == null) continue;
|
|
string title = cell.ToString().Trim();
|
|
if (!string.IsNullOrEmpty(title) && !columnMap.ContainsKey(title))
|
|
{
|
|
columnMap[title] = i;
|
|
}
|
|
}
|
|
}
|
|
|
|
// 確認每個必要欄位都存在於標頭列
|
|
foreach (var column in columns)
|
|
{
|
|
if (!columnMap.ContainsKey(column.Title))
|
|
{
|
|
errors.Add($"找不到欄位「{column.Title}」");
|
|
}
|
|
}
|
|
return errors;
|
|
}
|
|
|
|
public List<string> ValidateRow(IRow dataRow, Type type, Dictionary<string, int> columnMap, out bool stop)
|
|
{
|
|
stop = false;
|
|
List<string> errors = new List<string>();
|
|
|
|
// 以欄位名稱取得每欄的值
|
|
var cellValues = new Dictionary<string, string>();
|
|
foreach (var column in columns)
|
|
{
|
|
string cellValue = "";
|
|
if (columnMap.ContainsKey(column.Title))
|
|
{
|
|
var cell = dataRow.GetCell(columnMap[column.Title]);
|
|
cellValue = cell == null ? "" : excel.CellValueToString(cell);
|
|
}
|
|
cellValues[column.Title] = cellValue;
|
|
}
|
|
|
|
// 若所有欄位皆為空則回傳停止旗標
|
|
bool allEmpty = true;
|
|
foreach (var v in cellValues.Values)
|
|
{
|
|
if (!string.IsNullOrEmpty(v)) { allEmpty = false; break; }
|
|
}
|
|
if (allEmpty)
|
|
{
|
|
stop = true;
|
|
return errors;
|
|
}
|
|
|
|
foreach (var column in columns)
|
|
{
|
|
string cellValue = cellValues[column.Title];
|
|
if (column.Required && string.IsNullOrEmpty(cellValue))
|
|
{
|
|
errors.Add($"欄位「{column.Title}」為必填欄位");
|
|
}
|
|
|
|
// 應用 TransformFunction（如果設定）
|
|
if (column.TransformFunction != null)
|
|
{
|
|
cellValue = column.TransformFunction(cellValue)?.ToString() ?? "";
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(cellValue))
|
|
{
|
|
var property = type.GetProperty(column.FieldName);
|
|
if (property != null)
|
|
{
|
|
try
|
|
{
|
|
var targetType = property.PropertyType;
|
|
if (targetType.IsGenericType && targetType.GetGenericTypeDefinition() == typeof(Nullable<>))
|
|
{
|
|
targetType = Nullable.GetUnderlyingType(targetType);
|
|
}
|
|
if (targetType == typeof(DateTime))
|
|
{
|
|
ParseExcelDate(cellValue);
|
|
}
|
|
else
|
|
{
|
|
Convert.ChangeType(cellValue, targetType);
|
|
}
|
|
}
|
|
catch
|
|
{
|
|
var typeName = property.PropertyType.Name;
|
|
if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
|
|
{
|
|
var underlying = Nullable.GetUnderlyingType(property.PropertyType);
|
|
typeName = $"{underlying.Name}（可空）";
|
|
}
|
|
errors.Add($"欄位「{column.Title}」的值「{cellValue}」應該是 {typeName} 類型".Replace("<", "<").Replace(">", ">"));
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return errors;
|
|
}
|
|
|
|
public List<T> ReadRows<T>(string filePath, int beginRow = 1) where T : new()
|
|
{
|
|
var workbook = LoadWorkbook(filePath);
|
|
excel.Sheet = workbook.GetSheetAt(0);
|
|
var result = new List<T>();
|
|
|
|
// 從標頭列建立欄位名稱 → 欄位索引對照表
|
|
var columnMap = new Dictionary<string, int>();
|
|
var headerRow = excel.Sheet.GetRow(0);
|
|
if (headerRow != null)
|
|
{
|
|
for (int i = 0; i <= headerRow.LastCellNum; i++)
|
|
{
|
|
var cell = headerRow.GetCell(i);
|
|
if (cell == null) continue;
|
|
string title = cell.ToString().Trim();
|
|
if (!string.IsNullOrEmpty(title) && !columnMap.ContainsKey(title))
|
|
{
|
|
columnMap[title] = i;
|
|
}
|
|
}
|
|
}
|
|
|
|
for (int r = beginRow; r <= excel.Sheet.LastRowNum; r++)
|
|
{
|
|
var row = excel.Sheet.GetRow(r);
|
|
if (row == null)
|
|
{
|
|
break;
|
|
}
|
|
|
|
// 以欄位名稱取得每欄的值
|
|
var cellValues = new Dictionary<string, string>();
|
|
foreach (var column in columns)
|
|
{
|
|
string cellValue = "";
|
|
if (columnMap.ContainsKey(column.Title))
|
|
{
|
|
var cell = row.GetCell(columnMap[column.Title]);
|
|
cellValue = cell == null ? "" : excel.CellValueToString(cell);
|
|
}
|
|
cellValues[column.Title] = cellValue;
|
|
}
|
|
|
|
// 若所有欄位皆為空則停止
|
|
bool allEmpty = true;
|
|
foreach (var v in cellValues.Values)
|
|
{
|
|
if (!string.IsNullOrEmpty(v)) { allEmpty = false; break; }
|
|
}
|
|
if (allEmpty)
|
|
{
|
|
break;
|
|
}
|
|
|
|
var item = new T();
|
|
foreach (var column in columns)
|
|
{
|
|
string cellValue = cellValues[column.Title];
|
|
|
|
// 假設 T 有對應的屬性名稱與欄位標題相同
|
|
var property = typeof(T).GetProperty(column.FieldName);
|
|
if (property != null)
|
|
{
|
|
object valueToSet = null;
|
|
if (column.TransformFunction != null)
|
|
{
|
|
valueToSet = column.TransformFunction(cellValue);
|
|
}
|
|
else
|
|
{
|
|
if (string.IsNullOrEmpty(cellValue))
|
|
{
|
|
if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
|
|
{
|
|
valueToSet = null;
|
|
}
|
|
else
|
|
{
|
|
// 對於非 Nullable 類型，如果為空則跳過設定
|
|
continue;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
var targetType = property.PropertyType;
|
|
if (targetType.IsGenericType && targetType.GetGenericTypeDefinition() == typeof(Nullable<>))
|
|
{
|
|
targetType = Nullable.GetUnderlyingType(targetType);
|
|
}
|
|
if (targetType == typeof(DateTime))
|
|
{
|
|
valueToSet = ParseExcelDate(cellValue);
|
|
}
|
|
else
|
|
{
|
|
valueToSet = Convert.ChangeType(cellValue, targetType);
|
|
}
|
|
}
|
|
}
|
|
property.SetValue(item, valueToSet, null);
|
|
}
|
|
}
|
|
result.Add(item);
|
|
}
|
|
|
|
return result;
|
|
}
|
|
|
|
private static DateTime ParseExcelDate(string value)
|
|
{
|
|
if (DateTime.TryParse(value, out DateTime dt))
|
|
{
|
|
return dt;
|
|
}
|
|
if (double.TryParse(value, out double d) && d >= MinOADate && d <= MaxOADate)
|
|
{
|
|
return DateTime.FromOADate(d);
|
|
}
|
|
throw new FormatException($"無法將「{value}」轉換為日期");
|
|
}
|
|
|
|
public virtual T SelectExisting<T>(T item) where T : new()
|
|
{
|
|
throw new NotImplementedException("請在子類別中實作此方法以選取現有資料");
|
|
}
|
|
|
|
public virtual void InsertOne<T>(T item) where T : new()
|
|
{
|
|
throw new NotImplementedException("請在子類別中實作此方法以新增資料");
|
|
}
|
|
|
|
public virtual void UpdateOne<T>(T newItem, T existingItem) where T : new()
|
|
{
|
|
throw new NotImplementedException("請在子類別中實作此方法以更新資料");
|
|
}
|
|
|
|
public virtual string ImportRowErrorMessage<T>(T item, string errorMessage) where T : new()
|
|
{
|
|
return $"資料 {item.ToString()} 匯入失敗，錯誤原因：{errorMessage}";
|
|
}
|
|
|
|
public bool Import<T>(string filePath,
|
|
out string message,
|
|
int beginRow = 1) where T : new()
|
|
{
|
|
var importErrors = new List<string>();
|
|
message = "";
|
|
var insertCount = 0;
|
|
var updateCount = 0;
|
|
|
|
var rows = ReadRows<T>(filePath, beginRow);
|
|
if (rows.Count == 0)
|
|
{
|
|
importErrors.Add("匯入檔案中沒有任何資料");
|
|
return false;
|
|
}
|
|
|
|
using (var ts = DAC.NewTransactionScope())
|
|
{
|
|
foreach (var row in rows)
|
|
{
|
|
try
|
|
{
|
|
var existing = SelectExisting(row);
|
|
if (existing == null)
|
|
{
|
|
InsertOne(row);
|
|
insertCount++;
|
|
}
|
|
else
|
|
{
|
|
UpdateOne(row, existing);
|
|
updateCount++;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
importErrors.Add(ImportRowErrorMessage(row, ex.Message));
|
|
}
|
|
}
|
|
}
|
|
|
|
if (importErrors.Count == 0)
|
|
{
|
|
message = $"資料匯入成功，共新增 {insertCount} 筆資料，修改 {updateCount} 筆資料。";
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
message = $"資料匯入完成，共新增 {insertCount} 筆資料，修改 {updateCount} 筆資料，但有以下錯誤:<br/>" + string.Join("<br/>", importErrors.ToArray());
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 真假值轉換函數，把代表真值的字串轉換成 1，否則轉換成 0
|
|
/// </summary>
|
|
protected TransformFunction BoolTransform = (input) =>
|
|
{
|
|
if (string.IsNullOrEmpty(input)) return 0;
|
|
var inputStr = input.ToUpper();
|
|
return trueValues.Contains(inputStr) ? 1 : 0;
|
|
};
|
|
}
|
|
|
|
// 新增委派定義
|
|
public delegate object TransformFunction(string input);
|
|
|
|
public class ImportColumn
|
|
{
|
|
public string Title { get; set; }
|
|
public int Width { get; set; }
|
|
public string FieldName { get; set; }
|
|
public bool Required { get; set; }
|
|
public TransformFunction TransformFunction { get; set; }
|
|
public ImportColumn(string title, int width, bool required = false, string fieldName = null, TransformFunction transformFunction = null)
|
|
{
|
|
Title = title;
|
|
Width = width;
|
|
Required = required;
|
|
FieldName = fieldName ?? title;
|
|
TransformFunction = transformFunction;
|
|
}
|
|
}
|
|
} |