自己写了一个小工具将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;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
public partial class _Default : System.Web.UI.Page
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public SqlConnection sqlConn = null;
public OleDbConnection excelConn = null;
protected void Page_Load(object sender, EventArgs e)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Label2.Text = "";
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////<summary>
/// 获取Excel的文件名
/// </summary>
private string getFileName(FileUpload FileUpload1)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string fileName = null;
if (fileType(FileUpload1))
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
fileName = FileUpload1.FileName;
}
return fileName;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////<summary>
/// 获取Excel的文件路径
/// </summary>
private string getfilePath(FileUpload FileUpload1)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string filePath = null;
if (fileType(FileUpload1))
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
filePath = FileUpload1.PostedFile.FileName;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
return filePath;
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////<summary>
/// 判断Excel的文件类型,以及文件是不是成在
/// 如果的Excel文件即.xls的文件,可以进行转换,如果不是,不能进行转换,提示信息。
/// </summary>
private bool fileType(FileUpload FileUpload1)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
bool flag = false;
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string[] allowExtensions =
{ ".xls" };
if (FileUpload1.HasFile)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**/////取得上传文件之扩展文件名,并转换成小写字母
string fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
for (int i = 0; i < allowExtensions.Length; i++)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (fileExtension == allowExtensions[i])
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
flag =true;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
return flag;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////<summary>
/// 获取Excel表的第一个表的表名
/// Excel文件中第一个表名的缺省值是Sheet1$, 但有时也会被改变为其他名字.
/// 如果需要在C#中使用OleDb读写Excel文件, 就需要知道这个名字是什么
/// </summary>
private string getFirstTableName()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////<summary>
/// 连接所选择的Excel
/// HDR=Yes,这代表第一行是标题,不做为数据使用 ,如果用HDR=NO,则表示第一行不是标题,做为数据来使用。系统默认的是YES
///</summary>
private OleDbConnection getExcelConn()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//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
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
excelConn.Open();
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
throw ex;
}
return excelConn;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////<summary>
///
/// </summary>
private DataSet getExcelDataset()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//getExcelConn();
string tableName = getFirstTableName();
string str = "select * from [" + tableName+"]";
DataSet ExcelDs = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(str,getExcelConn());
try
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
da.Fill(ExcelDs, tableName);
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
return ExcelDs;
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Exce文件结构创建数据表
/// </summary>
private void createSqlTable()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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++ )
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sql += dt.Rows[0][i]+" nvarchar(50),";
}
sql += dt.Rows[0][i]+" nvarchar(50)"+")";
SqlCommand comm = new SqlCommand(sql, getSqlConn());
comm.ExecuteNonQuery();
getClose();
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**////<summary>
///打开sql连接
/// </summary>
private SqlConnection getSqlConn()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sqlConn.Open();
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return sqlConn;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
private void getClose()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (sqlConn.State == ConnectionState.Open)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sqlConn.Close();
}
if (excelConn.State == ConnectionState.Open)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
excelConn.Close();
}
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Excel文件的文件名创建sql数据库
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void createSqlDatabase()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (conn.State == ConnectionState.Closed)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open();
}
comm.ExecuteNonQuery();
comma.ExecuteNonQuery();
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
throw ex;
}
finally
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (conn.State == ConnectionState.Open)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Close();
}
}
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 判断数据库是否存在,如果存在将其删除
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void existDatabase()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (conn.State == ConnectionState.Closed)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
i = Convert.ToInt32(comm.ExecuteScalar());
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (conn.State == ConnectionState.Open)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Close();
}
}
if (i>0)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strDrop = "drop database " + dataBaseName;
SqlCommand commDrop = new SqlCommand(strDrop, conn);
try
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (conn.State == ConnectionState.Closed)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open();
}
commDrop.ExecuteNonQuery();
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
throw ex;
}
finally
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (conn.State == ConnectionState.Open)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Close();
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
}
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 复制数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void copy()
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string tableName = getFirstTableName();
DataSet ds = getExcelDataset();
DataTable dt = new DataTable();
dt = ds.Tables[0];
for (int i = 1; i < dt.Rows.Count; i++)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int j = 0;
string sql = "insert into [" + tableName + "] values(";
for (j = 0; j < dt.Columns.Count-1; j++)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sql +="'"+ dt.Rows[i][j]+"',";
}
sql += "'"+dt.Rows[i][j]+"'"+")";
SqlCommand comm = new SqlCommand(sql, getSqlConn());
comm.ExecuteNonQuery();
getClose();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
protected void Button1_Click(object sender, EventArgs e)
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (fileType(FileUpload1))//文件成在,并且是,xls文件
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
createSqlDatabase();
createSqlTable();
copy();
Label2.Text = "数据转换成功!";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
else
Label2.Text = "文件不成在或者该文件不是Excel文件";
}
}