| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 981 人关注过本帖
标题:Excel中的数据导入到SQL中
只看楼主 加入收藏
sjbird331
Rank: 1
等 级:新手上路
帖 子:76
专家分:0
注 册:2005-8-5
收藏
 问题点数:0 回复次数:4 
Excel中的数据导入到SQL中
你好,我的VS是2003版,请问各位如何将Excel中的数据导入到SQL中
搜索更多相关主题的帖子: Excel中 SQL中 数据 
2007-03-28 09:09
bygg
Rank: 16Rank: 16Rank: 16Rank: 16
来 自:乖乖的心中
等 级:版主
威 望:241
帖 子:13555
专家分:3076
注 册:2006-10-23
收藏
得分:0 
把将Excel里面的数据放到一个数据容器中,再更新到SQL中去...前面有...找找吧.

飘过~~
2007-03-28 09:57
上炕不脱鞋
Rank: 1
等 级:新手上路
威 望:1
帖 子:332
专家分:0
注 册:2006-3-19
收藏
得分:0 

正好前些天做过,代码给你看:
string strPath = File1.PostedFile.FileName;
// string strPath,FileName;
// strPath=File1.PostedFile.FileName;
// FileName=strPath.Substring(strPath.LastIndexOf("\\")+1);
if(strPath == "")
{
Response.Write("<script>alert('请先选择文件')</script>");
return;
}
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strPath + ";" +
"Extended Properties=Excel 8.0;";

// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(sConnectionString);
try
{

// Open connection with the database.
objConn.Open();

// The code to follow uses a SQL SELECT command to display the data from the worksheet.

// Create new OleDbCommand to return data from worksheet.
OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM[sheet1$]", objConn);
/*************************************************************
**********************测试读取单列****************************
OleDbDataReader dr = objCmdSelect.ExecuteReader();
while(dr.Read())
{
Response.Write("产品类别"+dr[2].ToString());
}
dr.Close();
***************************************************************/
// Create new OleDbDataAdapter that is used to build a DataSet
// based on the preceding SQL SELECT statement.
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

// Pass the Select command to the adapter.
objAdapter1.SelectCommand = objCmdSelect;

// Create new DataSet to hold information from the worksheet.
DataSet dsolb = new DataSet();

// Fill the DataSet with the information from the worksheet.
objAdapter1.Fill(dsolb);

// Bind data to DataGrid control.
DataGrid1.DataSource = dsolb;
DataGrid1.DataBind();
}
catch(Exception ee)
{
Response.Write("<script>alert('请先关闭文件')</script>");
return;
}
finally
{
// Clean up objects.
objConn.Close();
}

//从配置文件中取得数据库连接
string confstring = ConfigurationSettings.AppSettings["SQLConnectionString"].ToString();
string stringzhang = Session["Zhangce"].ToString();
string stringYear = Session["Year2"].ToString();
sqlconn = confstring+stringzhang+"_"+stringYear;

SqlConnection cnn = new SqlConnection(sqlconn);
string sqlcommand="insert into Inventory(cInvCode,cInvName,cInvStd,cInvCCode,dSDate,bsale,bpurchase,bself,bComSume,bProducing,itaxRate,bInvQuality,bInvBatch,bInvEntrust,bInvoverstock,bfree1,bfree2,bInvType,cGroupCode ,cComUnitCode,dModifyDate) "+
"values( @number,@name,@interface,@type,@date,@bsale,@bpurchase,@bself,@bComsume,@bProducing,@itaxRate,@binvquality,@bInvBatch,@binventrust,@binvoverstock,@bfree1,@bfree2,@bInvtype,@cGroupcode,@cComUnitCode,@deModifyDate)";
SqlCommand cmd = new SqlCommand(sqlcommand,cnn);
#region 遍历sql表
cmd.Parameters.Add(new SqlParameter("@type",SqlDbType.VarChar,20));
cmd.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,20));
cmd.Parameters.Add(new SqlParameter("@interface",SqlDbType.VarChar,20));
cmd.Parameters.Add(new SqlParameter("@number",SqlDbType.VarChar,20));
cmd.Parameters.Add(new SqlParameter("@date",SqlDbType.SmallDateTime,8,"yyyy-mm-dd"));//设定时间的格式

cmd.Parameters.Add(new SqlParameter("@bsale",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bpurchase",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bself",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bComsume",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bProducing",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@itaxRate",SqlDbType.Real));
cmd.Parameters.Add(new SqlParameter("@binvquality",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bInvBatch",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@binventrust",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@binvoverstock",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bfree1",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bfree2",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@bInvtype",SqlDbType.Bit));
cmd.Parameters.Add(new SqlParameter("@cGroupcode",SqlDbType.VarChar,10));
cmd.Parameters.Add(new SqlParameter("@cComUnitCode",SqlDbType.VarChar,10));
cmd.Parameters.Add(new SqlParameter("@deModifyDate",SqlDbType.DateTime,8,"yyyy-mm-dd"));
#endregion
#region 循环更新到数据库
int numcols = DataGrid1.Items.Count;
for(int i=0;i<=numcols-1;i++)
{
TableCell Name = DataGrid1.Items[i].Cells[0];
TableCell Interface = DataGrid1.Items[i].Cells[1];
TableCell Number = DataGrid1.Items[i].Cells[2];

cmd.Parameters["@type"].Value ="CCPP";

cmd.Parameters["@number"].Value = Number.Text.Trim();
cmd.Parameters["@name"].Value = Name.Text.Trim();
cmd.Parameters["@interface"].Value = Interface.Text.Trim();

cmd.Parameters["@date"].Value = DateTime.Now.ToShortDateString().ToString();

cmd.Parameters["@bsale"].Value = -1;
cmd.Parameters["@bpurchase"].Value = -1;
cmd.Parameters["@bself"].Value = -1;
cmd.Parameters["@bComsume"].Value = -1;

cmd.Parameters["@bProducing"].Value = -1;

cmd.Parameters["@itaxRate"].Value = 17;

cmd.Parameters["@binvquality"].Value = 0;

cmd.Parameters["@bInvBatch"].Value = 0;

cmd.Parameters["@binventrust"].Value = 0;

cmd.Parameters["@binvoverstock"].Value = 0;

cmd.Parameters["@bfree1"].Value = 0;

cmd.Parameters["@bfree2"].Value = 0;

cmd.Parameters["@bInvtype"].Value = 0;

cmd.Parameters["@cGroupcode"].Value = "SL";

cmd.Parameters["@cComUnitCode"].Value = "J";

cmd.Parameters["@deModifyDate"].Value = "2006-11-1";

cnn.Open();
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException ee)
{
Response.Write("<script>alert('不能重复导入相同的表')</script>");
return;
}
finally
{
cnn.Close();
}
#endregion


新建的群(C#):23384106 验证注明
2007-03-28 10:54
sjbird331
Rank: 1
等 级:新手上路
帖 子:76
专家分:0
注 册:2005-8-5
收藏
得分:0 

能给个WinForm下的吗?

2007-03-29 08:55
风月_无边
Rank: 16Rank: 16Rank: 16Rank: 16
等 级:版主
威 望:60
帖 子:2960
专家分:652
注 册:2007-1-19
收藏
得分:0 
好麻烦哦 !!!

我的网站 http://www.
2007-03-29 09:24
快速回复:Excel中的数据导入到SQL中
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.031985 second(s), 7 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved