多条件查找

 

 

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 Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Diagnostics;
namespace zizhuTools
{
    public partial class duotjcz : Form
    {
        Excel.Application EApp;  
        public duotjcz()
        {
            InitializeComponent();
        }
        Excel.Range rng_tj;
      //  object[,] arrTj = null;
        private void btn_tj_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
            rng_tj = EApp.InputBox("选择单元格", "在Excel中选取", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
            //rng_tj = rng_tj.Resize[rng_tj.Worksheet.UsedRange.Rows.Count, 1];
            textBox1.Text = rng_tj.Column.ToString();
          //  arrTj = rng_tj.Worksheet.UsedRange.Value;
            this.WindowState = FormWindowState.Normal;
        }
        int r;
        Excel.Worksheet sht;
        Excel.Worksheet sht2;
        Excel.Workbook wb;
        private void duotjcz_Load(object sender, EventArgs e)
        {
            EApp = Globals.ThisAddIn.Application;
            wb = EApp.ActiveWorkbook;
            sht = wb.ActiveSheet;         
        }
        Excel.Range rng_tjfw;
       // object[,] arrFw = null;
        private void btn_tjfw_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
            rng_tjfw = EApp.InputBox("选择单元格", "在Excel中选取", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
            //arrFw = rng_tjfw.Worksheet.UsedRange.Value;
            //rng_tjfw = rng_tjfw.Resize[rng_tjfw.Worksheet.UsedRange.Rows.Count, 1];
            textBox2.Text = rng_tjfw.Column.ToString();
            this.WindowState = FormWindowState.Normal;
        }


        private void btn_tjtj_Click(object sender, EventArgs e)
        {
            dataGridView1.Rows.Add(textBox1.Text, textBox2.Text);
        }

        Excel.Range rng_sjfw;
      //  object[,] arr_data = null;
        int dn = 1;
        private void btn_sjfw_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
            rng_sjfw = EApp.InputBox("选择单元格", "在Excel中选取", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
            textBox3.Text = rng_sjfw.get_Address(true, true, Excel.XlReferenceStyle.xlA1, true, Type.Missing);
           // arr_data = rng_sjfw.Resize[rng_sjfw.Worksheet.UsedRange.Rows.Count, 1].Value;
            dn = rng_sjfw.Column;
            this.WindowState = FormWindowState.Normal;
        }

        Excel.Range rng_cfjg;
        private void tbn_cfjg_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
            rng_cfjg = EApp.InputBox("选择【同排】单元格", "在Excel中选取", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
            textBox4.Text = rng_cfjg.get_Address();
           // rng_cfjg = rng_cfjg.Resize[1, 1];
            this.WindowState = FormWindowState.Normal;
        }
        Stopwatch sw = new Stopwatch();
        private void button6_Click(object sender, EventArgs e)
        {
            yctb.AppendText("\r\n程序开始运行:");
            this.Text = "多条件查找(程序运行中)";
            sw.Start();                  
                    Excel.Range rng;//rng = EApp.Evaluate("[工作簿1]Sheet1!$A$1:A6");
                    string sCal_Area = "";//条件区域
                    string sF_Area = "";//条件
                    object[,] arrTj = rng_tj.Worksheet.UsedRange.Value;//获得整个表的内容
                    object[,] arrFw = rng_tjfw.Worksheet.UsedRange.Value;//获得整个表的内容
                    object[,] arrResult = new object[(arrTj.GetLength(0) - rng_cfjg.Row)+1, 1];
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    int[] fwWz = new int[dataGridView1.Rows.Count];
                    int[] tjWz = new int[dataGridView1.Rows.Count];
                    int x = 0;

                    yctb.AppendText("\r\n正在处理数据:" + sw.Elapsed);

                    foreach (DataGridViewRow r in dataGridView1.Rows)
                    {
                            tjWz[x] = int.Parse(r.Cells[0].Value.ToString());
                            fwWz[x] = int.Parse(r.Cells[1].Value.ToString());
                            x += 1;
                    }

                    yctb.AppendText("\r\n正在获取数据:" + sw.Elapsed);
                  //  int ias = rng_cfjg.Row;

                    for (int i = 1; i <= arrFw.GetLength(0); i++)
                    {
                        try
                        {
                            string key = "";
                            string value = arrFw[i, dn] != null ? arrFw[i, dn].ToString() : "";//数据范围[,所在列]
                            foreach (int j in fwWz)
                            {
                                key += arrFw[i, j] != null ? arrFw[i, j].ToString().Replace("'","").ToUpper().Trim() : ""; //制造一个key
                            }

                            if (dic.ContainsKey(key) && dic[key] != value)
                            {
                                dic[key] += "," + value;
                                continue;
                            }
                            else if (dic.ContainsKey(key) && dic[key] == value)
                            {
                                continue;
                            }
                            dic.Add(key, value);
                        }
                        catch (Exception ex)
                        {
                            yctb.AppendText("\r\n条件范围区域第【" + i.ToString() + "】异常，已忽略,原因:" + ex.Message);
                        }
                    }
                    //------------------------------------------------------------------
                    //------------------------------------------------------------------

                    yctb.AppendText("\r\n正在填充结果:" + sw.Elapsed);
                    int kyc = 0;
                    for (int i = rng_cfjg.Row; i <= arrTj.GetLength(0); i++)
                    {
                        try
                        {
                            string key = "";
                            foreach (int j in tjWz)
                            {
                                key += arrTj[i, j] != null ? arrTj[i, j].ToString().Replace("'","").ToUpper().Trim() : "";
                            }

                            if (dic.ContainsKey(key))
                            {
                                arrResult[kyc, 0] = dic[key];
                            }
                        }
                        catch (Exception ex)
                        {
                            yctb.AppendText("\r\n条件区域第【" + i.ToString() + "】异常，已忽略,原因:" + ex.Message);
                        }
                        kyc += 1;
                    }

                    rng_cfjg.Resize[arrResult.GetLength(0), 1].Value = arrResult;
                 
               
            
            this.Text = "多条件查找";
            sw.Stop();
            MessageBox.Show("程序运行完毕:" + sw.Elapsed);
            this.TopMost = true;
            this.TopMost = false;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string fileName = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Image\\原图\\多条件查找.png";
            System.Diagnostics.Process.Start(fileName);
        }

    }
}