internal bool CanCheckSingleSheet(ref ExcelSheetTable S, ref ExcelSheetTable L)
{
//List<string> strTitles = Titles.Select(r => r.Text).ToList();
//if (!strTitles.Contains("单表比较") || strTitles.IndexOf("单表比较")==1) return false;
//int colP = strTitles.IndexOf("单表比较")+1, colN = strTitles.Count+1;
//S = new ExcelSheetTable(worksheet, 1, colP);
//L = new ExcelSheetTable(worksheet, colP + 1, colN);
int skipCol = 0, SBCol = 0, SECol = 0, LBCol = 0, LECol = 0;
ComputeLSBECol(ref skipCol, ref SBCol, ref SECol, ref LBCol, ref LECol);
if (SECol > SBCol && SBCol - 1 > 0)
{
S = new ExcelSheetTable(worksheet, SBCol - 1, SECol);
}
if (LECol > LBCol && LBCol - 1 > SECol)
{
L = new ExcelSheetTable(worksheet, LBCol - 1, LECol);
}
return(true);
}
private void CompareTable(ExcelSheetTable S, ExcelSheetTable 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;
}
}
///////////////////////// 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 col = 1; col < S.ColCount - 1; col++)
{
if (S.CellValue(c1.Row, S.NameCol + col) != L.CellValue(c2.Row, L.NameCol + col))
{
SP.Add(new Point(c1.Row, S.NameCol + col));
LP.Add(new Point(c2.Row, L.NameCol + col));
strb.AppendLine(c1.Text + " " + PointTostring(new Point(c1.Row, col)) + "=" + S.CellValue(c1.Row, S.NameCol + col) + "\t"
+ PointTostring(new Point(c2.Row, col)) + "=" + L.CellValue(c2.Row, L.NameCol + 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;
}
internal bool CanCheckSingleSheet(ExcelSheetTable S, ExcelSheetTable L)
{
throw new NotImplementedException();
}
private void CompareSingleSheetClick()
{
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);
ExcelSingleSheet ES = new ExcelSingleSheet(E.ExcelSheet(comboBoxSSheetName.SelectedItem.ToString()));
ExcelSheetTable S = null, L = null;
try
{
if (!ES.CanCheckSingleSheet(ref S, ref L))
{
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), i, S.TagCol);
c = _Same[i][1];
ES.SortExcelRange(new Point(c.Row, c.Col), i, L.TagCol);
}
}
/////
ES.SetExcelRangeTag(1, "比较结果", S.TagCol);
ES.SetExcelRangeTag(1, "比较结果", L.TagCol);
}
catch (Exception ex)
{
MessageBox.Show("程序有点小问题\r\n" + ex.Message);
}
finally
{
S = null;
L = null;
ExcelBook.excel.ActiveWorkbook.Save();
ExcelBook.excel.Workbooks.Close();
}
}