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 Microsoft.Vbe.Interop;
namespace zizhuTools
{
public partial class tqxinxi : Form //身份证号提取信息
{
public Excel.Application EApp;//声明公共对象变量
Excel.Range rng_add;
Excel.Range rng_tqsv;
Stopwatch sw = new Stopwatch();
Excel.Workbook wb;
////初始化
private void tqxinxi_Load(object sender, EventArgs e)
{
EApp = Globals.ThisAddIn.Application; //获得控制权
wb = EApp.ActiveWorkbook;
rng_add = EApp.Selection;//初始选择位置
tqxx_tb.Text = rng_add.get_Address();
zdage_tb.Text = DateTime.Today.ToString("yyyyMMdd");
}
public tqxinxi()
{
InitializeComponent();
}
private void tqxx_tb_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
rng_add = EApp.InputBox("选择单元格", "在Excel中选取", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
tqxx_tb.Text = rng_add.get_Address();
rng_add = rng_add.Resize[Type.Missing, 1];
this.WindowState = FormWindowState.Normal;
}
private void tqxx_btn_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
rng_add = EApp.InputBox("选择单元格", "在Excel中选取", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
tqxx_tb.Text = rng_add.get_Address();
rng_add = rng_add.Resize[Type.Missing, 1];
this.WindowState = FormWindowState.Normal;
}
XmlDocument xdoc = new XmlDocument();
private void tqxx_ent_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
rng_tqsv = EApp.InputBox("选择【同排】单元格", "在Excel中选取", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
this.WindowState = FormWindowState.Normal;
this.Text = "身份证号提取信息(程序运行中)";
sw.Start();
int rg_row, rg_col;
rg_row = rng_tqsv.Cells[1].row;
rg_col = rng_tqsv.Cells[1].Column;
//遍历数据
int rg_i = rg_row;
object[,] arr = rng_add.Value;
for (int i = 1; i <= arr.GetLength(0); i++)
{
arr[i, 1] = arr[i, 1] != null ? arr[i, 1].ToString().Replace("'","").ToUpper().Trim() : "身份证错误";
}
EApp.DisplayAlerts = false;
//提取籍贯
if (tqxx_jg.Checked)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
string sFolderPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
xdoc.Load(sFolderPath + "DataAsStatics\\quyudaima.xml");
XmlNodeList xmlnlist;
for (int i = 1; i <= arr.GetLength(0); i++)
{
arr[i, 1] = arr[i, 1].ToString() != "身份证错误" ? arr[i, 1].ToString().Substring(0, 6) : "000000";
if (!dic.ContainsKey(arr[i, 1].ToString()))
{
xmlnlist = xdoc.SelectNodes("//QY[@num=\"" + arr[i, 1].ToString() + "\"]/xml");
dic[arr[i, 1].ToString()] = xmlnlist.Count > 0 ? xmlnlist[0].Attributes["name"].Value : "身份证错误";
}
arr[i, 1] = dic[arr[i, 1].ToString()];
}
rng_tqsv.Resize[arr.GetLength(0), 1].Value = arr;
}
//提取出生年月
if (tqxx_bth.Checked)
{
if (tqxx_chk.Checked)
{
//显示公式
foreach (Excel.Range rn2 in rng_add)
{
EApp.Cells[rg_i, rg_col].Value = string.Format("=IF(LEN({0})=18,TEXT(MID({0},7,8),\"0000-00-00\"),IF(LEN({0})=15, \"19\" & TEXT(MID({0},7,8),\"00-00-00\"),\"\"))", rn2.get_Address());
}
}
else
{
for (int i = 1; i <= arr.GetLength(0); i++)
{
if (arr[i, 1].ToString() != "身份证错误")
{
if (arr[i, 1].ToString().Length != 15 && arr[i, 1].ToString().Length != 18)
{
continue;
}
arr[i, 1] = arr[i, 1].ToString().Substring(6, 8);
}
}
rng_tqsv.Resize[arr.GetLength(0), 1].Value = arr;
}
}
//提取性别
if (tqxx_gder.Checked)
{
if (tqxx_chk.Checked)
{
//显示公式
foreach (Excel.Range rn2 in rng_add)
{
EApp.Cells[rg_i, rg_col].Value = string.Format("=IF(LEN({0})=18,TEXT(MOD(MID({0},17,1),2),\"[=0]女;[=1]男\"),IF(LEN({0})=15, TEXT(MOD(MID({0},15,1),2),\"[=0]女;[=1]男\"),\"\"))", rn2.get_Address());
}
}
else
{
for (int i = 1; i <= arr.GetLength(0); i++)
{
if (arr[i, 1].ToString() != "身份证错误")
{
arr[i, 1] = int.Parse(arr[i, 1].ToString().Substring(14, 3)) % 2 == 0 ? "女" : "男";
}
}
rng_tqsv.Resize[arr.GetLength(0), 1].Value = arr;
}
}
//提取年龄
if (tqxx_age.Checked)
{
if (tqxx_chk.Checked)
{
//显示公式
foreach (Excel.Range rn2 in rng_add)
{
EApp.Cells[rg_i, rg_col].Value = string.Format("=DATEDIF(IF(LEN({0})=18,DATE(MID({0},7,4),MID({0},11,2),MID({0},13,2)),IF(LEN({0})=15,DATE(\"19\"&MID({0},7,2),MID({0},9,2),MID({0},11,2)),\"\")),TODAY(),\"Y\")", rn2.get_Address());
}
}
else
{
for (int i = 1; i <= arr.GetLength(0); i++)
{
if (arr[i, 1].ToString() != "身份证错误")
{
if (arr[i, 1].ToString().Length != 15 && arr[i, 1].ToString().Length != 18)
{
continue;
}
try
{
DateTime bri_d = DateTime.ParseExact(arr[i, 1].ToString().Substring(6, 8), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
arr[i, 1] = GetAgeByBirthdate(bri_d);
}
catch
{
arr[i, 1] = "身份证错误";
}
}
}
rng_tqsv.Resize[arr.GetLength(0), 1].Value = arr;
}
}
if (age_zd_btn.Checked)
{
if (tqxx_chk.Checked)
{
//显示公式
foreach (Excel.Range rn2 in rng_add)
{
EApp.Cells[rg_i, rg_col].Value = string.Format("=DATEDIF(IF(LEN({0})=18,DATE(MID({0},7,4),MID({0},11,2),MID({0},13,2)),IF(LEN({0})=15,DATE(\"19\"&MID({0},7,2),MID({0},9,2),MID({0},11,2)),\"\")),\"{1}\",\"Y\")", rn2.get_Address(), now);
}
}
else
{
string zdrq = zdage_tb.Text;
DateTime now = DateTime.ParseExact(zdrq, "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
for (int i = 1; i <= arr.GetLength(0); i++)
{
if (arr[i, 1].ToString() != "身份证错误")
{
if (arr[i, 1].ToString().Length != 15 && arr[i, 1].ToString().Length != 18)
{
continue;
}
try
{
DateTime bri_d = DateTime.ParseExact(arr[i, 1].ToString().Substring(6, 8), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
arr[i, 1] = GetAgeByBirthdate2(bri_d, now);
}
catch
{
arr[i, 1] = "身份证错误";
}
}
}
rng_tqsv.Resize[arr.GetLength(0), 1].Value = arr;
}
}
this.Text = "身份证号提取信息";
sw.Stop();
// EApp.DisplayAlerts = true;
MessageBox.Show("程序运行结束:"+ sw.Elapsed,"完成");
this.TopMost = true;
this.TopMost = false;
}
//foreach (Excel.Range rn2 in rng_add)
//{
// //空值处理
// string id_num = Convert.ToString(rn2.Value);
// if (id_num == "")
// {
// EApp.Cells[rg_i, rg_col].Value = "";
// continue;
// }
// rg_i += 1;
//}
//this.TopMost = true;
//this.TopMost = false;
//}
//提取籍贯的方法
public string tqjg(string tqjg_num)
{
//string tqjg_num2 = tqjg_num.Substring(0, 6);
XmlNodeList xmlnlist = xdoc.SelectNodes("//QY[@num=\"" + tqjg_num + "\"]/xml");
string xmlname = xmlnlist.Count > 0 ? xmlnlist[0].Attributes["name"].Value : "";
return xmlname;
}
DateTime now = DateTime.Now;
//提取年龄的方法（目前)
public int GetAgeByBirthdate(DateTime birthdate)
{
int age = now.Year - birthdate.Year;
if (now.Month < birthdate.Month || (now.Month == birthdate.Month && now.Day < birthdate.Day))
{
age--;
}
return age < 0 ? 0 : age;
}
//提取年龄的方法（指定日期)
public int GetAgeByBirthdate2(DateTime birthdate, DateTime now)
{
int age = now.Year - birthdate.Year;
if (now.Month < birthdate.Month || (now.Month == birthdate.Month && now.Day < birthdate.Day))
{
age--;
}
return age < 0 ? 0 : age;
}
////提取性别
//public string Get_Gender(string gen_num)
//{
// string gen;
// if (int.Parse(gen_num) % 2 == 0)//性别代码为偶数是女性奇数为男性
// {
// gen = "女";
// }
// else
// {
// gen = "男";
// }
// return gen;
//}
private void tqxx_jg_Click(object sender, EventArgs e)
{
tqxx_chk.Enabled = false;
}
private void tqxx_gder_Click(object sender, EventArgs e)
{
tqxx_chk.Enabled = true;
}
private void tqxx_bth_Click(object sender, EventArgs e)
{
tqxx_chk.Enabled = true;
}
private void tqxx_age_Click(object sender, EventArgs e)
{
tqxx_chk.Enabled = true;
}
//退出按钮
private void tqxx_exit_Click(object sender, EventArgs e)
{
this.Close();
}
public void btn_chk(int a)
{
switch(a)
{
case 1: tqxx_jg.Checked=true;
break;
case 2:tqxx_age.Checked = true;
break;
case 3:tqxx_gder.Checked = true;
break;
case 4:tqxx_bth.Checked = true;
break;
case 5: age_zd_btn.Checked = true;
break;
}
}
private void tqxx_jg_CheckedChanged(object sender, EventArgs e)
{
tqxx_chk.Enabled = tqxx_jg.Checked?false:true;
}
}
}
浙公网安备 33010602011771号