queryemployeesalary.cs

工资结算系统 拥有权限控制 临时工资表 自动导入导出Excel 以及邮件群发功能

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using Excel;
using System.Data;
using System.Data.OleDb;
//using System.Data.OracleClient;


namespace HrSalary.salary
{
	/// <summary>
	/// QueryEmployeeSalary 的摘要说明。
	/// </summary>
	public class QueryEmployeeSalary : System.Windows.Forms.Form
	{
		private int countDgRows =0;
		private  int countDgColumns=0;
		private  int namecolumn=0;
		private  int namerow=0;
		public  System.Windows.Forms.DataGrid dataGrid1;
		private System.Windows.Forms.Button button1;
		private System.Windows.Forms.Button button2;
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.TextBox textBox3;
		private System.Windows.Forms.Button button4;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Button button5;

		private System.Windows.Forms.MenuItem menuItem1;
		private System.Windows.Forms.MenuItem menuItem2;

		private System.Windows.Forms.OpenFileDialog openFileDialog1;
		/// <summary>
		/// 必需的设计器变量。
		/// </summary>
		private System.ComponentModel.Container components = null;
		private System.Windows.Forms.ContextMenu contextMenu1;
		private OleDbConnection dbCon = null;
		private System.Windows.Forms.SaveFileDialog saveFileDialog1;
		private System.Windows.Forms.RadioButton radioButton2;
		private System.Windows.Forms.RadioButton radioButton3;
		//	private OracleConnection dboracleCon = null;
		private OleDbDataAdapter dbAdapter = null;
		private System.Windows.Forms.TextBox totalNumber;
		private DataSet ds = null;

		public QueryEmployeeSalary()
		{
			//
			// Windows 窗体设计器支持所必需的
			//
			InitializeComponent();

			//
			// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
			//
		}

		/// <summary>
		/// 清理所有正在使用的资源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if(components != null)
				{
					
					components.Dispose();

				}
			}
			base.Dispose( disposing );
		}

		#region Windows 窗体设计器生成的代码
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{
			this.dataGrid1 = new System.Windows.Forms.DataGrid();
			this.contextMenu1 = new System.Windows.Forms.ContextMenu();
			this.menuItem1 = new System.Windows.Forms.MenuItem();
			this.menuItem2 = new System.Windows.Forms.MenuItem();
			this.button1 = new System.Windows.Forms.Button();
			this.button2 = new System.Windows.Forms.Button();
			this.groupBox1 = new System.Windows.Forms.GroupBox();
			this.button4 = new System.Windows.Forms.Button();
			this.radioButton3 = new System.Windows.Forms.RadioButton();
			this.radioButton2 = new System.Windows.Forms.RadioButton();
			this.textBox3 = new System.Windows.Forms.TextBox();
			this.label1 = new System.Windows.Forms.Label();
			this.totalNumber = new System.Windows.Forms.TextBox();
			this.button5 = new System.Windows.Forms.Button();
			this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
			this.saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
			this.groupBox1.SuspendLayout();
			this.SuspendLayout();
			// 
			// dataGrid1
			// 
			this.dataGrid1.BackgroundColor = System.Drawing.SystemColors.ActiveCaptionText;
			this.dataGrid1.CaptionVisible = false;
			this.dataGrid1.ContextMenu = this.contextMenu1;
			this.dataGrid1.DataMember = "";
			this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dataGrid1.Location = new System.Drawing.Point(8, 32);
			this.dataGrid1.Name = "dataGrid1";
			this.dataGrid1.ReadOnly = true;
			this.dataGrid1.Size = new System.Drawing.Size(880, 360);
			this.dataGrid1.TabIndex = 0;
			this.dataGrid1.MouseUp += new System.Windows.Forms.MouseEventHandler(this.dataGrid1_MouseUp);
			// 
			// contextMenu1
			// 
			this.contextMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
																						 this.menuItem1,
																						 this.menuItem2});
			this.contextMenu1.Popup += new System.EventHandler(this.contextMenu1_Popup);
			// 
			// menuItem1
			// 
			this.menuItem1.Index = 0;
			this.menuItem1.Text = "发送到Email";
			this.menuItem1.Click += new System.EventHandler(this.menuItem1_Click);
			// 
			// menuItem2
			// 
			this.menuItem2.Index = 1;
			this.menuItem2.Text = "修改关键信息(姓名,Email和银行卡号)";
			this.menuItem2.Click += new System.EventHandler(this.menuItem2_Click);
			// 
			// button1
			// 
			this.button1.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
			this.button1.Location = new System.Drawing.Point(24, 8);
			this.button1.Name = "button1";
			this.button1.Size = new System.Drawing.Size(96, 23);
			this.button1.TabIndex = 1;
			this.button1.Text = "打开Excel文件";
			this.button1.Click += new System.EventHandler(this.button1_Click);
			// 
			// button2
			// 
			this.button2.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
			this.button2.Location = new System.Drawing.Point(152, 8);
			this.button2.Name = "button2";
			this.button2.Size = new System.Drawing.Size(96, 23);
			this.button2.TabIndex = 2;
			this.button2.Text = "保存到数据库";
			this.button2.Click += new System.EventHandler(this.button2_Click);
			// 
			// groupBox1
			// 
			this.groupBox1.Controls.Add(this.button4);
			this.groupBox1.Controls.Add(this.radioButton3);
			this.groupBox1.Controls.Add(this.radioButton2);
			this.groupBox1.Controls.Add(this.textBox3);
			this.groupBox1.Location = new System.Drawing.Point(296, 0);
			this.groupBox1.Name = "groupBox1";
			this.groupBox1.Size = new System.Drawing.Size(456, 40);
			this.groupBox1.TabIndex = 6;
			this.groupBox1.TabStop = false;
			// 
			// button4
			// 
			this.button4.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
			this.button4.Location = new System.Drawing.Point(352, 8);
			this.button4.Name = "button4";
			this.button4.Size = new System.Drawing.Size(88, 23);
			this.button4.TabIndex = 12;
			this.button4.Text = "查询";
			this.button4.Click += new System.EventHandler(this.button4_Click);
			// 
			// radioButton3
			// 
			this.radioButton3.Checked = true;
			this.radioButton3.Location = new System.Drawing.Point(240, 8);
			this.radioButton3.Name = "radioButton3";
			this.radioButton3.TabIndex = 16;
			this.radioButton3.TabStop = true;
			this.radioButton3.Text = "全体员工";
			// 
			// radioButton2
			// 
			this.radioButton2.Location = new System.Drawing.Point(24, 8);
			this.radioButton2.Name = "radioButton2";
			this.radioButton2.Size = new System.Drawing.Size(56, 24);
			this.radioButton2.TabIndex = 15;
			this.radioButton2.Text = "姓名";
			// 
			// textBox3
			// 
			this.textBox3.Location = new System.Drawing.Point(104, 8);
			this.textBox3.Name = "textBox3";
			this.textBox3.Size = new System.Drawing.Size(128, 21);
			this.textBox3.TabIndex = 10;
			this.textBox3.Text = "";
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(568, 416);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(104, 23);
			this.label1.TabIndex = 11;
			this.label1.Text = "共有：";
			// 
			// totalNumber
			// 
			this.totalNumber.Location = new System.Drawing.Point(624, 408);
			this.totalNumber.Name = "totalNumber";
			this.totalNumber.ReadOnly = true;
			this.totalNumber.Size = new System.Drawing.Size(144, 21);
			this.totalNumber.TabIndex = 12;
			this.totalNumber.Text = "";
			// 
			// button5
			// 
			this.button5.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
			this.button5.Location = new System.Drawing.Point(784, 0);
			this.button5.Name = "button5";
			this.button5.Size = new System.Drawing.Size(96, 23);
			this.button5.TabIndex = 13;
			this.button5.Text = "导出Excel";
			this.button5.Click += new System.EventHandler(this.button5_Click);
			// 
			// QueryEmployeeSalary
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.BackColor = System.Drawing.Color.AliceBlue;
			this.ClientSize = new System.Drawing.Size(904, 445);
			this.Controls.Add(this.button5);
			this.Controls.Add(this.totalNumber);
			this.Controls.Add(this.label1);
			this.Controls.Add(this.button2);
			this.Controls.Add(this.button1);
			this.Controls.Add(this.dataGrid1);
			this.Controls.Add(this.groupBox1);
			this.Name = "QueryEmployeeSalary";
			this.StartPosition = System.Windows.Forms.FormStartPosition.CenterParent;
			this.Text = "工资管理框";
			this.Load += new System.EventHandler(this.QueryEmployeeSalary_Load);
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			this.groupBox1.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		string[] ConvertToStringArray(System.Array values)
		{
			string[] theArray = new string[values.Length];
			for (int i = 1; i <= values.Length; i++)
			{
				if (values.GetValue(1, i) == null)
					theArray[i-1] = "";
				else
					theArray[i-1] = (string)values.GetValue(1, i).ToString();
			}

			return theArray;
		}

		private string Get_TableHeader(System.Data.DataTable  dt,int num)
		{
			string  A="";
			for(int i=0;i<num;i++)
			{
				if((65+i)<91)
					A=((char)(65+i)).ToString();
				else
					A="A"+((char)(i+39)).ToString();   //i+39:Z后的字符转成A、B...
				dt.Columns.Add(A,Type.GetType("System.String"));
			}
			return A;
		}


		private void button1_Click(object sender, System.EventArgs e)
		{
			try
			{
				int dgItemnum=0;
				int line=1;
				this.openFileDialog1.FileName ="*.xls";//"csv files (*.csv)|*.csv|xls files (*.xls)|*.xls";
				this.openFileDialog1.RestoreDirectory=true;
				System.Data.DataTable dt = new System.Data.DataTable("mydatatable");
				Excel.Application ExcelObj = null;
				ExcelObj = new Excel.Application();
				ExcelObj.Visible =false;
			
			
				if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
				{
					try
					{
						Excel.Workbook theWorkbook = 
							ExcelObj.Workbooks.Open(
							openFileDialog1.FileName, 0, true, 5,
							"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 
							0,0,0,true);
    /*ExcelObj.Workbooks.Open()函数的参数
	 * ( System.String Filename ,  openFileDialog1.FileName
      System.Object UpdateLinks ,  0
	  System.Object ReadOnly ,  true
	  System.Object Format , 5
	  System.Object Password ,""
	  System.Object WriteResPassword ,""
	  System.Object IgnoreReadOnlyRecommended , true
	  System.Object Origin ,  Excel.XlPlatform.xlWindows
	  System.Object Delimiter , "\t"
	  System.Object Editable , false
	  System.Object Notify , false
	  System.Object Converter , 
	  System.Object AddToMru , 
	  System.Object Local ,
	  System.Object CorruptLoad )   */
						
						Excel.Sheets sheets = theWorkbook.Worksheets;
						object missing = System.Reflection.Missing.Value; 
						Excel.Worksheet worksheet1 =(Excel.Worksheet)sheets.get_Item(1);//Excel.Worksheet 
						this.Get_TableHeader(dt,42);				       
						Excel.Worksheet  worksheet =(Excel.Worksheet)sheets.get_Item(1);
						for (int i = 1; i <5; i++) //i是行					
						{
							Excel.Range range = worksheet.get_Range("A"+i.ToString(), "AX"+ i.ToString());//
							System.Array myvalues =(System.Array)range.Value2;//get_Value();//(System.Array)range.Cells.Value;
							if(myvalues.GetValue(1,1)==null) break;							
							int j=0;
							for(j=0;j<50;j++)
								if(myvalues.GetValue(1,j+1)==null)
									break;
							
							if(j>dgItemnum)
							{
								dgItemnum=j;
								line=i;
							}
						}   
						if(dgItemnum<1)
						{
							MessageBox.Show("此文件为空，不能在本系统中打开");
							return;
						}
						if(dgItemnum==1)
						{
							dgItemnum=2;
						}					

						dt.Columns.Clear();// =(Excel.Worksheet)sheets.Delete(); 
						string  A=this.Get_TableHeader(dt,dgItemnum);
						// get the first and only worksheet from the collection 
						// of worksheets
						worksheet =(Excel.Worksheet)sheets.get_Item(1);//Excel.Worksheet 
						  int k = 1;
						//System.DateTime.FromOADate(double);//
						for (int i = line; i <=65535; i++) //i是行
						{
							Excel.Range range = worksheet.get_Range("A"+i.ToString(), A+ i.ToString());//
							System.Array myvalues = (System.Array)range.Cells.Value2;
							if(myvalues.GetValue(1,1)==null) break;	
							string[] strArray = ConvertToStringArray(myvalues);//GetValue(1,1) 第一列
							DataRow dr = dt.NewRow();
							for(int j=0;j<strArray.Length;j++)
							{  
								int timeposition=strArray[j].ToString().IndexOf("0:00:00");
								if(timeposition != -1)
								{
									strArray[j]=strArray[j].Substring(0,timeposition);
								}
							dr[j] = strArray[j];
							}
							dt.Rows.Add(dr);
							k++;
						}  
						this.totalNumber.Text=k.ToString();
						System.Data.DataSet ds = new System.Data.DataSet("mydatatable");
						ds.Tables.Add(dt);
						this.dataGrid1.SetDataBinding(ds,"mydatatable");
						theWorkbook.Close(false,missing,missing);
						ExcelObj.Quit();
					}
					catch(Exception ee)
					{
						MessageBox.Show("读取Excel出现错误:"+ee.ToString());
					}
				}
			}
			catch(Exception ee)
			{
				MessageBox.Show(ee.ToString());
			}
		}
		
		private void dataGrid1_MouseUp(object sender, System.Windows.Forms.MouseEventArgs e)
		{
			
			System.Drawing.Point pt = new System.Drawing.Point(e.X, e.Y); 
 
			DataGrid.HitTestInfo hti = dataGrid1.HitTest(pt); 
 
			if(hti.Type == DataGrid.HitTestType.Cell) 
			{ 
				dataGrid1.CurrentCell = new DataGridCell(hti.Row, hti.Column); 
				dataGrid1.Select(hti.Row); 
			} 
		}
		
		private void menuItem1_Click(object sender, System.EventArgs e)
		{
			System.Data.DataSet ds = (System.Data.DataSet)this.dataGrid1.DataSource;
			if(ds==null|| ds.Tables.Count<=0) 			{
				MessageBox.Show("您没查询或导入数据表，不能发email！！！\n   请进行查询或导入数据表！！");
				return;
			}
			int  countfields=((System.Data.DataSet)this.dataGrid1.DataSource).Tables[0].Columns.Count;
			int numrows=((DataSet)(this.dataGrid1.DataSource)).Tables[0].Rows.Count;