|
Sorting documents in a collection before exporting to Word/Excel
Scenario
You would like to export documents to Word/Excel in the same order in which the documents are displayed in a specific view that is not the view from which the report is triggered.
The documents stored in a collection are not sorted in the same order as the view from which they were collected. Due to this, Integra sorts these documents before they are exported (unless the option Do NOT sort selected documents (collection) in current view is enabled). If you want to sort the documents in a different order, the code outlined below needs to be included in your Integra profile so that the documents are sorted as required.
The following profile settings were used:
- Data Filtering tab: All documents in View
- Data Filtering tab: Export all documents if none are selected
- Excel tab: Normal export method
The code below must be placed in the CB_BFORWRITECOM event inside the Integra profile:
case CB_BFORWRITECOM
'Set entry to last entry in the collection so that this event will only be executed once.
set stats.entry = stats.entrycol.getlastentry
if stats.fstcall then
dim docs() as string
dim tmpdoc as notesdocument
dim sortformula as string
dim sortorder as string
dim MaxRecords as long
dim SortViewName as string
dim pos as integer
dim sortview as notesview
'Change MyView to the name of the view which reflects the sort order you would like to use to export the documents
SortViewName = “MyView”
'Specify ASC to sort the data in ascending order
'Specify DESC to sort the data in descending order
SortOrder = “ASC”
'Get the formula for the first column in the view
'This can be modified to use a different column formula for sorting
set sortview = stats.srcdb.GetView(sortviewvame)
if (sortview.Columns(0).IsField) then
sortformula = sortsiew.Columns(0).ItemName
Else
sortformula = sortview.Columns(0).Formula
End IF
'Sort the documents according to the order of the first column in the selected view
call SortData(stats, stats.entrycol, docs, sortformula, sortorder)
for i = 0 to ubound(docs)
if isobject(stats.pbar) then
Call stats.pbar.UpdateProgressBar("Exporting the documents..."," ", stats.keycnt,MaxRecords)
end if
set tmpdoc = nothing
if not docs(i) = "" then
set tmpdoc = stats.srcdb.getdocumentbyunid(docs(i))
end if
if not tmpdoc is nothing then
stats.keycnt = stats.keycnt + 1
'Read data from the Notes document and manually export to Excel
call export.ReadFromDoc (stats, tmpdoc)
call export.WriteNext(stats)
end if
next
end if
'Do not export any documents. Export was done manually.
export.continue = false
The code below must be placed in the CustomCode script library, or other script library used by CustomCode. Below you will find a file called SortData.lss which contains this code. This can be used to import the code into the script library.
Public Sub SORTDATA(stats As Variant, col As NotesViewEntryCollection, docs() As String, formula As String, sortorder As String)
'Sort the view entry collection in the order of the formula passed as a parameter
On Error Goto Errhandle
Dim i As Long
Dim n As Long
Dim s1 As Variant
Dim tmpstr As Variant
Dim sortarr() As Variant
Dim tmpdoc As NotesDocument
n = col.count
Redim docs(n) As String
Redim sortarr(n) As Variant
If Isobject(stats.pbar) Then
Call stats.pbar.UpdateProgressBar("Sorting the documents..."," ", 0,0)
End If
'Store the entries in the view into an array of Notes documents
'Store sort column formulae into an variant array to be used by sorting algorithm
For i = 0 To n - 1
Set tmpdoc = col.GetNthEntry(i+1).Document
docs(i) = tmpdoc.UniversalID
'Evaluate the sort column formula for each Notes document
tmpstr = Evaluate(formula,tmpdoc)
If Isempty(tmpstr) Then
s1 = ""
Else
s1 = tmpstr(0)
End If
If Isnumeric(s1) Then
sortarr(i) = Cdbl(s1)
Else
If s1 = "" And sortorder = "ASC" Then
'Blank entries are placed at the bottom when sorting
sortarr(i) = "ZZZZZ"
Else
sortarr(i) = s1
End If
End If
Next
'Sort the documents according to the sort column formula
Call QuickSort(stats,sortarr(),docs(),sortorder)
ErrExit:
Exit Sub
Errhandle:
Call Addin_Messagebox("[SortData] " & Erl() & ": " & Err() & ", " & Error(),16,"Error Message",stats)
Resume ErrExit
End Sub
Sub QuickSort(stats As Variant, arr_sort() As Variant, arr_docs() As String,_
sortorder As String)
'Quick Sort algorithm to sort the documents
'The sorting array is sorted by the algorithm and the document array is sorted
'together with the sorting array.
On Error Goto ErrHandle
Dim bottom As Long
Dim top As Long
Dim i As Long
Dim x As Long
Dim tmpval As Variant
Dim tmpdoc As String
bottom = Lbound (arr_sort)
top = Ubound (arr_sort)
'Sort in descending order
If sortorder = "DESC" Then
For i = bottom+1 To top
x = i
tmpval = arr_sort(i)
tmpdoc = arr_docs(i)
Do While arr_sort(x-1) < tmpval
arr_sort(x) = arr_sort(x-1)
arr_docs(x) = arr_docs(x-1)
x = x - 1
If x=0 Then
Exit Do
End If
Loop
arr_sort(x) = tmpval
arr_docs(x) = tmpdoc
Next
'Sort in ascending order
Else
For i = bottom+1 To top
x = i
tmpval = arr_sort(i)
tmpdoc = arr_docs(i)
Do While arr_sort(x-1) > tmpval
arr_sort(x) = arr_sort(x-1)
arr_docs(x) = arr_docs(x-1)
x = x - 1
If x=0 Then
Exit Do
End If
Loop
arr_sort(x) = tmpval
arr_docs(x) = tmpdoc
Next
End If
ErrExit:
Exit Sub
Errhandle:
Call Addin_Messagebox("[QuickSort] " & Erl() & ": " & Err() & ", " & Error(),16,"Error Message",stats)
Resume ErrExit
End Sub
Public Sub Addin_Messagebox(msg As String, icon As Integer, title As String, stats As Variant)
If stats.pbar.enabled Then
Call stats.pbar.DisableProgressBar()
Messagebox msg,icon,title
Call stats.pbar.EnableProgressBar(False)
Else
Messagebox msg,icon,title
End If
End Sub
|
. |