将Excel文件导入到sql2008

直接贴代码了，写的比较详细，有问题大家一起研究：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Excel导入到Sql
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        string SqlStr = "";
        //txtDateBaseName用来自己定义数据库的名称
        string DBName = "";
        //选择excel文件，将文件路径存放并显示在textbox1中
        private void button1_Click(object sender, EventArgs e)
        {
            txtState.Text = "正在等待操作...";
            openFileDialog1.ShowDialog();
            this.txtFilePath.Text = openFileDialog1.FileName;
        }
        //建立数据集
        private DataSet ExcelToDataSet(string opnFileName)
        {  
            //如果HDR=YES那么第一行将被作为字段名,为了创建表，这里不将其作为字段名
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + opnFileName + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = new DataSet();
            strExcel = "select * from [sheet1$]";
            try
            {
                conn.Open();
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, "dtSource");
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入出错：" + ex, "错误信息");
                return ds;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        //将excel文件导入到sql中
        private void button2_Click(object sender, EventArgs e)
        {
        
            if (txtTableName.Text  == ""||txtDateBaseName.Text=="")
            {
                MessageBox.Show("请确保数据库名和表明都写入了信息！！");
                return;
            }
          
            DataSet ds = new DataSet();
            try
            {
                //从所选择的文件存放到数据集中
                ds = ExcelToDataSet(this.txtFilePath.Text.Trim());
                string insertsql = "";
                //计算出行于列的大小以便读取操作
                int column = ds.Tables[0].Columns.Count;
                int row = ds.Tables[0].Rows.Count;
                //自定义数据库名称
                DBName = txtDateBaseName.Text;
                //判断这个库是否存在
                string ifexit = "SELECT COUNT(*) FROM SYSDATABASES WHERE NAME="+"'"+DBName+"'";
                string exitornot=GetValue(ifexit);
                //如果数据库存在则删除
                if (exitornot == "1")
                {
                    string deleteDB = "DROP DATABASE " + DBName;
                    if (MessageBox.Show("您输入的库已经存在要删除吗？", "删除库", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
                    {
                        DoSql_CreateDB(deleteDB);
                        txtState.Text = "删除已存在的数据库：" + DBName;
                    }
                    else
                    return;
                }
                //生产创建数据库命令
                string CreateDB = "CREATE DATABASE "+DBName;
                //创建一个数据库
                DoSql_CreateDB(CreateDB);

                //txtTableName用来自己定义表的名称
                string TableName = txtTableName.Text;
                //这里定义数据的类型，当然也可以自己定义其他类型
                string DateType="varchar(50)";
                //创建一个表，开始拼字符串
                string CreateTable = "CREATE TABLE" +"  " +txtTableName.Text + " (";
                string table = "";
                //拼接字符串用来生成表
                for (int i = 0; i < column; i++)
                {
                    if(i!=column-1)
                    table += "["+ds.Tables[0].Rows[0][i].ToString().Trim() +"]"+ " " + DateType + ",";
                    else
                    table += ds.Tables[0].Rows[0][i].ToString().Trim() + " " + DateType;

                }
                CreateTable += table + ")";
                //执行sql语句，生成一个表
                DoSql(CreateTable);
                //读取数据集中的值将每一条值插入到数据库中的表里面
                for (int i = 1; i < row; i++)
                {
                    string sql = "insert into " + TableName + " values('";
                    string columnsql = "";
                    for (int j =0; j < column-1; j++)
                    {
                        columnsql += ds.Tables[0].Rows[i][j].ToString().Trim() + "','";
                    }
                    columnsql += ds.Tables[0].Rows[i][column - 1].ToString().Trim();
                    //拼接字符串
                    sql += columnsql + "')";
                    //插入到数据库中
                    DoSql(sql);
                    //insertsql是把所有的sql语句放在一起，我这里没有这么干，是单条插入
                    insertsql += sql + ",";
                   // DoSql(insertsql);
                }
                if (insertsql != "")
                {
                    txtState.Text = "导入DataSet成功,生产了相应的sql语句!";
                }
                MessageBox.Show("导入到数据库成功！");
                //  在textbox2中显示导入数据库成功的字样
                txtState.Text = "导入数据库成功！！";
                pictureBox1.Visible = true;
              

             }
                  catch (Exception ex)
                     {
                         txtState.Text = "导入数据出错了！！";
                         MessageBox.Show("非法操作："+ex);
                     }
                   finally
                      {
                        
                      }

        }
        //显示导入到数据集中的信息
        private void button3_Click(object sender, EventArgs e)
        {
            DataSet ds = ExcelToDataSet(this.txtFilePath.Text.Trim());
            this.dataGridView1.DataSource = ds.Tables[0];
        }
        //封装一个函数用来执行sql语句的函数
        private void DoSql(string sql)
        {
            string CnnString = "Data Source=DGUO3;Initial Catalog=" + DBName + ";Persist Security Info=True;User ID=sa;Password=[e-mail]";
            SqlConnection conn = new SqlConnection(CnnString);
            conn.Open();//打开数据库
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();//
            conn.Close();//关闭数据库
        }
        //专门用来创建数据库
        private void DoSql_CreateDB(string sql)
        {
            string CnnString = "Data Source=DGUO3;Initial Catalog=" + "master" + ";Persist Security Info=True;User ID=sa;Password=[e-mail]";
            SqlConnection conn = new SqlConnection(CnnString);
            conn.Open();//打开数据库
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.ExecuteNonQuery();//
            conn.Close();//关闭数据库
        }
        //从数据库中取得返回值，以判断库或者表是否存在
        public string GetValue(string SqlStr)
        {
            string Sql = SqlStr;
            string CnnString = "Data Source=DGUO3;Initial Catalog=" + "master" + ";Persist Security Info=True;User ID=sa;Password=[e-mail]";
            SqlConnection conn = new SqlConnection(CnnString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(Sql, conn);
            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    Sql = Convert.ToString(cmd.ExecuteScalar());
                }
            }
            catch (SqlException e)
            {
                MessageBox.Show(e.Message);
            }
            conn.Dispose();
            conn.Close();
            return Sql;
        }

        private void pictureBox1_Click(object sender, EventArgs e)
        {
            pictureBox1.Visible = false;
        }
      

        }

    }