?? form1.cs
字號:
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(" 數(shù)據(jù)庫鏈接配置不正確，請重新進行配置！","提示",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+="導入結(jié)束時間:"+timeEnd+"；\n";
this.richTextBox1.Text+="導入用時:"+timeSum+"。\n";
MessageBox.Show("數(shù)據(jù)已導入,詳細情況請看日志");
// 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("確定刪除數(shù)據(jù)嗎?","選擇操作方式",MessageBoxButtons.OKCancel);
if(result==DialogResult.OK)
{
if(tableName=="")
{
MessageBox.Show("請選擇數(shù)據(jù)庫表名！");
}
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();
}
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -