1 注意:上传控件上传的文件名,在IE浏览器中设置,必须是全路径 。设置方法((IE浏览器)工具→Internet选项→安全→自定义级别→将文件上载到服务器时包含本地目录路径→启用即可。
)
//excel数据源 path为excel的路径
private DataTable GetDataFromExcelWithAppointSheetName(string path)
{
//连接串
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
// string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'"; //上面为excel2003 注释的为excel2007的方法
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null,null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
conn.Close();
conn.Dispose();
//this.GridView1.DataSource = dt;
//this.GridView1.DataBind();
return dt;
}
2 //插入数据 dt为获取的excel,dataname为数据库名字
protected bool InsertSQLServer(DataTable dt, string dataname)
{
string strCon = @"Server=.;Integrated Security=true;";//无数据库名连接
string strTest = "Users";//表的名字
try
{
SqlConnection con = new SqlConnection(strCon);//创建数据库
con.Open();
string strSQL = "IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=N'" + dataname + "') CREATE DATABASE [" + dataname + "]";
SqlCommand command = new SqlCommand(strSQL, con);
command.ExecuteNonQuery();
//创建数据库表
strSQL = "USE[" + dataname + "] IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[DBO].[" + strTest + "]') AND type in (N'U')) CREATE TABLE " + strTest + " (";
string strColumn = string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL += "[" + dt.Columns[i].ColumnName + "] [nvarchar](256),";
strColumn += dt.Columns[i].ColumnName + ",";
}
strSQL += " )";
SqlCommand newcom = new SqlCommand(strSQL, con);
newcom.ExecuteNonQuery();
//插入数据
strColumn = strColumn.Substring(0, strColumn.Length - 1);
for (int i = 0; i < dt.Rows.Count; i++)//将i=0改为i=1可以再第二行加中文注释
{
strSQL = "USE[" + dataname + "] Insert into " + strTest + " (" + strColumn + ") values (";
for (int k = 0; k < dt.Columns.Count; k++)
{
strSQL += "'" + dt.Rows[i][k].ToString() + "',";
}
strSQL = strSQL.Substring(0, strSQL.Length - 1);
strSQL += ")";
SqlCommand insertCom = new SqlCommand(strSQL, con);
insertCom.ExecuteNonQuery();
//加一个session["no"]=dt.Rows.Count.toString();可以知道导入几条(注意要减1)
}
return true;
}
catch
{
return false;
}
}
3 插入按钮单击事件代码:
string dataname = "ClManage";//数据库名字
if (FuloadExcelFile.FileName == "")//FuloadExcelFile是上传控件FileUpload
{
return;
}
else
{
string fileExt = System.IO.Path.GetExtension(FuloadExcelFile.FileName);
if (fileExt != ".xls")//必须是EXCEL文件
{
return;
}
else
{
string filepath = FuloadExcelFile.PostedFile.FileName;//文件路径,FuloadExcelFile为上传文件的控件
DataTable dt = new DataTable();
dt = GetDataFromExcelWithAppointSheetName(filepath);
if (InsertSQLServer(dt, dataname))//导入数据库
{
Response.Write("成功");
}
else
{
Response.Write("失败!");
}
}
}