6、比较Excel中的数据

第5篇的方法是直接打开EXCEL进行操作，此种方法比较容易理解，但缺点就是代码执行起来比较缓慢。所以才有接下来的第6篇。此篇是用读取数据库的方式来读取EXCEL中的数据，然后将数据读取到DATATABLE中进行比较。因为DATATBLE是在内存中的，所以比较过程中读取数据的速度相对来说是快上不少。其实本来可以在一开始就将数据全部读入DATATBLE中然后进行后续的插入一列、排序、比较、恢复原序等等操作。但是因为个人比较懒吧也就没有去实现了。这里有一个地方需要注意：在使用COM控件直接从EXCEL中读取数据时上标是从1开始的，而1表示的位置是列名那一行。但是在DATATBLE中上标是从0开始的，并且此处已经数据第一行的开始了。（说的又不对和不当的地方，希望有大神指正！！！）下面附上代码。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel.Application;
using System.Threading;
using System.Diagnostics;
using System.Reflection;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;


namespace UI
{
    class OleCompare
    {
        #region 连接Excel
        public static DataSet LoadDataFromExcel(string filePath, Excel excel)
		 
        {
            try
            {
                Worksheet wSheet =  excel.Workbooks[1].Worksheets[1];
                excel.DisplayAlerts = false;
               // MessageBox.Show(wSheet.Name);

                string strConn;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
                OleDbConnection OleConn = new OleDbConnection(strConn);
                OleConn.Open();
                String sql = "SELECT * FROM  ["+wSheet.Name+"$]";//可是更改Sheet名称，比如sheet2，等等   

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
                DataSet oleDsExcel = new DataSet();
                OleDaExcel.Fill(oleDsExcel,"Sheet1");
                OleConn.Close();
                return oleDsExcel;
            }
            catch (Exception err)
            {
                MessageBox.Show("数据绑定Excel失败!失败原因：" + err.Message);
                return null;
            }
        }
        #endregion

        #region 插入一列并排序
        public Excel AddColumns(string str, Excel excel)
        {
            int i = 2;
            //excel进程实例化
            excel.Workbooks.Open(str);    //打开excel文件
            Workbook workBook = excel.Workbooks[1];
            Worksheet workSheet = workBook.Worksheets[1];
            workBook.ActiveSheet.Columns[1].Insert();      //获取活动页,插入一列      
            workSheet.Cells[1, 1] = "NO";  //在第一行第一列的位置插入“NO”

            //控制加入的NO数
            //progressBar1.Maximum = Convert.ToInt32(excel.ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row);
            int iRow = workBook.ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;  //文件工作页中的数据行数
            while (i <= iRow)
            {
                workSheet.Cells[i, 1] = i - 1;    //为单元格赋值
                i++;
                //xtraTabPage2.Refresh();
                //progressBar1.Value = i / 2;
            }

            //为指定的列排序
            workSheet.Columns.Sort(workSheet.get_Range("B2:B65536", Type.Missing), XlSortOrder.xlAscending,
                workSheet.get_Range("C2:C65536", Type.Missing), Type.Missing, XlSortOrder.xlAscending, workSheet.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 Compare(string txtSource, string txtAim)
        {
            Excel xlsSource = new Excel();
            Excel xlsAim = new Excel();
            xlsSource = AddColumns(txtSource, xlsSource);
            xlsAim = AddColumns(txtAim, xlsAim);
            DataSet ds = LoadDataFromExcel(txtSource, xlsSource);
            System.Data.DataTable dtSource = ds.Tables["Sheet1"];
            ds = LoadDataFromExcel(txtAim, xlsAim);
            System.Data.DataTable dtAim = ds.Tables["Sheet1"];
            //获取当前活动页
            Worksheet wsSource = xlsSource.Workbooks[1].Worksheets[1];
            Worksheet wsAim = xlsAim.Workbooks[1].Worksheets[1];

            int sRow = dtSource.Rows.Count;
            int aRow = dtAim.Rows.Count;          

            //取最大列
            int maxCol = dtSource.Columns.Count;
            int nextR = 0;  // 指示表2中的当前比较位置
            int i = nextR;
            //表1与表2相比
            string strSource, strAim; //排列序号

            while (i <= sRow && nextR < aRow)
            {
                //取排列序号
                strSource = dtSource.Rows[i]["カタログNo"].ToString() + dtSource.Rows[i]["セクションNo1"].ToString() + dtSource.Rows[i]["品名コード"].ToString();
                strAim = dtAim.Rows[nextR]["カタログNo"].ToString() + dtAim.Rows[nextR]["セクションNo1"].ToString() + dtAim.Rows[nextR]["品名コード"].ToString();

                if (strSource.CompareTo(strAim) == 0)    //两表中的排列序号存在且相等
                {
                    //相等的条件下比较内容
                    int p = nextR;
                    string strSourceRow, strAimRow;    //当前比较位置的数据内容
                    while (strSource.CompareTo(strAim) == 0)  //序号相等
                    {
                        //读取i 行中的数据
                        strSourceRow = dtSource.Rows[i]["セクションNo2"].ToString()+ dtSource.Rows[i]["セクションNo3"].ToString() + dtSource.Rows[i]["分割No"].ToString()
                            + dtSource.Rows[i]["品名"].ToString() + dtSource.Rows[i]["部品番号"].ToString() + dtSource.Rows[i]["員数"].ToString()
                            + dtSource.Rows[i]["外色/内色"].ToString() + dtSource.Rows[i]["互換記号"].ToString() + dtSource.Rows[i]["部品番号  ( ) 付"].ToString()
                            + dtSource.Rows[i]["適用記述"].ToString() + dtSource.Rows[i]["切替コード"].ToString();
                        //读取p行中的数据
                        strAimRow = dtAim.Rows[p]["セクションNo2"].ToString() + dtAim.Rows[p]["セクションNo3"].ToString() + dtAim.Rows[p]["分割No"].ToString()
                            + dtAim.Rows[p]["品名"].ToString() + dtAim.Rows[p]["部品番号"].ToString() + dtAim.Rows[p]["員数"].ToString()
                            + dtAim.Rows[p]["外色/内色"].ToString() + dtAim.Rows[p]["互換記号"].ToString() + dtAim.Rows[p]["部品番号  ( ) 付"].ToString()
                            + dtAim.Rows[p]["適用記述"].ToString() + dtAim.Rows[p]["切替コード"].ToString();
                        
                        // 将取得的单行数据进行相比
                        if (strSourceRow != strAimRow)     //数据值不相同
                        {
                            
                            //改变该行的字体颜色,因DataTable中的数据下标是从0开始的，所以必须在excel中要加上偏移量
                            xlsAim.Range[xlsAim.Cells[p+2, 1], xlsAim.Cells[p+2, maxCol]].Font.ColorIndex = 3;
                            p++;
                            //获取下一行的排列序号
                            strAim = dtAim.Rows[p]["カタログNo"].ToString() + dtAim.Rows[p]["セクションNo1"].ToString() + dtAim.Rows[p]["品名コード"].ToString();
                        }
                        else
                        {
                            xlsAim.Range[xlsAim.Cells[p+2, 1], xlsAim.Cells[p+2, maxCol]].Font.ColorIndex = 1;
                            i++;    //表1中下一条数据的读取位置
                            nextR = p + 1;  //表2中下一条数据的读取位置
                            break;
                        }
                    }
                    if (strSource.CompareTo(strAim) != 0)
                    {
                        wsSource.Range[wsSource.Cells[i+2, 1], wsSource.Cells[i+2, maxCol]].Font.ColorIndex = 3;
                        i++;
                        nextR++;
                    }
                }
                else if ((strSource.CompareTo(strAim)) > 0)  // 表1大于表2，则将表2中对应的行标识出
                {
                    wsAim.Range[wsAim.Cells[nextR+2, 1], wsAim.Cells[nextR+2, maxCol]].Font.ColorIndex = 5;
                    nextR++;
                }
                else if ((strSource.CompareTo(strAim)) < 0)                //表1小于表2，则将表1中的对应的行标识出
                {
                    wsSource.Range[wsSource.Cells[i+2, 1], wsSource.Cells[i+2, maxCol]].Font.ColorIndex = 5;
                    i++;
                }
            }
            ReSort(xlsSource);
            ReSort(xlsAim);
            KillProcess(xlsSource.Name);
            KillProcess(xlsAim.Name);
        }
        #endregion

        #region 恢复排序
        private void ReSort(Excel excel)
        {
            //两张表恢复本来顺序
           Worksheet ws = excel.Workbooks[1].Worksheets[1];
            ws.Columns.Sort(ws.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);

            //删除增加的序号列
            ws.get_Range("A1:A65536").Delete(Type.Missing);
            //保存并退出
            excel.DisplayAlerts = false;   //禁止弹出保存对话框
            excel.Workbooks[1].Save();
            excel.Workbooks.Close();
            excel.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
    }
}