c# - 使用没有 VBA 的 OpenXML 库 VS2017 在 Office Excel 中自动插入自定义功能区
问题描述
我们的应用程序允许用户下载 excel 以输入配置数据,然后将其保存并用于处理和估计结果。
到目前为止,我们有任务窗格,我们会自动将其插入下载的 excel 模板中。我们希望添加一个自定义功能区,其中包含用于打开/关闭任务窗格的按钮和更多功能。
目前我们正在使用 OpenXML 将任务窗格插入到使用共享存储(带有存储 id 、存储、类型和版本)的电子表格中,然后再将其上传到云存储,我们希望通过将 XML 配置存储在同一家商店并使用它来添加功能区。
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();
}
将 Ribbon xml 添加到 WebExtensionTaskpanePart 对我们不起作用。我们发现在 excel 对象“文档”中可以使用 RibbonExtensibilityPart 属性来添加自定义功能区,例如:
var ribbonPart = document.AddRibbonExtensibilityPart();
ribbonPart.CustomUI = new CustomUI(XMLContentString);
ribbonPart.CustomUI.Save();
但是我们使用的 xml 不适用于此,因为 CustomUI 需要 XML 标头标签“customui”,而我们使用的是 OfficeApp。
我们尝试使用 customui xml,但除了没有 VBA 的 ui 设计外,它不会让我们做太多事情。
自定义用户界面 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>
上面的 XML 对我们来说是不可用的,我们需要在不使用宏的情况下访问 URL 和图像,
我们可以使用 excel 上的加载项按钮添加原始的 OfficeApp XML(我们在这之前制作的),它可以完美运行,而且没有任何 VBA 宏,我们想要的只是通过在上传之前插入它来自动化该过程模板,以便在用户打开 excel 时功能区已经存在。
办公应用程序 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>
它的外观:
解决方案
使用 OpenXML 将其添加到 webextension 并附加 autoshow 属性
(名称=“Office.AutoShowTaskpaneWithDocument”值=“真”)
现在它的工作。
推荐阅读
- javascript - 使用多个关键字动态拆分字符串
- django - 带有 Gunicorn 的 Nginx 反向代理对站点名称的处理方式不同
- javascript - 如何在 ReactJS 中包含自定义 JavaScript?
- c# - 避免使用 WebBulder 并使用启动文件
- reactjs - (Redux) Uncaught TypeError: Object(...) is not a function
- karate - 空手道:如何遍历 JSON 有效负载并打印每个字段的值的长度
- javascript - Javascript嵌套无效函数
- html - 在 v-for 元素上使用模态?
- rdp - WTSVirtualChannelOpenEx 有时会失败?
- typescript - 获取泛型函数的参数类型