帮帮忙，高手进来帮我修改一下代码
关于excel导入sqlserver的代码string fName = this.File1.Value;
string sheetName = "Sheel1$";
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
conn.Open();
string strSql ="select * into table1 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE="+fName+"',"+sheetName+")";
SqlCommand objCMD = new SqlCommand(strSql,conn);
objCMD.ExecuteNonQuery();
conn.Close();
错误
OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 不包含表 'Sheel1$'。该表可能不存在，或当前用户没有使用该表的权限。 --------------------编程问答-------------------- 是不是 大小写 错了
Microsoft.Jet.OLEDB.4.0? --------------------编程问答-------------------- string sheetName = "Sheel1$";
改成string sheetName = "Sheet1";
这句应该是指EXCEL中工作表的名字 --------------------编程问答-------------------- 你这个是不是要做EXCEL的倒入 --------------------编程问答-------------------- 给你个例子,
public static DataSet CreateDataSource(string Src)
{
try
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Src + ";" +
"Extended Properties=\"Excel 8.0;IMEX=1;\""; //连接
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim(); //得到excel表名
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + tableName + "]", strConn); //查找excel数据
DataSet myDataSet = new DataSet(); //定义数据
myCommand.Fill(myDataSet);
conn.Close();
return myDataSet;
}
catch
{
return null;
}
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
string ya = this.DropDownList1.SelectedValue;
string yb = this.DropDownList2.SelectedValue;
string ti = this.DropDownList3.SelectedValue;
string ytime = ya + yb + ti;
string strsql = "";
DataSet ds = new DataSet();
strsql = "select distinct y_tm from [YB_gzd]";
ds=conn.ExecuteQuery(strsql);
if (ds.Tables[0].Rows.Count != 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i].ItemArray[0].ToString() == ytime || ds.Tables[0].Rows[i].ItemArray[0].ToString().Equals(ytime))
{
this.Response.Write("<script>alert('数据导入重复，请重新导入！');</script>");
return;
}
}
}
string test = this.FileUpload1.FileName;
string strFilePath = MapPath("down") + "\\" + test;
this.FileUpload1.PostedFile.SaveAs(strFilePath);
this.Label1.Text = this.FileUpload1.FileName.ToString();
StringBuilder sb = new StringBuilder();
//DataSet ds = new DataSet();
string mpath = "";
// jxcapital.DBConn conn= new jxcapital.DBConn();
mpath = this.Request.PhysicalApplicationPath + "down\\" + test;
// mpath = this.FileUpload1.FileName;
ds = CreateDataSource(mpath);
//DropDownList1.DataSource = ds;
//DropDownList1.DataMember = "Y_PP";
//DropDownList1.DataBind();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string Y_sj = ds.Tables[0].Rows[i].ItemArray[0].ToString();
string Y_dy = ds.Tables[0].Rows[i].ItemArray[1].ToString();
string Y_pp = ds.Tables[0].Rows[i].ItemArray[2].ToString();
string Y_time = ds.Tables[0].Rows[i].ItemArray[3].ToString();
string Y_yw = ds.Tables[0].Rows[i].ItemArray[4].ToString();
string Y_ID = ds.Tables[0].Rows[i].ItemArray[5].ToString();
string Y_TH = ds.Tables[0].Rows[i].ItemArray[6].ToString();
string Y_lz = ds.Tables[0].Rows[i].ItemArray[7].ToString();
string Y_zt = ds.Tables[0].Rows[i].ItemArray[8].ToString();
string Y_dt = ds.Tables[0].Rows[i].ItemArray[9].ToString();
int Y_01 = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[10]);
string Y_1txt = ds.Tables[0].Rows[i].ItemArray[11].ToString();
int Y_02 = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[12]);
string Y_2txt = ds.Tables[0].Rows[i].ItemArray[13].ToString();
int Y_03 = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[14]);
string Y_3txt = ds.Tables[0].Rows[i].ItemArray[15].ToString();
int Y_04 = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[16]);
string Y_4txt = ds.Tables[0].Rows[i].ItemArray[17].ToString();
int Y_05 = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[18]);
string Y_5txt = ds.Tables[0].Rows[i].ItemArray[19].ToString();
//csData csuser = new csData();
string sql = "insert into YB_gzd values ('" + Y_sj + "','" + Y_dy + "','" + Y_pp + "','" + Y_time + "','" + Y_yw + "','" + Y_ID + "','" + Y_TH + "','" + Y_lz + "','" + Y_zt + "','" + Y_dt + "'," + Y_01 + ",'" + Y_1txt + "', " + Y_02 + ",'" + Y_2txt + "', " + Y_03 + ",'" + Y_3txt + "', " + Y_04 + ",'" + Y_4txt + "', " + Y_05 + ",'" + Y_5txt + "','" + ytime + "')";
conn.ExecuteQuery(sql);
}
this.Label1.Text = "导入成功!";
} --------------------编程问答-------------------- 把 SqlConnection 改 为 OleDbConnection 试试
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + File1.PostedFile.FileName.ToString() + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(sConnectionString);
--------------------编程问答-------------------- Excel的数据源,当然要使用OleDB来打开了,
使用SqlConnection等类只能对SqlServer操作. --------------------编程问答-------------------- objConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [Sheet1＄]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect; --------------------编程问答-------------------- hbxtlhx 慧眼如炬啊，上次记得也是这样一个问题，也是被你一眼看出来.. --------------------编程问答-------------------- 还有一个地方你要注意下SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]).ToString();这里
不管你用的是什么 --------------------编程问答-------------------- hbxtlhx(平民百姓－自已动手，丰衣足食)
请问，我这整一段代码如果用OleDB应该改成什么样呢，
关于excel导入sqlserver我从来没做过，请大家帮帮忙 --------------------编程问答-------------------- 呀，你还用 OPENROWSET 啊， 直接使用 OleDb 去连接
可以参考
ADO.NET 如何读取 Excel （上） http://www.cnblogs.com/Jinglecat/archive/2006/08/15/477472.html --------------------编程问答-------------------- string fName = this.File1.Value;
string sheetName = "Sheet1$";
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
conn.Open();
string strSql ="select * into table2 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE="+fName+"',"+sheetName+")";
SqlCommand objCMD = new SqlCommand(strSql,conn);
objCMD.ExecuteNonQuery();
conn.Close();
导入数据成功了，现在是想向表里插入值，而不是创建新表，应该怎么做？ --------------------编程问答-------------------- 比如说table2表里有4列a,b,c,d
怎么判断插入？ --------------------编程问答-------------------- 你可以使用OleDB来打开Excel数据源,其实关键是连接字符串,可以参考如下:
string m_ConnString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;Imex=2;HDR=Yes;'", m_FileName);
有了连接字符串,可以使用OleDbConnection打开Excel数据源,并可以获取其数据,比如有如下的一个方法:
public static DataTable GetDataTable(string sqlText, CommandType cmdType, out string errorInfo)
{
errorInfo = null;
DataTable dt = null;
OleDbConnection con = null;
OleDbDataAdapter sqlAda = null;
try
{
con = new OleDbConnection(m_ConnString);
con.Open();
sqlAda = new OleDbDataAdapter();
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = sqlText;
cmd.CommandType = cmdType;
sqlAda.SelectCommand = cmd;
dt = new DataTable();
sqlAda.Fill(dt);
con.Close();
return dt;
}
catch (Exception ex)
{
if (con != null)
{
con.Close();
}
errorInfo = ex.Message;
return null;
}
}
得到了数据后,当然可以使用SqlConnection来打开 SqlServer并建立Sql语句使用Insert Into来向SqlServer的表插入数据,这是常见的做法,当然你可以使用其它的方法,比如使用SqlServer的Xml的支持来插入数据,或者使用SqlServer的其它功能,不是一定怎么做,而是合适怎么做就怎么做了. --------------------编程问答-------------------- 请问前几行的 File1 是什么啊？ --------------------编程问答--------------------
//从指定的Excel文件导入
public DataSet ImportFromExcel(string strFileName)
{
DataSet ds = new DataSet();
ds = doImport(strFileName);
return ds;
}
//执行导入
private DataSet doImport(string strFileName)
{
if(strFileName=="") return null;
strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+strFileName+";Extended Properties=Excel 8.0";
OleDbDataAdapter excelDA = new OleDbDataAdapter("select * from [Sheet1$]",this.strConn);//连接字符串
DataSet ExcelDs = new DataSet();//建立数据集,用于存放导入Excel的数据
try
{
excelDA.Fill(ExcelDs,"ExcelInfo");//填充DataSet
}
catch(Exception ee)
{
System.Windows.Forms.MessageBox.Show(ee.Message);
}
return ExcelDs;//返回DataSet
}
补充：.NET技术 , ASP.NET
