将Excel数据导入到数据库，用winfrom系统，如图：
该系统可以选择导入的Excel文件，设置要导入的数据库的基本设置。
代码：
winfrom窗体：
public partial class ExceldaoSql : Form{Sqlconnn sqlcon = new Sqlconnn();public ExceldaoSql(){InitializeComponent();}string str_Excel_Path;private void ExceldaoSql_Load(object sender, EventArgs e){txt_Server.Text = "(local)";//cbox_Server.Text = "machine";cbox_Server.DropDownStyle = ComboBoxStyle.DropDownList;DataTable dt = sqlcon.f1();if (dt != null){if (dt.Rows.Count != 0){for (int i = 0; i < dt.Rows.Count; i++){cbox_Server.Items.Add(dt.Rows[i][0].ToString().Trim());//向控件中添加数据 }}}ckbox_Windows.Checked = true;txt_Name.Enabled = false;//禁用按钮txt_Pwd.Enabled = false; }//选择多个Excel文件private void button1_Click(object sender, EventArgs e){//只能打开一个文件------------------------------------------openFileDialog1.Filter = "Excel文件|*.xlsx";//设置打开文件筛选器 openFileDialog1.Title = "打开Excel文件";//设置打开文件标题openFileDialog1.Multiselect = true;//允许选中多个文件if (openFileDialog1.ShowDialog() == DialogResult.OK)//判断是否选择了文件{str_Excel_Path = openFileDialog1.FileName.ToString();//获取选择的文件地址txt_Path.Text = str_Excel_Path;//在textBox1中显示选择的文件地址 } }//获取SQL Server服务器上的所有数据库信息 private void button2_Click(object sender, EventArgs e){cbox_Server.Items.Clear(); DataTable dt = sqlcon.f1();if (dt != null){if (dt.Rows.Count != 0){for (int i = 0; i < dt.Rows.Count; i++){cbox_Server.Items.Add(dt.Rows[i][0].ToString().Trim());//向控件中添加数据 }}}}//将选择的Excel表导入到SQL Server数据库中private void button3_Click(object sender, EventArgs e){if (txt_Path.Text != ""){string[] P_str_Names = txt_Path.Text.Split(',');//存储所有选择的Excel文件名string P_str_Name = "";//储存遍历到的Excel文件名 List<string> P_list_SheetNames = new List<string>();//创建泛型集合对象，用来存储工作表名称for (int i = 0; i < P_str_Names.Length; i++)//遍历所有选择的Excel文件名{P_str_Name = P_str_Names[i];//记录遍历到的Excel文件名 P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表{if (ckbox_Windows.Checked)//用Windows身份验证登录SQL Server //将工作表内容导出到SQL Server{ImportDataToSql(P_str_Name, P_list_SheetNames[j], "Data Source='" + txt_Server.Text + "';Initial Catalog='" + cbox_Server.Text + "';Integrated Security=True;");}else if (ckbox_SQL.Checked)//用SQL Server身份验证登录SQL Server {ImportDataToSql(P_str_Name, P_list_SheetNames[j], "Data Source='" + txt_Server.Text + "'Database='" + cbox_Server.Text + "';Uid='" + txt_Name.Text + "';Pwd='" + txt_Pwd.Text + "';");}}}MessageBox.Show("已经将所有选择的Excel工作表导入到了SQL Server数据库中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }else{MessageBox.Show("请选择需要导入数据库的文件！");} }//获取Excel文件中的所有工作表名称private List<string> GetSheetName(string P_str_Name){List<string > P_list_SheetName=new List<string> ();//创建泛型集合对象//连接Excel数据库//OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + P_str_Name + ";Extended Properties=Excel 8.0;");OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + P_str_Name + ";Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");olecon.Open();//打开数据库连接System.Data.DataTable DTable = olecon.GetSchema("Tables");//创建表对象DataTableReader DTReader = new DataTableReader(DTable);//创建表读取对象while (DTReader.Read()){string p_str_sName=DTReader ["Table_Name"].ToString ().Replace ('$',' ').Trim ();//记录工作表名称if (!P_list_SheetName.Contains(p_str_sName))//判断泛型集合是否已经存在该工作表名称P_list_SheetName.Add(p_str_sName);//将工作表加入到泛集合中}DTable =null;//清空表对象DTReader =null ;//清空表读取对象olecon .Close ();//关闭数据库连接return P_list_SheetName ;}/*将Excel中指定工作表内容导入SQL Server数据库中*/public void ImportDataToSql(string p_str_Excel,string p_str_SheetName,string p_str_SqlCon){DataSet myds = new DataSet();//创建数据集对象try{//获得全部数据//string P_str_OledbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p_str_Excel + ";Extended Properties=Excel 8.0;";string P_str_OledbCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + p_str_Excel + ";Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"";OleDbConnection oledbcon = new OleDbConnection(P_str_OledbCon);//创建Oledb数据库连接对象string p_str_ExcelSql = "";//记录要执行的Excel查询语句OleDbDataAdapter oledbda = null;//创建Oledb数据桥接器对象p_str_ExcelSql = string.Format("select * from [{0}$]",p_str_SheetName);//记录要执行的Excel查询语句oledbda = new OleDbDataAdapter(p_str_ExcelSql, P_str_OledbCon);//使用数据桥接器执行Excel查询oledbda.Fill(myds, p_str_SheetName);//填充数据//定义变量，用来记录创建表的SQL语句string P_str_CreateSql = string.Format("create table {0}(", p_str_SheetName);foreach (DataColumn c in myds .Tables [0].Columns )//遍历数据集中的所有行{P_str_CreateSql += string.Format("[{0}]text,", c.ColumnName);//在表中创建字段}P_str_CreateSql = P_str_CreateSql + ")";//完善创建表的SQL语句//创建SQL数据库连接对象using (SqlConnection sqlcon=new SqlConnection (p_str_SqlCon )){sqlcon.Open();//打开数据库连接SqlCommand sqlcmd = sqlcon.CreateCommand();//创建执行命令对象mandText = P_str_CreateSql;//指定要执行的SQL数据sqlcmd.ExecuteNonQuery();//执行操作sqlcon.Close();//关闭数据库连接}using (SqlBulkCopy bcp = new SqlBulkCopy(p_str_SqlCon))//用bcp导入数据{bcp.BatchSize = 100;//每次传输的行数bcp.DestinationTableName = p_str_SheetName;//定义目标表bcp.WriteToServer(myds.Tables[0]);//将数据写入SQL server数据表}}catch{MessageBox.Show("SQL Server 数据库中已经存在" + p_str_SheetName + "表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);}}private void ckbox_Windows_CheckedChanged(object sender, EventArgs e){if (ckbox_Windows.CheckState == CheckState.Checked)//当选择Windows身份验证{ckbox_SQL.Checked = false ;//SQL Server身份验证不能选中//txt_Name.ReadOnly = true;//设为只读//txt_Pwd.ReadOnly = true;txt_Name.Enabled = false;//禁用按钮txt_Pwd.Enabled = false;txt_Name.Text = "";txt_Path.Text = "";txt_Path.Text = str_Excel_Path;//在textBox1中显示选择的文件地址 }else{ckbox_SQL.Checked = true ;//txt_Name.ReadOnly = false ;//设为只读//txt_Pwd.ReadOnly = false;txt_Name.Enabled = true;//启用按钮txt_Pwd.Enabled = true;txt_Name.Text = "sa";txt_Path.Text = "";txt_Path.Text = str_Excel_Path;//在textBox1中显示选择的文件地址 }}private void ckbox_SQL_CheckedChanged(object sender, EventArgs e){if (ckbox_SQL.CheckState == CheckState.Checked){ckbox_Windows.Checked = false;//txt_Name.ReadOnly = false;//设为只读//txt_Pwd.ReadOnly = false;txt_Name.Enabled = true ;//启用按钮txt_Pwd.Enabled = true;txt_Name.Text = "sa";txt_Path.Text = "";txt_Path.Text = str_Excel_Path;//在textBox1中显示选择的文件地址 }else{ckbox_Windows.Checked = true ;//txt_Name.ReadOnly = true;//设为只读//txt_Pwd.ReadOnly = true;txt_Name.Enabled = false;//禁用按钮txt_Pwd.Enabled = false;txt_Name.Text = "";txt_Path.Text = "";txt_Path.Text = str_Excel_Path;//在textBox1中显示选择的文件地址 }}}
连接数据库的类：Sqlconnn
class Sqlconnn{private static string constr = "server=(local);Initial Catalog=D_total;Integrated Security=True";// private static string constr = "Data Source =192.168.1.201;Initial Catalog=D_total23 ;User Id=sa;Password=123";public DataTable f1(){string A = "select name from master..sysdatabases";//查询本数据库信息return Only_Table1(A);}public DataTable Only_Table1(string exec){System.Data.DataTable dt_jdl = new DataTable();try{using (SqlConnection con = new SqlConnection(constr)){if (con.State == ConnectionState.Closed){con.Open();} if (con.State == ConnectionState.Open || con.State == ConnectionState.Connecting){SqlDataAdapter sda2 = new SqlDataAdapter(exec, con);//全部通過寫存儲過程即可DataSet ds2 = new DataSet();sda2.Fill(ds2, "cxq");dt_jdl = ds2.Tables["cxq"];sda2.Dispose();ds2.Dispose();}con.Close();}return dt_jdl;}catch (Exception EX){return null;}}}
系统优点：可以导入Excel的多个工作表的数据
系统有一个缺陷：无法将相同的表导入数据库多次，也就是说只能导入数据库一次，无法覆盖和添加。
源码：/download/qq_42711010/79581822
