Excel中两列数据的比较处理

     昨天下午看我老婆在处理Excel表格，比较两列：找出两列中相同的项，第一列中有而第二例中没有的项，第二列中有而第一例中没有的项，第一列中重复的项，第二列中重复的项。哎！她一个一个的“Ctrl+F”,有好多记录啊！这样真是累死。她让我帮她这样找。她有其他的事情要做。我可不愿意这么做。花时间编写了一个小程序。

 

 

    /*    本程序用于处理Excel表中两列数据的比较。
 *    没有做过多的考虑，只是找出想要的信息。以后有时间再去优化！
 */
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Collections;

public partial class _Default : System.Web.UI.Page
{
    public int COUNT;//Excel表总的行数。
    public ArrayList c1= new ArrayList ();//第一列的值。
    public ArrayList c2 = new ArrayList();//第一列的值。
    public ArrayList Csame = new ArrayList();//两列中相同的值。
    public ArrayList c1NotSame = new ArrayList();//第一列中除去两列相同的剩余的那部分。
    public ArrayList c2NotSame = new ArrayList();//第二列中除去两列相同的剩余的那部分。
   

    protected void Page_Load(object sender, EventArgs e)
    { 
        string Path = "c://1.xlsx";
        string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        string strExcel = "";
        OleDbDataAdapter myCommand = null;
        DataSet ds = null;
        strExcel = "select * from [sheet1$]";
        myCommand = new OleDbDataAdapter(strExcel, strConn);
        ds = new DataSet();
        myCommand.Fill(ds, "table1");

 

        COUNT = ds.Tables["table1"].Rows.Count;

       
        for (int i = 0; i < COUNT;i++ )
        {
          
            if (ds.Tables["table1"].Rows[i][0].ToString()!="" && ds.Tables["table1"].Rows[i][0].ToString()!=null)
            {
                c1.Add(ds.Tables["table1"].Rows[i][0].ToString());
               
            }

            if (ds.Tables["table1"].Rows[i][1].ToString() != "" && ds.Tables["table1"].Rows[i][1].ToString() != null)
            {
                c2.Add(ds.Tables["table1"].Rows[i][1].ToString());

            }
        }

        Csame = getSame(c1, c2);
       
        //for (int x = 0; x < c1Notin.Count; x++)
        //{
        //    Response.Write(c1Notin[x].ToString() + "/n");
        //}
        ds.Clear();
        conn.Close();

    }

    /// <summary>
    /// 获取量数组中相同的部分
    /// </summary>
    /// <param name="list1"></param>
    /// <param name="list2"></param>
    /// <returns>返回两个数组中相同的部分,(ArrayList)。</returns>
    private ArrayList getSame(ArrayList list1, ArrayList list2)
    {

        ArrayList list = new ArrayList();
        for (int i = 0; i < list1.Count; i++)
        {
            for (int j = 0; j < list2.Count; j++)
            {
                if (list1[i].ToString()==list2[j].ToString())
                {
                    list.Add(list1[i].ToString());
                    break;
                }
            }
        }

        return list;
    }
    /// <summary>
    /// Child是Father的一部分，该方法求出是Father中不为Child的那部分
    /// </summary>
    /// <param name="Child"></param>
    /// <param name="Father"></param>
    /// <returns>返回类型：ArrayList</returns>
    private ArrayList getNotIn(ArrayList Child, ArrayList Father)
    {
        ArrayList list = new ArrayList();

        for (int i = 0; i < Father.Count; i++)
        {
            if (!Child.Contains(Father[i].ToString()))
            {
                list.Add(Father[i].ToString());
            }
        }
        return list;
    }
    /// <summary>
    /// 列出本身中间有相同的项。
    /// </summary>
    /// <param name="list"></param>
    /// <returns></returns>
    private ArrayList getSameinself(ArrayList list)
    {
        ArrayList returnlist = new ArrayList();
        for (int i = 0; i < list.Count; i++)
        {
            for (int j = i + 1; j < list.Count; j++)
            {
                if (list[i].ToString() == list[j].ToString())
                {
                    returnlist.Add(list[i].ToString());
                }
            }
        }

        return returnlist;
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        ArrayList sameinself = getSameinself(c1);
        DataTable workTable = new DataTable("Customers");
        workTable.Columns.Add("自身中的相同项", typeof(String));
        DataRow row;

        for (int i = 0; i < sameinself.Count; i++)
        {
            row = workTable.NewRow();

            row["自身中的相同项"] = sameinself[i].ToString();
            workTable.Rows.Add(row);

        }

        GridView1.DataSource = workTable;
        GridView1.DataBind();
       

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        c1NotSame = getNotIn(Csame, c1);
        DataTable workTable = new DataTable("Customers");
        workTable.Columns.Add("Name", typeof(String));
        DataRow row;

        for (int i = 0; i < c1NotSame.Count; i++)
        {
            row = workTable.NewRow();
            row["Name"] = c1NotSame[i].ToString();
            workTable.Rows.Add(row);

        }

        GridView2.DataSource = workTable;
        GridView2.DataBind();

    }
    protected void Button3_Click(object sender, EventArgs e)
    {

        DataTable workTable = new DataTable("Customers");

         workTable.Columns.Add("Name", typeof(String));
        DataRow row;

        for (int i = 0; i < Csame.Count; i++)
        {
            row = workTable.NewRow();
            row["Name"] = Csame[i].ToString();
            workTable.Rows.Add(row);

        }
        GridView3.DataSource = workTable;
        GridView3.DataBind();
    
    }

    protected void Button4_Click(object sender, EventArgs e)
    {

        ArrayList sameinself = getSameinself(c2);
        DataTable workTable = new DataTable("Customers");
        workTable.Columns.Add("自身中的相同项", typeof(String));
        DataRow row;

        for (int i = 0; i < sameinself.Count; i++)
        {
            row = workTable.NewRow();

            row["自身中的相同项"] = sameinself[i].ToString();
            workTable.Rows.Add(row);

        }

        GridView4.DataSource = workTable;
        GridView4.DataBind();

    }
    protected void Button5_Click(object sender, EventArgs e)
    {
        c1NotSame = getNotIn(Csame, c1);
        DataTable workTable = new DataTable("Customers");
        workTable.Columns.Add("Name", typeof(String));
        DataRow row;
        for (int i = 0; i < c1NotSame.Count; i++)
        {
            row = workTable.NewRow();
            row["Name"] = c1NotSame[i].ToString();
            workTable.Rows.Add(row);

        }

        GridView5.DataSource = workTable;
        GridView5.DataBind();

    }
}