/******* 导出到excel EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'/*********** 导入Excel SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+' ' 转换后的别名 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions/** 导入文本文件 EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'/** 导出文本文件 EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword' 或 EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'导出到TXT文本,用逗号分开 exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password' BULK INSERT 库名..表名 FROM 'c:\test.txt' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' ) --/* dBase IV文件 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]') --*/--/* dBase III文件 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]') --*/--/* FoxPro 数据库 select * from openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 'select * from [aa.DBF]') --*//**************导入DBF文件****************/ select * from openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver; SourceDB=e:\VFP98\data; SourceType=DBF', 'select * from customer where country != "USA" order by country') go /***************** 导出到DBF ***************/ 如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句insert into openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 'select * from [aa.DBF]') select * from 表说明: SourceDB=c:\ 指定foxpro表所在的文件夹 aa.DBF 指定foxpro表的文件名. /*************导出到Access********************/ insert into openrowset('Microsoft.Jet.OLEDB.4.0', 'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表/*************导入Access********************/ insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0', 'x:\A.mdb';'admin';'',A表)
也可以直接读取excel的内容进行保存操作示例: On Error Resume Next '把查询到的数据放到excel中打印 Dim PageNum As Integer Dim Size As Integer Dim File As String Dim I As Integer Dim R As Integer Dim P As Integer PageNum = 1 '打印的页数 Size = 18 '一页可以打印的行数 '得到打印的页数 While Not (PageNum * Size) - (Msf.Rows - 3) > 0 PageNum = PageNum + 1 Wend'循环打印每一页 For P = 1 To PageNum Dim Exl As New Excel.Application Dim Book As Excel.Workbook Dim Sheet As Excel.Worksheet
FileCopy App.path & "\report\合作医疗经费补偿统计表(地区统计)new.xls", App.path & "\合作医疗经费补偿统计表(地区统计)new.xls" File = App.path & "\合作医疗经费补偿统计表(地区统计)new.xls" Set Book = Exl.Workbooks.Open(File) Set Sheet = Book.Worksheets(1) Book.Application.DisplayAlerts = False '把数据写入sheet中 With Sheet For R = 1 To Size For I = 1 To 10 .Cells(R + 5, I) = Msf.TextMatrix((P - 1) * Size + 2 + R, I - 1) Next Next .PageSetup.Orientation = xlLandscape .PrintOut
End With Book.Save Book.Close Set Book = Nothing Exl.Quit Set Exl = Nothing Next
word和excel当成二进制存入数据库,跟图片的处理相同:'存储照片文件到数据库 Public Function WriteToDB(ByRef col As ADODB.Field, ByVal FileName As String) As Boolean On Error GoTo ErrMsg Dim mStream As ADODB.Stream Set mStream = New ADODB.Stream
mStream.Close Set mStream = Nothing WriteToDB = True Exit Function ErrMsg: MsgBox "存储照片到数据库时出现错误." & vbCrLf & Err.Description, vbExclamation + vbOKOnly, "提示" End Function'设置临时照片文件 Public Function ReadDB(col As ADODB.Field, ByRef imgFile As String) As Boolean On Error GoTo ErrRead Dim mStream As New ADODB.Stream ReadDB = False
If col.ActualSize < 200 Then Exit Function
mStream.Type = adTypeBinary mStream.Open mStream.Write col.Value mStream.SaveToFile imgFile, adSaveCreateOverWrite ReadDB = True Exit Function ErrRead: MsgBox "设置临时照片文件时出现错误:" & vbCrLf & Err.Description, vbInformation, "提示" ReadDB = False End Function
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'/*********** 导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+' ' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions/** 导入文本文件
EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'/** 导出文本文件
EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'导出到TXT文本,用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
BULK INSERT 库名..表名
FROM 'c:\test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料4.dbf]')
--*/--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:\','select * from [客户资料3.dbf]')
--*/--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
--*//**************导入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'select * from [aa.DBF]')
select * from 表说明:
SourceDB=c:\ 指定foxpro表所在的文件夹
aa.DBF 指定foxpro表的文件名.
/*************导出到Access********************/
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表) select * from 数据库名..B表/*************导入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
'x:\A.mdb';'admin';'',A表)
On Error Resume Next
'把查询到的数据放到excel中打印
Dim PageNum As Integer
Dim Size As Integer
Dim File As String
Dim I As Integer
Dim R As Integer
Dim P As Integer
PageNum = 1 '打印的页数
Size = 18 '一页可以打印的行数
'得到打印的页数
While Not (PageNum * Size) - (Msf.Rows - 3) > 0
PageNum = PageNum + 1
Wend'循环打印每一页
For P = 1 To PageNum
Dim Exl As New Excel.Application
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet
FileCopy App.path & "\report\合作医疗经费补偿统计表(地区统计)new.xls", App.path & "\合作医疗经费补偿统计表(地区统计)new.xls"
File = App.path & "\合作医疗经费补偿统计表(地区统计)new.xls"
Set Book = Exl.Workbooks.Open(File)
Set Sheet = Book.Worksheets(1)
Book.Application.DisplayAlerts = False '把数据写入sheet中
With Sheet
For R = 1 To Size
For I = 1 To 10
.Cells(R + 5, I) = Msf.TextMatrix((P - 1) * Size + 2 + R, I - 1)
Next
Next
.PageSetup.Orientation = xlLandscape .PrintOut
End With
Book.Save
Book.Close
Set Book = Nothing
Exl.Quit
Set Exl = Nothing
Next
Public Function WriteToDB(ByRef col As ADODB.Field, ByVal FileName As String) As Boolean
On Error GoTo ErrMsg
Dim mStream As ADODB.Stream
Set mStream = New ADODB.Stream
WriteToDB = False
mStream.Type = adTypeBinary
mStream.Open
mStream.LoadFromFile FileName
col.Value = mStream.Read
mStream.Close
Set mStream = Nothing
WriteToDB = True
Exit Function
ErrMsg:
MsgBox "存储照片到数据库时出现错误." & vbCrLf & Err.Description, vbExclamation + vbOKOnly, "提示"
End Function'设置临时照片文件
Public Function ReadDB(col As ADODB.Field, ByRef imgFile As String) As Boolean
On Error GoTo ErrRead
Dim mStream As New ADODB.Stream
ReadDB = False
If col.ActualSize < 200 Then Exit Function
mStream.Type = adTypeBinary
mStream.Open
mStream.Write col.Value
mStream.SaveToFile imgFile, adSaveCreateOverWrite
ReadDB = True
Exit Function
ErrRead:
MsgBox "设置临时照片文件时出现错误:" & vbCrLf & Err.Description, vbInformation, "提示"
ReadDB = False
End Function
这上面有很多源码,希望能对你有所帮助。
'把图片写入到数据库
strSQL = "select * from " & strTable _
& " where BBID='" & strBBID & "'" _
& " and ReportIndex=" & objControl.Index _
& " and ReportType=" & WPhoto
Set rsTemp = New ADODB.Recordset
rsTemp.Open strSQL, GCon, adOpenDynamic, adLockOptimistic
WriteToDB rsTemp("ReportPhoto"), mstrTempFile
rsTemp.Update
rsTemp.CloseReadDB比较简单,就不多说了。