public bool CanCheckSingleSheet(ref ExcelRedSheetTable S, ref ExcelRedSheetTable L, ref string cmptablename, bool bmultiskip = false)
{
int skipCol = 0, SBCol = 0, SECol = 0, LBCol = 0, LECol = 0;
if (!bmultiskip)
{
ComputeLSBECol(ref skipCol, ref SBCol, ref SECol, ref LBCol, ref LECol);
}
else
{
cmptablename =
ComputeLSBECol_Skip(ref skipCol, ref SBCol, ref SECol, ref LBCol, ref LECol);
}
if (SECol > SBCol && SBCol > 0)
{
List <Cells> Titles = CollectRedTitles(SBCol, SECol);
S = new ExcelRedSheetTable(worksheet, Titles, SBCol, SECol);
}
if (LECol > LBCol && LBCol - 1 > SECol)
{
List <Cells> Titles = CollectRedTitles(LBCol, LECol);
L = new ExcelRedSheetTable(worksheet, Titles, LBCol, LECol);
}
if (S == null || L == null)
{
return(false);
}
return(true);
}
private void CompareTable(ExcelRedSheetTable S, ExcelRedSheetTable L)
{
if (!CompareListString(S.Titles.Select(r => r.Text).ToList(), L.Titles.Select(r => r.Text).ToList()))
{
textBoxOut.Text = "两张表的表头不一致，无法比较";
return;
}
ClearData();
string str = "", strL = "", strS = "", strLS = "";
StringBuilder strb = new StringBuilder();
StringBuilder strT = new StringBuilder();
Dictionary <string, Cells> LDic = ListToDictionary(L.Names, ref strL);
Dictionary <string, Cells> SDic = ListToDictionary(S.Names, ref strS);
Dictionary <string, Cells> LDicSpecial = new Dictionary <string, Cells>();
Dictionary <string, Cells> SDicSpecial = new Dictionary <string, Cells>();
///////////////////////////////////////////////////// Duplication
if (checkBoxCopyDuplicationL.Checked)
{
LDicDuplication = FindDuplication(L.Names);
}
if (checkBoxCopyDuplicationS.Checked)
{
SDicDuplication = FindDuplication(S.Names);
}
if (strS != "")
{
str += "\r\n============小表中以下值存在重复============\r\n" + strS;
}
if (strL != "")
{
str += "\r\n============大表中以下值存在重复============\r\n" + strL;
}
foreach (Cells c in S.Names)
{
if (!LDic.ContainsKey(c.Text))
{
strLS += c + "\t";
}
}
//////////////////////////////////////////////// Special
if (checkBoxTagS.Checked || checkBoxSkipDuplicate.Checked) //排除重复
{
foreach (Cells c in S.Names)
{
if (!LDic.ContainsKey(c.Text) && !SDicDuplication.ContainsKey(c.Text))
{
SSpecial.Add(new Point(c.Row, c.Col));
if (!SDicSpecial.ContainsKey(c.Text))
{
SDicSpecial[c.Text] = c;
}
}
}
}
if (checkBoxTagL.Checked || checkBoxSkipDuplicate.Checked)
{
foreach (Cells c in L.Names)
{
if (!SDic.ContainsKey(c.Text) && !LDicDuplication.ContainsKey(c.Text))
{
LSpecial.Add(new Point(c.Row, c.Col));
if (!LDicSpecial.ContainsKey(c.Text))
{
LDicSpecial[c.Text] = c;
}
}
}
}
if (strLS != "")
{
strLS = "\r\n============小表中的以下值在大表中不存在============\r\n" + strLS;
}
if (str != "")
{
textBoxOut.Text = str + strLS;
if (!checkBoxSkipDuplicate.Checked)
{
MessageBox.Show("存在重复值，没有进一步比对，可选择忽略之后，继续比对");
return;
}
}
if (SDicDuplication.Count > 0 || SDicSpecial.Count > 0)
{
if (str == "")
{
str = " ";
}
}
///////////////////////// DIF
SP = new List <Point>();
LP = new List <Point>();
_Same = new List <List <Cells> >();
foreach (Cells c1 in S.Names)
{
if (str != "" &&
(SDicDuplication.ContainsKey(c1.Text) ||
SDicSpecial.ContainsKey(c1.Text)))
{
continue;
}
Cells c2 = LDic[c1.Text];
for (int i = 1; i < S.Titles.Count; i++)
{
if (S.CellValue(c1.Row, S.Titles[i].Col) != L.CellValue(c2.Row, L.Titles[i].Col))
{
SP.Add(new Point(c1.Row, S.Titles[i].Col));
LP.Add(new Point(c2.Row, L.Titles[i].Col));
strb.AppendLine(c1.Text + " " + PointTostring(new Point(c1.Row, S.Titles[i].Col)) + "=" + S.CellValue(c1.Row, S.Titles[i].Col) + "\t"
+ PointTostring(new Point(c2.Row, L.Titles[i].Col)) + "=" + L.CellValue(c2.Row, L.Titles[i].Col));
}
}
_Same.Add(new List <Cells>()
{
c1, c2
});
}
string str1 = strb.ToString();
if (str1 == "")
{
MessageBox.Show("除忽略项外，待比较项目完全相同");
}
else
{
str1 = "\r\n============以下单元格内容两表不一致 （行号，列号）============\r\n" + str1;
}
textBoxOut.Text = str + strLS + str1;
}
private void CompareRedSingleSheetClick()
{
DataTable DL = (DataTable)dgvL.DataSource;
DataTable DS = (DataTable)dgvS.DataSource;
if (DL == null || DS == null || DL.Columns.Count != DS.Columns.Count)
{
return;
}
for (int i = 0; i < DL.Columns.Count; i++)
{
if (DL.Columns[i].ColumnName != DS.Columns[i].ColumnName)
{
MessageBox.Show("两边待比较的表头不一致");
return;
}
}
ExcelBook E = new ExcelBook(m_cfg.XlsName);
ExcelSingleRedSheet ES = new ExcelSingleRedSheet(E.ExcelSheet(comboBoxSSheetName.SelectedItem.ToString()));
ExcelRedSheetTable S = null, L = null;
string cmptablename = "";
try
{
if (!ES.CanCheckSingleSheet(ref S, ref L, ref cmptablename, checkBoxMultiBlackSkip.Checked))
{
MessageBox.Show("S=" + (S != null) + " " + "L=" + (L != null) + " SL两表之一可能未完成");
return;
}
if (checkBoxClearBackColor.Checked)
{
S.ClearBackColor();
L.ClearBackColor();
}
CompareTable(S, L);
if (checkBoxTagL.Checked || checkBoxTagS.Checked)
{
if (LSpecial.Count > 0 || SSpecial.Count > 0)
{
ES.FormatExcelRange(SSpecial, _colorSpe, "特有", S.TagCol);
ES.FormatExcelRange(LSpecial, _colorSpe, "特有", L.TagCol);
}
}
if (checkBoxCopyDuplicationL.Checked || checkBoxCopyDuplicationS.Checked)
{
if (LDicDuplication.Count > 0 || SDicDuplication.Count > 0)
{
List <int> LDuplication = new List <int>();
List <int> SDuplication = new List <int>();
foreach (List <int> l in LDicDuplication.Values)
{
LDuplication.AddRange(l);
}
foreach (List <int> l in SDicDuplication.Values)
{
SDuplication.AddRange(l);
}
List <Point> SD = SDuplication.Select(r => new Point(r, S.NameCol)).ToList();
List <Point> LD = LDuplication.Select(r => new Point(r, L.NameCol)).ToList();
ES.FormatExcelRange(SD, _colorDup, "重复", S.TagCol);
ES.FormatExcelRange(LD, _colorDup, "重复", L.TagCol);
}
}
if (checkBoxDifL.Checked || checkBoxDifS.Checked)
{
if (LP.Count > 0 || SP.Count > 0)
{
ES.FormatExcelRange(SP, _colorDif, "不同", S.TagCol + 1);
ES.FormatExcelRange(LP, _colorDif, "不同", L.TagCol + 1);
}
}
for (int i = 0; i < _Same.Count; i++)
{
if (_Same[i].Count == 2)
{
Cells c = _Same[i][0];
ES.SortExcelRange(new Point(c.Row, c.Col), cmptablename + i, S.TagCol);
c = _Same[i][1];
ES.SortExcelRange(new Point(c.Row, c.Col), cmptablename + i, L.TagCol);
}
}
/////
ES.SetExcelRangeTag(1, cmptablename + "比较结果", S.TagCol);
ES.SetExcelRangeTag(1, cmptablename + "比较结果", L.TagCol);
}
catch (Exception ex)
{
MessageBox.Show("程序有点小问题\r\n" + ex.Message);
}
finally
{
S = null;
L = null;
ExcelBook.excel.ActiveWorkbook.Save();
ExcelBook.excel.Workbooks.Close();
}
}