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);
}
}
}
浙公网安备 33010602011771号