将EXCEL表数据导入到数据库中

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace ConsoleApplication11
{
    class Program
    {
        static void Main(string[] args)
        {

            DataSet ds = CreateTables(@"Z:\C\2016年.xls");
            foreach (DataTable table in ds.Tables)
            {
                PopulateData(table);
            }
            PressQToExist();

        }

        private static void PopulateData(DataTable dt)
        {

            string connStr = "Data Source=.; initial catalog =test; integrated security=true; ";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                
                foreach (DataRow row in dt.Rows)
                {
                    SqlCommand sqlCmd = conn.CreateCommand();
                    StringBuilder cmd = new StringBuilder();
                    cmd.Append(string.Format(" INSERT INTO {0} ",dt.TableName));
                    cmd.Append(" VALUES ");
                    cmd.Append(" ( ");
                    int paramIndex = 0;
                    foreach (DataColumn col in dt.Columns)
                    {
                        
                        string columnValue = row[col.ColumnName].ToString();
                        cmd.Append(string.Format("@param{0},",paramIndex));
                        sqlCmd.Parameters.Add(new SqlParameter(string.Format("@param{0}", paramIndex++), columnValue));
                    }
                    string cmd_temp =  cmd.ToString().TrimEnd(',');
                    string cmd_final  = cmd_temp + " ) ";
                    
                    sqlCmd.CommandText = cmd_final;
                    sqlCmd.ExecuteNonQuery();
                }  
            }
        }

        public static DataSet CreateTables(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            DataTable excelSchema  = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            List<string> tableName = new List<string>();
            foreach (DataRow row in excelSchema.Rows)
            {
                string tempTableName = row["TABLE_NAME"].ToString();
                if (tempTableName.EndsWith("$"))
                {
                    tableName.Add(tempTableName.TrimEnd('$'));
                }
            }
            ds = new DataSet();
            foreach (string tn in tableName)
            {
                strExcel = string.Format("select * from [{0}$]",tn);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, tn);
            }

            foreach(DataTable dt in ds.Tables)
            {
                CreateTable(dt);
            }
            return ds;
        }

        public static void CreateTable(DataTable dt)
        {
            StringBuilder cmd = new StringBuilder();

            cmd.Append(string.Format("CREATE TABLE [dbo].[{0}]", dt.TableName));
            cmd.Append("(");
            foreach (DataColumn col in dt.Columns)
            {
                cmd.Append(string.Format("[{0}] [nvarchar](256) NOT NULL,", col.ColumnName));
            }

            string cmd_final = cmd.ToString().TrimEnd(',') + ")";

            string connStr = "Data Source=.; initial catalog =test; integrated security=true; ";
            using(SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlCommand sqlCmd = conn.CreateCommand();
                sqlCmd.CommandText = cmd_final;
                sqlCmd.ExecuteNonQuery();
            }
        }

        static void PressQToExist()
        {
            Console.WriteLine("Press Q to exist.");
            ConsoleKey key;
            do
            {
                key = Console.ReadKey().Key;
            } while (key != ConsoleKey.Q);
        }
    }
}