Use different Excel TFS / VSTS Addin at the same time

If you are a consultant, it is quite common that you work with various version of TFS Server at the same time. I have my personal account on VSTS, always updated to the latest version, but I have also customer that still uses TFS 2012 or TFS 2010.

Microsoft test newer version of TFS against lots of applications to be sure that newer versions of TFS do not break usage of existing tools. This means that usually you can upgrade your TFS without worrying that your VS 2010 or Visual Basic 6 stops working. You need to be aware that the opposite is not true. This imply that newer version of Visual Studio could not work well with older version of TFS. This decision is done because Microsoft is encouraging people to keep their TFS installation up to date, and it would be a nightmare to always guarantee that newer tools would be able to communicate with the older service API.

To minimize compatibility problems, you should keep your TFS on-premise updated to the latest version.

Tool as Visual Studio are usually not a problem, you can keep as many VS version you want side by side, so if you still use TFS2012 you can still use VS 2012 without any problem. But you can have problems with other tools.

Office TFS addin is installed automatically with Visual Studio Team Explorer or with any version of Visual Studio. This means that whenever you update your VS or you installa new VS version the Office addin is also updated.

Starting from Visual Studio 2015 there is no Team Explorer anymore, if you want to install only the Office addin you can use the standalone installer following links on this post from Brian Harry.

In Italian Visual Studio forum there is a question where a user experienced problem in exporting Work Item Query result to Excel after upgrading to Visual Studio 2015 Update 3. He is able to connect Excel to VSTS, but the addin does not work anymore with on-premise TFS 2012. This situation prove that the addin is working correctly with latest TFS version, but it does not support anymore older TFS version.

The solution to this problem is simple, because you can choose in Excel the addin version you want to use. You just need to go to Excel Settings, then choose Add-ins (1) then manage Com Add-ins (2) and finally press the Go button.

Figure 1: Managing Excel addins from settings pane.

If you scroll down the addin list, you should see several version of the addin for TFS, one for each version of Visual Studio you have installed. In my machine I have VS2012, VS2013 and VS2015 so I have three distinct version of the addin.

Figure 2: Multiple TFS Addin installed if you have multiple version of Team Explorer.

You can understand the version of the addin simply looking at the location, but the cool part is that you can enable more than one addin at the very same time. As a result you have multiple Team ribbon tab in your Excel as shown in Figure 3.

Figure 3: Multiple TFS Addin enabled at the very same time

I need to admit that this is not really a nice situation to have, because you are confused and there is no clear clue to which version of the add-in each tab is referring to, but at least you can use both of them at the very same time. If you prefer you can simply enable an old version (say 2012 version) to make sure that it works with your main TFS instance. Usually if you enable an older version it should be capable of working with newer instance of TFS.

I’ve not tested this technique thoroughly but it should work without problem.

Gian Maria.

Open Xml Project

I’ve received some comments in an old post, regarding the manipulation of docx documents, with the purpose of substitute parts of the original document with images or pieces of text. I’ve blogged also how to use Excel to create reports, and populate data programmatically with OpenXml standard.

The original code was developed for an Italian company named ActValue I collaborate with, and some people asked me to publish the full code. I cannot publish the exact version of the current library, but, thanks to the courtesy of ActValue, I can now publish an old version of the code, that contains all the techniques I’ve described in my old post about OpenXml Format.

Please use the code only as a reference to better understand the technique I explained in my blog, this is not fully ready production code. You can do everything you want with it, this is the disclaimer.

Copyright (c) 2009 Ricci Gian Maria
All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
* Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
* Neither the name of the author (Ricci Gian Maria) nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Please remember that nor I nor Actvalue will be give any warranties on this code, as described in the above license. Please feel free to signal me any malfunction.

The code can be downloaded here.

Alk.

Tags:

OpenXml excel and formulas

In an old post, I deal with a simple way to create excel report using openXml format. The trick is a simple manipulation of the document with Linq to Xml.

Now I need to add another feature, I need to open an excel document with formulas, fill some cells, leaving formulas intact. My first version does not work as expected, I simply created an excel with simple formulas, then fire my function and when I open the resulting excel I see all zero on formula column, but the formula is there, and if I change some cell referenced by the formula I’ll obtain the right value.

This problem arise because formula are stored in original sheet with such a xml

<c r="C2">
  <f t="shared" ref="C2:C10" si="0">A2+B2</f>
  <v>0</v>
</c>

This means that the cell C2 contains the formula A2+B2, but the node <v>0</v> tells Excel that actual value is Zero. So when you open the resulting excel file, excel found that content of the cell is 0 and shows this value until some related cell changes content. To solve this problem I simply added a bit of code that removes the <v> element.

originalElement.Descendants(ExcelFiller.ns_s + "v").Remove();
row.Add(originalElement);

Now when excel opens elaborated document, it found no <v> (value) element, so it recalculate it based on formula.

alk.

Tags:

Manage image in openXml format part2

In a previous post I deal with image insertion into an openXml document. Now it is time to show how to change image dimension, I want to be able to define new dimension and to choose if the image should be stretched or no. The w:drawing element has two distinct part to manage image dimension, the first is the wp:extent node, child of the wp:inline one.

image

This node determines the extent of the area of the document that will contain the image, but to really change dimension of the image we should operate in a different tag:

image

The spPr node is used to determines the shape properties as described in the section 4.4.1.41 of the specification of the openXml format, it contains the xfrm node, that is used to apply 2D transformation to an object. This particular node is used to specify the offset of the picture into the area, and the ext is used to set the real image dimension. The code to change image width is the following

String nodeContent = Properties.Resources.XmlContentForEmbedImage.Replace( "###imageid###", document.WordProcessingDocument.MainDocumentPart.GetIdOfPart(newImage)) .Replace("###width###", (Width * 9525).ToString()) .Replace("###height###", (Height * 9525).ToString()); nodeContent = SetImageDimension(nodeContent);

I store a sample of the image xml code in the resource file of the project, then I simply change image Id as described in the previous post, finally I change the width and height of the wp:extent. Since the value of these tag are to be expressed in EMUs (that is English Metric Units and not the famous animal) I multiply for 9525, a constant that converts from pixel unit to EMUs. The function SetImageDimension sets the a:xfrm node.

private string SetImageDimension(string nodeContent) { if (StretchImage) { nodeContent = nodeContent.Replace("###widthr###", (Width * 9525).ToString()) .Replace("###heightr###", (Height * 9525).ToString()); } else { Double widthRatio = OriginalWidth / (Double) Width; Double heightRatio = OriginalHeight / (Double) Height; Double realRatio = Math.Max(widthRatio, heightRatio); nodeContent = nodeContent.Replace("###widthr###", (OriginalWidth * realRatio * 9525).ToString()) .Replace("###heightr###", (OriginalHeight * realRatio * 9525).ToString()); } return nodeContent; }

As you can see in the sample image code I write ###widthr### and ###heightr### tag to set the correct format, if the StretchImage is set to true, I simply set the width and height as specified from the user, if it is false I should make some simple calculation to avoid image stretching.

Now I’m able to resize the image as desidered.

alk.

Tags:

OpenXml format, insert an image into a document

In previous post I showed how to open a docx file, search for a specific text, and replace the text with another string. The reason for doing this is simply to create a master report file in docx format, and let an application insert data in specific part of the document itself.

The next step is to substitute text with an image, this is a more complex process, because we need first to insert the image into the package, then we need to reference it in the main document. The first part is really simple

ImagePart newImage = document.MainDocumentPart.AddImagePart(imageType); using (Stream image = new FileStream(imageFileName, FileMode.Open, FileAccess.Read, FileShare.Read)) { newImage.FeedData(image); }

This code is a part of a little library I’m developing, in the first line I use the AddImagePart method of the MainDocumentPart, that creates another part of the document that will contain an image, then I simply open a fileStream to read the image data and use the method FeedData of the ImagePart object. Now we have the image included in the document.

The next step is to add a reference to the image into the document, to accomplish this I store an XML fragment in project resources

<root xmlns:ve="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml"> <w:drawing> <wp:inline distT="0" distB="0" distL="0" distR="0"> <wp:extent cx="955040" cy="955040"/> <wp:effectExtent l="19050" t="0" r="0" b="0"/> <wp:docPr id="1" name="Immagine 1" descr=""/> <wp:cNvGraphicFramePr> <a:graphicFrameLocks xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main" noChangeAspect="1"/> </wp:cNvGraphicFramePr> <a:graphic xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/picture"> <pic:pic xmlns:pic="http://schemas.openxmlformats.org/drawingml/2006/picture"> <pic:nvPicPr> <pic:cNvPr id="0" name="Picture 1" descr=""/> <pic:cNvPicPr> <a:picLocks noChangeAspect="1" noChangeArrowheads="1"/> </pic:cNvPicPr> </pic:nvPicPr> <pic:blipFill> <a:blip r:embed="###imageid###"/> <a:srcRect/> <a:stretch> <a:fillRect/> </a:stretch> ... </root>

This is the code that word generates for an image. Despite the complexities of the fragment, the important part is that the only things you need to change to include the image is substute the ###imageid### with the real id of the embedded object.

String nodeContent = Properties.Resources.XmlContentForEmbedImage.Replace( "###imageid###", document.MainDocumentPart.GetIdOfPart(newImage));

Thanks to the method GetIdOfPart() of MainDocumentPart we are able to get the id of the previously embedded image, now we can simply insert this piece of Xml into the MainDocumentPart to include the image in the word document.

alk.

Tags:

Technorati Tags: