转贴：NOTES和EXCEL之间相互导入数据的代码

小木可 2003-03-17 03:47:35

加精

1.notes--->excel:＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

Sub Click(Source As Button)
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As Notesview
Dim doc As NotesDocument
Dim excelapplication As Variant
Dim excelworkbook As Variant
Dim excelsheet As Variant
Dim i As Integer
Dim uvcols As Integer
Dim selection As Variant
'path=session.GetEnvironmentString ("D:",True)
'gzpath=path+"\"+"test.xls"
Set excelapplication=CreateObject("Excel.Application")
excelapplication.statusbar="正在创建工作表，请稍等....."
excelapplication.Visible=True
'==================
'excelapplication.excel.open(gzpath)
excelapplication.Workbooks.Add
excelapplication.referencestyle=2
Set excelsheet=excelapplication.Workbooks(1).worksheets(1)
excelsheet.name="notes export"
Dim rows As Integer
Dim cols As Integer
Dim maxcols As Integer
Dim fieldname As String
Dim fitem As NotesItem
rows=1
cols=1
Set db=session.CurrentDatabase
Set view=db.GetView ("注册表视图")
uvcols=Ubound(view.Columns)
For x=0 To Ubound(view.Columns)
excelapplication.statusbar="正在创建单元格，请稍等....."
If view.Columns(x).IsHidden=False Then
If view.Columns(x).title<>"" Then
'excelsheet.Cells(1,1).Value="姓名"
'excelsheet.Cells(1,2).Value="年龄"
excelsheet.Cells(rows,cols).Value=view.Columns(x).Title
cols=cols+1
End If
End If
Next
maxcols=cols-1
Set doc=view.GetFirstDocument
rows=2
cols=1
While Not(doc Is Nothing)
For x=0 To Ubound(view.Columns)
excelapplication.statusbar="正在从Notes中引入数据，请稍等....."
If view.Columns(x).IsHidden=False Then
If view.Columns(x).title<>"" Then
fieldname=view.Columns(x).itemname
Set fitem=doc.GetFirstItem(fieldname)
excelsheet.Cells(rows,cols).Value=fitem.Text
cols=cols+1
End If
End If
Next
rows=rows+1
cols=1
Set doc=view.GetNextDocument (doc)
Wend
With excelapplication.worksheets(1)
.pagesetup.orientation=2
.pagesetup.centerheader="report_confidential"
.pagesetup.rightfooter="page &P"&Chr$(13) &"Date:&D"
.pagesetup.CenterFooter=""
End With
excelapplication.referencestyle=1
excelapplication.range("A1").Select
excelapplication.statusbar="数据导入完成。"
excelsheet.PageSetup.PrintGridlines=True
'excelworkbook.printout
'excelworkbook.SaveAs("d:\test.xls")
'excelworkbook.Save
excelapplication.Quit
Set excelapplication=Nothing
End Sub

2.excel-->notes＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋＋

Sub Click(Source As Button)
Dim workspace As New NotesUIWorkspace
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim item As NotesItem
Dim files As Variant
Dim schar As String
Dim excelApplication As Variant
Dim excelWorkbook As Variant
Dim excelsheet As Variant
Dim i As Integer
Set db = session.CurrentDatabase
Set view = db.GetView( "ExcelRegister" )
files=workspace.OpenFileDialog (False,"选择引入数据文件","Excel file|*.xls","c:")
If files(0)="" Then
Exit Sub
Else
Set excelApplication=CreateObject("Excel.Application")
Set excelWorkbook =excelApplication.Workbooks.Open(files)
Set excelsheet=excelWorkbook.Worksheets(1)
i=2
stemp=excelSheet.Cells(i,1).Value
Do Until Cstr(stemp)=""
Set cpdoc=New NotesDocument(db)
cpdoc.form="Excel_notes注册表"
cpdoc.lx=excelsheet.Cells(i,1).Value
stemp=excelSheet.Cells(i,1).Value
stemp2=excelSheet.Cells(i,2).Value
cpdoc.NameExcel=stemp
cpdoc.AgeExcel=stemp2
i=i+1
Call cpdoc.save(True,False)
Call workspace.ViewRefresh
Loop
excelWorkbook.close(False)
excelApplication.Quit
Set excelApplication=Nothing
End If
End Sub

...全文