using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Threading;
using System.Diagnostics;
namespace UI
{
class ExcelCompare
{
#region 线程操作
public void UseThread(string textStr1, string textStr2)
{
//插入一列序号
Microsoft.Office.Interop.Excel.Application excel1 = AddColumns(textStr1);
Microsoft.Office.Interop.Excel.Application excel2 = AddColumns(textStr2);
//调用比较函数，并改变异同处的颜色，并恢复初始排序CompareExcel(excel1, excel2);
KillProcess(excel1.Name.ToString());
KillProcess(excel2.Name.ToString());
}
#endregion
#region 增加一列并排序
//增加NO列，并重新排序
public Microsoft.Office.Interop.Excel.Application AddColumns(string str)
{
int i = 2;
//excel进程实例化
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(str); //打开excel文件
excel.ActiveSheet.Columns[1].Insert(); //获取活动页,插入一列
excel.Cells[1, 1] = "NO"; //在第一行第一列的位置插入“NO”
//控制加入的NO数
//progressBar1.Maximum = Convert.ToInt32(excel.ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row);
int iRow = Convert.ToInt32(excel.ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row);
while (i <= iRow)
{
excel.Cells[i, 1] = i - 1;
i++;
//xtraTabPage2.Refresh();
//progressBar1.Value = i / 2;
}
//为指定的列排序
excel.Columns.Sort(excel.get_Range("B2:B65536", Type.Missing), XlSortOrder.xlAscending,
excel.get_Range("C2:C65536", Type.Missing), Type.Missing, XlSortOrder.xlAscending, excel.get_Range("G2:G65536", Type.Missing),
XlSortOrder.xlAscending, XlYesNoGuess.xlYes, Type.Missing, Type.Missing, XlSortOrientation.xlSortColumns,
XlSortMethod.xlPinYin, XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal);
return excel;
}
#endregion
//比较两张表中的数据，并在比较完成之后重新排序，删除冗余列
#region 比较两表
public void CompareExcel(Microsoft.Office.Interop.Excel.Application excel1, Microsoft.Office.Interop.Excel.Application excel2)
{
string str1, str2;
string str1Row, str2Row;
int nextR = 2, i, p;
//取两表中行数多的表的最大行数int maxRow = Convert.ToInt32(excel1.ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row);
i = Convert.ToInt32(excel2.ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row);
maxRow = (maxRow >= i) ? maxRow : i;
i = 2;
//取最大列
int maxCol = Convert.ToInt32(excel1.ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Column);
//表1与表2相比
while (i <= maxRow && nextR <= maxRow)
{
//取排列序号
str1 = excel1.Cells[i, 2].Text + excel1.Cells[i, 3].Text + excel1.Cells[i, 7].Text;
str2 = excel2.Cells[nextR, 2].Text + excel2.Cells[nextR, 3].Text + excel2.Cells[nextR, 7].Text;
if (str1.CompareTo(str2) == 0) //两表中的排列序号存在且相等
{
p = nextR;
while (str1.CompareTo(str2) == 0) //序号相等
{
//读取i 行中的数据
str1Row = excel1.Cells[i, 4].Text + excel1.Cells[i, 5].Text + excel1.Cells[i, 6].Text +
excel1.Cells[i, 8].Text + excel1.Cells[i, 9].Text + excel1.Cells[i, 10].Text + excel1.Cells[i, 11].Text +
excel1.Cells[i, 12].Text + excel1.Cells[i, 13].Text + excel1.Cells[i, 14].Text + excel1.Cells[i, 15].Text;
str2Row = excel2.Cells[p, 4].Text + excel2.Cells[p, 5].Text + excel2.Cells[p, 6].Text +
excel2.Cells[p, 8].Text + excel2.Cells[p, 9].Text + excel2.Cells[p, 10].Text + excel2.Cells[p, 11].Text +
excel2.Cells[p, 12].Text + excel2.Cells[p, 13].Text + excel2.Cells[p, 14].Text + excel2.Cells[p, 15].Text;
// 将取得的单行数据进行相比
if (str1Row != str2Row) //数据值不相同
{
//改变该行的字体颜色
excel2.Range[excel2.Cells[p, 1], excel2.Cells[p, maxCol]].Font.ColorIndex = 3;
p++;
//获取下一行的排列序号
str2 = excel2.Cells[p, 2].Text + excel2.Cells[p, 3].Text + excel2.Cells[p, 7].Text;
}
else
{
excel2.Range[excel2.Cells[p, 1], excel2.Cells[p, maxCol]].Font.ColorIndex = 1;
i++;
nextR = p + 1;
break;
}
}
if (str1.CompareTo(str2) != 0)
{
excel1.Range[excel1.Cells[i, 1], excel1.Cells[i, maxCol]].Font.ColorIndex = 3;
i++;
nextR++;
}
}
else if ((str1.CompareTo(str2)) > 0) // 表1大于表2，则将表2中对应的行标识出
{
excel2.Range[excel2.Cells[nextR, 1], excel2.Cells[nextR, maxCol]].Font.ColorIndex = 5;
nextR++;
}
else if ((str1.CompareTo(str2)) < 0) //表1小于表2，则将表1中的对应的行标识出
{
excel1.Range[excel1.Cells[i, 1], excel1.Cells[i, maxCol]].Font.ColorIndex = 5;
i++;
}
}
//两张表恢复本来顺序
excel1.Columns.Sort(excel1.get_Range("A1:A65536", Type.Missing), XlSortOrder.xlAscending,
Type.Missing, Type.Missing, XlSortOrder.xlAscending, Type.Missing,
XlSortOrder.xlAscending, XlYesNoGuess.xlYes, Type.Missing, Type.Missing, XlSortOrientation.xlSortColumns,
XlSortMethod.xlPinYin, XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal);
excel2.Columns.Sort(excel2.get_Range("A1:A65536", Type.Missing), XlSortOrder.xlAscending,
Type.Missing, Type.Missing, XlSortOrder.xlAscending, Type.Missing,
XlSortOrder.xlAscending, XlYesNoGuess.xlYes, Type.Missing, Type.Missing, XlSortOrientation.xlSortColumns,
XlSortMethod.xlPinYin, XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal, XlSortDataOption.xlSortNormal);
//删除增加的序号列
excel1.get_Range("A1:A65536").Delete(Type.Missing);
excel2.get_Range("A1:A65536").Delete(Type.Missing);
//保存并退出
excel1.Workbooks.Item[1].Save();
excel2.Workbooks.Item[1].Save();
excel1.Workbooks.Close();
excel2.Workbooks.Close();
excel1.Application.Quit();
excel2.Application.Quit();
}
#endregion
#region 杀死进程
public void KillProcess(string processName)
{
//获得进程对象，以用来操作
System.Diagnostics.Process myproc = new System.Diagnostics.Process();
//得到所有打开的进程
try
{
//获得需要杀死的进程名
foreach (Process thisproc in Process.GetProcessesByName(processName))
{
//立即杀死进程
thisproc.Kill();
}
}
catch (Exception Exc)
{
throw new Exception("", Exc);
}
}
#endregion
}
}