Use Lotus script to generate graphical EXCEL reports with Lotus Domino data

Introduction:Lotus Domino is very powerful and can be usedLotusDesigner can develop a variety of Domino applications that are very complex and powerful.Program. This article describes how to use Lotus script to generate Graphical Data Using Domino data.ExcelReport, and integrate the Excel report into the domino application.

Introduction

In general, Lotus Domino has powerful functions. using Lotus designer, you can develop a variety of Domino applications that are very complex and powerful. However, Domino itself cannot generate a wide range of functions such as Microsoft Excel, especially graphical reports. This article describes how to use Lotus script to generate a graphical Excel report using Domino data and integrate the Excel report into the domino application. The Excel application will not appear throughout the process, and the end user will only see the domino application at the front end. All interactions with Excel will be completed in the background.

Object Linking and Embedding (OLE) object

OLE technology, called object connection and embedding technology, is an object-oriented technology. By defining and implementing applications as a mechanism for connecting objects to each other, the integration between applications is completed. Ole is a set of comprehensive standards for transmitting and sharing information between customer applications. Based on the Component Object Model (COM), Ole is now widely used in workbooks, word processing, financial software, project management software.

Createobject and GetObject Functions

To create or obtain an OLE object in LotusScript., you must use the Createobject function or the GetObject function.

Createobject function:

Create an OLE object of the specified type

Syntax

Createobject (classname)

Parameters

Classname is a string in appname. appclass format, indicating the type of the object to be created, such as "wordpro. application ". Appname indicates the application name that supports Ole, and appclass indicates the type of the object to be created

Return ValueReturns an OLE object reference.

Usage

In LotusScript., set is used to assign the object reference returned by the Createobject function to a variable of the variant type (variant.

If this type of application is not running, the Createobject function starts this application before creating an OLE object. OLE object reference is valid only when the application is running. If the application exits when OLE object reference is used, LotusScript will throw a runtime error. Each OLE object has a set of classes defined and provided by it, through which the application can be operated.

Example

Sub Initialize 'create a Word.Application  object Set MyApp = CreateObject (" Word.Application "" 'set the visible property of the object to true MyApp.Visible  = True End Sub

GetObject function:

Open the OLE object contained in an application file or return a specified type of OLE object for the current activity.

Syntax

GetObject ([pathname] [, classname])

Parameters

Pathname: An application file that contains the full path file name or is empty. This application must support Ole. If it is an empty string ("") or omitted, you must specify a classname and return the objects of the current activity.

Classname is the same as the classname parameter of Createobject. However, it can be omitted. If it is omitted, more pathname is used to determine the returned OLE object.

Return ValueReturns an OLE object reference.

UsageSame as Createobject

Example

Sub Initialize Dim myDoc As Variant 'from the file WordPro.Document Object. Set mydoc = getobject("d:\wordpro\docs\ test.lwp "," WordPro.Document "" 'call WordPro.Document  The print method of the object. myDoc.Print End Sub

Key points and skills for generating Microsoft Excel graphical reports using LotusScript.

1. Make sure that the Excel file is properly installed.
2. To learn more about VBACodeYou can use a recording macro to generate the corresponding VBA code, and then modify it into your own code in the generated VBA code. In this way, you can quickly understand the VBA functions that need to complete a function, and then use the help to quickly understand the function calls that have completed related functions.
3. If you use LotusScript. to directly generate a chart and fine-tune the chart format, you need a lot of Code related to the format adjustment. If you want to adjust the format later, you also need to change the code, which is inconvenient. You can use Excel to draw a chart with the adjusted format, save it as a template, and store it in the domino application as an attachment. Then, when you need to generate a chart, unbind it and open the Excel table, write the corresponding data into an Excel table, so that the chart is automatically generated, and it is easy to adjust the format.

The following two examples illustrate how to use Domino data to generate a graphical Excel report.

Example 1: use Excel to generate a simple pie chart and display it in the domino application.

There is already a view exceltest in exceltest. nsf, which contains some documents, each containing the name and age information, such:

Create an Excel file, fill in the following data in the rectangle between A1 and G2, create a pie chart, set its source data to A1 to G2, and then set other attributes of the pie chart. This Excel file will be used as a template. You can find the template chart1.xls in the attachment.

Create a view named generate chart1. The function is to count the age groups (under 20, 20 ~ 29,30 ~ ~ 49,50 ~ 59,60 and above) and use chart1.xls as the template to generate an Excel pie chart. to generate different charts, you only need to change the chart format type in the template and save it. The Code is as follows:

Listing 1

Sub Click(Source As Button) 'define an array to hold the number of people in each age group Dim countArr(5) As Integer Dim s As New NotesSession Dim ws As New NotesUIWorkspace Dim db As NotesDatabase Set db = s.CurrentDatabase Dim vw As NotesView Set vw =  db.GetView ("ExcelTest") Dim doc As NotesDocument Set doc =  vw.GetFirstDocument 'calculate the number of people in each age group While Not doc Is Nothing age% = Cint( doc.Age (0)) If age%<20 Then countArr(0) = countArr(0) + 1 Elseif age%>=20 And age%<30 Then countArr(1) = countArr(1) + 1 Elseif age%>=30 And age%<40 Then countArr(2) = countArr(2) + 1 Elseif age%>=40 And age%<50 Then countArr(3) = countArr(3) + 1 Elseif age%>=50 And age%<60 Then countArr(4) = countArr(4) + 1 Else countArr(5) = countArr(5) + 1 End If Set doc =  vw.GetNextDocument (doc) Wend 'generate excel chart Call generateExcelChart1(countArr, "C:\Chart1.xls") Dim uiChartDoc As NotesUIDocument Set uiChartDoc =  ws.ComposeDocument ( "", "", "Chart" ) uiChartDoc.GotoField ("Body") 'paste the generated excel chart into a document Call  uiChartDoc.Paste End Sub

The generateexcelchart1 () code for generating an Excel chart is as follows:

Listing 2

Sub generateExcelChart1(countArr As Variant, excelFileName As String) 'define excel related variables Dim excelApplication As Variant Dim excelWorkbook As Variant Dim excelSheet As Variant 'create an excel object Set excelApplication = CreateObject(" Excel.Application "" 'make the EXCEL program invisible excelApplication.Visible  = False 'open the template file Set excelWorkbook =  excelApplication.Workbooks.Open (excelFileName) Set excelSheet =  excelWorkbook.Worksheets ("Sheet1") 'fill the chart with source data excelSheet.Cells (2,2) = countArr(0) excelSheet.Cells (2,3) = countArr(1) excelSheet.Cells (2,4) = countArr(2) excelSheet.Cells (2,5) = countArr(3) excelSheet.Cells (2,6) = countArr(4) excelSheet.Cells (2,7) = countArr(5) 'copy the generated chart to the clipboard excelSheet.ChartObjects (1). Chart.ChartArea.Copy 'exit the Excel application without saving excelWorkbook.Close  False excelApplication.Quit End Sub

Click Generate chart1 in view exceltest1 to generate the following pie chart in notes:

In the above example, the age group is fixed into several categories. Therefore, you can write it in an Excel template and select the data source of the pie chart directly. If this category is dynamic, the above method won't work. At this time, you need to use a program to set the attributes of some Excel Charts.

Example 2: use Excel to generate a dynamic classification chart and display it in the domino application.

In this example, count the number of Chinese surnames and generate a Statistical Chart.

Create an Excel file, create a graph, and set its attributes, such as font and color. This Excel file will be used as a template. You can find the template chart2.xls in the attachment.

In the exceltest. nsf view exceltest, create a view named generate chart2. The function is to collect the last name distribution and generate a Statistical Chart Based on the personal information in Domino. The Code is as follows:

Listing 3

Sub Click(Source As Button) 'define a list to hold the number of people with each surname Dim nameList List As Integer Dim s As New NotesSession Dim ws As New NotesUIWorkspace Dim db As NotesDatabase Set db = s.CurrentDatabase Dim vw As NotesView Set vw =  db.GetView ("ExcelTest") Dim doc As NotesDocument Set doc =  vw.GetFirstDocument 'count the number of each surname While Not doc Is Nothing sName$ = Left( doc.Name (0), 1) If Iselement (namelist(sName$)) Then nameList(sName$) = nameList(sName$) + 1 Else nameList(sName$) = 1 End If Set doc =  vw.GetNextDocument (doc) Wend 'generate excel chart Call generateExcelChart2(nameList, "C:\Chart2.xls") Dim uiChartDoc As NotesUIDocument Set uiChartDoc =  ws.ComposeDocument ( "", "", "Chart" ) uiChartDoc.GotoField ("Body") 'paste the generated excel chart into a document Call  uiChartDoc.Paste End Sub

The generateexcelchart1 () code for generating an Excel chart is as follows:

Listing 4

Sub generateExcelChart2(nameList As Variant, excelFileName As String) 'define excel related variables Dim excelApplication As Variant Dim excelWorkbook As Variant Dim excelSheet As Variant 'create an excel object Set excelApplication = CreateObject(" Excel.Application "" 'make the EXCEL program invisible excelApplication.Visible  = False 'open the template file Set excelWorkbook =  excelApplication.Workbooks.Open (excelFileName) Set excelSheet =  excelWorkbook.Worksheets ("Sheet1") 'fill the chart with source data excelSheet.Cells (2,1) = "surname distribution map" i% = 2 Forall counter In nameList sName$ = Listtag(counter) excelSheet.Cells (1,i%) = sName$ excelSheet.Cells (2,i%) = counter i% = i% + 1 End Forall 'set the source data for the chart Call  excelSheet.ChartObjects ("Chart 1"). Chart.SetSourceData ( excelSheet.Range ( excelSheet.Cells (1, 1),  excelSheet.Cells (2, i%)), 1) 'copy the generated chart to the clipboard excelSheet.ChartObjects (1). Chart.ChartArea.Copy 'exit the Excel application without saving excelWorkbook.Close  False excelApplication.Quit End Sub

In view exceltest1, click Generate chart2 to generate a table like a table in notes and run excel in the background. The user does not know that this operation is related to excel throughout the process.

Summary

Generally, the above two examples show that the use of Lotus script. it is very easy to generate a graphical Excel report using Domino data, so that excel is seamlessly integrated into Domino, and Excel's powerful report functions can be used in Domino programs, this is a major supplement to the lack of Domino's own report functions. With this method, you can easily develop a powerful report program with many functions.