private void ExportSpecialExcel(ExcelBook E, string FileName = "")
{
ExcelBook N = new ExcelBook();
try
{
ExcelSheet S = new ExcelSheet(E.ExcelSheet(comboBoxSSheetName.SelectedItem.ToString()));
ExcelSheet L = new ExcelSheet(E.ExcelSheet(comboBoxLSheetName.SelectedItem.ToString()));
if (FileName != "")
{
N.Create();
if (LSpecial.Count > 0)
{
new ExcelSheet(N.CopySheetAndDeleteOther(L)).FormatExcelRange(LSpecial, Color.Pink, "特有");
}
if (SSpecial.Count > 0)
{
if (LSpecial.Count > 0)
{
new ExcelSheet(N.CopySheet(S)).FormatExcelRange(SSpecial, Color.Pink, "特有");
}
else
{
new ExcelSheet(N.CopySheetAndDeleteOther(S)).FormatExcelRange(SSpecial, Color.Pink, "特有");
}
}
N.SaveAs(FileName);
}
else
{
S.FormatExcelRange(SSpecial, _colorSpe, "特有");
L.FormatExcelRange(LSpecial, _colorSpe, "特有");
}
}
finally
{
N = null;
}
}
public Worksheet CopySheetAndDeleteOther(ExcelSheet S)
{
Worksheet ws = (Worksheet)workbook.Sheets[1];
S.WorkSheet.Copy(ws);
//int trytimes = 0;
//while (workbook.Sheets.Count >2 && trytimes++<10)
string sheetname = "";
foreach (Worksheet w in workbook.Worksheets)
{
sheetname += w.Name + "\t";
}
sheetname += "========\r\n";
for (int i = 1; i < 4; i++)
{
Excel.Worksheet w = workbook.Sheets["Sheet" + i];
sheetname += w.Name + "\t";
w.Delete();
}
ws = workbook.Worksheets[1];
ws.Name = S.Name;
return(ws);
}
private void Compare(ExcelSheet S, ExcelSheet L)
{
if (!CompareListString(S, L))
{
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>();
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 = 3; col < S.ColCount; col++)
{
if (S.CellValue(c1.Row, col) != L.CellValue(c2.Row, col))
{
SP.Add(new Point(c1.Row, col));
LP.Add(new Point(c2.Row, col));
strb.AppendLine(c1.Text + " " + PointTostring(new Point(c1.Row, col)) + "=" + S.CellValue(c1.Row, col) + "\t"
+ PointTostring(new Point(c2.Row, col)) + "=" + L.CellValue(c2.Row, col));
}
}
}
string str1 = strb.ToString();
if (str1 == "")
{
MessageBox.Show("除忽略项外，待比较项目完全相同");
}
else
{
str1 = "\r\n============以下单元格内容两表不一致 （行号，列号）============\r\n" + str1;
}
textBoxOut.Text = str + strLS + str1;
}