public void AddWarning(RowData rowData, string message)
{
lock (_WarningRows)
{
if (_WarningRows.ContainsKey(rowData))
{
_WarningRows[rowData] += "、" + message;
}
else
{
_WarningRows.Add(rowData, message);
}
}
}
public void AddError(RowData rowData, string message)
{
lock (_ErrorRows)
{
if (_ErrorRows.ContainsKey(rowData))
{
_ErrorRows[rowData] += "、" + message;
}
else
{
_ErrorRows.Add(rowData, message);
}
}
}
private void FillReport(Workbook wb, Dictionary <RowData, int> rowDataIndex)
{
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 errorStyle2 = wb.Styles[wb.Styles.Add()];
Style warningStyle2 = wb.Styles[wb.Styles.Add()];
errorStyle.Font.Color = Color.Red;
errorStyle.Font.Underline = FontUnderlineType.Single;
warningStyle.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
warningStyle.Font.Underline = FontUnderlineType.Single;
warningStyle2.Font.Color = wb.GetMatchingColor(Color.Goldenrod);
errorStyle2.Font.Color = Color.Red;
#region 填入驗證結果
SortedList <int, RowData> markedRow = new SortedList <int, RowData>();
foreach (var row in _ErrorRows.Keys)
{
if (rowDataIndex.ContainsKey(row))
{
markedRow.Add(rowDataIndex[row], row);
}
}
foreach (var row in _WarningRows.Keys)
{
if (rowDataIndex.ContainsKey(row) && !markedRow.ContainsKey(rowDataIndex[row]))
{
markedRow.Add(rowDataIndex[row], row);
}
}
foreach (var index in markedRow.Keys)
{
RowData row = markedRow[index];
if (_ErrorRows.ContainsKey(row))
{
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(index + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("錯誤");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(_ErrorRows[row]);
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[index, 0].Name);
wb.Worksheets[0].Cells[index, 0].Style = errorStyle;
wb.Worksheets[0].Hyperlinks.Add(index, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
if (_WarningRows.ContainsKey(row))
{
errorSheet.Cells[errorSheetRowIndex, 0].PutValue(index + 1);
errorSheet.Cells[errorSheetRowIndex, 1].PutValue("警告");
errorSheet.Cells[errorSheetRowIndex, 2].PutValue(_WarningRows[row]);
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[index, 0].Name);
if (!_ErrorRows.ContainsKey(row))
{
wb.Worksheets[0].Cells[index, 0].Style = errorStyle;
wb.Worksheets[0].Hyperlinks.Add(index, 0, 1, 1, "'" + errorSheetName + "'!" + errorSheet.Cells[errorSheetRowIndex, 0].Name);
}
errorSheet.AutoFitRow(errorSheetRowIndex);
errorSheetRowIndex++;
}
}
#endregion
errorSheet.AutoFitColumn(0);
errorSheet.AutoFitColumn(1, 1, 500);
errorSheet.AutoFitColumn(2, 1, 500);
}
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];
Style passStyle = wb.Styles[wb.Styles.Add()];
passStyle.Font.Color = Color.Green;
Dictionary <RowData, int> rowDataIndex = new Dictionary <RowData, int>();
double progress = 0.0;
#region 產生RowData資料
for (int i = 1; i <= wb.Worksheets[0].Cells.MaxDataRow; i++)
{
RowData rowdata = new RowData();
foreach (string fieldName in importFields.Keys)
{
int index = importFields[fieldName];
if (wb.Worksheets[0].Cells[i, index].Type == CellValueType.IsDateTime)
{
rowdata.Add(fieldName, wb.Worksheets[0].Cells[i, index].DateTimeValue.ToString());
}
else
{
rowdata.Add(fieldName, GetTrimText("" + wb.Worksheets[0].Cells[i, index].StringValue));
}
}
wb.Worksheets[0].Cells[i, 0].Style = passStyle;
rowDataIndex.Add(rowdata, i);
}
if (RowsLoad != null)
{
var args = new RowDataLoadEventArgs();
args.RowDatas.AddRange(rowDataIndex.Keys);
RowsLoad(this, args);
}
#endregion
#region 識別RowData資料
foreach (RowData row in rowDataIndex.Keys)
{
IdentifyRowEventArgs args = new IdentifyRowEventArgs();
args.RowData = row;
if (IdentifyRow != null)
{
IdentifyRow(this, args);
}
if (!string.IsNullOrEmpty(args.ErrorMessage))
{
AddError(row, args.ErrorMessage);
}
if (!string.IsNullOrEmpty(args.WarningMessage))
{
AddWarning(row, args.WarningMessage);
}
}
#endregion
#region 驗證資料
List <string> list = new List <string>();
foreach (RowData row in rowDataIndex.Keys)
{
if (!list.Contains(row.ID))
{
list.Add(row.ID);
}
}
if (ValidateStart != null)
{
ValidateStartEventArgs args = new 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;
ValidateRowEventArgs args = new 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)
{
string message = "";
message += (message == "" ? "" : "\n") + "";
foreach (string key in errorFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + errorFields[key];
}
AddError(row, message);
}
if (warningFields.Count != 0)
{
string message = "";
message += (message == "" ? "" : "\n") + "";
foreach (string key in warningFields.Keys)
{
message += (message == "" ? "" : "\n") + " " + key + "：" + warningFields[key];
}
AddWarning(row, message);
}
#endregion
if (bkw.CancellationPending)
{
e.Cancel = true;
//填入驗證結果
FillReport(wb, rowDataIndex);
_ErrorWB = wb;
return;
}
count++;
bkw.ReportProgress((int)(progress + count * (100.0 - progress) / totleCount), new int[] { GetErrorCount(rowDataIndex), GetWarningCount(rowDataIndex) });
}
if (ValidateComplete != null)
{
ValidateComplete(this, new EventArgs());
}
#endregion
List <RowData> rows = new List <RowData>();
rows.AddRange(rowDataIndex.Keys);
bkw.ReportProgress(100, new int[] { GetErrorCount(rowDataIndex), GetWarningCount(rowDataIndex) });
//填入驗證結果
FillReport(wb, rowDataIndex);
e.Result = new object[] { wb, GetErrorCount(rowDataIndex) == 0, rows, selectedFields };
}