基本信息

---

下载

   友情提示：（无需注册或充值，赞助后即可获取资源下载链接）

---

     嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：78630559

本次赞助数额为： 10 元　

支付宝支付

微信支付

   源码介绍

---

对比的是指定格式的excel，仅供参考学习

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
//using Microsoft.Office.Interop.Excel;

namespace Excel比对
{
    public partial class Form1 : Form
    {
        private static DataTable dt_1 = new DataTable();    //表1数据
        private static DataTable dt_2 = new DataTable();    //表2数据

        private static DataTable dt_o1 ;    //只有表一有
        private static DataTable dt_o2 ;    //只有表二有
        private static DataTable dt_oy ;    //表一表二共有
        private static DataSet _DataSet;
        

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog saveFileDialog1 = new OpenFileDialog();
            
            //ofd.ShowDialog();

            saveFileDialog1.Filter = "导出Excel (*.xls)|*.xls|Word (*.doc)|*.doc";
            saveFileDialog1.FilterIndex = 1;
            saveFileDialog1.RestoreDirectory = true;//保存对话框是否记忆上次打开的目录
            //saveFileDialog1.CreatePrompt = true;
            saveFileDialog1.Title = "导出文件保存路径";
            saveFileDialog1.ShowDialog();

            if (saveFileDialog1.FileName == "")
                return;
            string fileExt = System.IO.Path.GetExtension(saveFileDialog1.FileName);
            if (fileExt != ".xls")//必须是EXCEL文件
                return;
            string filepath = saveFileDialog1.FileName;//文件路径
            //DataTable dt = new DataTable();
            dt_1 = CallExcel(filepath);//返回EXCEL文件的数据
            dv_1.DataSource = dt_1.DefaultView;

            lbl_1.Text = "表一共有记录：" dt_1.Rows .Count  "条";

        }

        protected DataTable CallExcel(string filepath)
        {
            OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="   filepath   ";Extended Properties='Excel 8.0;'");
            con.Open();//这个部分报错，提示外部表不是预期的格式。
            string sql = "select * from [Sheet1$]";//选择第一个数据SHEET
            //OleDbCommand command = new OleDbCommand(sql, con);
            //OleDbDataReader reader = command.ExecuteReader();
            //if (reader.Read())
            //{
            // reader[0].ToString();//直接读出数据
            //}
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            //reader.Close();
            //command.Dispose();
            con.Close();
            con.Dispose();
            return dt;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog saveFileDialog1 = new OpenFileDialog();

            //ofd.ShowDialog();

            saveFileDialog1.Filter = "导出Excel (*.xls)|*.xls|Word (*.doc)|*.doc";
            saveFileDialog1.FilterIndex = 1;
            saveFileDialog1.RestoreDirectory = true;//保存对话框是否记忆上次打开的目录
            //saveFileDialog1.CreatePrompt = true;
            saveFileDialog1.Title = "导出文件保存路径";
            saveFileDialog1.ShowDialog();

            if (saveFileDialog1.FileName == "")
                return;
            string fileExt = System.IO.Path.GetExtension(saveFileDialog1.FileName);
            if (fileExt != ".xls")//必须是EXCEL文件
                return;
            string filepath = saveFileDialog1.FileName;//文件路径
            //DataTable dt = new DataTable();
            dt_2 = CallExcel(filepath);//返回EXCEL文件的数据
            dv_2.DataSource = dt_1.DefaultView;

            lbl_2.Text = "表二共有记录："   dt_2.Rows.Count   "条";
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (dt_1.Rows.Count == 0)
            {
                MessageBox.Show("请选择Excel表1文件");
                return;
            }
            if (dt_2.Rows.Count == 0)
            {
                MessageBox.Show("请选择Excel表2文件");
                return;
            }

            if (dt_oy != null)
            {
                dt_oy.Rows.Clear();
                dt_o1.Rows.Clear();
                dt_o2.Rows.Clear();
            }

            dt_oy = dt_1.Clone();
            dt_o1 = dt_1.Clone();
            dt_o2 = dt_2.Clone();

            for (int i = 0; i < dt_1.Rows.Count; i  )
            {
                string ylxm = dt_1.Rows[i]["育妇姓名"].ToString().Trim();
                string poxm = dt_1.Rows[i]["配偶姓名"].ToString().Trim();
                string znrq = dt_1.Rows[i]["子女出生日期"].ToString().Trim();

                //DateTime d_znrq = Convert.ToDateTime(znrq);

                bool xt_flag = false;       

                for (int j = 0; j < dt_2.Rows.Count; j  )
                {
                    string ylxm2 = dt_2.Rows[j]["育妇姓名"].ToString().Trim();
                    string poxm2 = dt_2.Rows[j]["配偶姓名"].ToString().Trim();
                    string znrq2 = dt_2.Rows[j]["小孩出生"].ToString().Trim();

                    try
                    {
                        znrq2 = znrq2.Substring(0, 4)   "-"   znrq2.Substring(4, 2)   "-"   znrq2.Substring(6, 2);
                    }
                    catch { }
                    //DateTime d_znrq2 = Convert.ToDateTime(znrq2);

                    if (ylxm == ylxm2 && poxm == poxm2 && znrq == znrq2)
                    {
                        xt_flag = true;
                        dt_oy.Rows.Add(dt_1.Rows[i].ItemArray);
                        //j = dt_2.Rows.Count;        //求表一表二都有，跳出
                        break;
                    }
                }
                if (xt_flag == false)       //求只有表一有
                {
                    dt_o1.Rows.Add(dt_1.Rows [i].ItemArray);
                }
            }

            //求只有表二有
            for (int i = 0; i < dt_2.Rows.Count; i  )
            {
                string ylxm = dt_2.Rows[i]["育妇姓名"].ToString().Trim();
                string poxm = dt_2.Rows[i]["配偶姓名"].ToString().Trim();
                string znrq = dt_2.Rows[i]["小孩出生"].ToString().Trim();

                try
                {
                    znrq = znrq.Substring(0, 4)   "-"   znrq.Substring(4, 2)   "-"   znrq.Substring(6, 2);
                }
                catch { }

                bool xt_flag = false;

                for (int j = 0; j < dt_1.Rows.Count; j  )
                {
                    string ylxm2 = dt_1.Rows[j]["育妇姓名"].ToString().Trim();
                    string poxm2 = dt_1.Rows[j]["配偶姓名"].ToString().Trim();
                    string znrq2 = dt_1.Rows[j]["子女出生日期"].ToString().Trim();

                    if (ylxm == ylxm2 && poxm == poxm2 && znrq == znrq2)
                    {
                        xt_flag = true;
                        //j = dt_2.Rows.Count;        //相同，跳出
                        break;
                    }
                }
                if (xt_flag == false)       //只有表二有
                {
                    dt_o2.Rows.Add(dt_2.Rows[i].ItemArray);
                }
            }

            only_1.DataSource = dt_o1.DefaultView;      //只有表一有
            only_2.DataSource = dt_o2.DefaultView;      //只有表二有
            only_y.DataSource = dt_oy.DefaultView;      //二个表都有

            lbl_only1.Text =  dt_o1.Rows .Count "条";
            lbl_only2.Text = dt_o2.Rows.Count   "条";
            lbl_onlyy.Text = dt_oy.Rows.Count   "条";

            t_only1.Text = "c:\\只有表一.xls";
            t_only2.Text = "c:\\只有表二.xls";
            t_onlyy.Text = "c:\\表一表二共有.xls";

            MessageBox.Show("比对完成，请查看！");
        }


        #region 注释
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="gridView"></param>
        public static void SaveAs(DataGridView gridView)
        {
            //导出到execl   
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                saveFileDialog.ShowDialog();
                string strName = saveFileDialog.FileName;
                if (strName.Length != 0)
                {
                    //toolStripProgressBar1.Visible = true;
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;
                    Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    excel.Application.Workbooks.Add(true); ;
                    excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
                    if (excel == null)
                    {
                        MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                    Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                    sheet.Name = "test";

                    int m = 0, n = 0;
                    //生成列名称   这里i是从1开始的 因为我第0列是个隐藏列ID  没必要写进去
                    for (int i = 1; i < gridView.ColumnCount; i  )
                    {

                        excel.Cells[1, i] = gridView.Columns[i].HeaderText.ToString();

                    }

                    //填充数据
                    for (int i = 0; i < gridView.RowCount; i  )
                    {

                        //j也是从1开始  原因如上  每个人需求不一样
                        for (int j = 1; j < gridView.ColumnCount; j  )
                        {

                            if (gridView[j, i].Value.GetType() == typeof(string))
                            {
                                if (i < 9 && j == 0)
                                {
                                    string s = "0"   gridView[j-1, i].Value.ToString().Trim();
                                    excel.Cells[i   2, j] = "'"   s;
                                    continue;
                                }
                                excel.Cells[i   2, j] = "'"   gridView[j-1, i].Value.ToString();


                            }
                            else
                            {
                                excel.Cells[i   2, j] = gridView[j-1, i].Value.ToString();
                            }



                        }
//                        toolStripProgressBar1.Value  = 100 / gridView.RowCount;
                    }

                    sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                    book.Close(false, miss, miss);
                    books.Close();
                    excel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

                    GC.Collect();
                    MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                  //  toolStripProgressBar1.Value = 0;


                    System.Diagnostics.Process.Start(strName);
                }
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
            }

        }
        #endregion

        private void button4_Click(object sender, EventArgs e)
        {
            if (dt_o1.Rows.Count > 0)
            {
                AsposeExcel _Excel = new AsposeExcel(t_only1.Text, "表一");
                _Excel.DatatableToExcel(dt_o1);

                MessageBox.Show("导出成功，请查看！");
                //SaveAs(only_1);
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (dt_o2.Rows.Count > 0)
            {
                AsposeExcel _Excel = new AsposeExcel(t_only2.Text , "表二");
                _Excel.DatatableToExcel(dt_o2);

                MessageBox.Show("导出成功，请查看！");
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            if (dt_oy.Rows.Count > 0)
            {
                AsposeExcel _Excel = new AsposeExcel(t_onlyy.Text , "表一表二共有");
                _Excel.DatatableToExcel(dt_oy);

                MessageBox.Show("导出成功，请查看！");
            }
        }

        #region 注释
        //protected bool InsertSQLServer(DataTable dt, string dataname)
        //{
        //    string strCon = @"server=.;database=testmyIVR;uid=sa;pwd=123";
        //    try
        //    {
        //        SqlConnection con = new SqlConnection(strCon);//连接数据库
        //        con.Open();//打开

        //        //插入数据
        //        for (int i = 0; i < dt.Rows.Count; i  )
        //        {
        //            string strSQL = " Insert into userss values (";
        //            for (int k = 0; k < dt.Columns.Count; k  )
        //            {
        //                strSQL  = "'"   dt.Rows[k].ToString()   "',";
        //            }
        //            strSQL = strSQL.Substring(0, strSQL.Length - 1);
        //            strSQL  = ")";
        //            SqlCommand insertCom = new SqlCommand(strSQL, con);
        //            insertCom.ExecuteNonQuery();
        //        }
        //        return true;
        //    }
        //    catch
        //    {
        //        return false;
        //    }
        //}

        //public System.Data.DataView ExcelDataView = new System.Data.DataView();

        ///// <summary>
        ///// 导入EXCEL
        ///// </summary>
        //public bool Import(string FileName)
        //{
        //    string sheetname;

        //    try
        //    {
        //        sheetname = GetSheetName(FileName);
        //        //导入数据；
        //        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"   "Data Source="   FileName   ";"   "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
        //        OleDbConnection conn = new OleDbConnection(strConn);
        //        string SQL = "SELECT * FROM ["   sheetname   "$]";
        //        OleDbDataAdapter da = new OleDbDataAdapter(SQL, conn);
        //        DataSet ds = new DataSet();
        //        da.Fill(ds, "table");
        //        conn.Close();
        //        ExcelDataView = ds.Tables["table"].DefaultView;
        //        ds.Tables.Clear();
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        return false;
        //    }

        //}
        #endregion
    }
}