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; // 讀取驗證資料工作表使用 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; #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 }; }