--EXCEL表导入SQL,创建一个新表(HDR=yes or no 表示是否使用EXCEL表中首行做为字段名) select * into utable2 fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=NO;DATABASE=d:\a.xls',a$) --EXCEL表导入SQL,再原有表基础上(HDR=yes or no 表示是否使用EXCEL表中首行做为字段名) insert into utable3 select * fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=NO;DATABASE=d:\b.xls',sheet1$)写成存储过程 路径传参
select * into utable2 fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=NO;DATABASE=d:\a.xls',a$)
--EXCEL表导入SQL,再原有表基础上(HDR=yes or no 表示是否使用EXCEL表中首行做为字段名)
insert into utable3 select * fromOPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=NO;DATABASE=d:\b.xls',sheet1$)写成存储过程 路径传参
insert into AA(字段名)
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\ab.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
这是操作Excel文件的类,里面只给了获得DataSet的方法,其它的方法需要的话自己加上去吧。public class ExcelDB
{
protected OleDbConnection Connection;
protected string ConnectionString; /// <summary>
/// 构造函数
/// </summary>
/// <param name="_fileName">数据库的完全路径</param>
public ExcelDB(string _fileName)
{
//数据库连接串
ConnectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", _fileName);
} /// <summary>
/// 保护方法,打开数据库连接
/// </summary>
private void Open()
{
//判断数据库连接是否存在
if (Connection == null)
{
//不存在,新建并打开
Connection = new OleDbConnection(ConnectionString);
Connection.Open();
}
else
{
//存在,判断是否处于关闭状态
if (Connection.State.Equals(ConnectionState.Closed))
Connection.Open(); //连接处于关闭状态,重新打开
}
} /// <summary>
/// 公有方法,关闭数据库连接
/// </summary>
public void Close()
{
if (Connection.State.Equals(ConnectionState.Open))
{
Connection.Close(); //连接处于打开状态,关闭连接
}
} /// <summary>
/// 获取Excel中数据,用DataSet保存
/// </summary>
/// <param name="XSqlString"></param>
/// <param name="table"></param>
/// <returns></returns>
public DataSet GetDataSetExcel(string XSqlString, string table)
{
DataSet Ds = new DataSet();
string table_name = "[" + table + "$]";
try
{
Open();//打开数据库
OleDbDataAdapter Adapter = new OleDbDataAdapter(XSqlString, Connection);
Adapter.Fill(Ds, table_name);
}
catch (Exception ex)
{
throw ex;
}
Close();//关闭连接
return Ds;
} }
private void UpExcel()//获取Excel数据,并存入数据库
{
DataBase DB = new DataBase(); //连接数据库的类
DataSet myDataSet = new DataSet();
string strSql = "";
string strTable = "Sheet1"; //Excel中选项卡的名称,默认是Sheet1、Sheet2、Sheet3
string strExcelPath = "D:\\Excel.xls"; //数据源完全路径
ExcelDB edb = new ExcelDB(strExcelPath);//连接Excel strSql = "select * from [" + strTable + "$]";
myDataSet = edb.GetDataSetExcel(strSql, strTable);//保存到dataset for (int i = 0; i < myDataSet.Tables[0].Rows.Count; i++)
{
string[] a = new string[4]; //用数组保存DataSet的值,假定只要四列数据。 for (int j = 0; j < myDataSet.Tables[0].Columns.Count; j++)
{
a[j] = myDataSet.Tables[0].Rows[i][j].ToString();
} string strSql2 = String.Format("insert into tb_myTB([Name],[JobAddress],[Tel],[Mail]) values('{0}','{1}','{2}','{3}')", a[0], a[1], a[2], a[3]);
if (DB.Insert(strSql2) > 0) //存入数据库
{
//添加成功
}
else
{
//添加失败
}
} }
先获取excel的数据
public DataSet GetImportExcelData(string excelPath){
using(System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';",excelPath))){
try{
conn.Open();
DataTable SchemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
string tableName = string.Empty;
DataSet ds = new DataSet();
foreach(DataRow row in SchemaTable.Rows){
tableName = string.Format("select * from [{0}] ", row[2].ToString());
using(System.Data.OleDb.OleDbDataAdapter dapter = new System.Data.OleDb.OleDbDataAdapter(tableName,conn)){
System.Data.DataTable dt = new System.Data.DataTable();
dapter.Fill(dt);
ds.Tables.Add(dt);
}
}
return ds;
}
catch(Exception ex){throw ex;}
}
}获取之后再怎么插入数据库 自己看着办了PS:最简单的方式是 直接从数据库导入excle