Archive for category Office Development

Adding custom pane to the ribbon in Microsoft Word 2007 using OpenXML SDK 2.0 and VBA

Source code
In the last article we looked at how we add ribbon to a word document, how do we add controls to the ribbon and how do we provide interactivity to the ribbon controls and our tab looked like this:-

clip_image002

However, what if we have more links or more contacts (which is indeed is the case).

clip_image004

Looks clumsy, isn’t it? Also, it would get clumsier if we have more links and contacts. So what If we have something like this:-

clip_image006

Notice the little downward arrow at the right corner of each group. Clicking each would open a pane showing all the links/email addresses. Again clicking any link/email address in the custom pane would insert the link/email in the document. This way we show very few things to the user (thereby not making the ribbon clumsy) and If user wants to see more, he opens the pane to see more. This is very similar to the “Font” group in the “Home” tab.

This time we will make changes to the existing solution and following are the changes:-

a) Prepare the list of hyperlinks and email addresses to be shown to the user in the custom pane.

b) Creating the custom panes

c) Adding data to the custom panes

d) Creating that clip_image008 (dialog launcher) button (the button when when clicked will open the custom pane)

e) Add interactivity for the custom pane.

a) Prepare the list of hyperlinks and email addresses to be shown to the user in the custom pane :-

One way we can add the list of hyperlinks and the email addresses to the document is to add them as XML fragments (also called “CustomXmlParts”). Following is how we do it:-

i) Create XML for the list we want to show in the pane. Data for this XML can also come from the database.

clip_image010

ii) Add the XML to the document as CustomXmlPart

clip_image012

AddCustomXmlPartsToDocument():-

149-150 – Delete all the existing CustomXMLParts from the document.

152-153 – Add the TextHyperlinks XML to the document as CustomXMLPart by calling AddCustomXmlPart()

155-156 – Add the EmailLinks XML to the document as CustomXMLPart by calling AddCustomXmlPart()

iii) Add the call to AddCustomXmlPartsToDocument() to the AddRibbonToDocument()

clip_image014

At this point of time if we run the application, the word document does not contain anything visiblely different in the Microsoft word. However, If you zip and unzip it, you should see the custom XML parts as separate files in the zipped file.

clip_image016

clip_image018

clip_image020

There must be a way to give a proper name to the CustomXMLPart. I need to see that as well. That, however is not important for this example.


b) Creating the custom panes :-

We need two custom panes. One for the hyperlinks and other for the email addresses.

Open the “TheDocumentWithMacros.docm” and open the developer tab. Add two “User Forms”

clip_image022

In each “User Form”, add a listbox. My project explorer looks like this:-

clip_image024

“EmailLinksPane” user form contains a listbox with name “lstTextHyperlinks” and “TextHyperlinksPane” user form contains a listbox named “lstEmailLinks”.

Remember we created these user forms in the “TheDocumentWithMacro” and the all the user forms and the associated controls (listbox) will get copied to the document just like the macros get copied.


c) Adding data to the custom panes:-

We need to add data to the custom panes when the ribbon loads. CustomIUI XML provide a onLoad event on the ribbon element. (not showing whole of XML below)

clip_image026

That “RibbonLoad” is a VBA subroutine. We can get the customXMLParts from the document and put them in global variables. When the user clicks on the clip_image008[1] button, we fill the listboxes with the corresponding global variable. Following

clip_image028

clip_image030

clip_image032

d) Creating that clip_image008[2] button

clip_image008[3] is a dialogbox launcher. Following is the modified ribbon XML with added part highlighted:-clip_image034

At this point of time if we run the application, we should see the button (clip_image008[4]) for both the links and the contacts group.

clip_image036

e) Adding interactivity for the custom pane :-

Notice the dialogbox launcher has a button which has a action handler for onAction. In that action handler, we will load the listbox of the corresponding user form with the global variable created on ribbon onload and show the user form to the user.

clip_image038

clip_image040

At this point of time if we run the application, we should be able to show/hide the hyperlinks/email panes from the document.

User should also be able to insert the hyperlinks/email from the custom pane. For that, we need to add the code in the listbox click event which will call the same function which is called when you click a visible button which is right on the ribbon.

This is for the hyperlinks custom pane list-

clip_image042

This is for the emails custom pane list:-

clip_image044

Now If you click on any hyperlink/email item in the corresponding listbox, the hyperlink/email will be inserted in the document.

, ,

Leave a comment

Customizing Ribbon in Office 2007 using Open Office XML and VBA

Code for this article can be downloaded from here.

Ribbon:-

clip_image002

clip_image004

From http://office.microsoft.com/en-gb/help/use-the-ribbon-HA010089895.aspx :-“The Ribbon is designed to help you quickly find the commands that you need to complete a task. Commands are organized in logical groups, which are collected together under tabs. Each tab relates to a type of activity, such as writing or laying out a page. To reduce clutter, some tabs are shown only when needed. For example, the Picture Tools tab is shown only when a picture is selected.”

What to expect from this article:-

At the end of this article you should be able to customize the ribbon.

clip_image006

We will add a tab named “My Zone” containing two groups, “My links” and “My Contacts” containing your web site links and email addresses of your contacts.

clip_image008

When you click on any of the “links” in the “My Links” group, that link will get added to the document at the current position of the cursor.

clip_image010
clip_image012

Ribbon XML:-

From http://msdn.microsoft.com/en-us/library/aa942866.aspx:-

The Ribbon (XML) item enables you to customize a Ribbon by using XML. Use the Ribbon (XML) item if you want to customize the Ribbon in a way that is not supported by the Ribbon (Visual Designer) item

Example RibbonXML:-

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="RibbonLoad">
<ribbon>
<tabs>
<tab id="tabMyZone" label="My Zone">
<group id="grpLinks" label="My links">
<button id="Google" label="Google" tag="http://www.google.com"/>
<button id="Facebook" label="Facebook" tag="http://www.facebook.com"/>
<button id="Gmail" label="Gmail" tag="http://www.gmail.com"/>
</group>
<group id="grpEmailAddresses" label="My Contacts">
<button id="e1" label="Ashish Gupta" tag="ashish.kuber@wipro.com"/>
<button id="e2" label="Sachin Tendulkar" tag="sachinrt@yahoo.com"/>
<button id="e3" label="James Hetfield" tag="James.Hetfield@metallica.com"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>

How to add RibbonXML(CustomUI XML) to the word using OpenXML SDK

Pre-requisite :- Open XML SDK 2.0 which can be downloaded from here. (Download the OpenXMLSDKv2.msi which is about 4 MB in size)

Additional tools :-

a) The schema for ribbon XML which can be downloaded from here.

b) Custom UI Editor which allows you to add a custom UI part to a document. This can be downloaded from here.

Lets Start….

Create a console application and add a empty word document to it. The document should be macro-enabled (with .docm) extension. Add the Xml for the Ribbon as well. My solution explorer looks like this :-

clip_image002[8]

For the purpose of the example in this document we will have a Ribbon XML created already and we would put the same into an existing word document on run-time.

static string documentName = "document.docm";
static string ribbonXMLFileName = "Ribbon.xml";
static void Main(string[] args)
{
   byte[] updatedByteContent = AddRibbonToDocument(File.ReadAllBytes(documentName));
   if (updatedByteContent != null)
   {
         using (FileStream fileStream = new FileStream(documentName, FileMode.Create))
         {
             fileStream.Write(updatedByteContent, 0, updatedByteContent.Length);
         }

   }
   if (File.Exists(documentName)) 
   {
    Process.Start(documentName);
   }

}

Line 6:- Reads the contents of the document.docm file and pass the binary content to the AddRibbonToDocument() method which will add the ribbon to the document.

Line 7-14:- The binary content of the file (which also contains the ribbon now) is written to the same file as original and opened.

Add the below method in Program.cs. This method takes the binary content of a word file and adds the Ribbon XML to it.

public static byte[] AddRibbonToDocument(byte[] documentContent)
{
   byte[] updatedDocumentContent = null;
   if (documentContent != null)
   {
       using (MemoryStream memoryStream = new MemoryStream())
       {
         memoryStream.Write(documentContent, 0, documentContent.Length);
         string ribbonXMLAsString = GetRibbonXML().ToString();
         using (WordprocessingDocument myDoc = WordprocessingDocument.Open(memoryStream, true))
         {
           MainDocumentPart mainPart = myDoc.MainDocumentPart;
           if (myDoc.GetPartsCountOfType<RibbonExtensibilityPart>() > 0)
           {
                myDoc.DeletePart(myDoc.GetPartsOfType<RibbonExtensibilityPart>().First());
           }  

           RibbonExtensibilityPart ribbonExtensibilityPart = myDoc.AddNewPart<RibbonExtensibilityPart>();
           ribbonExtensibilityPart.CustomUI = new DocumentFormat.OpenXml.Office.CustomUI.CustomUI(ribbonXMLAsString);
           myDoc.CreateRelationshipToPart(ribbonExtensibilityPart);
         }

         updatedDocumentContent = memoryStream.ToArray();
       }

    }

    return updatedDocumentContent;
}

Line 4-8 :- The binary content of the document is put in a MemoryStream which will be used for any modification in the content.

Line 9 :– The ribbon XML is got from the GetRibbonXML() method which can either get the XML from a static file or dynamically construct from the database values.

Line 10 :- WordProcessingDocument object is initialized from the memorysteam of the document content. The second parameter of the constructor is “IsEditable” is set to true as we are going to modify its content.

Line 14- 15 :- Get the main document part from the wordprocessing document and delete any existing custom ribbon from it.

Line 17 – 19 :- Content of the ribbon XML needs to be added as a RibbonExtensibilityPart to the main document and a relationship will be created for the same. Infact any type of content you add to a document as a part, you must create its (part’s) relationship with the document so that document can load up that part when you open the document in MSWord (or in general MS Office).

So, at this point of time If you run the application, the document.docm should get opened and you should see the tab and the buttons.

clip_image008

More explanation on line 17-19

Document structure before you ran the above code :-

clip_image002[10]

Look at the _rels/.rels file. You don’t see anything related to Custom UI here.

clip_image004[6]

Document structure after the above code is run:-

You will see a customUI folder created here:-

clip_image006[5]

Open the CustomUI folder and the file inside it will contain the same ribbonXml you inserted using the code above:-

clip_image008[5]

Look at the _rels/.rels file. You will see a new entry stating the relationship with the CustomUI.xml file here.

clip_image010[6]

btw, If you click on any of the buttons now, nothing would happen as we haven’t added any interactivity features to those buttons.

Adding interactivity to the ribbon elements:-

1) Make a copy of the Document.docm and rename the copied document to “DocumentWithMacros.docm”. Open the developer tab and insert a “Module” to the project.

clip_image002[13]

 

2) Rename the module to OfficeMacroHelper:-

clip_image004[9]

3) Add the following macro code to the module. Then save and close the word file:-

Sub CreateTextHyperLink(control As IRibbonControl)
 CreateHyperlink control.Tag, control.id
End Sub

Sub InsertTextHyperlink(hyperlink As String, textTodisplay As String)
 CreateHyperlink hyperlink, textTodisplay
End Sub

Sub CreateEmailHyperLink(control As IRibbonControl)
 InsertEmailHyperliink control.Tag, control.Tag
End Sub

Sub InsertEmailHyperliink(hyperlink As String, textTodisplay As String)
 Dim emailLink As String
 emailLink = "mailto:" & hyperlink
 CreateHyperlink emailLink, textTodisplay
End Sub

Sub CreateHyperlink(address As String, textTodisplay As String)
 ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, address:="" & address & "", SubAddress:="", textTodisplay:="" & textTodisplay & ""
End Sub

4) At this point of time if you view the TheDocumentWithMacros.docm, you see vbaProject.bin. This file has got the binary form of all the macros the document contains. This is what we need to copy in our main document (document.docm).

clip_image002[15]

5) Now we have the macros in “TheDocumentWithMacro.docm”. Its time to call them from our main document “Document.docm”.We call those macro functions from the onAction event of the button.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
             <tab id="tabMyZone" label="My Zone">
             <group id="grpLinks" label="My links">
                <button id="Google"   label="Google" tag="http://www.google.com"  onAction="CreateTextHyperLink"/>
                <button id="Facebook" label="Facebook" tag="http://www.facebook.com" onAction="CreateTextHyperLink"/>
                <button id="Gmail" label="Gmail" tag="http://www.gmail.com"  onAction="CreateTextHyperLink"/>
             </group>

             <group id="grpEmailAddresses" label="My Contacts">
                <button id="e1" label="Ashish Gupta" tag="ashish.kuber@wipro.com"  onAction="CreateEmailHyperLink"/>
                <button id="e2" label="Sachin Tendulkar" tag="sachinrt@yahoo.com"        onAction="CreateEmailHyperLink"/>
                <button id="e3" label="James Hetfield" tag="James.Hetfield@metallica.com" onAction="CreateEmailHyperLink"/>
             </group>
        </tab>
        </tabs>
    </ribbon>
</customUI>

6) Copy the macro code from the “TheDocumentWithMacro.docm” to the “Document.docm”:-

public static void CopyMacro(byte[] documentWithMacroContent, WordprocessingDocument document)
{
   const string vbaProjectRelationShipType = "http://schemas.microsoft.com/office/2006/relationships/vbaProject";
   VbaProjectPart vbaProjectPart = null;
   using (MemoryStream memoryStream = new MemoryStream(documentWithMacroContent, false))
   {
       using (WordprocessingDocument documentWithMacro = WordprocessingDocument.Open(memoryStream, false))
       {
           MainDocumentPart mainPart = documentWithMacro.MainDocumentPart;
           foreach (IdPartPair partPair in mainPart.Parts)
           {
             if (partPair.OpenXmlPart.RelationshipType == vbaProjectRelationShipType)
             {
                vbaProjectPart = (VbaProjectPart)partPair.OpenXmlPart;
                break;
             }
           }
 
           MainDocumentPart mainPart1 = document.MainDocumentPart;
           ExtendedPart extendedPart = null;
           foreach (IdPartPair partPair in mainPart1.Parts)
           {
             if (partPair.OpenXmlPart.RelationshipType == vbaProjectRelationShipType)
             {
                 extendedPart = (ExtendedPart)partPair.OpenXmlPart;
                 break;
             }
           }

           if (extendedPart != null)
           mainPart1.DeletePart(extendedPart);

           if (vbaProjectPart != null)
           mainPart1.AddPart<VbaProjectPart>(vbaProjectPart);
        }

    }

}

Line 11- 18 :- Get the VbaProjectPart from the document with macro.

Line 20-29  :- Get the VbaProjectPart from the document.

Line 31-32  :- Delete the existing VbaProjectPart from the document If any.

Line 34-35  :- Add the VbaProjectPart got from the Line (125-133) to the document.

7) Get the binary content of the “TheDocumentWithMacro.docm” (line 7).

static string fileName = "document.docm";
static string ribbonXMLFileName = "Ribbon.xml";
static string documentWithMacroFileName = "TheDocumentWithMacro.docm";
static byte[] documentWithMacroContent;
static void Main(string[] args)
{
   documentWithMacroContent = File.ReadAllBytes(documentWithMacroFileName);
   byte[] updatedByteContent = AddRibbonToDocument(File.ReadAllBytes(fileName));
   if (updatedByteContent != null)
   {
      using (FileStream fileStream = new FileStream(fileName, FileMode.Create))
      {
         fileStream.Write(updatedByteContent, 0, updatedByteContent.Length);
      }
   }
   
   if (File.Exists(fileName)) Process.Start(fileName);
}

8) Modify the AddRibbonToDocument() to add call to CopyMacro() method (line 20).

public static byte[] AddRibbonToDocument(byte[] documentContent)
{
  byte[] updatedDocumentContent = null;
  if (documentContent != null)
  {
     using (MemoryStream memoryStream = new MemoryStream())
     {
        memoryStream.Write(documentContent, 0, documentContent.Length);
        string ribbonXMLAsString = GetRibbonXML().ToString();
        using (WordprocessingDocument myDoc = WordprocessingDocument.Open(memoryStream, true))
        {
           MainDocumentPart mainPart = myDoc.MainDocumentPart;
           if (myDoc.GetPartsCountOfType<RibbonExtensibilityPart>() > 0)
                myDoc.DeletePart(myDoc.GetPartsOfType<RibbonExtensibilityPart>().First());

           RibbonExtensibilityPart ribbonExtensibilityPart = myDoc.AddNewPart<RibbonExtensibilityPart>();
           ribbonExtensibilityPart.CustomUI = new DocumentFormat.OpenXml.Office.CustomUI.CustomUI(ribbonXMLAsString);
           myDoc.CreateRelationshipToPart(ribbonExtensibilityPart);
           CopyMacro(documentWithMacroContent, myDoc);
        }
       updatedDocumentContent = memoryStream.ToArray();
     }
  }
  return updatedDocumentContent;
}

At this point of time, If you run the application you should see the buttons on the newly added tab. Clicking on the buttons embed links on the document.

NOTE :- If you are like me, you must be thinking of creating the VBProject dynamically to the document.docm file rather than maintaining another file (ThedocumentWithMacro.docm) and copying from the same. Although theoretically Its possible, Its way too complex to implement or I just dont have the time to implement that way. See this msdn.microsoft.com/en-us/library/cc313094(v=office.12).aspx.

, ,

1 Comment

Programmatically showing only the custom styles in the style pane of a Word 2007 file

The project in which I working nowadays is a content authoring and management system and makes extensive use of Word 2007. The system has two parts in context of the problem I am going to discuss about – One part where the Admin uploads a Word 2003 (.doc) file containing all the custom styles created in there into the system. Let us call this file as a word template. The second part is where the user uploads his own content files Word 2003 (.doc) files in the system. When the user uploads the content in the system, the styles from the word template gets into the to the uploaded content file (more on this later). This facilitated the styles being introduced in the system only once (or whenever the Admin wants) using the template and the same styles getting used in all the content files without having the user to recreate them in each content file. This also offered the consistency in the styles used in the content files used in the system.

Problem :- When the user uploads the content and then later opens the content for editing, he was seeing the inbuilt styles as well and they wanted only the custom styles and “Clear Formatting” option to be seen in the content file. Silly , isn’t it. But this is was requirement.:-)

Just to make sure we we are all clear on what an in-built and what a custom style is :-

Open Word 2003 and choose Format > Styles and Formatting and what ever styles you see in the style pane are all in-built styles.

image

And you can create a new style by clicking on  the “New Style” button. I created one “MyCustomStyle”. So this is the custom style in my file.

image

I need to take the attention back to my following line which I mentioned earlier :-

“When the user uploads the content in the system, the styles from the word template gets into the to the uploaded content file (more on this later). “

The way we do this is following :-

1. Convert the template word 2003 file to word 2007 format using a third party component named “Aspose.Words.dll” (www.aspose.com)
2. Convert the content word 2003 file to word 2007 using Aspose.Words.dll.

Just in case you are not aware, a Word 2007 file is an archive/zip file. You can rename any Word file (.docx) to zip file and extracts its contents as if it was a Zip file. When you look into the contents of that zip file, you will see each component of the word 2007 being represented by a file. Read about this here.
3.Copy the custom styles in the style.xml of template file to the style.xml content file.
4. Convert the content word 2007 file back to word 2003 using Aspose.Words.dll.

All the above four steps happen while the user attempts to open the file and when the file was ultimately opened, he sees the inbuilt styles as well along with the custom styles which is the problem.

When I started looking into the problem(an you, dear reader must have realized by now for sure), it seemed that user can always filter the custom styles:-

image

However, this is exactly what the users of the application did not want to do. So the effort of convincing them was in vain.

So, first I started looking at if Aspose.Words.dll offers any API to change the filter to show only the custom styles in the word 2003 file, when we convert the content 2007 file back to 2003. It turns out that It does not and a request for incorporating that change would take months.

I started looking at any other commercial product which would do a better job than Aspose and even the using Office Migration Utility(OFC) and wordconv.exe (comes with the Office compatibility pack). Those did not help either.

So then I looked at if we can find something in the files in the content word 2007 archive itself. There must be something in that archive which is telling Microsoft Word 2007 which types of styles to show in the style pane.

I came across an article at http://msdn.microsoft.com/en-us/library/documentformat.openxml.wordprocessing.stylepaneformatfilter(office.14).aspx

Which states that For this , the w:stylePaneFormatFilter element in the settings.xml of the docx file can have the following values:-
0x1000 –  Specifies that a style should be present which removes all formatting and styles from text.
0x0002 – Specifies that only styles with the customStyle attribute should be displayed in the list of document styles.
So I sum the hex values above to get 1002 to show both Custom Styles and the Clear Formatting.
To test this, I created a new file in Word 2007 and created a new style named “MyCustomStyle” in it.

image

Then I rename that .docx file to .zip and then unzipped the same:-

image renamed to image

and extracted and navigate to Word folder to see the Settings.xml:-

image

Opened that file and see the following structure :-

image

I added the following node right under the root of the xml:-
<w:stylePaneFormatFilter w:val=”1002″/>

image

Updated the zip file and renamed the zip file back to docx and opened the docx file and saw only my custom style and “Clear All”!

image

1 Comment

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands