private void ExportDifferentExcel(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();
ExcelSheet NL = new ExcelSheet(N.CopySheetAndDeleteOther(L));
ExcelSheet NS = new ExcelSheet(N.CopySheet(S));
NS.FormatExcelRange(SP, _colorDif, "不同");
NL.FormatExcelRange(LP, _colorDif, "不同");
N.SaveAs(FileName);
}
else
{
S.FormatExcelRange(SP, _colorDif, "不同");
L.FormatExcelRange(LP, _colorDif, "不同");
}
}
finally
{
N = null;
}
}
private void buttonBrowXlsNameS_Click(object sender, EventArgs e)
{
string f = OpenXmlFile("打开小表文件");
if (f != null)
{
m_cfg.XlsName = f;
textBoxExcelFileName.Text = f;
ClearData();
if (!m_cfg.XlsName.EndsWith(".xls"))
{
MessageBox.Show("文件输入不对");
return;
}
ExcelBook E = new ExcelBook(m_cfg.XlsName);
try
{
comboBoxLSheetName.Items.Clear();
comboBoxSSheetName.Items.Clear();
comboBoxLSheetName.Items.AddRange(E.SheetNames.ToArray());
comboBoxSSheetName.Items.AddRange(E.SheetNames.ToArray());
comboBoxSSheetName.SelectedIndex = 0;
if (comboBoxLSheetName.Items.Count > 1)
{
comboBoxLSheetName.SelectedIndex = 1;
}
}
finally
{
E = null;
ExcelBook.excel.Workbooks.Close();
}
}
}
private void ExportDuplicationExcel(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()));
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);
}
if (FileName != "")
{
N.Create();
if (LDuplication.Count > 0)
{
new ExcelSheet(N.CopySheetRowsAndDeleteOther(L, LDuplication));
}
if (SDuplication.Count > 0)
{
if (LDuplication.Count > 0)
{
new ExcelSheet(N.CopySheetRows(S, SDuplication));
}
else
{
new ExcelSheet(N.CopySheetRowsAndDeleteOther(S, SDuplication));
}
}
N.SaveAs(FileName);
}
else
{
List <Point> SD = SDuplication.Select(r => new Point(r, 2)).ToList();
List <Point> LD = SDuplication.Select(r => new Point(r, 2)).ToList();
S.FormatExcelRange(SD, _colorDup, "重复");
L.FormatExcelRange(LD, _colorDup, "重复");
}
}
finally
{
N = null;
}
}
private void CompareMultiSheetClick()
{
ExcelBook E = new ExcelBook(m_cfg.XlsName);
ExcelSheet S = new ExcelSheet(E.ExcelSheet(comboBoxSSheetName.SelectedItem.ToString()));
ExcelSheet L = new ExcelSheet(E.ExcelSheet(comboBoxLSheetName.SelectedItem.ToString()));
try
{
if (checkBoxClearBackColor.Checked)
{
S.ClearBackColor();
L.ClearBackColor();
}
Compare(S, L);
if (checkBoxTagL.Checked || checkBoxTagS.Checked)
{
if (LSpecial.Count > 0 || SSpecial.Count > 0)
{
ExportSpecialExcel(E);
}
}
if (checkBoxCopyDuplicationL.Checked || checkBoxCopyDuplicationS.Checked)
{
if (LDicDuplication.Count > 0 || SDicDuplication.Count > 0)
{
ExportDuplicationExcel(E);
}
}
if (checkBoxDifL.Checked || checkBoxDifS.Checked)
{
if (LP.Count > 0 || SP.Count > 0)
{
ExportDifferentExcel(E);
}
}
}
catch (Exception ex)
{
MessageBox.Show("程序有点小问题\r\n" + ex.Message);
}
finally
{
S = null;
L = null;
ExcelBook.excel.ActiveWorkbook.Save();
ExcelBook.excel.Workbooks.Close();
}
}
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;
}
}
private void comboBoxSSheetName_SelectedIndexChanged(object sender, EventArgs e)
{
if (checkBoxCompareSingle.Checked)
{
ExcelBook E = new ExcelBook(m_cfg.XlsName);
DataTable S = null, L = null;
try
{
if (!checkBoxRedTitle.Checked)
{
ExcelSingleSheet ES = new ExcelSingleSheet(E.ExcelSheet(comboBoxSSheetName.SelectedItem.ToString()));
if (!ES.CanCheckSingleSheet(ref S, ref L))
{
MessageBox.Show(ES.Msg);
return;
}
}
else
{
ExcelSingleRedSheet ES = new ExcelSingleRedSheet(E.ExcelSheet(comboBoxSSheetName.SelectedItem.ToString()));
if (!ES.CanCheckSingleSheet(ref S, ref L, checkBoxMultiBlackSkip.Checked))
{
MessageBox.Show(ES.Msg);
return;
}
}
dgvL.DataSource = L;
dgvS.DataSource = S;
}
finally
{
E = null;
ExcelBook.excel.Workbooks.Close();
}
}
}
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();
}
}