using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace ExcelToSqlServer
{
///
/// Form1 的摘要说明。
///
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.TextBox txtServer;
private System.Windows.Forms.TextBox txtUid;
private System.Windows.Forms.TextBox txtPwd;
private System.Windows.Forms.TextBox txtDataBase;
private System.Windows.Forms.TextBox txtExcelPath;
private System.Windows.Forms.ProgressBar progressBar1;
private System.Windows.Forms.Button btnYes;
private System.Windows.Forms.Button btnExit;
private System.Windows.Forms.OpenFileDialog openFileDialog1;
///
/// 必需的设计器变量。
///
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();
//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
}
///
/// 清理所有正在使用的资源。
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows 窗体设计器生成的代码
///
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
///
private void InitializeComponent()
{
System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(Form1));
this.button1 = new System.Windows.Forms.Button();
this.groupBox1 = new System.Windows.Forms.GroupBox();
this.txtDataBase = new System.Windows.Forms.TextBox();
this.label4 = new System.Windows.Forms.Label();
this.txtPwd = new System.Windows.Forms.TextBox();
this.label3 = new System.Windows.Forms.Label();
this.txtUid = new System.Windows.Forms.TextBox();
this.label2 = new System.Windows.Forms.Label();
this.txtServer = new System.Windows.Forms.TextBox();
this.label1 = new System.Windows.Forms.Label();
this.txtExcelPath = new System.Windows.Forms.TextBox();
this.progressBar1 = new System.Windows.Forms.ProgressBar();
this.btnYes = new System.Windows.Forms.Button();
this.btnExit = new System.Windows.Forms.Button();
this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
this.groupBox1.SuspendLayout();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(8, 12);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(120, 28);
this.button1.TabIndex = 0;
this.button1.Text = "选择 Excel 数据源";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// groupBox1
//
this.groupBox1.Controls.Add(this.txtDataBase);
this.groupBox1.Controls.Add(this.label4);
this.groupBox1.Controls.Add(this.txtPwd);
this.groupBox1.Controls.Add(this.label3);
this.groupBox1.Controls.Add(this.txtUid);
this.groupBox1.Controls.Add(this.label2);
this.groupBox1.Controls.Add(this.txtServer);
this.groupBox1.Controls.Add(this.label1);
this.groupBox1.Location = new System.Drawing.Point(12, 56);
this.groupBox1.Name = "groupBox1";
this.groupBox1.Size = new System.Drawing.Size(536, 152);
this.groupBox1.TabIndex = 1;
this.groupBox1.TabStop = false;
this.groupBox1.Text = "目的库";
//
// txtDataBase
//
this.txtDataBase.Location = new System.Drawing.Point(80, 116);
this.txtDataBase.Name = "txtDataBase";
this.txtDataBase.Size = new System.Drawing.Size(180, 21);
this.txtDataBase.TabIndex = 7;
this.txtDataBase.Text = "Qzcp2008";
//
// label4
//
this.label4.Location = new System.Drawing.Point(12, 120);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(60, 12);
this.label4.TabIndex = 6;
this.label4.Text = "数据库";
//
// txtPwd
//
this.txtPwd.Location = new System.Drawing.Point(80, 84);
this.txtPwd.Name = "txtPwd";
this.txtPwd.Size = new System.Drawing.Size(180, 21);
this.txtPwd.TabIndex = 5;
this.txtPwd.Text = "";
//
// label3
//
this.label3.Location = new System.Drawing.Point(12, 88);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(60, 16);
this.label3.TabIndex = 4;
this.label3.Text = "密码";
//
// txtUid
//
this.txtUid.Location = new System.Drawing.Point(80, 52);
this.txtUid.Name = "txtUid";
this.txtUid.Size = new System.Drawing.Size(180, 21);
this.txtUid.TabIndex = 3;
this.txtUid.Text = "sa";
//
// label2
//
this.label2.Location = new System.Drawing.Point(12, 56);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(60, 16);
this.label2.TabIndex = 2;
this.label2.Text = "用户";
//
// txtServer
//
this.txtServer.Location = new System.Drawing.Point(80, 24);
this.txtServer.Name = "txtServer";
this.txtServer.Size = new System.Drawing.Size(180, 21);
this.txtServer.TabIndex = 1;
this.txtServer.Text = "(local)";
//
// label1
//
this.label1.Location = new System.Drawing.Point(12, 28);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(64, 16);
this.label1.TabIndex = 0;
this.label1.Text = "服务器";
//
// txtExcelPath
//
this.txtExcelPath.Location = new System.Drawing.Point(136, 16);
this.txtExcelPath.Name = "txtExcelPath";
this.txtExcelPath.Size = new System.Drawing.Size(412, 21);
this.txtExcelPath.TabIndex = 2;
this.txtExcelPath.Text = "";
//
// progressBar1
//
this.progressBar1.Location = new System.Drawing.Point(16, 240);
this.progressBar1.Name = "progressBar1";
this.progressBar1.Size = new System.Drawing.Size(528, 23);
this.progressBar1.TabIndex = 3;
//
// btnYes
//
this.btnYes.Image = ((System.Drawing.Image)(resources.GetObject("btnYes.Image")));
this.btnYes.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
this.btnYes.Location = new System.Drawing.Point(376, 284);
this.btnYes.Name = "btnYes";
this.btnYes.TabIndex = 4;
this.btnYes.Text = "执行";
this.btnYes.Click += new System.EventHandler(this.btnYes_Click);
//
// btnExit
//
this.btnExit.Image = ((System.Drawing.Image)(resources.GetObject("btnExit.Image")));
this.btnExit.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
this.btnExit.Location = new System.Drawing.Point(468, 284);
this.btnExit.Name = "btnExit";
this.btnExit.TabIndex = 5;
this.btnExit.Text = "退出";
this.btnExit.Click += new System.EventHandler(this.btnExit_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(556, 326);
this.Controls.Add(this.btnExit);
this.Controls.Add(this.btnYes);
this.Controls.Add(this.progressBar1);
this.Controls.Add(this.txtExcelPath);
this.Controls.Add(this.groupBox1);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "Excel 导入 SqlServer";
this.Load += new System.EventHandler(this.Form1_Load);
this.groupBox1.ResumeLayout(false);
this.ResumeLayout(false);
}
#endregion
///
/// 应用程序的主入口点。
///
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void button1_Click(object sender, System.EventArgs e)
{
txtExcelPath.Text = GetExcel();
}
private string GetExcel()
{
string strPath = string.Empty;
openFileDialog1.Filter="Excel文件(*.xls)|*.xls";
openFileDialog1.InitialDirectory=@"E:\党员系统资料\";
if(openFileDialog1.ShowDialog() == DialogResult.OK )
{
strPath = openFileDialog1.FileName;
openFileDialog1.RestoreDirectory=true;
}
return strPath;
}
///
/// 操作EXCEL 到数据库
///
private void OpterData()
{
if(txtExcelPath.Text.Trim()=="") return;
string strSql=string.Empty;
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";"+"data source=" + txtExcelPath.Text;
strSql = "Select * From [所学专业对应$]";//籍贯//新旧对应代码表-组织//新旧对应代码表-人员
OleDbConnection con1 = null;
try
{
con1 = new OleDbConnection(connStr);
OleDbDataAdapter pter1 = new OleDbDataAdapter(strSql,con1);
con1.Open();
DataTable dtb1 = new DataTable();
DataSet ds = new DataSet();
pter1.Fill(ds);
dtb1=ds.Tables[0];
SaveToSql cls1 = new SaveToSql();
cls1.Server = txtServer.Text;
cls1.DataBase = txtDataBase.Text;
cls1.Uid = txtUid.Text;
cls1.Pwd = txtPwd.Text;
int i=0;
progressBar1.Value =0;
progressBar1.Maximum = dtb1.Rows.Count;
foreach(DataRow drw in dtb1.Rows)
{
cls1.strNewCodeId = drw[0].ToString();
cls1.strNewCode = drw[1].ToString();
cls1.strNewDes = drw[2].ToString();
cls1.strOldCodeId = drw[3].ToString();
cls1.strOldCode = drw[4].ToString();
cls1.strOldDes = drw[5].ToString();
cls1.TableNames = "GPMS_Code_Psn"; //目的表名
cls1.Save();
i++;
progressBar1.Value=i;
}
MessageBox.Show("导入成功！");
}
catch(Exception Ex)
{
MessageBox.Show(Ex.Message);
}
finally
{
con1.Dispose();
}
}
private void btnExit_Click(object sender, System.EventArgs e)
{
this.Close();
}
private void btnYes_Click(object sender, System.EventArgs e)
{
OpterData();
}
private void Form1_Load(object sender, System.EventArgs e)
{
}
}
#region 保存数据类
///
/// 保存数据类
///
public class SaveToSql
{
public string strNewCodeId = string.Empty;
public string strNewCode = string.Empty;
public string strNewDes = string.Empty;
public string strOldCodeId = string.Empty;
public string strOldCode = string.Empty;
public string strOldDes = string.Empty;
public string Server = string.Empty;
public string DataBase = string.Empty;
public string Uid = string.Empty;
public string Pwd = string.Empty ;
private string TableName = string.Empty;
///
/// 目的表的名称
///
public string TableNames
{
get{return TableName;}
set{TableName=value;}
}
public SaveToSql()
{
}
///
/// 保存数据
///
public void Save()
{
string strConPath = string.Format("server={0};database={1};uid={2};pwd={3}",this.Server,this.DataBase,this.Uid,this.Pwd);
SqlConnection con1 = new SqlConnection(strConPath);
con1.Open();
SqlCommand com1 = new SqlCommand();
com1.CommandText = "Insert into "+TableName+"(newCodeId,newCode,newDes,oldCodeId,oldCode,oldDes) Values('"+strNewCodeId+"','"+strNewCode+"','"+strNewDes+"','"+strOldCodeId+"','"+strOldCode+"','"+strOldDes+"')";
com1.Connection=con1;
com1.ExecuteNonQuery();
con1.Dispose();
}
}
#endregion
}
|