asp.net读出excel数据然后插入另一张表

[zhoululu8888]

有三张excel表没有数据库只能操作excel
一张是计划表包括id,name,no,qq等,一张是实际表id,no,
id是不变的,实际的no可能不一样,根据id联合查询两张表,把查出的数据插入到excel中怎样实现啊！在线等啊

[分享大哥]

将数据读取到datatable中，然后通过datatable来进行数据的操作；

[zhoululu8888]

具体怎么做啊

[楼外楼]

insert into Aim_Tab
SELECT * FROM OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=no;DataBase=C:/Excel_Example_Aim.xls;',PlanSheetName$)
UNION
SELECT * 。。。。。

或者你直接用OleDB读取，然后合并数据再插入

如果你的目标Excel不存在，建议先执行COM程序，建立一个Excel文件
http://lixx.cnblogs.com有我以前写的

[zhoululu8888]

我想了半天都想不起来怎么做啊

[zhoululu8888]

网址打不开啊

[zhoululu8888]

用oleDB怎样做啊

[mengxj85]

[Quote=引用 10 楼 migang 的回复:]
this.filetext.PostedFile.SaveAs(Server.MapPath("Data/ProgramList.xls")); //文件上传保存

#region 读取Excel中的数据
mytable_data.Rows.Clear();
mytable_air.Rows.Clear();

string ConnStr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Server.MapPath("Data/ProgramList.xls") + ";Extended Properties=Excel 8.0";//从Excel中读数…
[/Quote]
有空研究一下

[leo_bogard]

up

[zhoululu8888]

3楼用oledb怎么做啊

[greatverve]

研究一下，我只导出过，没有导入过。

[migang]

this.filetext.PostedFile.SaveAs(Server.MapPath("Data/ProgramList.xls")); //文件上传保存

#region 读取Excel中的数据
mytable_data.Rows.Clear();
mytable_air.Rows.Clear();

string ConnStr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Server.MapPath("Data/ProgramList.xls") + ";Extended Properties=Excel 8.0";//从Excel中读数据
OleDbConnection myconnection = new OleDbConnection(ConnStr);
try
{
string sql = "select * from [programlist$] where air_sno is not null";//[programlist$] [Sheet1$
myconnection.Open();
OleDbDataAdapter myadapter = new OleDbDataAdapter(sql, myconnection);
myadapter.Fill(mytable_data);
myadapter.SelectCommand.CommandText = "select distinct air_sno from [programlist$]";
myadapter.Fill(mytable_air);
myconnection.Close();

}
catch (Exception a)
{
myconnection.Close();
}
#endregion

#region 汇入数据库中
SqlConnection connection = new SqlConnection(ConStr);
connection.Open();
SqlCommand mycommand = new SqlCommand();
mycommand.Connection = connection;
SqlTransaction st = connection.BeginTransaction();
mycommand.Transaction = st;
try
{
//删除过期节目表
mycommand.CommandText = "delete from tab_programlist where Programlist_time<@time and programlist_sno is not null";
mycommand.Parameters.Add("@time", SqlDbType.DateTime, 9).Value = DateTime.Now.Date.AddDays(-3);
mycommand.ExecuteNonQuery();
//删除用户过期录影
mycommand.CommandText = "delete from tab_programlist_user where programlist_sno in "
+" (select programlist_sno from tab_programlist_user where programlist_sno not in (select programlist_sno from tab_programlist ))";
mycommand.ExecuteNonQuery();
for (int i = 0; i < mytable_data.Rows.Count; i++)
{
mycommand.Parameters.Clear();
string sql = "if not exists(select air_sno from tab_programlist where programlist_time=@time and air_sno=@air) insert into tab_ProgramList(air_sno,programlist_name,programlist_time) values(@air,@content,@time)";
mycommand.CommandText = sql;
mycommand.Parameters.Add("@air", SqlDbType.Decimal, 9).Value = Convert.ToDecimal(mytable_data.Rows[i]["air_sno"]);
mycommand.Parameters.Add("@content", SqlDbType.NVarChar, 255).Value = mytable_data.Rows[i]["name"].ToString();

string time = mytable_data.Rows[i]["time"].ToString();//regextime.Replace(mytable_data.Rows[i]["time"].ToString(), "${year}-${month}-${day} ${minute}:${second}");

mycommand.Parameters.Add("@time", SqlDbType.SmallDateTime).Value = DateTime.Parse(time);
int index=mycommand.ExecuteNonQuery();
if (index > 0)
{
sql = "select @@identity from tab_programlist";
mycommand.Parameters.Clear();
mycommand.CommandText = sql;
decimal sno = decimal.Parse(mycommand.ExecuteScalar().ToString());

sql = "update tab_programlist set film_no=@film_no where Programlist_sno=@sno";
mycommand.Parameters.Clear();
mycommand.CommandText = sql;
mycommand.Parameters.Add("@film_no", SqlDbType.NVarChar).Value = Convert.ToInt32(sno).ToString("00000000");
mycommand.Parameters.Add("@sno", SqlDbType.Decimal).Value = sno;
mycommand.ExecuteNonQuery();
}
}
st.Commit();
this.lbl_ErrorMessage.Text = "节目表 汇入成功!";
System.Threading.Thread.Sleep(2000);
}
catch (Exception a)
{
st.Rollback();
this.lbl_ErrorMessage.Text = a.Message;

}
finally
{
st.Dispose();
mycommand.Dispose();
myconnection.Close();
}
#endregion

[migang]

..失误。。没看清题。

[G_hawk_P]

关注一下，在做excel数据上传时，也遇到过这个问题！

[zhoululu8888]

求救啊各位大侠

[mengxj85]

SQL SERVER 和EXCEL的数据导入导出
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------

2、将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------

3、将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码：
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名；U是用户；P是密码
说明：还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码：
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
------------------------------------------------------------------------------------------------

4、在SQL SERVER里往Excel插入数据:
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)

T-SQL代码：
INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt) VALUES (20, 'Test')

------------