sql server Excel 数据导入

string fileName = FileUpload1.PostedFile.FileName;

string FileName = DateTime.Now.ToString("yyyyMMddHHmmssffff") + fileName.Substring(fileName.LastIndexOf("//") + 1);

string Xname = fileName.Substring(fileName.LastIndexOf('.') + 1);

string SaveFile = Server.MapPath("UploadExcel/") + FileName;

string TableName = DropDow.SelectedValue;

string ExcelName = this.excel.Value.Trim() + "$";

string ColumnsName = string.Empty;

if (Xname != "xls")

{

Page.ClientScript.RegisterStartupScript(this.GetType(), "1", "<mce:script type="text/javascript"><!--

showE();

// --></mce:script> ");

return;

}

else

{

FileUpload1.SaveAs(SaveFile);

Page.ClientScript.RegisterStartupScript(this.GetType(), "2e", "<mce:script type="text/javascript"><!--

IsOver2();

// --></mce:script>");

ColumnsName = ReColumnName(TableName);

// string FilePath = Request.UrlReferrer.Authority + "//UploadExcel//" + FileName;

TranData(TableName, SaveFile, ExcelName, ColumnsName);

}

}

public void TranData(string tableName, string filePath, string excelName, string clumnsName)

{

try

{

string str = "insert into " + tableName + " select "+clumnsName +" from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0; HDR=no; IMEX=1;DATABASE=" + filePath + "'," + excelName + ") where f1 not in(select top 1 f1 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' , 'Excel 5.0; HDR=no; IMEX=1;DATABASE=" + filePath + "'," + excelName + "))";

if (DbHelperSQL.ExecuteSql (str) > 0)

{

Page.ClientScript.RegisterStartupScript(this.GetType(), "2s", "<mce:script type="text/javascript"><!--

IsOver();

// --></mce:script>");

try

{

QJY.Model.Users umd = (QJY.Model.Users)Session["currentuser"];

//记录日志

QJY.BLL.EN_Log LogBll = new QJY.BLL.EN_Log();

QJY.Model.EN_Log LogMoel = new QJY.Model.EN_Log();

LogMoel.Log_ID = LogBll.GetMaxId();

LogMoel.Log_User = umd.Userid;

LogMoel.Log_Even = "导入数据到" + DropDow.SelectedItem.Text.Trim();

LogMoel.Log_Date = DateTime.Parse(DateTime.Now.ToString());

LogBll.Add(LogMoel);

}

catch (Exception e)

{

}

}

}

catch(Exception ex)

{

Page.ClientScript.RegisterStartupScript(this.GetType(), "3", "<mce:script type="text/javascript"><!--

show();

// --></mce:script>");

}

}

protected string ReColumnName( string TableName)

{

string NameList = string.Empty;

string Count= DbHelperSQL.GetSingle ("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + TableName + "'").ToString ();

int ColumnsCount = Convert.ToInt32(Count);

for (int i = 0; i <ColumnsCount ; i++)

{

int j = i+1;

NameList += "F" + j +",";

}

if (NameList.IndexOf(',') > 0)

{

NameList = NameList.Substring(0, NameList.Length - 1);

}

return NameList;

}

首先将excel上传到数据库服务器