ado.net怎么访问excel
ado.net怎么访问excel 列名是什么，带参SQL怎么写 --------------------编程问答-------------------- SELECT * into newtableFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\a.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
上传excel文档到服务器
using(OleDbConnection myConn = new OleDbConnection(""))
{
myConn.Open();
string str = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(str, myConn);
DataSet ds= new DataSet();
myCommand.Fill(ds);
myConn.Close();
}
Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1 F2 F3
--------------------编程问答-------------------- 范围查询
OleDbConnection conn = new OleDbConnection(strConn);
OleDbCommand com = new OleDbCommand("SELECT * FROM [sheet1$A0:A5]", conn);
OleDbDataAdapter da = new OleDbDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
--------------------编程问答--------------------
--------------------编程问答-------------------- wuyq11
/// <summary>
/// 文件地址
/// </summary>
public string CurFilePath
{
get
{
//return curFilePath;//可在这里改成测试时的实际路径
return "d:\test\test.xls";//如改成这个，当然文件要存在
}
}
string m_connectionString = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = {0};Extended Properties=\"Excel 8.0;HDR={1}\"";
/// <summary>
/// 连接字符串
/// </summary>
public string ConnectionString
{
get
{
string hasColName = "No";
if (hasColumnName)
{
hasColName = "Yes";
}
string conn;
conn = string.Format(m_connectionString, CurFilePath, hasColName);
return conn;
}
}
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(this.ConnectionString);
System.Data.DataTable tabInfo = null;
try
{
conn.Open();
tabInfo = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//可在此处设断点查看列名
}
catch (Exception)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
怎么插入和删除和修改数据呢
带参的SQL怎么写 --------------------编程问答--------------------
string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + fPath + "';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs, "Test");
CheckData.DataSource = myDs.Tables["Test"];
CheckData.DataBind();
//从excel文件获得数据后，插入记录到SQL Server的数据表
SqlConnection sqlCon = new SqlConnection("Data Source=.;Initial Catalog=xzwlManager;User ID=sa;Password=8555");
DataTable dataTable1 = new DataTable();
SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT * FROM dbo.textInfo", sqlCon);
SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
sqlDA1.Fill(dataTable1);
TeaUsers user = (TeaUsers)Session["user"];
foreach (DataRow dataRow in myDs.Tables["Test"].Rows)
{
DataRow dataRow1 = dataTable1.NewRow();
dataRow1["gid"] = user.GId;
dataRow1["cid"] = user.CId;
dataRow1["sno"] = dataRow[0];
dataRow1["testname"] = dataRow[2];
dataRow1["score"] = dataRow[3];
dataRow1["texttime"] = dataRow[4];
dataRow1["subjectid"]= SchoolManager.BLL.SubjectManager.GetSubIdbyName(dataRow[5].ToString());
dataRow1["bz"] = dataRow[6];
dataTable1.Rows.Add(dataRow1);
}
sqlDA1.Update(dataTable1);
cnnxls.Close();
补充：.NET技术 , C#
