in

Utah .NET User Group

Home of Utah's professional .NET developers.

Joe's Blog

July 2007 - Posts

  • Using WPF With VSTO & Office 2007

    Hosting Windows Forms controls in Office 2007 Custom Task Pane's is pretty simple, as shown in this MSDN article.  I created a simple addin to display a list of image files in a given folder so that the user can double-click on the file name (in a ListBox) and insert the picture into the document.  With little effort it was simple to instead implement a WPF UserControl to spruce up the addin.  You can download the code here.

    Document1 - Microsoft Word

     

    You can host WPF Elements using the ElementHost control found in the WindowsFormsIntegration assemly that ships with the .NET 3.0 runtime.  The CustomTaskPane requires a Windows Forms UserControl, so I'm just adding the ElementHost control to a plain Windows Forms UserControl and then passing that UserControl to be created with the CustomTaskPane.

    internal void AddTaskPane()

    {

        ElementHost host = new ElementHost();

        browser = new ImageBrowserControl();

        host.Child = browser;

        host.Dock = DockStyle.Fill;

     

        browser.PictureSelected += ImageBrowserControl_PictureSelected;

     

        UserControl userControl = new UserControl();

        userControl.Controls.Add(host);

     

        pane = this.CustomTaskPanes.Add(userControl, "Image Browser");

     

        pane.Visible = true;

    }

     

    Inserting images into a Word document is just as simple, as shown in the following code snippet:

    private void ImageBrowserControl_PictureSelected(Object sender, EventArgs<Picture> e)

    {

        Object missing = System.Reflection.Missing.Value;

     

        Globals.ThisAddIn.Application.Selection.InlineShapes.AddPicture(

            e.Item.Filename, ref missing, ref missing, ref missing);

    }

     

    The EventArgs<T> class used in the previous handler is a nice trick to use in conjunction with EventHandler<T>.  A tip from Jean-Paul S. Boodhoo's Blog.

    public class EventArgs<T> : EventArgs

    {

        private T m_Item;

     

        public EventArgs(T item)

        {

            m_Item = item;

        }

     

        public T Item

        {

            get

            {

                return m_Item;

            }

        }

    }

     

    public event EventHandler<EventArgs<Picture>> PictureSelected;

     

    To create  a Ribbon tab you define a set of XML.  The onAction on the button is an event that you can handle in your codebehind file.

    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnLoad">

      <ribbon>

        <tabs>

          <tab id="VSTOAddIns" label="Addins" >

            <group id="Group1" label="Helpers" visible ="1">

              <button id="ImageBrowser" label="Insert Image"

              onAction="ImageButtonClick"

              imageMso="ContentControlDate"/>

            </group>

          </tab>

        </tabs>

      </ribbon>

    </customUI>

     

    Handler for toggling showing / hiding the task pane:

    public void ImageButtonClick(Office.IRibbonControl control)

    {

        if (!m_ImagePaneExists)

        {

            Globals.ThisAddIn.AddTaskPane();

        }

        else

        {

            Globals.ThisAddIn.RemoveTaskPane();

        }

     

        m_ImagePaneExists = !m_ImagePaneExists;

    }

     

     The WPF UserControl styles a simple Picture class, shown below:

    public class Picture

    {

        private FileInfo m_FileInfo;

     

        public Picture(String filename)

        {

            m_FileInfo = new FileInfo(filename);

        }

     

        public String Filename

        {

            get { return m_FileInfo.FullName; }

            set { m_FileInfo = new FileInfo(value); }

        }

     

        public String Name

        {

            get { return m_FileInfo.Name; }

        }

    }

     

     DataTemplate for the Picture class:

    <DataTemplate DataType="{x:Type local:Picture}">

      <StackPanel MinHeight="0px" MinWidth="0px" MaxHeight="150px" MaxWidth="150px" >

        <Image Stretch="UniformToFill" Source="{Binding Filename}" />

        <TextBlock Text="{Binding Name}"

                  FontWeight="Bold"

                  HorizontalAlignment="Center"

                  />

      </StackPanel>

    </DataTemplate>

     

    The ListBox style is taken from my previous blog post on styling ListBoxItem's.

    <Style TargetType="ListBoxItem">

      <Setter Property="Template">

        <Setter.Value>

          <ControlTemplate TargetType="ListBoxItem">

            <Border x:Name="ItemBorder"

                    BorderBrush="Black"

                    Background="LightGray"

                    BorderThickness="2"

                    CornerRadius="4"

                    Margin="3"

                    >

              <ContentPresenter Margin="2" />

            </Border>

            <ControlTemplate.Triggers>

              <Trigger Property="IsSelected" Value="True">

                <Setter TargetName="ItemBorder"

                        Property="BorderBrush"

                        Value="Red"

                        />

              </Trigger>

              <Trigger Property="IsMouseOver" Value="True">

                <Setter TargetName="ItemBorder"

                        Property="BorderBrush"

                        Value="Blue"

                        />

              </Trigger>

              <MultiTrigger>

                <MultiTrigger.Conditions>

                  <Condition Property="IsMouseOver" Value="False" />

                  <Condition Property="IsSelected" Value="False" />

                </MultiTrigger.Conditions>

                <Setter TargetName="ItemBorder"

                        Property="Opacity"

                        Value="0.50"

                        />

              </MultiTrigger>

            </ControlTemplate.Triggers>

          </ControlTemplate>

        </Setter.Value>

      </Setter>

    </Style>

     

    A couple things to remember when building Word Addins is that all Assembly's that you use need to be signed and granted full-trust by the .NET runtime for Word to load them.  Other than that creating Word Addins is pretty simple!

    Posted Jul 17 2007, 09:31 PM by Joe's Blog
    Filed under: ,
  • Querying Excel Files Using ADO.NET

    Recently I needed to query an excel file and import that data into a database.  I could not immediately recall how to accomplish this, so with a bit of searching I found the connection settings that I needed.  Bellow is a code snippet that can be used to load all of the data in a workbook into a GridView on an ASP.NET page.  Note the use of the "Extended Properties='Excel 8.0;'" in the connection template as well as Sheet1$ in the SQL command to specify the name of the workbook in the worksheet.

    using System;

    using System.Data;

    using System.Data.OleDb;

    using System.Web.UI;

     

    public partial class _Default : Page

    {

        protected void Page_Load(Object sender, EventArgs e)

        {

            GridView1.DataSource = GetProductData();

            GridView1.DataBind();

        }

     

        private DataSet GetProductData()

        {

            String connectionTemplate = @"Provider=Microsoft.Jet.OLEDB.4.0;" +

                "Data Source={0}; Extended Properties='Excel 8.0;'";

     

            using (OleDbConnection connection = new OleDbConnection(

                    String.Format(connectionTemplate,

                                  Server.MapPath("~/App_Data/Products.xls"))

                  ))

            {

                OleDbCommand command = new OleDbCommand(

                    "Select * FROM [Sheet1$]", connection

                );

     

                OleDbDataAdapter adapter = new OleDbDataAdapter(command);

     

                DataSet dataSet = new DataSet();

     

                adapter.Fill(dataSet);

     

                return dataSet;

            }

        }

    }

Copyright © 2000-2007, Utah .NET User Group
Powered by Community Server (Commercial Edition), by Telligent Systems