void wizard_ValidateRow(object sender, SmartSchool.API.PlugIn.Import.ValidateRowEventArgs e)
{
#region 驗各欄位填寫格式
int t;
foreach (string field in e.SelectFields)
{
string value = e.Data[field];
switch (field)
{
default:
break;
case "學年度":
if (value == "" || !int.TryParse(value, out t))
{
e.ErrorFields.Add(field, "此欄為必填欄位，必須填入整數。");
}
break;
case "學期":
if (value == "" || !int.TryParse(value, out t))
{
e.ErrorFields.Add(field, "此欄為必填欄位，必須填入整數。");
}
else if (t != 1 && t != 2)
{
e.ErrorFields.Add(field, "必須填入1或2");
}
break;
case "年級":
if (value == "" || !int.TryParse(value, out t))
{
e.ErrorFields.Add(field, "此欄為必填欄位，必須填入整數。");
}
break;
}
}
#endregion
string ValidateKey = e.Data.ID + "-" + e.Data["學年度"] + "-" + e.Data["學期"];
if (ValidateKeys.Contains(ValidateKey))
{
e.ErrorMessage = "學生編號、學年及學期的組合不能重覆!";
}
else
{
ValidateKeys.Add(ValidateKey);
}
}
private void _BKWValidate_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker bkw = (BackgroundWorker)sender;
List<string> selectedFields = (List<string>)((object[])e.Argument)[0];
Dictionary<string, int> importFields = (Dictionary<string, int>)((object[])e.Argument)[1];
Workbook wb = (Workbook)((object[])e.Argument)[2];
wb.Worksheets[0].Comments.Clear();
wb.Worksheets[0].Hyperlinks.Clear();
int errorSheetIndex = wb.Worksheets.Add();
{
int errc = 0;
#region 命名
for (; ; errc++)
{
bool pass = true;
string n = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
foreach (Aspose.Cells.Worksheet var in wb.Worksheets)
{
if (n == var.Name)
{
pass = false;
break;
}
}
if (pass) break;
}
#endregion
wb.Worksheets[errorSheetIndex].Name = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
}
string errorSheetName = wb.Worksheets[errorSheetIndex].Name;
Worksheet errorSheet = wb.Worksheets[errorSheetIndex];
errorSheet.Cells[0, 0].PutValue("行號");
errorSheet.Cells[0, 1].PutValue("種類");
errorSheet.Cells[0, 2].PutValue("說明");
int errorSheetRowIndex = 1;
Style errorStyle = wb.Styles[wb.Styles.Add()];
Style warningStyle = wb.Styles[wb.Styles.Add()];
Style passStyle = wb.Styles[wb.Styles.Add()];
Style errorStyle2 = wb.Styles[wb.Styles.Add()];
Style warningStyle2 = wb.Styles[wb.Styles.Add()];
//errorStyle.Pattern = BackgroundType.Solid;
//errorStyle.ForegroundColor = Color.Red;
errorStyle.Font.Color = Color.Red;
errorStyle.Font.Underline = FontUnderlineType.Single;
//warningStyle.Pattern = BackgroundType.Solid;
//warningStyle.ForegroundColor = Color.Yellow;
warningStyle.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
warningStyle.Font.Underline = FontUnderlineType.Single;
//passStyle.Pattern = BackgroundType.Solid;
//passStyle.ForegroundColor = Color.Green;
passStyle.Font.Color = Color.Green;
warningStyle2.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
errorStyle2.Font.Color = Color.Red;
int errorCount = 0;
int warningCount = 0;
Dictionary<RowData, int> rowDataIndex = new Dictionary<RowData, int>();
Dictionary<int, string> fieldIndex = new Dictionary<int, string>();
foreach (string field in selectedFields)
{
fieldIndex.Add(importFields[field], field);
}
double progress = 0.0;
// 讀取驗證資料工作表使用
Dictionary<string, int> checkSameDataDic = new Dictionary<string, int>();
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (!checkSameDataDic.ContainsKey(chkStr))
checkSameDataDic.Add(chkStr, 0);
}
#region 產生RowData資料
if (importFields.ContainsKey("姓名") && importFields.ContainsKey("暱稱"))
{
#region 用「姓名+暱稱」驗證資料
List<K12.Data.TeacherRecord> stus = K12.Data.Teacher.SelectAll();
K12.Data.TeacherRecord stu = new K12.Data.TeacherRecord();
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
// , "學號", "班級", "座號", "科別", "姓名"
string name = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["姓名"]].StringValue);
string nickName = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["暱稱"]].StringValue);
if ((name+nickName) != "")
{
string rowError = "";
// 檢查重複資料
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (checkSameDataDic.ContainsKey(chkStr))
{
checkSameDataDic[chkStr]++;
if (checkSameDataDic[chkStr] > 1)
rowError = "有重複資料!";
}
#region 驗明正身
if (stus != null)
{
if (stus.Where(x => (x.Name.Trim() == name.Trim() && x.Nickname.Trim() == nickName.Trim())).Count() == 0)
rowError = "教師不存在於系統中，請先新增教師資料!!";
}
else
rowError = "教師不存在於系統中，請先新增教師資料!!";
#endregion
if (rowError == "")
{
stu = stus.Where(x => (x.Name.Trim() == name.Trim() && x.Nickname.Trim() == nickName.Trim())).ElementAt(0);
if (!(stu.Status == _StudStatus))
{
#region 警告非在職教師
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
//errorSheet.Cells[errorSheetRowIndex, 2].PutValue("學生不是在校生。");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("教師不在所選擇狀態內。");
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
warningCount++;
#endregion
}
RowData rowdata = new RowData();
rowdata.ID = stu.ID;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else
{
bool isNullRow = true;
for (byte c = 0; c < wb.Worksheets[0].Cells.MaxDataColumn; c++)
{
if (GetTrimText("" + wb.Worksheets[0].Cells[i, c].StringValue) != "")
isNullRow = false;
}
if (!isNullRow)
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("驗證欄位(姓名+暱稱)不得空白");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
#endregion
#region 驗證資料
List<string> list = new List<string>();
foreach (RowData row in rowDataIndex.Keys)
{
if (!list.Contains(row.ID))
list.Add(row.ID);
}
//_Process.StartValidate(list);
if (ValidateStart != null)
{
SmartSchool.API.PlugIn.Import.ValidateStartEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateStartEventArgs();
args.List = list.ToArray();
ValidateStart(this, args);
}
double totleCount = (double)rowDataIndex.Count;
double count = 0.0;
foreach (RowData row in rowDataIndex.Keys)
{
#region 驗證
string rowError = "";
Dictionary<string, string> errorFields, warningFields;
//RowDataValidatedEventArgs args = _Process.ValidateRow(row, selectedFields);
SmartSchool.API.PlugIn.Import.ValidateRowEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateRowEventArgs();
args.Data = row;
args.SelectFields.AddRange(selectedFields);
if (ValidateRow != null)
{
ValidateRow(this, args);
}
errorFields = args.ErrorFields;
warningFields = args.WarningFields;
rowError += args.ErrorMessage;
if (rowError == "" && errorFields.Count == 0 && warningFields.Count == 0)
{
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = passStyle;
}
else
{
string message = "";
bool hasError = false;
if (rowError != "" || errorFields.Count != 0)
{
errorCount++;
hasError = true;
message = rowError;
message += (message == "" ? "" : "\n") + "";
foreach (string key in errorFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + errorFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue(hasError ? "錯誤" : "警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
message = "";
if (warningFields.Count != 0)
{
if (!hasError)
warningCount++;
message += (message == "" ? "" : "\n") + "";
foreach (string key in warningFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + warningFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
if (!hasError)
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = hasError ? errorStyle : warningStyle;
}
#endregion
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
count++;
bkw.ReportProgress((int)(progress + count * (100.0 - progress) / totleCount), new int[] { errorCount, warningCount });
}
//_Process.FinishValidate();
if (ValidateComplete != null)
ValidateComplete(this, new EventArgs());
#endregion
List<RowData> rows = new List<RowData>();
rows.AddRange(rowDataIndex.Keys);
bkw.ReportProgress(100, new int[] { errorCount, warningCount });
errorSheet.AutoFitColumn(0);
errorSheet.AutoFitColumn(1, 1, 500);
errorSheet.AutoFitColumn(2, 1, 500);
e.Result = new object[] { wb, errorCount == 0, rows, selectedFields };
}
private void _BKWValidate_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker bkw = (BackgroundWorker)sender;
List<string> selectedFields = (List<string>)((object[])e.Argument)[0];
Dictionary<string, int> importFields = (Dictionary<string, int>)((object[])e.Argument)[1];
Workbook wb = (Workbook)((object[])e.Argument)[2];
wb.Worksheets[0].Comments.Clear();
wb.Worksheets[0].Hyperlinks.Clear();
int errorSheetIndex = wb.Worksheets.Add();
{
int errc = 0;
#region 命名
for (; ; errc++)
{
bool pass = true;
string n = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
foreach (Aspose.Cells.Worksheet var in wb.Worksheets)
{
if (n == var.Name)
{
pass = false;
break;
}
}
if (pass) break;
}
#endregion
wb.Worksheets[errorSheetIndex].Name = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
}
string errorSheetName = wb.Worksheets[errorSheetIndex].Name;
Worksheet errorSheet = wb.Worksheets[errorSheetIndex];
errorSheet.Cells[0, 0].PutValue("行號");
errorSheet.Cells[0, 1].PutValue("種類");
errorSheet.Cells[0, 2].PutValue("說明");
int errorSheetRowIndex = 1;
Style errorStyle = wb.Styles[wb.Styles.Add()];
Style warningStyle = wb.Styles[wb.Styles.Add()];
Style passStyle = wb.Styles[wb.Styles.Add()];
Style errorStyle2 = wb.Styles[wb.Styles.Add()];
Style warningStyle2 = wb.Styles[wb.Styles.Add()];
//errorStyle.Pattern = BackgroundType.Solid;
//errorStyle.ForegroundColor = Color.Red;
errorStyle.Font.Color = Color.Red;
errorStyle.Font.Underline = FontUnderlineType.Single;
//warningStyle.Pattern = BackgroundType.Solid;
//warningStyle.ForegroundColor = Color.Yellow;
warningStyle.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
warningStyle.Font.Underline = FontUnderlineType.Single;
//passStyle.Pattern = BackgroundType.Solid;
//passStyle.ForegroundColor = Color.Green;
passStyle.Font.Color = Color.Green;
warningStyle2.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
errorStyle2.Font.Color = Color.Red;
int errorCount = 0;
int warningCount = 0;
Dictionary<RowData, int> rowDataIndex = new Dictionary<RowData, int>();
Dictionary<int, string> fieldIndex = new Dictionary<int, string>();
foreach (string field in selectedFields)
{
fieldIndex.Add(importFields[field], field);
}
double progress = 0.0;
// 讀取驗證資料工作表使用
Dictionary<string, int> checkSameDataDic = new Dictionary<string, int>();
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (!checkSameDataDic.ContainsKey(chkStr))
checkSameDataDic.Add(chkStr, 0);
}
#region 產生RowData資料
if (importFields.ContainsKey("學生系統編號"))
{
#region 用編號驗證資料
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
// , "學號", "班級", "座號", "科別", "姓名"
string id = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["學生系統編號"]].StringValue);
if (id != "")
{
string rowError = "";
// 檢查重複資料
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (checkSameDataDic.ContainsKey(chkStr))
{
checkSameDataDic[chkStr]++;
if (checkSameDataDic[chkStr] > 1)
rowError =chkStr+"，有重複資料!";
}
#region 驗明正身
StudentRecord stu = Student.SelectByID(id);
if (stu != null)
{
if (importFields.ContainsKey("學號") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["學號"]].StringValue) != stu.StudentNumber)
{
//rowError = "學號與系統內學生資料不同!!";
rowError += (rowError == "" ? "" : "、\n") + "系統內學生學號為\"" + stu.StudentNumber + "\"";
}
if (importFields.ContainsKey("姓名") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["姓名"]].StringValue) != stu.Name)
{
//rowError = "姓名與系統內學生資料不同!!";
rowError += (rowError == "" ? "" : "、\n") + "系統內學生姓名為\"" + stu.Name + "\"";
}
}
else
{
rowError += "學生不存在!!";
}
#endregion
if (rowError == "")
{
if (!(stu.Status == _StudStatus))
{
#region 警告非在校生
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
//errorSheet.Cells[errorSheetRowIndex, 2].PutValue("學生不是在校生。");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("學生不在所選擇狀態內。");
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
warningCount++;
#endregion
}
RowData rowdata = new RowData();
rowdata.ID = id;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else
{
bool isNullRow = true;
for (byte c = 0; c < wb.Worksheets[0].Cells.MaxDataColumn; c++)
{
if (GetTrimText("" + wb.Worksheets[0].Cells[i, c].StringValue) != "")
isNullRow = false;
}
if (!isNullRow)
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("驗證欄位(學生系統編號)不得空白");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
else if (importFields.ContainsKey("學號"))
{
#region 用學號驗證資料
Dictionary<string, List<StudentRecord>> studentNumberStudents = new Dictionary<string, List<StudentRecord>>();
#region 整理學號對應學生清單(如索引欄不試系統編號時用)
foreach (StudentRecord stu in Student.SelectAll())
{
if (stu.Status == _StudStatus)
{
if (!studentNumberStudents.ContainsKey(stu.StudentNumber))
studentNumberStudents.Add(stu.StudentNumber, new List<StudentRecord>(new StudentRecord[] { stu }));
else
studentNumberStudents[stu.StudentNumber].Add(stu);
}
}
#endregion
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string num = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["學號"]].StringValue);
//wb.Worksheets[0].Cells.
if (num != "")
{
string rowError = "";
// 檢查重複資料
string chkStr = ImportUtil.GetChekcDataStr(i, wb.Worksheets[0], importFields);
if (checkSameDataDic.ContainsKey(chkStr))
{
checkSameDataDic[chkStr]++;
if (checkSameDataDic[chkStr] > 1)
rowError = chkStr+"，有重複資料!";
}
#region 驗明正身
StudentRecord stu = null;
if (studentNumberStudents.ContainsKey(num))
{
if (studentNumberStudents[num].Count > 1)
{
#region 必需要其他欄位做索引
bool err = true;
foreach (string validateKey in new string[] { "班級", "科別", "座號", "姓名" })
{
if (importFields.ContainsKey(validateKey))
{
err = false;
foreach (StudentRecord var in studentNumberStudents[num])
{
bool pass = true;
//if (importFields.ContainsKey("班級") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級"]].StringValue) != (var.Class != null ? var.Class.Name : ""))
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生班級為\"" + (var.Class != null ? var.Class.Name : "") + "\"";
// pass &= false;
//}
//if (importFields.ContainsKey("座號") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["座號"]].StringValue) != "" + var.SeatNo)
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生座號為\"" + var.SeatNo + "\"";
// pass &= false;
//}
if (importFields.ContainsKey("姓名") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["姓名"]].StringValue) != var.Name)
{
if (studentNumberStudents[num].Count == 1)
rowError += (rowError == "" ? "" : "、\n") + "系統內學生姓名為\"" + var.Name + "\"";
pass &= false;
}
if (pass)
{
stu = var;
break;
}
}
}
break;
}
if (err)
{
rowError = "系統內發現多名相同學號學生且皆為在校學生，\n需要其他學生欄位進行識別。";
}
#endregion
}
if (studentNumberStudents[num].Count == 1)
{
StudentRecord var = studentNumberStudents[num][0];
bool pass = true;
//if (importFields.ContainsKey("班級") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級"]].StringValue) != (var.Class != null ? var.Class.Name : ""))
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生班級為\"" + (var.Class != null ? var.Class.Name : "") + "\"";
// pass &= false;
//}
//if (importFields.ContainsKey("座號") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["座號"]].StringValue) != "" + var.SeatNo)
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生座號為\"" + var.SeatNo + "\"";
// pass &= false;
//}
if (importFields.ContainsKey("姓名") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["姓名"]].StringValue) != var.Name)
{
if (studentNumberStudents[num].Count == 1)
rowError += (rowError == "" ? "" : "、\n") + "系統內學生姓名為\"" + var.Name + "\"";
pass &= false;
}
if (pass)
{
stu = var;
}
}
}
else
{
rowError += "學生不存在所選擇狀態內!!";
}
if (rowError == "" && stu == null)
rowError = "學生資料有誤!!";
#endregion
if (rowError == "")
{
RowData rowdata = new RowData();
rowdata.ID = stu.ID;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else
{
bool isNullRow = true;
for (byte c = 0; c < wb.Worksheets[0].Cells.MaxDataColumn; c++)
{
if (GetTrimText("" + wb.Worksheets[0].Cells[i, c].StringValue) != "")
isNullRow = false;
}
if (!isNullRow)
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("驗證欄位(學號)不得空白");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
#endregion
#region 驗證資料
List<string> list = new List<string>();
foreach (RowData row in rowDataIndex.Keys)
{
if (!list.Contains(row.ID))
list.Add(row.ID);
}
//_Process.StartValidate(list);
if (ValidateStart != null)
{
SmartSchool.API.PlugIn.Import.ValidateStartEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateStartEventArgs();
args.List = list.ToArray();
ValidateStart(this, args);
}
double totleCount = (double)rowDataIndex.Count;
double count = 0.0;
foreach (RowData row in rowDataIndex.Keys)
{
#region 驗證
string rowError = "";
Dictionary<string, string> errorFields, warningFields;
//RowDataValidatedEventArgs args = _Process.ValidateRow(row, selectedFields);
SmartSchool.API.PlugIn.Import.ValidateRowEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateRowEventArgs();
args.Data = row;
args.SelectFields.AddRange(selectedFields);
if (ValidateRow != null)
{
ValidateRow(this, args);
}
errorFields = args.ErrorFields;
warningFields = args.WarningFields;
rowError += args.ErrorMessage;
if (rowError == "" && errorFields.Count == 0 && warningFields.Count == 0)
{
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = passStyle;
}
else
{
string message = "";
bool hasError = false;
if (rowError != "" || errorFields.Count != 0)
{
errorCount++;
hasError = true;
message = rowError;
message += (message == "" ? "" : "\n") + "";
foreach (string key in errorFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + errorFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue(hasError ? "錯誤" : "警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
message = "";
if (warningFields.Count != 0)
{
if (!hasError)
warningCount++;
message += (message == "" ? "" : "\n") + "";
foreach (string key in warningFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + warningFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
if (!hasError)
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = hasError ? errorStyle : warningStyle;
}
#endregion
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
count++;
bkw.ReportProgress((int)(progress + count * (100.0 - progress) / totleCount), new int[] { errorCount, warningCount });
}
//_Process.FinishValidate();
if (ValidateComplete != null)
ValidateComplete(this, new EventArgs());
#endregion
List<RowData> rows = new List<RowData>();
rows.AddRange(rowDataIndex.Keys);
bkw.ReportProgress(100, new int[] { errorCount, warningCount });
errorSheet.AutoFitColumn(0);
errorSheet.AutoFitColumn(1, 1, 500);
errorSheet.AutoFitColumn(2, 1, 500);
e.Result = new object[] { wb, errorCount == 0, rows, selectedFields };
}
private void _BKWValidate_DoWork(object sender, DoWorkEventArgs e)
{
BackgroundWorker bkw = (BackgroundWorker)sender;
List<string> selectedFields = (List<string>)((object[])e.Argument)[0];
Dictionary<string, int> importFields = (Dictionary<string, int>)((object[])e.Argument)[1];
Workbook wb = (Workbook)((object[])e.Argument)[2];
wb.Worksheets[0].Comments.Clear();
wb.Worksheets[0].Hyperlinks.Clear();
int errorSheetIndex = wb.Worksheets.Add();
{
int errc = 0;
#region 命名
for (; ; errc++)
{
bool pass = true;
string n = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
foreach (Aspose.Cells.Worksheet var in wb.Worksheets)
{
if (n == var.Name)
{
pass = false;
break;
}
}
if (pass) break;
}
#endregion
wb.Worksheets[errorSheetIndex].Name = "錯誤&警告說明" + (errc == 0 ? "" : "(" + errc + ")");
}
string errorSheetName = wb.Worksheets[errorSheetIndex].Name;
Worksheet errorSheet = wb.Worksheets[errorSheetIndex];
errorSheet.Cells[0, 0].PutValue("行號");
errorSheet.Cells[0, 1].PutValue("種類");
errorSheet.Cells[0, 2].PutValue("說明");
int errorSheetRowIndex = 1;
Style errorStyle = wb.Styles[wb.Styles.Add()];
Style warningStyle = wb.Styles[wb.Styles.Add()];
Style passStyle = wb.Styles[wb.Styles.Add()];
Style errorStyle2 = wb.Styles[wb.Styles.Add()];
Style warningStyle2 = wb.Styles[wb.Styles.Add()];
//errorStyle.Pattern = BackgroundType.Solid;
//errorStyle.ForegroundColor = Color.Red;
errorStyle.Font.Color = Color.Red;
errorStyle.Font.Underline = FontUnderlineType.Single;
//warningStyle.Pattern = BackgroundType.Solid;
//warningStyle.ForegroundColor = Color.Yellow;
warningStyle.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
warningStyle.Font.Underline = FontUnderlineType.Single;
//passStyle.Pattern = BackgroundType.Solid;
//passStyle.ForegroundColor = Color.Green;
passStyle.Font.Color = Color.Green;
warningStyle2.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
errorStyle2.Font.Color = Color.Red;
int errorCount = 0;
int warningCount = 0;
Dictionary<RowData, int> rowDataIndex = new Dictionary<RowData, int>();
Dictionary<int, string> fieldIndex = new Dictionary<int, string>();
foreach (string field in selectedFields)
{
fieldIndex.Add(importFields[field], field);
}
double progress = 0.0;
#region 產生RowData資料
if (importFields.ContainsKey("班級系統編號"))
{
#region 用編號驗證資料
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string id = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級系統編號"]].StringValue);
if (id != "")
{
string rowError = "";
#region 驗明正身
//取得班級
ClassRecord stu = Class.SelectByID(id);
if (stu != null)
{
if (importFields.ContainsKey("班級名稱") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級名稱"]].StringValue) != stu.Name)
{
//rowError = "[班級名稱]與系統內[名稱]資料不同!!";
rowError += (rowError == "" ? "" : "、\n") + "系統內班級名稱為\"" + stu.Name + "\"";
}
}
else
{
rowError = "班級不存在!!";
}
#endregion
if (rowError == "")
{
RowData rowdata = new RowData();
rowdata.ID = id;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else //如果ID是空的
{
bool isNullRow = true;
for (byte c = 0; c < wb.Worksheets[0].Cells.MaxDataColumn; c++)
{
if (GetTrimText("" + wb.Worksheets[0].Cells[i, c].StringValue) != "")
isNullRow = false;
}
if (!isNullRow)
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("驗證欄位(班級系統編號)不得空白");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
else if (importFields.ContainsKey("班級名稱"))
{
#region 用教師姓名+暱稱驗證資料
Dictionary<string, List<ClassRecord>> studentNumberStudents = new Dictionary<string, List<ClassRecord>>();
#region 整理學號對應學生清單(如索引欄不試系統編號時用)
foreach (ClassRecord teach in Class.SelectAll())
{
string Number = teach.Name;
if (!studentNumberStudents.ContainsKey(Number))
studentNumberStudents.Add(Number, new List<ClassRecord>(new ClassRecord[] { teach }));
else
studentNumberStudents[Number].Add(teach);
}
#endregion
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
string num = GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級名稱"]].StringValue);
//wb.Worksheets[0].Cells.
if (num != "")
{
string rowError = "";
#region 驗明正身
ClassRecord stu = null;
if (studentNumberStudents.ContainsKey(num))
{
if (studentNumberStudents[num].Count > 1)
{
}
if (studentNumberStudents[num].Count == 1)
{
ClassRecord var = studentNumberStudents[num][0];
bool pass = true;
//if (importFields.ContainsKey("班級") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級"]].StringValue) != (var.Class != null ? var.Class.Name : ""))
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生班級為\"" + (var.Class != null ? var.Class.Name : "") + "\"";
// pass &= false;
//}
//if (importFields.ContainsKey("座號") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["座號"]].StringValue) != "" + var.SeatNo)
//{
// if (studentNumberStudents[num].Count == 1)
// rowError += (rowError == "" ? "" : "、\n") + "系統內學生座號為\"" + var.SeatNo + "\"";
// pass &= false;
//}
if (importFields.ContainsKey("班級名稱") && GetTrimText("" + wb.Worksheets[0].Cells[i, importFields["班級名稱"]].StringValue) != var.Name)
{
if (studentNumberStudents[num].Count == 1)
rowError += (rowError == "" ? "" : "、\n") + "系統內班級名稱為\"" + var.Name + "\"";
pass &= false;
}
if (pass)
{
stu = var;
}
}
}
else
{
rowError = "班級不存在!!";
}
if (rowError == "" && stu == null)
rowError = "班級資料有誤!!";
#endregion
if (rowError == "")
{
RowData rowdata = new RowData();
rowdata.ID = stu.ID;
foreach (int index in fieldIndex.Keys)
{
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldIndex[index], wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
rowdata.Add(fieldIndex[index], GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
rowDataIndex.Add(rowdata, i);
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(rowError);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
}
else
{
errorCount++;
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(i + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("班級名稱不得空白!");
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[i, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(i, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
wb.Worksheets[0].Cells[i, 0].Style = errorStyle;
}
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
progress = ((double)i) * 100.0 / 100000;
bkw.ReportProgress((int)progress, new int[] { errorCount, warningCount });
}
#endregion
}
#endregion
#region 驗證是否有班級,重覆標記相同類別
Dictionary<string, List<string>> Dic2 = new Dictionary<string, List<string>>();
foreach (RowData row in rowDataIndex.Keys)
{
string id = row.ID;
foreach (string each in row.Values)
{
id += each;
}
if (!Dic2.ContainsKey(id))
{
Dic2.Add(id, new List<string>());
}
Dic2[id].Add(row.ID);
}
List<string> list2 = new List<string>();
foreach (string each in Dic2.Keys)
{
if (Dic2[each].Count > 1) //大於1,則此Row資料重覆
{
list2.Add(Dic2[each][0]);
}
}
//錯誤資料ID
foreach (string each in list2)
{
errorSheet.Cells[errorSheetRowIndex, 0].PutValue("(無法標記行號)");
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue("班級不可重覆設定相同類別!!系統編號:" + each);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[errorSheetRowIndex, 0].Name);
wb.Worksheets[0].Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
errorCount++;
}
#endregion
#region 驗證資料
List<string> list = new List<string>();
foreach (RowData row in rowDataIndex.Keys)
{
if (!list.Contains(row.ID))
list.Add(row.ID);
}
//_Process.StartValidate(list);
if (ValidateStart != null)
{
SmartSchool.API.PlugIn.Import.ValidateStartEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateStartEventArgs();
args.List = list.ToArray();
ValidateStart(this, args);
}
double totleCount = (double)rowDataIndex.Count;
double count = 0.0;
foreach (RowData row in rowDataIndex.Keys)
{
#region 驗證
string rowError = "";
Dictionary<string, string> errorFields, warningFields;
//RowDataValidatedEventArgs args = _Process.ValidateRow(row, selectedFields);
SmartSchool.API.PlugIn.Import.ValidateRowEventArgs args = new SmartSchool.API.PlugIn.Import.ValidateRowEventArgs();
args.Data = row;
args.SelectFields.AddRange(selectedFields);
if (ValidateRow != null)
{
ValidateRow(this, args);
}
errorFields = args.ErrorFields;
warningFields = args.WarningFields;
rowError += args.ErrorMessage;
if (rowError == "" && errorFields.Count == 0 && warningFields.Count == 0)
{
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = passStyle;
}
else
{
string message = "";
bool hasError = false;
if (rowError != "" || errorFields.Count != 0)
{
errorCount++;
hasError = true;
message = rowError;
message += (message == "" ? "" : "\n") + "";
foreach (string key in errorFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + errorFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue(hasError ? "錯誤" : "警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = errorStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = errorStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = errorStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
message = "";
if (warningFields.Count != 0)
{
if (!hasError)
warningCount++;
message += (message == "" ? "" : "\n") + "";
foreach (string key in warningFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + warningFields[key];
}
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(rowDataIndex[row] + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(message);
errorSheet.Cells[errorSheetRowIndex, 0].Style = warningStyle;
errorSheet.Cells[errorSheetRowIndex, 1].Style = warningStyle2;
errorSheet.Cells[errorSheetRowIndex, 2].Style = warningStyle2;
errorSheet.Hyperlinks.Add(errorSheetRowIndex, 0, 1, 1, "'" + wb.Worksheets[0].Name + "'!" + wb.Worksheets[0].Cells[rowDataIndex[row], 0].Name);
if (!hasError)
wb.Worksheets[0].Hyperlinks.Add(rowDataIndex[row], 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
wb.Worksheets[0].Cells[rowDataIndex[row], 0].Style = hasError ? errorStyle : warningStyle;
}
#endregion
if (bkw.CancellationPending)
{
e.Cancel = true;
_ErrorWB = wb;
return;
}
count++;
bkw.ReportProgress((int)(progress + count * (100.0 - progress) / totleCount), new int[] { errorCount, warningCount });
}
//_Process.FinishValidate();
if (ValidateComplete != null)
ValidateComplete(this, new EventArgs());
#endregion
List<RowData> rows = new List<RowData>();
rows.AddRange(rowDataIndex.Keys);
bkw.ReportProgress(100, new int[] { errorCount, warningCount });
errorSheet.AutoFitColumn(0);
errorSheet.AutoFitColumn(1, 1, 500);
errorSheet.AutoFitColumn(2, 1, 500);
e.Result = new object[] { wb, errorCount == 0, rows, selectedFields };
}
void wizard_ValidateRow(object sender, SmartSchool.API.PlugIn.Import.ValidateRowEventArgs e)
{
#region 驗各欄位填寫格式
DateTime dt;
foreach (string field in e.SelectFields)
{
string value = e.Data[field];
switch (field)
{
default:
break;
case "學號":
if (value.Replace(" ", "") == "")
{
e.ErrorFields.Add(field, "此欄為必填欄位。");
}
break;
case "生日":
if (value != "")
{
DateTime dt1;
if (DateTime.TryParse(value, out dt1) == false)
{
e.ErrorFields.Add(field, "資料必須為日期格式。");
}
}
break;
case "性別":
if (value != "")
{
if (value == "男" || value == "女")
{
}
else
{
e.ErrorFields.Add(field, "資料必須為男或女。");
}
}
break;
case "年級":
if (value != "")
{
int i1;
if (int.TryParse(value, out i1) == false)
{
e.ErrorFields.Add(field, "資料必須為整數。");
}
}
break;
case "座號":
if (value != "")
{
int i2;
if (int.TryParse(value, out i2) == false)
{
e.ErrorFields.Add(field, "資料必須為整數。");
}
}
break;
case "入學日期":
if (value != "")
{
DateTime dt2;
if (DateTime.TryParse(value, out dt2) == false)
{
e.ErrorFields.Add(field, "資料必須為日期格式。");
}
}
break;
case "畢業日期":
if (value != "")
{
DateTime dt3;
if (DateTime.TryParse(value, out dt3) == false)
{
e.ErrorFields.Add(field, "資料必須為日期格式。");
}
}
break;
case "狀態":
if (value != "")
{
if (value == "一般" || value == "畢業或離校" || value == "休學" || value == "輟學" || value == "刪除")
{
}
else
{
e.ErrorFields.Add(field, "資料必須為一般、畢業或離校、休學、輟學、刪除。");
}
}
break;
case "戶籍:郵遞區號":
if (value != "")
{
int i3;
if (int.TryParse(value, out i3) == false)
{
e.ErrorFields.Add(field, "資料必須為整數。");
}
}
break;
case "聯絡:郵遞區號":
if (value != "")
{
int i4;
if (int.TryParse(value, out i4) == false)
{
e.ErrorFields.Add(field, "資料必須為整數。");
}
}
break;
}
}
#endregion
#region 驗證主鍵
string Key = e.Data.ID;
string errorMessage = string.Empty;
if (_Keys.Contains(Key))
{
errorMessage = "";
}
else
{
_Keys.Add(Key);
}
e.ErrorMessage = errorMessage;
#endregion
}
void wizard_ValidateRow(object sender, SmartSchool.API.PlugIn.Import.ValidateRowEventArgs e)
{
#region 驗各欄位填寫格式
int t;
DateTime dt;
foreach (string field in e.SelectFields)
{
string value = e.Data[field];
switch (field)
{
default:
break;
case "學號":
if (value == "")
{
e.ErrorFields.Add(field, "此欄為必填欄位。");
}
break;
case "英文別名":
break;
case "居留證號":
break;
case "入學日期":
if (value != "")
{
if (!DateTime.TryParse(value, out dt))
{
e.ErrorFields.Add(field, "資料必須為日期格式。");
}
}
break;
case "畢業日期":
if (value != "")
{
if (!DateTime.TryParse(value, out dt))
{
e.ErrorFields.Add(field, "資料必須為日期格式。");
}
}
break;
}
}
#endregion
#region 驗證主鍵
string Key = e.Data.ID;
string errorMessage = string.Empty;
if (_Keys.Contains(Key))
{
errorMessage = "學生編號、學年度、學期、幹部類別及幹部名稱的組合不能重覆!";
}
else
{
_Keys.Add(Key);
}
e.ErrorMessage = errorMessage;
#endregion
}
