Lotus Notes 生成excel 報表

最新推荐文章于 2025-08-22 08:42:02 发布

原创最新推荐文章于 2025-08-22 08:42:02 发布 · 746 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #lotus #string #os

Lotus Notes開發點滴专栏收录该内容

4 篇文章

订阅专栏

这个子程序通过VBA在Lotus Notes中生成Excel报表，接收标题、文件集合、字段描述和字段名作为参数。它创建一个新的Excel工作簿，设置标题，填充列头，并根据Notes文档的多值字段拆解数据到相应单元格。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Sub createExcel(title As String ,dc As NotesDocumentCollection,description() As String,fieldName() As String)
'***************************************************************************

'title 表名 dc文件集 descripton 栏位描述 fieldname栏位名
'生成excel 報表
'***************************************************************************
Dim rdoc As NotesDocument
Dim ritem As NotesItem

Dim xlApp As Variant
Dim xlsheet As Variant
Dim t As String
Dim j As Integer
Dim row As Integer
Dim col As Integer
Dim s As Integer
Dim maxNO As Integer
Dim tmp As Integer

col=Ubound(description)

Set xlApp = CreateObject("Excel.application")
xlApp.Workbooks.Add
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
XlApp.Windows(1).DisplayGridlines=True
xlsheet.Activate
xlsheet.Visible=1
xlApp.Visible=True
xlsheet.name=title

xlApp.Range("A1:"+Chr$(col+64)+"2").Select

With xlApp.Selection
  '.HorizontalAlignment = xlCenter
  '.VerticalAlignment = xlCenter
  .WrapText = False
  .Orientation = 0
  .AddIndent = False
  .IndentLevel = 0
  .ShrinkToFit = False
  '.ReadingOrder = xlContext
  .MergeCells = True
End With

xlsheet.Range("A1").value=title

Set rdoc=dc.GetFirstDocument
count=dc.Count
i=4
For j=65 To col+64
  xlsheet.Range(Chr$(j)+"3")=description(j-65)
Next

While Not rdoc Is Nothing
  tmp=1
  For j=65 To col+64
   Set ritem=rdoc.GetFirstItem(fieldname(j-65))
   If Not ritem Is Nothing Then
    s=i
    maxNO=0

    //多值栏位拆解
    Forall o In ritem.Values
     xlsheet.Range(Chr$(j)+Cstr(s)).value=o
     s=s+1
     maxNO=maxNO+1
     If maxNO>tmp Then
      tmp=maxNO
     End If
    End Forall
   Else
    xlsheet.Range(Chr$(j)+Cstr(i)).value=""
   End If
  Next
  Set rdoc=dc.GetNextDocument(rdoc)
  i=i+tmp
Wend

End Sub