form1.cs

c#写的excel导入oracle源码。

			this.groupBox2.ResumeLayout(false);
			this.groupBox3.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 应用程序的主入口点。
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

		private void openExcel_Click(object sender, System.EventArgs e)
		{
			this.OpenFile();
		}


		public void OpenFile()
		{
			OpenFileDialog openFileDialog=new OpenFileDialog(); 
			openFileDialog.InitialDirectory="c:\\";//注意这里写路径时要用c:\\而不是c:\ 
			//			openFileDialog.Filter="文本文件|*.*|Excel文件|*.xls|C#文件|*.cs|所有文件|*.*"; 
			openFileDialog.Filter="Excel文件|*.xls"; 
			openFileDialog.RestoreDirectory=true; 
			openFileDialog.Title = "打开文件";
			openFileDialog.FilterIndex=1; 
			if (openFileDialog.ShowDialog()==DialogResult.OK) 
			{ 
				fName=openFileDialog.FileName; 
				strPath=fName;
				this.textBox1.Text=strPath;
				//				File fileOpen=new File(fName); 
				//isFileHaveName=true; 
				//				richTextBox1.Text=fileOpen.ReadFile(); 
				//				richTextBox1.AppendText(""); 
			} 
             
			if(strPath!="")
			{
				try
				{
					string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+strPath+";Extended Properties=Excel 8.0" ;
					OleDbConnection myConn = new OleDbConnection ( strCon ) ;
					string strCom = " SELECT TOP 2 * FROM [Sheet1$] " ;
					myConn.Open ( ) ;
					OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
					myDataSet = new DataSet ( ) ;
					myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
					columnNum=myDataSet.Tables[0].Columns.Count;
					for (int i=checkedListBox1.Items.Count-1;i>=0;i--)
					{
						checkedListBox1.Items.Remove(checkedListBox1.Items[i]);
			
					}

					for(int k=0;k<columnNum;k++)
					{
						checkedListBox1.Items.Add(myDataSet.Tables[0].Columns[k]);
		
					}
					//this.label1.Text=Convert.ToString(myDataSet.Tables[0].Rows[0][0]);
					myConn.Close ( ) ; 
					//			dataGrid1.DataMember="[Sheet1$]" ;
					//			dataGrid1.DataSource = myDataSet ;
				}
				catch
				{
					MessageBox.Show("本程序要求Excel 2002及以下版本！");
				}
			}
			
		
		}

		private void Form1_Load(object sender, System.EventArgs e)
		{
			this.menuItem7.Enabled=false;
			
			
			
		}
		public void uplever(System.Windows.Forms.CheckedListBox chklb)
		{
			bool chkJust;
			int indexNum=chklb.SelectedIndex;
			string strtext;
			if(chklb.SelectedItems.Count>0)
			{
				if(indexNum!=0)
				{
					strtext=chklb.Items[indexNum].ToString();
					chkJust=chklb.GetItemChecked(indexNum);
					chklb.Items[indexNum]=chklb.Items[indexNum-1].ToString();
					chklb.SetItemChecked(indexNum,chklb.GetItemChecked(indexNum-1));
					chklb.Items[indexNum-1]=strtext;
					chklb.SetItemChecked(indexNum-1,chkJust);
					chklb.SelectedIndex=chklb.SelectedIndex-1;
				}
			}
		
		}
		public void downlever(System.Windows.Forms.CheckedListBox chklb)
		{
			bool chkJust;
			int indexNum=chklb.SelectedIndex;
			string strtext;
			if( chklb.SelectedItems.Count>0)
			{
				if(indexNum!=chklb.Items.Count-1)
				{
					strtext=chklb.Items[indexNum].ToString();
					chkJust=chklb.GetItemChecked(indexNum);
					chklb.Items[indexNum]=chklb.Items[indexNum+1].ToString();
					chklb.SetItemChecked(indexNum,chklb.GetItemChecked(indexNum+1));
					chklb.Items[indexNum+1]=strtext;
					chklb.SetItemChecked(indexNum+1,chkJust);
					chklb.SelectedIndex=chklb.SelectedIndex+1;
				}
			}
		}

		private void button2_Click(object sender, System.EventArgs e)
		{
			this.uplever(checkedListBox1);
		}

		private void button3_Click(object sender, System.EventArgs e)
		{
			this.downlever(checkedListBox1);
		}

		public void datasourceSet()
		{
			Form2 form2=new Form2();
			form2.ShowDialog();


			if(severName!=null&& datasource!=null&& uidName!=null && uidPwd!=null)
			{
				try
				{
					OracleConnection con1=new OracleConnection("server="+severName+";Data Source="+datasource+";uid="+uidName+";pwd="+uidPwd+"");
					con1.Open();
					string mystrCom1= "select table_name from user_tables";
					OracleDataAdapter myCommand1 = new OracleDataAdapter ( mystrCom1 , con1) ;
					myDataSet1 = new DataSet ( ) ;
					myCommand1.Fill ( myDataSet1) ;
					for(int k=0;k<myDataSet1.Tables[0].Rows.Count;k++)
					{
						this.comboBox1.Items.Add(myDataSet1.Tables[0].Rows[k][0].ToString());
		
					}
					con1.Close();
				
				}
				catch
				{
					DialogResult result=MessageBox.Show(" 数据库链接配置不正确，请重新进行配置！","提示",MessageBoxButtons.OKCancel);
					if(result==DialogResult.OK)
					{
						this.datasourceSet();
					
					}
					else
					{
						Application.Exit();
					}
						
				}            
				this.menuItem7.Enabled=true;
			}
		
		}

		private void button1_Click(object sender, System.EventArgs e)
		{
			this.datasourceSet();

		}

		private void button4_Click(object sender, System.EventArgs e)
		{
			this.uplever(checkedListBox2);
		}

		private void button5_Click(object sender, System.EventArgs e)
		{
			this.downlever(checkedListBox2);
		}
		public void inputData()
		{
			if(checkedListBox1.CheckedItems.Count==checkedListBox2.CheckedItems.Count && checkedListBox1.CheckedItems.Count>0)
			{
                 this.IsDelete();
				this.richTextBox1.Text="";
				//yi2
				strPath=fName;
				string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+strPath+";Extended Properties=Excel 8.0" ;
				OleDbConnection myConn = new OleDbConnection ( strCon ) ;

				string strSeleCmd="select ";
				for (int i = 0; i <checkedListBox1.CheckedItems.Count; i++)
				{
					if(i==checkedListBox1.CheckedItems.Count-1)
					{
						strSeleCmd=strSeleCmd+checkedListBox1.CheckedItems[i].ToString();
					}
					else
					{
						strSeleCmd=strSeleCmd+checkedListBox1.CheckedItems[i].ToString()+",";
					}
			
				
				}
				strSeleCmd=strSeleCmd+" FROM [Sheet1$] ";
				myConn.Open ( ) ;
				OleDbDataAdapter myCommand = new OleDbDataAdapter ( strSeleCmd , myConn ) ;
				myDataSet4= new DataSet ( ) ;
				myCommand.Fill ( myDataSet4 , "[Sheet1$]" ) ;
				rowsNum=myDataSet4.Tables[0].Rows.Count;
				myConn.Close ( ) ; 
		//yi1
				excepNum=0;
				string strInse="insert into "+tableName+"( ";
				for (int i = 0; i <checkedListBox2.CheckedItems.Count;  i++)
				{
					if(i==checkedListBox2.CheckedItems.Count-1)
					{
						strInse=strInse+checkedListBox2.CheckedItems[i].ToString();
					}
					else
					{
						strInse=strInse+checkedListBox2.CheckedItems[i].ToString()+",";
					}
			
				
					//			MessageBox.Show(checkedListBox2.CheckedItems[i].ToString());
				}
				strInse=strInse+")";
				string strCmd=strInse;
				//yuanlai input
			DateTime datetime1=System.DateTime.Now;
				timeStart=datetime1.ToString();
				OracleConnection con=new OracleConnection("server="+severName+";Data Source="+datasource+";uid="+uidName+";pwd="+uidPwd+"");
				con.Open();
				progressBar1.Minimum=0;
				progressBar1.Maximum=rowsNum-1;
				progressBar1.Value=0;
			   
				for(int i=0;i<rowsNum;i++)
				{
                     
					strInse=strCmd+" values(";
					for (int s = 0; s < checkedListBox2.CheckedItems.Count; s++)
					{
						if(s==checkedListBox2.CheckedItems.Count-1)
						{
							strInse=strInse+"'"+myDataSet4.Tables[0].Rows[i][s].ToString()+"'";
						}
						else
						{
							strInse=strInse+"'"+myDataSet4.Tables[0].Rows[i][s].ToString()+"'"+",";
						}
					}


					strInse=strInse+")";
					OracleCommand cmd=new OracleCommand();
					cmd.Connection=con;
					cmd.CommandText=strInse;
					try
					{
						cmd.ExecuteNonQuery();
						progressBar1.Value=i;
						
					}
					catch
					{
						excepNum++;
						this.richTextBox1.Text+= "导入语句："+strInse+"没有成功；\n";
						progressBar1.Value=i;
						if(excepNum>100)
						{
							DialogResult result=MessageBox.Show("Excel表列与Oracle表列类型不符合的记录太多，请检查整理后再导入!","提示",MessageBoxButtons.OK);
							if(result==DialogResult.OK)
							{
								return;
							}
						}
						
					}
				}
				DateTime datetime2=System.DateTime.Now;
				timeEnd=datetime2.ToString();
				timeSum=Convert.ToString(datetime2-datetime1).Substring(0,8);
			
				this.richTextBox1.Text+="导入开始时间:"+timeStart+"；\n";
				this.richTextBox1.Text+="导入结束时间:"+timeEnd+"；\n";
				this.richTextBox1.Text+="导入用时:"+timeSum+"。\n";

				MessageBox.Show("数据已导入,详细情况请看日志");
 
				//		   OracleDataAdapter myCommand = new OracleDataAdapter ("select * from student",con);
				//		   DataSet  myDataSet = new DataSet ( ) ;
				//			myCommand.Fill ( myDataSet , "student" ) ;
				//			dataGrid1.DataMember="student" ;
				//			dataGrid1.DataSource = myDataSet ;
				con.Close();
			}
			else
			{
				MessageBox.Show("请您重新选择Excel或oracle表列！");
			}
			
		}
		public void IsDelete()
		{
			if( this.checkBox1.Checked==true)
			{
				DialogResult result=MessageBox.Show("确定删除数据吗?","选择操作方式",MessageBoxButtons.OKCancel);
				if(result==DialogResult.OK)
				{
					
					if(tableName=="")
					{
						MessageBox.Show("请选择数据库表名！");
					}
					else
					{
						OracleConnection con3=new OracleConnection("server="+severName+";Data Source="+datasource+";uid="+uidName+";pwd="+uidPwd+"");
						con3.Open();
						string deleteStr= "delete from "+tableName+" " ;
						OracleCommand deleteCmd = new OracleCommand(deleteStr,con3);
						deleteCmd.ExecuteNonQuery();			
						con3.Close();
					}
				}
								
					
			}



		
		}

		private void button6_Click(object sender, System.EventArgs e)
		{
			this.inputData();
			

		}
	
             

		private void menuItem4_Click(object sender, System.EventArgs e)
		{
			this.datasourceSet();
		}

		private void menuItem5_Click(object sender, System.EventArgs e)
		{
			this.OpenFile();
		}

		private void menuItem6_Click(object sender, System.EventArgs e)
		{
			Application.Exit();
		}

		private void menuItem8_Click(object sender, System.EventArgs e)
		{
			Form3 form3=new Form3();
			form3.Show();
		}

		private void menuItem9_Click(object sender, System.EventArgs e)
		{
			Form4 form4=new Form4();
			form4.Show();
		}

		private void menuItem7_Click(object sender, System.EventArgs e)
		{
			this.inputData();
		}

		private void button7_Click(object sender, System.EventArgs e)
		{
			
			

			
		}


		public void saveAs()
		{
		
			SaveFileDialog saveFileDialog=new SaveFileDialog(); 
			saveFileDialog.Filter="文本文件|*.txt"; 
			saveFileDialog.FilterIndex=1; 
			saveFileDialog.RestoreDirectory=true; 
			if(saveFileDialog.ShowDialog()==DialogResult.OK) 
			{ 
				
				string fName1=saveFileDialog.FileName; 
				File fSaveAs=new File(fName1); 
				    
				//					isFileHaveName=true; 
				//file://保存的文件有名字 
				fSaveAs.WriteFile(richTextBox1.Text); 
				
			} 
		}

	

		private void groupbox1_Enter(object sender, System.EventArgs e)
		{
		
		}

		private void label1_Click(object sender, System.EventArgs e)
		{
		
		}

		private void comboBox1_SelectedValueChanged(object sender, System.EventArgs e)
		{
			OracleConnection con2=new OracleConnection("server="+severName+";Data Source="+datasource+";uid="+uidName+";pwd="+uidPwd+"");
			con2.Open();
			tableName=this.comboBox1.Text.Trim();
			string mystrCom = "select column_name from user_tab_columns where table_name='"+tableName+"'" ;
			OracleDataAdapter myCommand2 = new OracleDataAdapter ( mystrCom , con2) ;
			myDataSet2 = new DataSet ( ) ;
			myCommand2.Fill ( myDataSet2) ;
			columnNum=myDataSet2.Tables[0].Rows.Count;
			for (int i =checkedListBox2.Items.Count-1; i>=0;i--)
			{
				checkedListBox2.Items.Remove(checkedListBox2.Items[i]);
			}
			for(int k=0;k<myDataSet2.Tables[0].Rows.Count;k++)
			{
				checkedListBox2.Items.Add(myDataSet2.Tables[0].Rows[k][0].ToString());
		
			}
			
			con2.Close();
		
		}

		private void button7_Click_1(object sender, System.EventArgs e)
		{
			Application.Exit();
		}

	}
}