Our application lets users download excel to enter configuration data which is then saved and used to process and estimate results.
As of now we have task pane which we auto insert in the excel templates which are download. We wish to add a custom ribbon along with it which will hold buttons to open/close task pane and few more features .
Currently we are using OpenXML to insert the task pane to the spreadsheet using a shared Store (with store id , store,type and version) before uploading it to cloud storage, we want to do the same for Ribbon by storing an XML config in the same store and using it to add the ribbon .
using (SpreadsheetDocument document = SpreadsheetDocument.Open(FilePath, true))
{
//// Gets the WebExTaskpanesPart of the Spreadsheet document
var existingWebExTaskpanesPart = document.WebExTaskpanesPart;
if (existingWebExTaskpanesPart != null)
{
CollectWebExTaskpanesPartChildrenData(existingWebExTaskpanesPart.Parts, document);
var storeReferenceDataCollectionCount = StoreReferenceDataCollection.Count();
var selectedStoreReferenceData = StoreReferenceDataCollection.FirstOrDefault(store =>
store.StoreId == storeReferenceData.StoreId &&
store.Store == storeReferenceData.Store &&
store.StoreType == storeReferenceData.StoreType &&
store.StoreVersion == storeReferenceData.StoreVersion);
PrepareExcelForTpaInsertion(existingWebExTaskpanesPart, selectedStoreReferenceData, document);
}
else
{
var excelWebExTaskPanesPart = document.AddWebExTaskpanesPart();
CollectWebExTaskpanesPartChildrenData(excelWebExTaskPanesPart.Parts, document);
var storeReferenceDataCollectionCount = (StoreReferenceDataCollection == null) ? default(int) : StoreReferenceDataCollection.Count();
var selectedStoreReferenceData = (StoreReferenceDataCollection == null) ? null : StoreReferenceDataCollection.FirstOrDefault(store => store.StoreId == storeReferenceData.StoreId);
PrepareExcelForTpaInsertion(existingWebExTaskpanesPart, selectedStoreReferenceData, document);
}
var savedDoc = document.SaveAs(FilePathForTpaInsertedTemplate);
savedDoc.Close();
}
Adding the Ribbon xml to the WebExtensionTaskpanePart did not work for us . we found that a RibbonExtensibilityPart property is available in excel object "document" to add custom ribbon like:
var ribbonPart = document.AddRibbonExtensibilityPart();
ribbonPart.CustomUI = new CustomUI(XMLContentString);
ribbonPart.CustomUI.Save();
But the the xml we are using does not work with this as CustomUI requires XML header tags 'customui' whereas we were using OfficeApp.
We tried using customui xml but it would not let us do much except ui design without VBA.
CustomUI XML:
<customUI xmlns = "http://schemas.microsoft.com/office/2006/01/customui:Button" xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ribbon >
<tabs>
<tab id="customTab" insertAfterMso="TabAddIns" label="OSE TPA New">
<group id = "Contoso.Tab1.Group1" label="OSE TPA Tab ">
<button
id="customButton"
label="Show TPA"
image="https://i.imgur.com/FkSShX9.png"
size="large"
onAction="<asks for macro>"/>
<control xsi:type="Button">
<Label resid="Show OSE TPA" />
<Supertip>
<Title DefaultValue="Show OSE TPA" />
<Description DefaultValue="Click to Show the OSE TPA" />
</Supertip>
<Icon>
<bt:Image size="16" DefaultValue="https://i.imgur.com/FkSShX9.png" />
<bt:Image size="32" DefaultValue="https://i.imgur.com/FkSShX9.png" />
<bt:Image size="80" DefaultValue="https://i.imgur.com/FkSShX9.png" />
</Icon>
<Action xsi:type="ShowTaskpane">
<TaskpaneId>Button2Id1</TaskpaneId>
<!--Provide a url resource id for the location that will be displayed on the task pane -->
<SourceLocation DefaultValue="https://osewebappdit4-dev.azurewebsites.net/Excel/Index" />
</Action>
</control>
</group>
</tab>
</tabs>
This above XML is not usable for us we need to access URL's and images without use of macros,
We are able to add the original OfficeApp XML (that we made before all this) using add-in button on excel and it works flawlessly, and that is without any VBA macros , all we want is to automate that process by inserting it before uploading template so that the ribbon already exists when the excel is opened by users.
OfficeApp XML :
<?xml version="1.0" encoding="utf-8"?>
<OfficeApp xmlns="http://schemas.microsoft.com/office/appforoffice/1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0" xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="TaskPaneApp">
<!-- See https://github.com/OfficeDev/Office-Add-in-Commands-Samples for documentation-->
<!-- BeginBasicSettings: Add-in metadata, used for all versions of Office unless override provided -->
<!--IMPORTANT! Id must be unique for your add-in. If you clone this manifest ensure that you change this id to your own GUID -->
<Id>e504fb41-a92a-4526-b101-542f357b7a76</Id>
<Version>1.0.0.0</Version>
<ProviderName>Auto-Web APP DIT4 Dev</ProviderName>
<DefaultLocale>en-US</DefaultLocale>
<!-- The display name of your add-in. Used on the store and various placed of the Office UI such as the add-ins dialog -->
<DisplayName DefaultValue="Auto-Web APP DIT4 Dev" />
<Description DefaultValue="OSE TPA Addin for DIT4-Dev env" />
<!--Icon for your add-in. Used on installation screens and the add-ins dialog -->
<IconUrl DefaultValue="https://i.imgur.com/oZFS95h.png" />
<AppDomains>
<AppDomain>https://login.microsoftonline.com/</AppDomain>
<AppDomain>https://msft.sts.microsoft.com</AppDomain>
<AppDomain>https://osewebappdit4-dev.azurewebsites.net/Excel/Index</AppDomain>
</AppDomains>
<!--BeginTaskpaneMode integration. Office 2013 and any client that doesn't understand commands will use this section.
This section will also be used if there are no VersionOverrides -->
<Hosts>
<Host Name="Workbook"/>
</Hosts>
<DefaultSettings>
<SourceLocation DefaultValue="https://osewebappdit4-dev.azurewebsites.net/Excel/Index" />
</DefaultSettings>
<!--EndTaskpaneMode integration -->
<Permissions>ReadWriteDocument</Permissions>
<!--BeginAddinCommandsMode integration-->
<VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
<Hosts>
<!--Each host can have a different set of commands. Cool huh!? -->
<!-- Workbook=Excel Document=Word Presentation=PowerPoint -->
<!-- Make sure the hosts you override match the hosts declared in the top section of the manifest -->
<Host xsi:type="Workbook">
<!-- Form factor. Currenly only DesktopFormFactor is supported. We will add TabletFormFactor and PhoneFormFactor in the future-->
<DesktopFormFactor>
<!--GetStarted information used on the callout that appears when installing the add-in.
Ensure you have build 16.0.6769 or above for GetStarted section to work-->
<GetStarted>
<!--Title of the Getting Started callout. resid points to a ShortString resource -->
<Title resid="Contoso.GetStarted.Title"/>
<!--Description of the Getting Started callout. resid points to a LongString resource -->
<Description resid="Contoso.GetStarted.Description"/>
<!--Not used right now but you need to provide a valid resource. We will add code in the future to consume this URL.
resid points to a Url resource -->
<LearnMoreUrl resid="Contoso.GetStarted.LearnMoreUrl"/>
</GetStarted>
<!--Function file is an html page that includes the javascript where functions for ExecuteAction will be called.
Think of the FunctionFile as the "code behind" ExecuteFunction-->
<FunctionFile resid="Contoso.FunctionFile.Url" />
<!--PrimaryCommandSurface==Main Office Ribbon-->
<ExtensionPoint xsi:type="PrimaryCommandSurface">
<!--Use OfficeTab to extend an existing Tab. Use CustomTab to create a new tab -->
<!-- Documentation includes all the IDs currently tested to work -->
<CustomTab id="Contoso.Tab1">
<!--Group ID-->
<Group id="Contoso.Tab1.Group1">
<!--Label for your group. resid must point to a ShortString resource -->
<Label resid="Contoso.Tab1.GroupLabel" />
<Icon>
<!-- Sample Todo: Each size needs its own icon resource or it will look distorted when resized -->
<!--Icons. Required sizes 16,31,80, optional 20, 24, 40, 48, 64. Strongly recommended to provide all sizes for great UX -->
<!--Use PNG icons and remember that all URLs on the resources section must use HTTPS -->
<bt:Image size="16" resid="Contoso.TaskpaneButton.Icon" />
<bt:Image size="32" resid="Contoso.TaskpaneButton.Icon" />
<bt:Image size="80" resid="Contoso.TaskpaneButton.Icon" />
</Icon>
<!--Control. It can be of type "Button" or "Menu" -->
<Control xsi:type="Button" id="Contoso.TaskpaneButton">
<Label resid="Contoso.TaskpaneButton.Label" />
<Supertip>
<Title resid="Contoso.TaskpaneButton.Label" />
<Description resid="Contoso.TaskpaneButton.Tooltip" />
</Supertip>
<Icon>
<bt:Image size="16" resid="Contoso.TaskpaneButton.Icon" />
<bt:Image size="32" resid="Contoso.TaskpaneButton.Icon" />
<bt:Image size="80" resid="Contoso.TaskpaneButton.Icon" />
</Icon>
<Action xsi:type="ShowTaskpane">
<TaskpaneId>Button2Id1</TaskpaneId>
<!--Provide a url resource id for the location that will be displayed on the task pane -->
<SourceLocation resid="Contoso.Taskpane1.Url" />
</Action>
</Control>
<Control xsi:type="Button" id="Contoso.TaskpaneButton2">
<Label resid="Contoso.TaskpaneButton2.Label" />
<Supertip>
<Title resid="Contoso.TaskpaneButton2.Label" />
<Description resid="Contoso.TaskpaneButton2.Tooltip" />
</Supertip>
<Icon>
<bt:Image size="16" resid="Contoso.TaskpaneButton.Icon" />
<bt:Image size="32" resid="Contoso.TaskpaneButton.Icon" />
<bt:Image size="80" resid="Contoso.TaskpaneButton.Icon" />
</Icon>
<Action xsi:type="ShowTaskpane">
<TaskpaneId>Office.AutoShowTaskpaneWithDocument</TaskpaneId>
<!--Provide a url resource id for the location that will be displayed on the task pane -->
<SourceLocation resid="Contoso.Taskpane2.Url" />
</Action>
</Control>
<!-- Menu example -->
</Group>
<!-- Label of your tab -->
<!-- If validating with XSD it needs to be at the end, we might change this before release -->
<Label resid="Contoso.Tab1.TabLabel" />
</CustomTab>
</ExtensionPoint>
</DesktopFormFactor>
</Host>
</Hosts>
<Resources>
<bt:Images>
<bt:Image id="Contoso.TaskpaneButton.Icon" DefaultValue="https://i.imgur.com/FkSShX9.png" />
<bt:Image id="Contoso.FunctionButton.Icon" DefaultValue="https://i.imgur.com/qDujiX0.png" />
</bt:Images>
<bt:Urls>
<bt:Url id="Contoso.FunctionFile.Url" DefaultValue="https://osewebappdit4-dev.azurewebsites.net/Excel/Index" />
<bt:Url id="Contoso.Taskpane1.Url" DefaultValue="https://osewebappdit4-dev.azurewebsites.net/Excel/Index" />
<bt:Url id="Contoso.Taskpane2.Url" DefaultValue=" https://aka.ms/osefaqs" />
<!--LearnMore URL currently not used -->
<bt:Url id="Contoso.GetStarted.LearnMoreUrl" DefaultValue="https://osewebappdit4-dev.azurewebsites.net/Excel/Index">
</bt:Url>
</bt:Urls>
<bt:ShortStrings>
<bt:String id="Contoso.FunctionButton.Label" DefaultValue="Execute Function" />
<bt:String id="Contoso.TaskpaneButton.Label" DefaultValue="Show OSE TPA" />
<bt:String id="Contoso.TaskpaneButton2.Label" DefaultValue="OSE FAQs" />
<bt:String id="Contoso.Dropdown.Label" DefaultValue="Dropdown" />
<bt:String id="Contoso.Item1.Label" DefaultValue="Show Taskpane 1" />
<bt:String id="Contoso.Item2.Label" DefaultValue="Show Taskpane 2" />
<bt:String id="Contoso.Tab1.GroupLabel" DefaultValue="OSE TPA Group" />
<bt:String id="Contoso.Tab1.TabLabel" DefaultValue="OSE TPA Tab" />
<bt:String id="Contoso.GetStarted.Title" DefaultValue="OSE add-in was succesfully loaded" />
</bt:ShortStrings>
<bt:LongStrings>
<bt:String id="Contoso.FunctionButton.Tooltip" DefaultValue="Click to Execute Function" />
<bt:String id="Contoso.TaskpaneButton.Tooltip" DefaultValue="Click to Show the OSE TPA" />
<bt:String id="Contoso.TaskpaneButton2.Tooltip" DefaultValue="Click to Show the OSE FAQs" />
<bt:String id="Contoso.Dropdown.Tooltip" DefaultValue="Click to Show Options on this Menu" />
<bt:String id="Contoso.Item1.Tooltip" DefaultValue="Click to Show Taskpane1" />
<bt:String id="Contoso.Item2.Tooltip" DefaultValue="Click to Show Taskpane2" />
<bt:String id="Contoso.GetStarted.Description" DefaultValue="Get going by opening OSE TPA TAB on the Ribbon" />
</bt:LongStrings>
</Resources>
</VersionOverrides>
</OfficeApp>
How it looks:
