Recently I have become aware that many of our Office.JS folks at Microsoft spend time answering questions on stackoverflow. I want to call out that there is a specific office.js category where you can get your questions answered.
I hope to spend a little of my time there each week answering some questions as well. I have already asked on and got a swift response.
There is a common problem I see when automating Excel from an external .NET application:
You will launch Excel, open a workbook, perform work, and then try to close Excel and find that it remains in memory. You might even have code in your solution to try to re-use existing instance of Excel and find that it uses one of these “zombie” instances that do not have any of the add-ins loaded… and maybe an add-in your code relies on.
The issue is that Excel is not able to close because the primary thread of your application is holding on to a COM reference. Even adding Marshal.ComReleaseObject() and GC.Collect() like this probably will not improve the situation either:
| Marshal.ReleaseComObject(excelWorkbook); | |
| Marshal.ReleaseComObject(excelApp); | |
| excelApp = null; | |
| excelWorkbook = null; | |
| GC.Collect(); | |
| GC.Collect(); | |
| GC.WaitForPendingFinalizers(); | |
| GC.WaitForFullGCComplete(); |
From my experience, and from what I have gathered is that until the thread is stopped, the COM reference will be locked. Not sure where the issue lies (EXCEL, COM, or .NET), but I have found a workaround to this. What you have to do is terminate your thread. But because you do not want to terminate your application, you need to create a NEW thread. So the following pattern is what I have found works:
| OpenFileDialog openFileDialog1 = new OpenFileDialog(); | |
| openFileDialog1.Filter = "Excel Files|*.xls*"; | |
| DialogResult result = openFileDialog1.ShowDialog(); | |
| if (result == DialogResult.OK) | |
| { | |
| new Thread(() => | |
| { | |
| Excel.Application excelApp = null; | |
| Excel.Workbook excelWorkbook = null; | |
| this.Invoke(new Action(() => | |
| { | |
| try | |
| { | |
| excelApp = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"); | |
| } | |
| catch | |
| { | |
| excelApp = new Excel.Application(); | |
| } | |
| string workbookPath = openFileDialog1.FileName; | |
| excelWorkbook = excelApp.Workbooks.Open(workbookPath); | |
| excelApp.Visible = true; | |
| })); | |
| Marshal.ReleaseComObject(excelWorkbook); | |
| Marshal.ReleaseComObject(excelApp); | |
| excelApp = null; | |
| excelWorkbook = null; | |
| openFileDialog1 = null; | |
| GC.Collect(); | |
| GC.Collect(); | |
| GC.WaitForPendingFinalizers(); | |
| GC.WaitForFullGCComplete(); | |
| }).Start(); | |
| } |
I have been working with a number of customers over the years that come from the world of Lotus Notes. And one of the areas they often complain about with regards to Outlook is the calendar printing options. There are certain things you just cannot do in Outlook from the printing perspective that leaves them wont for more.
So, I have been working over the years on this add-in. This has actually gone through a few iterations – the current version 1.2.0.9 is the most recent and most fully-featured version.
The full source code is on GitHub, here. It is totally open source and free to use, modify, etc. Here is what it can do:
The exact details on customization, installation and usage are all covered in the user guide, here.
A customer I was working with wanted help developing a fully on-premises Outlook web add-in. By this, they wanted no part of it to reach out to the Internet (Azure or Office 365). They wanted:
This is the topology we are trying to achieve:
If you have developed an Office Web Add-in lately, you find it is inherently biased to the Internet. Even the samples and solutions provided assume Office 365/Exchange and Azure websites. In a default, new Visual Studio solution, the links to the Office.js libraries and stylesheets are all pointing to the web. And, so as you might expect, there are some challenges to getting it to work on-premises only.
This posting covers what you must do to get such a solution to work, including getting past some pitfalls.
Setting up for 100% on-premises is difficult, but it CAN be done. There are a lot of steps, but if you follow the above prescription, you should get it to work. In time, I hope to see this process get easier. But in an online world where Microsoft Office 365 and Azure are main focus, “old fashioned” on-premises solutions are going to require a little more elbow grease.
NOTE: This entry was contributed to by Arthel Bibbens (MSFT) / Exchange PFE. You can follow his posts on this topic here:
 |
Administering Office Add-ins within Exchange 2013 and Exchange 2016
blogs.technet.microsoft.com I recently worked with a developer to deploy an Office add-in within an Exchange 2013 on-premises environment. This project highlighted a capability of Exchange and Outlook that is a huge shift in the way mail add-ins are developed, deployed, and maintained. Let’s take a look at the key components of Exchange 2013 that support this… |
It is sometimes tough to determine what is happening in a production environment and you need to get logging information from the add-in to see what is happening. How can you do that?
One way is to build a console.log() option into your add-in that looks for a Debug flag in the manifest. So, you will create two manifest, one that enabled Debugging and another than disables it. More on that in a bit. To start, here is the basic class I created in order to handle this:
[code lang=”javascript” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
/*!
* logger JavaScript Library v1.0.1
* http://davecra.com
*
* Copyright David E. Craig and other contributors
* Released under the MIT license
* https://tldrlegal.com/license/mit-license
*
* Date: 2016-08-09T12:00EST
*/
var console = (function () {
"use strict";
var console = {};
console.initialize = function () {
///
<summary>
/// Add a textarea/console to the bottom of the page and then setup the logger
/// </summary>
/// <param name="DebugMode" type="Boolean">If debug mode enabled – we show the console for logging</param>
var debugMode = getParameterByName("Debug") == "true";
if (debugMode) {
// add the console to the screen
$("body").append("<textarea id=’log’ style=’width:100%’ cols=’2000′ rows=’7′ wrap=’off’></textarea>");
$("body").append("<button id=’saveLog’>Copy Log to Clipboard</button>");
$("#saveLog").click(function () {
var field = $("#log");
field.select();
document.execCommand("copy");
});
}
console.log = function (msg) {
///
<summary>
/// GLOBAL: Logs to the textarea on the page
/// </summary>
/// <param name="msg" type="string">The message to log</param>
if (debugMode) {
var d = new Date(Date.now());
var current = d.getHours() + ":" + d.getMinutes() + ":" + d.getSeconds();
var data = $("#log").val();
$("#log").val(current + " – " + msg + "\r\n" + data);
}
};
}
function getParameterByName(name) {
///
<summary>
/// Get a parameter form the URL
/// </summary>
/// <param name="name" type="String">Name of the parameter to get from the query string</param>
/// <returns type="String">Value of the paramater</returns>
var url = window.location.href;
name = name.replace(/[\[\]]/g, "\\$&");
var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"),
results = regex.exec(url);
if (!results) return null;
if (!results[2]) return ”;
return decodeURIComponent(results[2].replace(/\+/g, " "));
}
return console;
})();
[/code]
| /*! | |
| * logger JavaScript Library v1.0.1 | |
| * http://davecra.com | |
| * | |
| * Copyright David E. Craig and other contributors | |
| * Released under the MIT license | |
| * https://tldrlegal.com/license/mit-license | |
| * | |
| * Date: 2016-08-09T12:00EST | |
| */ | |
| var console = (function () { | |
| "use strict"; | |
| var console = {}; | |
| console.initialize = function () { | |
| /// <summary> | |
| /// Add a textarea/console to the bottom of the page and then setup the logger | |
| /// </summary> | |
| /// <param name="DebugMode" type="Boolean">If debug mode enabled – we show the console for logging</param> | |
| var debugMode = getParameterByName("Debug") == "true"; | |
| if (debugMode) { | |
| // add the console to the screen | |
| $("body").append("<textarea id='log' style='width:100%' cols='2000' rows='7' wrap='off'></textarea>"); | |
| $("body").append("<button id='saveLog'>Copy Log to Clipboard</button>"); | |
| $("#saveLog").click(function () { | |
| var field = $("#log"); | |
| field.select(); | |
| document.execCommand("copy"); | |
| }); | |
| } | |
| console.log = function (msg) { | |
| /// <summary> | |
| /// GLOBAL: Logs to the textarea on the page | |
| /// </summary> | |
| /// <param name="msg" type="string">The message to log</param> | |
| if (debugMode) { | |
| var d = new Date(Date.now()); | |
| var current = d.getHours() + ":" + d.getMinutes() + ":" + d.getSeconds(); | |
| var data = $("#log").val(); | |
| $("#log").val(current + " – " + msg + "\r\n" + data); | |
| } | |
| }; | |
| } | |
| function getParameterByName(name) { | |
| /// <summary> | |
| /// Get a parameter form the URL | |
| /// </summary> | |
| /// <param name="name" type="String">Name of the parameter to get from the query string</param> | |
| /// <returns type="String">Value of the paramater</returns> | |
| var url = window.location.href; | |
| name = name.replace(/[\[\]]/g, "\\$&"); | |
| var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"), | |
| results = regex.exec(url); | |
| if (!results) return null; | |
| if (!results[2]) return ''; | |
| return decodeURIComponent(results[2].replace(/\+/g, " ")); | |
| } | |
| return console; | |
| })(); |
To enable logging you will add the following code to your initialize:
[code language=”javascript”]
console.initialize();
console.log("Started…");
[/code]
Once initialized this will add a TEXTAREA to the bottom on the page where the log entries will be loaded. Additionally, it will place a “Copy To Clipboard” button at the bottom that when clicked will copy the contents of the TEXTAREA to the clipboard so that they can be forwarded to you as needed.
Once implemented and initialized, you can add a console.log() anywhere you want in your code to add an entry to the log. Now, how do you turn this on. What this is doing in initialize is to see if the debug flag is set in the Query String of the SourceLocation setting in the Manifest. To turn on debugging, you change the following line as such:
[code language=”xml”]
<DesktopSettings>
<SourceLocation DefaultValue="~remoteAppUrl/MessageRead.html?Debug=true"/>
<RequestedHeight>250</RequestedHeight>
</DesktopSettings>
[/code]
That is it. From this you will be able to share two manifests with your users/administrators. The first one will be your default production manifest and the second one can be loaded if you need debugging information from the add-in.
Recently while preparing an internal Chalk Talk on Office Web Add-in Development, a co-worker presented me with two links I had not seen before and I wanted to share them with everyone:
These Code Explorers are pretty cool in that they contain some common use code patterns that you might find useful in your projects.
Unfortunately, there does not appear to be one for PowerPoint and or Outlook yet. But the fact they are there for Excel and Word is pretty cool.
The EWSEditor tool has been around a while and is managed/developed by some of my co-workers that sit on the Outlook Developer Support Team. As I have been developing more and more Office Web Add-ins for Outlook, I have found knowing and using EWS to be a very important skill.
EWSEditor helps in this regard. It is a very powerful, full featured EWS test bed. To get started you download this and extract the contents of the ZIP to a folder. From there you launch it and from the File menu, click Next Exchange Service enter in your email address and then select password and click Ok. Then viola, you are connected:
Once connected, you can start browsing your mailbox using EWS. To get to your Inbox, for example, you select TopOfInformationStore and then select Inbox:
From there you can go to different folders and look at the items, properties, and values stored in your mailbox. It is quite handy to understand how these things are structured and stored.
Next, you can click Tools, EWS Post and test your EWS skills. What I did was entered in my information to connect to my server and filled it in as such:
I then entered the following XML:
[code language=”xml”]
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:m="http://schemas.microsoft.com/exchange/services/2006/messages"
xmlns:t="http://schemas.microsoft.com/exchange/services/2006/types"
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<t:RequestServerVersion Version="Exchange2010_SP2" />
</soap:Header>
<soap:Body>
<m:GetFolder>
<m:FolderShape>
<t:BaseShape>AllProperties</t:BaseShape>
</m:FolderShape>
<m:FolderIds>
<t:DistinguishedFolderId Id=’inbox’/>
</m:FolderIds>
</m:GetFolder>
</soap:Body>
</soap:Envelope>
[/code]
And when I hit run, I got a response you see above. It is that simple. And there are also lots of examples as well. If you click Load Example you will see a lot of XML SOAP requests you can test with:
Download it and give it a try.
What! So, you are getting started writing an Apps for Office or Office add-in as they are now known, and you need to display a JavaScript alert. Maybe you are doing this because you need to prompt the user with some information, or maybe you are doing this because you need to test something. So you enter your code:
window.alert("Hello world!");
When you run it, nothing happens.
This is because JavaScript allows you to redefine anything. And the Office team redefined certain window functions because they felt they were intrusive and because they do not work the same (or at all) on some platforms. So the suggestion is to not use them. The proper way to do this is to use the app.ShowNotifification command:
app.showNotification(title, text)
However, if you are like me and use them to help you debug/code/proof things, you might really need them. I found that if you add the following to your Office.initialize method, they will work:
// The initialize function must be run each time a new page is loaded
Office.initialize = function (reason) {
$(document).ready(function () {
app.initialize();
delete window.alert; // assures alert works
delete window.confirm; // assures confirm works
delete window.prompt; // assures prompt works
/* the rest of your code here */
}}
With one month and one week to go, it is time to start moving off Windows XP and Office 2003. Here is another great article from Microsoft about how/why:
Support for Windows XP and Office 2003 ends April 8, 2014 — what’s next?
http://blogs.technet.com/b/firehose/archive/2014/02/26/support-for-windows-xp-and-office-2003-ends-april-8-2014-what-s-next.aspx
A few interesting highlights from the article:
If you are considering the move and have questions about your Microsoft Office Integrated Line of Business Applications, there are many ways Microsoft and Microsoft partners can assist you in assessing and remediating these solutions.
You can learn more about Office 365 for your business here: http://blogs.office.com/office365forbusiness/
I have had this question a number of times and surprised myself when I did not see it in my blog. So here goes.
There are times when (like my Master Add-in Entry), you need to expose a set of methods or properties in your add-in so that other add-ins or applications are able to access them. Here is how it is done:
1) You create an interface and a class that uses that interface, like this:
[ComVisibleAttribute(true)] public interface IExposedMethods { void LoadDocument(string path); } [ComVisibleAttribute(true)] [ClassInterface(ClassInterfaceType.None)] public class ExposedClass : IExposedMethods { public void LoadDocument(string path) { Globals.ThisAddIn.Application.Workbooks.Open(path); } }
2) Next, you need to add the following code to your ThisAddin.cs:
private ExposedClass _exposedInstance; protected override object RequestComAddInAutomationService() { if (_exposedInstance == null) _exposedInstance = new ExposedClass(); return _exposedInstance; }
3) And finally, to call it and access this from another application instance, you do this:
Excel.Application xlApp = new Excel.Application(); xlApp.Visible = true; object addinName = "MyAddinName"; Office.COMAddIn addIn = xlApp.COMAddIns.Item(ref addinName); addIn.Object.LoadDocument(@"c:\path\test.xlsm");
It is actually quite easy to implement. And the beauty here is that the ExposedClass you created can have all sorts of methods and properties that are exposed. And because the _exposedInstance is global to ThisAddIn (via Globals.ThisAddin._exposedInstance), you can access it anywhere. So it is a perfect way for one add-in to contact another or for a parent Windows Form application to reach into the Add-in and set properties and call methods and have it open documents and such for you.