自己写了一个小工具将Excel的数据完整的复制到sql server中，表的结构，字段名一起复制！
很简单不用多说直接贴代码！
Code
using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.OleDb;using System.Data.SqlClient;public partial class _Default : System.Web.UI.Page 
{
public SqlConnection sqlConn = null; public OleDbConnection excelConn = null; protected void Page_Load(object sender, EventArgs e)
{ Label2.Text = "";
} /**////<summary> /// 获取Excel的文件名 /// </summary> private string getFileName(FileUpload FileUpload1) { string fileName = null; if (fileType(FileUpload1)) { fileName = FileUpload1.FileName; } return fileName; } /**////<summary> /// 获取Excel的文件路径 /// </summary> private string getfilePath(FileUpload FileUpload1) { string filePath = null; if (fileType(FileUpload1)) { filePath = FileUpload1.PostedFile.FileName; } return filePath; } /**////<summary> /// 判断Excel的文件类型,以及文件是不是成在 /// 如果的Excel文件即.xls的文件，可以进行转换，如果不是，不能进行转换，提示信息。 /// </summary> private bool fileType(FileUpload FileUpload1) { bool flag = false; string[] allowExtensions ={ ".xls" }; if (FileUpload1.HasFile) { /**/////取得上传文件之扩展文件名，并转换成小写字母 string fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower(); for (int i = 0; i < allowExtensions.Length; i++) { if (fileExtension == allowExtensions[i]) { flag =true; } } } return flag; } /**////<summary> /// 获取Excel表的第一个表的表名 /// Excel文件中第一个表名的缺省值是Sheet1$, 但有时也会被改变为其他名字. /// 如果需要在C#中使用OleDb读写Excel文件, 就需要知道这个名字是什么 /// </summary> private string getFirstTableName() { string tableName = null; string fileName = getFileName(FileUpload1); string filePath = getfilePath(FileUpload1); OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source="+filePath); conn.Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null); tableName = dt.Rows[0][2].ToString().Trim(); conn.Close(); return tableName; } /**////<summary> /// 连接所选择的Excel /// HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES ///</summary> private OleDbConnection getExcelConn() { //string fileName = getFileName(FileUpload1); string filePath = getfilePath(FileUpload1); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=NO; IMEX=1';Data Source=" + filePath; excelConn = new OleDbConnection(strConn); try { excelConn.Open(); } catch (Exception ex) { throw ex; } return excelConn; } /**////<summary> /// /// </summary> private DataSet getExcelDataset() { //getExcelConn(); string tableName = getFirstTableName(); string str = "select * from [" + tableName+"]"; DataSet ExcelDs = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(str,getExcelConn()); try { da.Fill(ExcelDs, tableName); } catch (Exception ex) { throw ex; } return ExcelDs; } /**//// <summary> /// 根据Exce文件结构创建数据表 /// </summary> private void createSqlTable() { int i = 0; string tableName = getFirstTableName(); string fileName = getFileName(FileUpload1); string dataBaseName = fileName.Substring(0, fileName.Length - 4); string sql = "use " + dataBaseName+" "; sql+="create table ["+tableName+ "](" ; DataSet sqlDs=getExcelDataset(); DataTable dt=new DataTable(); dt=sqlDs.Tables[0]; for ( i = 0; i<dt.Columns.Count-1;i++ ) { sql += dt.Rows[0][i]+" nvarchar(50),"; } sql += dt.Rows[0][i]+" nvarchar(50)"+")"; SqlCommand comm = new SqlCommand(sql, getSqlConn()); comm.ExecuteNonQuery(); getClose(); } /**////<summary> ///打开sql连接 /// </summary> private SqlConnection getSqlConn() { string fileName = getFileName(FileUpload1); string dataBaseName = fileName.Substring(0, fileName.Length - 4); string strConn = "Server=localhost;Integrated security=SSPI;database="+dataBaseName; sqlConn = new SqlConnection(strConn ); try { sqlConn.Open(); } catch (Exception ex) { throw ex; } return sqlConn; } private void getClose() { if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); } if (excelConn.State == ConnectionState.Open) { excelConn.Close(); } } /**//// <summary> /// 根据Excel文件的文件名创建sql数据库 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void createSqlDatabase() { string fileName=getFileName(FileUpload1); string dataBaseName = fileName.Substring(0,fileName.Length - 4); existDatabase(); string strConn = "Server=localhost;Integrated security=SSPI;database=master"; SqlConnection conn = new SqlConnection(strConn); string str = "CREATE DATABASE "+dataBaseName+" ON PRIMARY "+ "(NAME=" + dataBaseName+"_data," + " FILENAME = 'C:\\"+dataBaseName+"_data.mdf', " + "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " + "LOG ON (NAME ="+dataBaseName+"_Log, " + "FILENAME = 'C:\\"+dataBaseName+"_Log.ldf', " + "SIZE = 1MB, " + "MAXSIZE = 5MB, " + "FILEGROWTH = 10%)"; string strSql = "ALTER DATABASE " + dataBaseName + " COLLATE Chinese_PRC_90_CI_AS";//设置数据库属性的排序规则 SqlCommand comm = new SqlCommand(str, conn); SqlCommand comma = new SqlCommand(strSql,conn); try { if (conn.State == ConnectionState.Closed) { conn.Open(); } comm.ExecuteNonQuery(); comma.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } } } /**//// <summary> /// 判断数据库是否存在，如果存在将其删除 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void existDatabase() { string fileName = getFileName(FileUpload1); string dataBaseName = fileName.Substring(0, fileName.Length - 4); int i = 0; string strConn = "Server=localhost;Integrated security=SSPI;database=master"; SqlConnection conn = new SqlConnection(strConn); string str= "select count(*) from master.dbo.sysdatabases where name = '" + dataBaseName + "'"; SqlCommand comm=new SqlCommand(str,conn); try { if (conn.State == ConnectionState.Closed) { conn.Open(); } i = Convert.ToInt32(comm.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } } if (i>0) { string strDrop = "drop database " + dataBaseName; SqlCommand commDrop = new SqlCommand(strDrop, conn); try { if (conn.State == ConnectionState.Closed) { conn.Open(); } commDrop.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } } } } /**//// <summary> /// 复制数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void copy() { string tableName = getFirstTableName(); DataSet ds = getExcelDataset(); DataTable dt = new DataTable(); dt = ds.Tables[0]; for (int i = 1; i < dt.Rows.Count; i++) { int j = 0; string sql = "insert into [" + tableName + "] values("; for (j = 0; j < dt.Columns.Count-1; j++) { sql +="'"+ dt.Rows[i][j]+"',"; } sql += "'"+dt.Rows[i][j]+"'"+")"; SqlCommand comm = new SqlCommand(sql, getSqlConn()); comm.ExecuteNonQuery(); getClose(); } } protected void Button1_Click(object sender, EventArgs e) { if (fileType(FileUpload1))//文件成在,并且是,xls文件 { createSqlDatabase(); createSqlTable(); copy(); Label2.Text = "数据转换成功！"; } else Label2.Text = "文件不成在或者该文件不是Excel文件"; }
}
