Use DDE in PB
Department of Electronic and Information Engineering, Huazhong University of Science and Technology
PowerBuilder (hereinafter referred to as PB) is a powerful enterprise database application system development tool that can easily develop a database system front desk application. However, since the report developed by PB has certain limitations: the heads, column width, etc. of the report cannot be adjusted by the user in the application, so if the format of the user report changes, it must be in the original program. The report is adjusted, which greatly reduces the software flexibility.
Excel is Microsoft's form handling software, due to its simple use, in recent years, in recent years, it has been widely used in report processing in units. So, whether you can call Excel in the application to manage the data in the database in the application developed by PB, and adjust and print reports in the user-familiar EXCEL table processing software?
The answer is affirmative, this article describes how to use dynamic data exchange DDE (Dynamic Data Exchange) technology to transfer the data in the database to the Excel application in the PB so that the print report is adjusted by the user in Excel.
DDE is used to dynamically exchange data between two running applications on a Windows platform, which is a message-based protocol in a Windows operating system. Dynamic Data Exchange always occurs between two running programs, transmitting and receiving commands and data between two applications. These two running programs are called client programs and server programs, respectively, and the client is a program that requests the service, and the program for providing services is called a server program.
Implementation
DDE applications must be initially requested by the client before starting switching, establishing DDE connections to the server (also known as channels or sessions). The client can continuously send data requests and command requests to the server after establishing a connection.
In the application of this article, we do DDE application server programs with Excel, perform DDE connections in PB.
1. Start DDE Server Application
Excel.exe can be started separately in a Windows environment or start with RUN functions in the PB.
The syntax format of the RUN function in the PB is:
Run (string {, windowstate})
● String: Character type, program file name to be executed;
● WindowState: Enumeration, the window status after the program is launched, can be minimized (minimized!), Maximize (maxized!) Or original size (Normal!).
In this article, we start the server application Excel.exe from the client program PB and assume that the path to Excel.exe is:
C: / Program Files / Microsoft Office / Office / Excel .exe
The command to start the server application in the PB is:
Run ("C: / Program Files / Microsoft Office / Office / Excel.exe", Minimized!)
2. Establish DDE connection
After both the client program and the server programs are started, the connections between the client and server programs must be established. In the client program PB, you can use the OpenChannel function to establish this connection, open a channel from the client to the server program.
The format of the OpenChannel function in the PB is:
OpenChannel (appname, topicname {, windowhandle}))
● Appname: Application name of the server program;
● TopicName: Theme name, that is, the instance of the client to use, such as a Word document or Excel document;
● WINDOWHANDLE: The client's window handle, if the client program opens multiple windows, you need to specify one of the following as the client window. Establish a DDE connection is divided into cold connection, warm connection and thermal connection:
● Cold connection: refers to the direct data exchange between the client program and the server program, does not need to establish channels or connections, and specify the application name and project name each time.
● Warm connection: refers to the connection established with the OpenChannel function, which can operate the specified application using the handle returned by the OpenChannel function;
● Thermal connection: refers to the connection established with StartHotLink.
Whether it is a cold connection or a warm connection, it is unable to know whether the server is modified in the client, and the server side cannot know the client's data modification. With hot connections, you can make the server side and the client know whether the data is modified.
In this article, the Excel report is called in the PB, and the database data is transferred, and the modification processing of the data is not required, so it is appropriate to use warm connection.
3. Send data from the client to the server side
After establishing a client and server program, you can send database data to the server application's report in the client program to generate a complete report.
According to the DDE connection method, the setRemote function has two formats for cold connection and warm connections, respectively.
Cold connection:
SetRemote (Location, Value, Applname, TopicName)
Warm connection:
SetRemote (Location, Value, Handle {, WindowHandle})
We can read the data in the database and written to the Excel report file to print.
4. Execute the server-side command through the client
After the client is sent to the server Excel, the user can make appropriate adjustments to the report in Excel, then print the adjusted report, and close the Excel.
The commands in the server application (Excel.exe) can be executed remotely in the client, and the function of the remote command is Execremote. This function is also a cold connection and a warm connection according to DDE connection.
Cold connection:
Execremote (Command, Appname, TopicName)
Warm connection:
Execremote (Command, Handle, {WindowHandle})
Using this command, you can perform operations such as saving, printing, and closing of the Excel program at the PB client.
5. End DDE connection
When DDE is completed, use the CloseChannel function to close the DDE connection. The syntax format of this function is:
CloseChannel (Handle, {WindowHandle})
Programming instance
Suppose there is a school's grade management system, the student's grade report format is shown in Figure 1.
XX class test results list 2000 XX month
Name Chinese Mathematical English Physical Chemistry Total Average Average
First, in Excel, make a good news grade1.xls in the above format, and the average score between the whole class and the unevenness of the heads (assuming the number of students do not exceed 100). Then design a full-class average score, individual total score and calculation formula of personal achievements and personal averages in Excel to automate the numerical values required. Finally, the data is retrieved from the database and populates in Data Window Control DW_1.
Some source code is as follows:
Int I, Handle, N
String Excel, Road_excel
// Get the path of Excel
Registryget ("HKEY_CURRENT_USER / SOFTWARE / Microsoft / Office / 8.0 /" & "Excel / Microsoft Excel", "GalleryPath", RegString!, Road_Excel) Excel = Road_excel "/ Excel.exe"
/ / Start the server program and open the report grade1.xls
Run (Excel "Grade1.xls)
/ / Create the connection channel to the server program
Handle = OpenChannel ("Excel", "grade1.xls")
n = dw_1.count ()
/ / Pass data to Excel report
For i = 1 to n
Handle = OpenChannel ("Excel", "grade1.xls")
SetRemote ("R" String (i 1) "C1", String (dw_1.getitemnumber (i 1)), Handle)
SetRemote ("R" String (i 2) "C1", String (dw_1.getitemnumber (i 2)), Handle)
SetRemote ("R" String (i 3) "C1", String (dw_1.getitemnumber (i 3)), Handle)
SetRemote ("R" String (i 4) "C1", String (dw_1.getitemnumber (i 4)), handle
SetRemote ("R" String (i 5) "C1", String (dw_1.getitemnumber (i 5)), handle
NEXT
/ / Print an Excel report in the client program
Execremote ("[Print ()]", "Excel", "grade1.xls")
// Close does not save grade.xls
Execremote ("[Close (False)", "Excel", "Grade1.xls")
// Exit Excel
Execremote ("Quit ()]", "Excel", "System")
// Turn off the connection channel and end the DDE session
Close (Handle)
Note: Starting the Excel server program from the client program and the establishment of the connection and transfer data must have a certain time interval, it is best to put the boot Excel in an event, put the establishment connection and pass data in another event, Avoiding that Excel has not fully launched due to connection and delivery data, resulting in establishing connection and delivery data abnormal.
