Inherits System.Windows.Forms.Form Dim DS1 As New System.Data.DataSet Dim DS2 As New System.Data.DataSet Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Dim MyConnection As System.Data.OleDb.OleDbConnection MyConnection = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source=e:\BOOK1.XLS; " & _ "Extended Properties=Excel 8.0;") ' Select the data from Sheet1 of the workbook. MyCommand = New System.Data.OleDb.OleDbDataAdapter( _ "select * from [Sheet1$]", MyConnection) '或者是DS1.ReadXml("c:\abcd.xml") MyCommand.Fill(DS1, "handle") DataGrid1.DataSource = DS1 MyConnection.Close() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Try Dim j As Integer Dim person_id, information_id, handle As String Dim handle_time As System.DateTime 'If Me.SqlConnection1.Close() Then Me.SqlConnection1.Open() 'End If For j = 1 To DS1.Tables("handle").Rows.Count - 1 Me.SqlDataAdapter1.InsertCommand.Parameters("@person_id").Value = DS1.Tables("handle").Rows(j).Item(0) Me.SqlDataAdapter1.InsertCommand.Parameters("@information_id").Value = DS1.Tables("handle").Rows(j).Item(1) 'Me.SqlDataAdapter1.InsertCommand.Parameters("@handle").Value = DS1.Tables("handle").Rows(j).Item(2) Me.SqlDataAdapter1.InsertCommand.Parameters("@handle_time").Value = CDate(DS1.Tables("handle").Rows(j).Item(3)) Me.SqlDataAdapter1.InsertCommand.ExecuteNonQuery() Next 'MessageBox.Show(SqlDataAdapter1.InsertCommand.CommandText) Me.SqlConnection1.Close() Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub一个例子,原理就是先把Excel或xml文件的数据通过DataSet读到Datagrid中,再一个个取出保存到数据库中去。
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OleDb;public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { DataSet myData = new DataSet(); string myXML; string strFilePath = "C:\\"; string mySelectQuery = "SELECT * FROM SampleFile.CSV"; //Query for getting data from multiple tables //string mySelectQuery = "SELECT * FROM SampleFile.CSV As Sample1, SampleFile2.CSV As Sample2 Where Sample1.Number=Sample2.Number"; OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\;Extended Properties=\"text;HDR=YES;FMT=Delimited\""); OleDbDataAdapter dsCmd = new OleDbDataAdapter(mySelectQuery, myConnection); //Fill the DataSet object dsCmd.Fill(myData, "CustomerOwners"); //Create a text XML document myData.WriteXml("D:\\TestXML.txt"); myConnection.Close(); } }
sql 2008好像可以完成你说的功能
Inherits System.Windows.Forms.Form
Dim DS1 As New System.Data.DataSet
Dim DS2 As New System.Data.DataSet
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=e:\BOOK1.XLS; " & _
"Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)
'或者是DS1.ReadXml("c:\abcd.xml") MyCommand.Fill(DS1, "handle")
DataGrid1.DataSource = DS1
MyConnection.Close() End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Try
Dim j As Integer
Dim person_id, information_id, handle As String
Dim handle_time As System.DateTime
'If Me.SqlConnection1.Close() Then
Me.SqlConnection1.Open()
'End If
For j = 1 To DS1.Tables("handle").Rows.Count - 1 Me.SqlDataAdapter1.InsertCommand.Parameters("@person_id").Value = DS1.Tables("handle").Rows(j).Item(0)
Me.SqlDataAdapter1.InsertCommand.Parameters("@information_id").Value = DS1.Tables("handle").Rows(j).Item(1)
'Me.SqlDataAdapter1.InsertCommand.Parameters("@handle").Value = DS1.Tables("handle").Rows(j).Item(2)
Me.SqlDataAdapter1.InsertCommand.Parameters("@handle_time").Value = CDate(DS1.Tables("handle").Rows(j).Item(3))
Me.SqlDataAdapter1.InsertCommand.ExecuteNonQuery()
Next 'MessageBox.Show(SqlDataAdapter1.InsertCommand.CommandText)
Me.SqlConnection1.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub一个例子,原理就是先把Excel或xml文件的数据通过DataSet读到Datagrid中,再一个个取出保存到数据库中去。
if(File.Exists(path))
{
File.Delete(path);
}
//将准备好Excel文件拷贝至用户下载文件夹,再进行写入
File.Copy(this.MapPath("../user.xls"),path); //然后将Excel文件作为数据源进行数据写入的操作
//在服务器端加载Excel文件
OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+
this.MapPath("../user.xls")+";Extended Properties=Excel 8.0;");
OleDbCommand cmd = cn.CreateCommand();
cmd.CommandText = "INSERT INTO [user$] VALUES(@a,@b,@b)";
cn.Open();
foreach(DataGridItem item in this.dgExcel.Items)
{
cmd.Parameters.Clear();
cmd.Parameters.Add("@a",item.Cells[2].Text.Trim());
cmd.Parameters.Add("@b",item.Cells[1].Text.Trim());
cmd.Parameters.Add("@c",item.Cells[3].Text.Trim()); cmd.ExecuteNonQuery();
}
cn.Close();
前者要用sql2008后者可以把数据信息导入到sql中 用gridview显示出来
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataSet myData = new DataSet();
string myXML;
string strFilePath = "C:\\";
string mySelectQuery = "SELECT * FROM SampleFile.CSV";
//Query for getting data from multiple tables
//string mySelectQuery = "SELECT * FROM SampleFile.CSV As Sample1, SampleFile2.CSV As Sample2 Where Sample1.Number=Sample2.Number";
OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\;Extended Properties=\"text;HDR=YES;FMT=Delimited\"");
OleDbDataAdapter dsCmd = new OleDbDataAdapter(mySelectQuery, myConnection);
//Fill the DataSet object
dsCmd.Fill(myData, "CustomerOwners");
//Create a text XML document
myData.WriteXml("D:\\TestXML.txt");
myConnection.Close();
}
}
如果真想存到数据库中,需要先将文件上传到服务中的项目文件下,再将EXCEL读成二进制流保存到数据,下载就直接输出二进制流数据并转换就可以了,看看9楼的方法吧
楼主可以考虑将EXCEL动态转成HTML文件,再读成流存到数据库中,输出流就可以直接打开文件了,只是再次存进去可能有些不好处理
二、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')
-------------------------------------------------------------------------------------------------总结:利用以上语句,我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换,为我们提供了极大方便!
EXEC master..xp_cmdshell 'bcp "select OrderID,ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.dbo.orders" queryout "d:\Oreders.txt" -t"|" -c -q -S"127.0.0.1" -U"sa" -P""'
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="xxxx.xls";Extended Properties="Excel 8.0";IMEX=1;Persist Security Info=False')...[a1$]