WPS Report Export Plugin

1 VB Development WPS Plug-in step 1.1 start the program 1.1.1 The sample code on the network

Option Explicit

Implements IDTExtensibility2

Private Sub idtextensibility2_onconnection (ByVal application as Object, ByVal ConnectMode as Addindesignerobjects.ext_ ConnectMode, ByVal addInInst as Object, custom () as Variant)

On Error Resume Next ' ERROR-Proof handling

' WPs Toolbar object is Kso.commandbar (represents a toolbar)

' WPs's toolbar collection is Kso.commandbars (representing all toolbars)

' We can create a toolbar with the Add method provided by Kso.commandbars, such as

Dim myComBar as Kso.commandbar ' defines a toolbar object

Application.CommandBars ("My Custom Toolbars"). Delete ' generally we want to delete a toolbar with the same name as possible before creating a new toolbar

Set myComBar = Application.CommandBars.Add ("My custom Toolbar", Ksobartop, True) ' Create a toolbar

The four parameters of the Add method are: Toolbar name, location, whether to replace the active menu bar with the new command bar, whether it is a temporary command bar, generally except for the first name, the other three parameters such as the above settings can be

' Well, now we've created a toolbar, but the toolbar is just a container with nothing on it, so we're going to create buttons and pop-up menus on the toolbar:

Dim Mypopup as Kso.commandbarpopup ' defines a popup menu

Dim mybtn as Kso.commandbarbutton ' defines a button

Set mypopup = MyComBar.Controls.Add (Ksocontrolpopup,,, True) ' Create a pop-up menu on the toolbar myComBar

Mypopup.caption = "I am the popup menu on the toolbar" ' Set the Caption property of the popup menu, it will be displayed on the interface

Set mybtn = MyComBar.Controls.Add (Ksocontrolbutton,,, True) ' Create a button on the toolbar myComBar

Mybtn.caption = "I am the button on the toolbar" The Caption property of the Set button, which will be displayed on the interface

' Now that there's a pop-up menu and a button on the toolbar, but there's nothing on the pop-up menu, we now create two buttons on the popup menu:

Set mybtn = MyPopup.Controls.Add (Ksocontrolbutton,,, True) ' Create a button on the pop-up menu Mypopup

Mybtn.caption = "I am the button on the pop-up menu 1"

Set mybtn = MyPopup.Controls.Add (Ksocontrolbutton,,, True) ' Create a button on the pop-up menu Mypopup

Mybtn.caption = "I am the button on the pop-up menu 2"

Mycombar.visible = True ' finally set the Visible property of the newly created toolbar to true to make it visible

' Now with the above code as a template, you can do the following several events

' 1. Create one or more toolbars

' 2. Create one or more pop-up menus and buttons on the toolbar

' 3. Create one or more buttons on the pop-up menu

End Sub

Private Sub idtextensibility2_ondisconnection (ByVal RemoveMode as AddInDesignerObjects.ext_DisconnectMode, custom () As Variant)

End Sub

Private Sub Idtextensibility2_onstartupcomplete (custom () as Variant)

End Sub

Private Sub idtextensibility2_onaddinsupdate (custom () as Variant)

End Sub

Private Sub Idtextensibility2_onbeginshutdown (custom () as Variant)

End Sub

1.1.2 Initial adjustment of the Code

Option Explicit

Private WithEvents BtnNew1 as CommandBarButton

Private WithEvents BtnNew2 as CommandBarButton

Private WithEvents BTNNEW3 as CommandBarButton

Implements IDTExtensibility2

Private Sub idtextensibility2_onconnection (ByVal application as Object, ByVal ConnectMode as Addindesignerobjects.ext_ ConnectMode, ByVal addInInst as Object, custom () as Variant)

On Error Resume Next ' ERROR-Proof handling

' WPs Toolbar object is Kso.commandbar (represents a toolbar)

' WPs's toolbar collection is Kso.commandbars (representing all toolbars)

' We can create a toolbar with the Add method provided by Kso.commandbars, such as

Dim myComBar as Kso.commandbar ' defines a toolbar object

Application.CommandBars ("My Custom Toolbars"). Delete ' generally we want to delete a toolbar with the same name as possible before creating a new toolbar

Set myComBar = Application.CommandBars.Add ("My custom Toolbar", Ksobartop, True) ' Create a toolbar

The four parameters of the Add method are: Toolbar name, location, whether to replace the active menu bar with the new command bar, whether it is a temporary command bar, generally except for the first name, the other three parameters such as the above settings can be

' Well, now we've created a toolbar, but the toolbar is just a container with nothing on it, so we're going to create buttons and pop-up menus on the toolbar:

Set BtnNew1 = MyComBar.Controls.Add

Btnnew1.caption = "Export Weekly"

Set btnNew2 = MyComBar.Controls.Add

Btnnew2.caption = "Export Weekly"

Set BTNNEW3 = MyComBar.Controls.Add

btnnew3.caption = "Config"

Mycombar.visible = True ' finally set the Visible property of the newly created toolbar to true to make it visible

' Now with the above code as a template, you can do the following several events

' 1. Create one or more toolbars

' 2. Create one or more pop-up menus and buttons on the toolbar

' 3. Create one or more buttons on the pop-up menu

End Sub

Private Sub idtextensibility2_ondisconnection (ByVal RemoveMode as AddInDesignerObjects.ext_DisconnectMode, custom () As Variant)

End Sub

Private Sub Idtextensibility2_onstartupcomplete (custom () as Variant)

End Sub

Private Sub idtextensibility2_onaddinsupdate (custom () as Variant)

End Sub

Private Sub Idtextensibility2_onbeginshutdown (custom () as Variant)

End Sub

Private Sub Btnnew1_click (ByVal Ctrl as Kso.commandbarbutton, CancelDefault as Boolean)

MsgBox (1)

End Sub

Private Sub Btnnew2_click (ByVal Ctrl as Kso.commandbarbutton, CancelDefault as Boolean)

MsgBox (2)

End Sub

Private Sub Btnnew3_click (ByVal Ctrl as Kso.commandbarbutton, CancelDefault as Boolean)

MsgBox (3)

End Sub

1.2 Registration documents

Windows Registry Editor Version 5.00

[Hkey_current_user\software\kingsoft\office\et\addins\work.report]

"FriendlyName" = "wps Add-on Demo"

"Description" = "Add-ons"

"LoadBehavior" =d:00000003

"CommandLineSafe" =d:00000001

1.3 Registration Steps

1, through the VB Production Work.dll dynamic Library

2, the production of registration documents Work.reg

3, registration files: double-click Work.reg; In the Run box, enter: regsvr32 .../work.dll

2 VB Development WPS Plugin 2.1 VB Add toolbar

Public Sub Create toolbar pop-up menu button ()

On Error Resume Next ' ERROR-Proof handling

' WPs Toolbar object is Kso.commandbar (represents a toolbar)

' WPs's toolbar collection is Kso.commandbars (representing all toolbars)

' We can create a toolbar with the Add method provided by Kso.commandbars, such as

Dim myComBar as Kso.commandbar ' defines a toolbar object

Application.CommandBars ("My Custom Toolbars"). Delete ' generally we want to delete a toolbar with the same name as possible before creating a new toolbar

Set myComBar = Application.CommandBars.Add ("My custom Toolbar", Ksobartop, True) ' Create a toolbar

The four parameters of the Add method are: Toolbar name, location, whether to replace the active menu bar with the new command bar, whether it is a temporary command bar, generally except for the first name, the other three parameters such as the above settings can be

' Well, now we've created a toolbar, but the toolbar is just a container with nothing on it, so we're going to create buttons and pop-up menus on the toolbar:

Dim Mypopup as Kso.commandbarpopup ' defines a popup menu

Dim mybtn as Kso.commandbarbutton ' defines a button

Set mypopup = MyComBar.Controls.Add (Ksocontrolpopup,,, True) ' Create a pop-up menu on the toolbar myComBar

Mypopup.caption = "I am the popup menu on the toolbar" ' Set the Caption property of the popup menu, it will be displayed on the interface

Set mybtn = MyComBar.Controls.Add (Ksocontrolbutton,,, True) ' Create a button on the toolbar myComBar

Mybtn.caption = "I am the button on the toolbar" The Caption property of the Set button, which will be displayed on the interface

' Now that there's a pop-up menu and a button on the toolbar, but there's nothing on the pop-up menu, we now create two buttons on the popup menu:

Set mybtn = MyPopup.Controls.Add (Ksocontrolbutton,,, True) ' Create a button on the pop-up menu Mypopup

Mybtn.caption = "I am the button on the pop-up menu 1"

Set mybtn = MyPopup.Controls.Add (Ksocontrolbutton,,, True) ' Create a button on the pop-up menu Mypopup

Mybtn.caption = "I am the button on the pop-up menu 2"

Mycombar.visible = True ' finally set the Visible property of the newly created toolbar to true to make it visible

' Now with the above code as a template, you can do the following several events

' 1. Create one or more toolbars

' 2. Create one or more pop-up menus and buttons on the toolbar

' 3. Create one or more buttons on the pop-up menu

End Sub

2.2 VB parsing xml

Sub ee ()

Dim Objxmldom as New DOMDocument

Dim Objxmlnodelist as IXMLDOMNodeList

Dim objXMLNode as IXMLDOMNode

Dim document as New DOMDocument

Objxmldom.async = False

Objxmldom.validateonparse = False

Dim bsuccess as Boolean

bsuccess = Objxmldom.load ("D:\sample.xml")

' bsuccess = objxmldom.load (str)

MsgBox bsuccess

MsgBox Objxmldom.xml

Dim Str as String

str = Objxmldom.xml

Dim ObjXMLNodeList2 as IXMLDOMNodeList

Dim ObjXMLNode2 as IXMLDOMNode

Document.loadxml Str

MsgBox document.xml

End Sub

3 API for exporting reports

API address to access: http://115.28.150.92:80/

config_access_key= e44e560940e5a0a180948ef814804a91

Config_secret_key=5792c3964094e0be8077ceb0f145f2e7

Weekly:

Http://115.28.150.92:80/taskreports/get_taskweekreport?access_key=e44e560940e5a0a180948ef814804a91&secret_ key=1a14605e4077eacb80812cc85cc4a120&user_id=5510f536409454a2803db85754686cb3&week_start=2014.41& week_end=2014.41

Monthly:

Http://115.28.150.92:80/taskreports/get_taskmonthreport?access_key=e44e560940e5a0a180948ef814804a91&secret _key=5792c3964094e0be8077ceb0f145f2e7&user_id=2a446e0e40e01356801344a4a9a3af84&month_start=2014.9& month_end=2014.9

4 WPS Form Development problems encountered 4.1 run-time error ' 91 '

1 ) Issue: Add the following code to the Click event

Dim FM as Form1

Fm.show

The following error will be reported

Run-time error ' 91 '

Object variable or with block variable not set (has an object variable defined, but not set)

2 ) Countermeasures:

Modify the code as follows

Dim FM as New FORM1

Fm.show

3) new question: run-time error ' 406 '

Non-modle forms cannot is display in the this host application from the active Dll,active Control,or property Page (cannot be moved by Active State Library, active control, property page display the non-modal window in the host program)

4) New strategy: Modify to modal window

Dim FM as New FORM1

Fm.show (1)

4.2 Revised the style of the monthly report, not the results of the data. 4.2.1 Determine if there is a sheet with the same name

Try:on Error GoTo Catch

' New Monthly Report form

Set xlapp = GetObject (, "ET. Application ")

' Determine if there is a current workbooks, select the current activity, or create a new one

If xlApp.Workbooks.Count > 0 Then

Set xlbook = Xlapp.activeworkbook

Else

Set xlbook = XlApp.Workbooks.Add

End If

' Dim I as Integer

' For i = 1 to Xlbook.worksheets.count-1

' Xlbook.worksheets (i). Delete

' Next

' Set xlsheet = Xlbook.activesheet

Set xlsheet = XlBook.Sheets.Add

Xlsheet.name = strSheetName

Newmonthsheet = True

Finally

MsgBox "Quit"

xlapp = Nothing

Exit Function

Catch:

MsgBox "New Workbook error"

Resume finally

4.2.2 HTTP GET information

Weekly Click events

Private Sub Btnnew1_click (ByVal Ctrl as Kso.commandbarbutton, CancelDefault as Boolean)

' Get weekly

Dim Bytdata () as Byte

Dim Objhttp as Object

Dim URL as String

Try:on Error GoTo Catch

url = "http://115.28.150.92:80/taskreports/get_taskweekreport?access_key=e44e560940e5a0a180948ef814804a91& secret_key=5792c3964094e0be8077ceb0f145f2e7&user_id=2a446e0e40e01356801344a4a9a3af84&week_start= 2014.40&week_end=2014.40 "

Set objhttp = CreateObject ("MSXML2. XMLHTTP ")

Objhttp.open "GET", url, False

Objhttp.send

If Objhttp.status =

Bytdata = Objhttp.responsebody

Debug.Print StrConv (Bytdata, Vbunicode)

Bytdata = Utf8_decode (bytdata)

' MsgBox (Bytdata)

' Parse the JSON string, you must first convert the byte type to a string type

Dim SS as String

SS = Bytdata

Dim strxml

Strxml = Parsejson (ss)

' MsgBox (strxml)

' Analytic weekly

Parseweeklyxml (Strxml)

End If

Finally

Set objhttp = Nothing

Exit Sub

Catch:

MsgBox "request failed, please confirm that the requested information entered is valid"

Resume finally

End Sub

4.3 Format requirements for exporting weekly reports

1, do not modify the report format of the weekly, otherwise it will cause the exported data error

2, the weekly report of the line is: title, week One days of the specific work content (do not leave blank line, otherwise parse error), weekly summary and plan.

3, the last three lines of the weekly report must be: This week's work summary, the next week's working plan, I suggest (General situation, I suggest is empty, but also can't delete it)

4, get the JSON string, you need to remove its space, otherwise it cannot parse XML

5 plug-in deployment Test 5.1 deploy mode

To register the file, write the batch file Setup.bat

1. Registration date control. The general machine is not registered date control Mscomct2.ocx, determine the number of bits of the machine registration date control

2, install the WPS et plug-in. Split into two steps: Write the registry and register the dynamic library.

3. batch files are as follows:

Click Install

@ The plugin implemented in WPS et export NERCOA report

ECHO Registration VB Date control

If%processor_architecture%==x86 (Echo 32 bit)

Copy MSCOMCT2. OCX%windir%\system32\

Else (echo 64 bit)

Copy MSCOMCT2. OCX%windir%\syswow64\

regsvr32 MSCOMCT2. ocx/s

ECHO, please wait a moment.

ECHO install WPS et plugin

REGEDIT/S Wpsetplugin.reg

ECHO, please wait a moment.

ECHO registers WPS's dynamic link library

regsvr32 work.dll/s

ECHO, please wait a moment.

EXIT

5.2 Problems that arise

1. The add-in did not succeed

Cause: Batch registration file failed with insufficient reason permissions

Countermeasures: Step through the registry, registration files

New problem: ... has been loaded, but the call to DllRegisterServer failed.

Cause: Insufficient permissions for the operation user

New countermeasure: Open "Command Prompt" as administrator, enter "regsvr32 ... \work.dll", display registration successful

2, access to personal weekly data is not complete

Show XML parsing errors

3, HTTP request failed, get user basic information error

Objhttp.open "GET", URL, False error

Pop-up error message:

WPS Report Export Plugin