sqlite 提取

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.Xml;
using System.Diagnostics;
using System.Data.SQLite;
namespace zizhuTools
{
    public partial class Jizhonglianpian : Form
    {
        public Jizhonglianpian()
        {
            InitializeComponent();
        }
        public Excel.Application Eapp;//声明公共对象变量
        Excel.Workbook Student_wb;
        Excel.Worksheet Student_sht;
        Excel.Workbook wb;
        Excel.Worksheet sht;
        int T1 = 1;
        int T2 = 1;
        Excel.Range rng;
        int cols;
        int rows;
        Stopwatch sw = new Stopwatch();

        private void Jizhonglianpian_Load(object sender, EventArgs e)
        {
            Eapp = Globals.ThisAddIn.Application;
            wb = Eapp.ActiveWorkbook;
            Student_wb = Eapp.ActiveWorkbook;
            sht = wb.ActiveSheet;
            Student_sht = wb.ActiveSheet;

            ////判断籍贯 是否连片地区
            //string sFolderPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
            //xdoc.Load(sFolderPath + "DataAsStatics\\jizhonglianpian.xml");
            //XmlNodeList xmlnlist;
            //object [,] arr = sht.UsedRange.Value;
            //for (int i = 2; i <= arr.GetLength(0); i++)
            //{
            //    xmlnlist = xdoc.SelectNodes("//QY[@name=\"" + arr[i, 1].ToString().Trim() + "\"]/xml");
            //    arr[i, 1] = xmlnlist.Count > 0 ? "集中" : "";

            //}
            //sht.Range["A2"].Resize[arr.GetLength(0), 1].Value = arr;
            for (int i = 1; i <= Student_wb.Worksheets.Count; i++)
            {
                // cb1.Items.Add(wb.Worksheets[i].Name);
                cb2.Items.Add(Student_wb.Worksheets[i].Name);
            }
            // cb1.SelectedItem = sht.Name;
            cb2.SelectedItem = Student_sht.Name;

            cols = sht.UsedRange.Columns.Count;
            rows = sht.UsedRange.Rows.Count;
            object[,] arr = sht.Range["A1"].Resize[1, cols].Value;
            for (int i = 1; i <= arr.GetLength(1); i++)
            {
                cb1.Items.Add(arr[1, i] != null ? arr[1, i].ToString() : "Column" + i);
            }
            cb1.SelectedIndex = 0;

        }

        private void nud1_ValueChanged(object sender, EventArgs e)
        {
            cb1.Items.Clear();
            T1 = int.Parse(nud1.Value.ToString());
            nud3.Value = T1 + 1;
            object[,] arr = sht.Range["A"+T1].Resize[1, cols].Value;
            for (int i = 1; i <= arr.GetLength(1); i++)
            {
                cb1.Items.Add(arr[1, i] != null ? arr[1, i].ToString() : "Column" + i);
            }
            cb1.SelectedIndex = 0;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            oFD1.ShowDialog();
            textBox1.Text = oFD1.FileName;
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            cb2.Items.Clear();
            Student_wb = Eapp.Workbooks.Open(textBox1.Text);
            for (int i = 1; i <= Student_wb.Worksheets.Count; i++)
            {
                cb2.Items.Add(Student_wb.Worksheets[i].Name);
            }
            cb2.SelectedIndex = 0;
        }
        Excel.Range rng_tqsv;
       // XmlDocument xdoc = new XmlDocument();
        private void button2_Click(object sender, EventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
            rng_tqsv = Eapp.InputBox("选择【N-1排】单元格", "在Excel中选取", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
            this.WindowState = FormWindowState.Normal;
            this.Text = "集中连片地区判断(程序运行中)";

            sht = wb.ActiveSheet;
            cols = sht.UsedRange.Columns.Count;
            rows = sht.UsedRange.Rows.Count;

            T1 = int.Parse(nud3.Value.ToString());
           
            object[,] arr = sht.Cells[T1,cb1.SelectedIndex+1].Resize[(rows-T1)+1, 1].Value;
            object[,] result = new object[arr.GetLength(0)+1, 1];
            result[0, 0] = "判断结果";
            Dictionary<string, int> dic = new Dictionary<string, int>();
            for (int i = 1; i <= arr.GetLength(0); i++)
            {
                if (arr[i, 1] != null)
                {
                    string values = arr[i, 1].ToString().ToUpper().Trim().Replace("'", "");
                    dic[values] = 0;
                }
            }

            T2 = int.Parse(nud2.Value.ToString());

            Student_sht = Student_wb.Worksheets[cb2.SelectedIndex + 1];

            object[,] studentArr = Student_sht.UsedRange.Value;

            List<string> list1 = new List<string>();

            int x_zx = 0;
            for (int i = 1; i <= studentArr.GetLength(1); i++)
            {
                list1.Add(studentArr[T2, i] != null ? studentArr[T2, i].ToString() : "");
            }

            int idnum = list1.Contains("身份证件号") ? list1.IndexOf("身份证件号") + 1 : 4;//    身份证件号         
            int rxny = list1.Contains("入学年月") ? list1.IndexOf("入学年月") + 1 : 15;//    学制
            int xingshi = list1.Contains("学习形式") ? list1.IndexOf("学习形式") + 1 : 35;//    学习形式
            int xinngzhi = list1.Contains("户口性质") ? list1.IndexOf("户口性质") + 1 : 19;//    户口性质
            int jiguan = list1.Contains("籍贯") ? list1.IndexOf("籍贯") + 1 : 17;//    籍贯
          // 在校学生表-须包含字段：【身份证件号】,【入学年月】,【学制】,【户口性质】,【学习形式】;
            //string sFolderPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
            //xdoc.Load(sFolderPath + "DataAsStatics\\jizhonglianpian.xml");
            //XmlNodeList xmlnlist;
            //用数据库查询的写法
            string sFolderPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
            sFolderPath = sFolderPath.Replace("\\", "/") + "DataAsStatics/NewDataBase.sqlite";
            SQLiteConnection  m_dbConnection = new SQLiteConnection("Data Source=" + sFolderPath);
            m_dbConnection.Open();

            SQLiteCommand command = new SQLiteCommand(m_dbConnection);
            string sql = "select 1 from xinlianpian where  quanchen = '{0}'  limit 1";
            SQLiteDataReader rdr;
            SQLiteTransaction tx = m_dbConnection.BeginTransaction();
            command.Transaction = tx;

            string pd_rxny;
            DateTime rxny_pd = DateTime.Now.Date;
            DateTime zx_rxny;
            for (int i = (T2+1); i <= studentArr.GetLength(0); i++)
            {
                if (studentArr[i, idnum] != null)
                {
                    string values = studentArr[i, idnum].ToString().ToUpper().Trim().Replace("'","");
                    if(dic.ContainsKey(values))
                    {
                        try
                        {
                            dic[values] = 0;
                            //判断籍贯 是否连片地区
                            //xmlnlist = xdoc.SelectNodes("//QY[@name=\"" + studentArr[i, jiguan].ToString().Trim() + "\"]");
                            command.CommandText =string.Format(sql,studentArr[i, jiguan].ToString().Trim());
                            rdr = command.ExecuteReader();
                            dic[values] += rdr.HasRows ? 1 : 0;
                            rdr.Close();
                           // dic[values] += xmlnlist.Count > 0 ? 1 : 0;
                            //判断学习形式 是否全日制
                            dic[values] += studentArr[i, xingshi].ToString().Trim() == "全日制" ? 1 : 0;
                            //判断户口性质是否农业
                            dic[values] += (studentArr[i, xinngzhi].ToString().Trim() == "农业户口" || studentArr[i, xinngzhi].ToString().Trim() == "乡镇非农户口") ? 1 : 0;


                            //判断年级 是否一二年级
                            //   dic[values] += studentArr[i, nianji].ToString().Trim() == "全日制" ? 1 : 0;

                            pd_rxny = (studentArr[i, rxny].ToString().Trim() + "-01").Replace("-", "/");//入学年月转换日期格式

                            zx_rxny = DateTime.Parse(pd_rxny);//转换日期
                            zx_rxny = zx_rxny.AddMonths(23);//加上两年的日期

                            x_zx = zx_rxny.CompareTo(rxny_pd);

                            dic[values] += x_zx <= 0 ? 0 : 1;
                        }
                        catch(Exception ex)
                        {
                            ycbox.AppendText("\r\n 产生异常关键字【" + values + "】,原因:"+ex.Message);
                        }

                    }
                }
            }
            tx.Commit();

            int kp = 1;
            for (int i = 1; i <= arr.GetLength(0); i++)
            {
                if (arr[i, 1] != null)
                {
                    string values = arr[i, 1].ToString().ToUpper().Trim().Replace("'", "");
                    result[kp, 0] =dic[values] == 4 ? "集中连片地区" : "";
                }
                kp += 1;
            }
            rng_tqsv.Resize[result.GetLength(0), 1].Value = result;

            sw.Stop();
            MessageBox.Show("程序运行时长:" + sw.Elapsed, "完成");
            this.Text = "集中连片地区判断";
            this.TopMost = true;
            this.TopMost = false;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string fileName = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Image\\原图\\集中连片地区判断.png";
            System.Diagnostics.Process.Start(fileName);
        }

      
    }
}