用C＃对比Excel文件（原创）

                                                                                            用C＃对比Excel文件（原创）
                                                                                                        Anakin
                                                                                                     2004-10-8
        刚开始学习编程，人家说多动动手。正好公司需要把一批excel文件进行比较。故算动机！
        一、首先需要引用office的动态链接库（office.dll、excel.dll、vbide.dll）需要此文件可联系MSN：wzhj520@hotmail.com。E-mail:zhaojuunwu@gmail.com。
        二、设计界面：因为在对比需要先打开2个文件（OpenExcelFile1、OpenExcelFile\2）和指定范围（range）StartRow、StartColumn、EndRow、EndColumn；

三、写代码：首先获得Excel的目录

private void butOpen1_Click(object sender, System.EventArgs e)

{

OpenFileDialog openExcelFile1 = new OpenFileDialog();

openExcelFile1.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";

if(openExcelFile1.ShowDialog() == DialogResult.OK)

{

this.tbFileName1.Text = openExcelFile1.FileName;

}

/// <summary>

///

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

private void butOpen2_Click(object sender, System.EventArgs e)

{

OpenFileDialog openExcelFile2 = new OpenFileDialog();

openExcelFile2.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";

if(openExcelFile2.ShowDialog() == DialogResult.OK)

{

this.tbFileName2.Text = openExcelFile2.FileName;

}

再就是打开Excel文件到内存中了

string openExcelFile1 = this.tbFileName1.Text;

_excelApp = new Excel.ApplicationClass();

_workBook1 = _excelApp.Workbooks.Open( openExcelFile1,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value ) as Excel.WorkbookClass;

Excel.Sheets sheets1 = _workBook1.Sheets;

Excel._Worksheet workSheet1 = ( Excel._Worksheet )sheets1[SheetN];

string openExcelFile2 = this.tbFileName2.Text;

_workBook2 = _excelApp.Workbooks.Open( openExcelFile2,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value ) as Excel.WorkbookClass;

Excel.Sheets sheets2 = _workBook2.Sheets;

Excel._Worksheet workSheet2 = ( Excel._Worksheet )sheets2[SheetN];

然后就是指定对比所需的范围了

string cellStrA = string.Empty; //用来表示cell的变量

char colCharA = ( char )( SCN+64 ); //用char类型转换64为A（excel中Column是A列、B列）

string colStrA = colCharA.ToString(); //把char转换为string

string cellStrB = string.Empty;

char colCharB = (char)(SCN+64);

string colStrB = colCharB.ToString();

string startCell = colStrA.ToString() + SRN.ToString (); //组成一个单元格例如:A1

string endCell = colStrB.ToString() + ERN.ToString ();

Excel.Range rangeA = workSheet1.get_Range (startCell,endCell); //指定对比范围

Excel.Range rangeB = workSheet2.get_Range( startCell,endCell );

循环了

for ( int j = SCN;j < ECN +1;j++ )

{

for ( int i = SRN;i<ERN +1;i++ )

{

if (((Excel.Range)rangeA[i,j]).Text.ToString() != ((Excel.Range)rangeB[i,j]).Text.ToString())

{

char StartColumn = (char)(j+64);

string TargetStartCell = StartColumn.ToString() + i.ToString();

Excel.Range rngA= workSheet1.get_Range (TargetStartCell,Type.Missing);

rngA.Interior.ColorIndex=20;

_excelApp.Visible = true;

Excel.Range rngB= workSheet2.get_Range (TargetStartCell,Type.Missing);

rngB.Interior.ColorIndex=20;

_excelApp.Visible = true;

}

关于对比后关闭线程的问题网上有很多介绍

例如：

_excelApp.Quit();