Office Excel interop Example - host an excel application in the WPF application  - 在wpf host一个Excel application. 

there are many a discussion on  how to host an excel or any other type of Microsoft Office/PDF in the Wpf application.

In general the following has been discussed and implemented. 

  • Host ActiveX in WPF
  • Host with Interoperability
  • Host in the WebBrowser control

If you search on the internet, you may find the following link: Is there any possible way to embed the excel sheet in wpf control.

Also, in the Codeplex site, there is one example that shows you a possible way that leverage the 

WebBrowser. here is the link: How to integrate Excel in a Windows Forms Aplication with the WebBrowser.

And among all those post,there is one post that uses the win32 API as well as the WindowsFormshost, here is the original post. Word Control for .Net

Now, let's see our code that uses  similar approach as Word Control for .Net. 

To start, you will need to reference the COM reference 

Microsoft Excel 14.0 Object Library

Microsoft Excel 14.0 Object library is for Office 2010, if you are working on Office 2007, you may use a different one, such as Microsoft Excel 12.0 Object Library;

Here is the picture of how to add the reference.

When this is added, you will see two references added (the Excel.Core will use the Microsoft.Office.Core). as below. 

Second, you will need to create a Windows Form User Control. e.g. we craete the user control with name ExcelView. Open the code and we will complete the code as below. 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
using System.Diagnostics;
using System.Globalization;
using System.Threading;


namespace ExcelInterop
{
  public partial class ExcelView : UserControl
  {
    public ExcelView()
    {
      InitializeComponent();
      // init to the Excel.Application 
      Init();
      // setup the Handler to ExcelView_Resize
      this.Resize += new EventHandler(ExcelViewer_Resize);
    }
    #region Fields
    
    private Excel.Application application;
    private Process process;
    public IntPtr excelHandle;
    private bool initialized = false;
    private Excel.Workbooks workbooks;
    private Excel.Workbook workbook;
    private CultureInfo threadCulture;

    #endregion Fields


    #region Hosting Excel Properties
    public bool Saved { get { return workbook.Saved; } }
    public Microsoft.Office.Interop.Excel.Application Application { get  { return application; } }
    public Workbook Workbook { get { return workbook; } }

    #endregion Hosting Excel Properties



    #region Proxy Method
    public void Init()
    {
      threadCulture = Thread.CurrentThread.CurrentCulture;
      Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

      application = new Excel.Application();
      application.WindowState = Excel.XlWindowState.xlNormal;

      application.Visible = true;
      application.DisplayFormulaBar = true;

      excelHandle = new IntPtr(application.Hwnd); // get the excelHandle, it is created in a separate process, through the use of 
                                                  // Hwnd, we can get to the handle to the main Wnd 
      SetParent(excelHandle, this.Handle);       // set the current Host as the Parent of the Excel Hwnd
      int lngStyle = GetWindowLong(excelHandle, GWL_STYLE);
      lngStyle = lngStyle ^ WS_CAPTION;
      lngStyle = lngStyle ^ WS_SIZEBOX;

      SetWindowLong(excelHandle, GWL_STYLE, lngStyle); // apply the new style
      SetWindowPos(excelHandle, new IntPtr(0), 0, 0, this.Width, this.Height, SWP_FRAMECHANGED);

      int pid = 0;
      GetWindowThreadProcessId(excelHandle, out pid);
      process = Process.GetProcessById(pid);

      initialized = true; // do not initalize a second tieme
    }

    public void OpenFile(string fileName, bool isExclusive = true, bool isReadonly = true)
    {
      if (!initialized)
        Init();
      if (isExclusive && application.ActiveWorkbook != null) // close existing apps
        application.ActiveWorkbook.Close(false);            // close without saving
      workbooks = application.Workbooks;                    
      workbook = workbooks.Open(fileName, isReadonly);       // Open mode - readonly

    }

    public void CloseExcel()
    {
      InternalCloseExcel();
    }
    private void InternalCloseExcel()
    {
      try
      {
        if (workbook != null)
        {
          workbook.Close(false);
          Marshal.ReleaseComObject(workbooks);   // take care of the ref counting? 
          Marshal.ReleaseComObject(workbook);
          if (application != null)
          {
            application.Quit();
            Marshal.ReleaseComObject(application);
          }
        }
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
      }
      finally
      {
        workbooks = null;
        workbook = null;
        application = null;
        if (process != null && !process.HasExited)
        {
          process.Kill();
        }
        Thread.CurrentThread.CurrentCulture = threadCulture;
        initialized = false;
      }
    }

    public void KillExcel()
    {
      if (process != null && !process.HasExited)
      {
        process.Kill();
      }
    }

    public void SaveActiveWorkbook()
    {
      workbook.Save();
    }

    public void SaveActiveBookAs(string filename)
    {
      Debug.Assert(!string.IsNullOrEmpty(filename));
      workbook.SaveAs(filename);
    }

    public void SaveCopyOfActiveBookAs(string filename)
    {
      Debug.Assert(!string.IsNullOrEmpty(filename));
      workbook.SaveCopyAs(filename);
    }


    #region Internal Handlers
    // once the host is resized, the content should shall resize according to the new host
    private void ExcelViewer_Resize(object sender, EventArgs args)
    {
      if (excelHandle != IntPtr.Zero)
      {
        SetWindowPos(excelHandle, new IntPtr(0), 0, 0, this.Width, this.Height, SWP_NOACTIVATE);
      }
    }

    
    #endregion Internal Handlers

    
    #endregion Proxy Method

    #region Override
    protected override void OnHandleDestroyed(EventArgs e)
    {
      CloseExcel();
      base.OnHandleDestroyed(e);
    }
    #endregion Override

    #region P/Invoke

    private const int SWP_FRAMECHANGED = 0x0020;
    private const int SWP_DRAWFRAME = 0x20;
    private const int SWP_NOMOVE = 0x2;
    private const int SWP_NOSIZE = 0x1;
    private const int SWP_NOZORDER = 0x4;
    private const int GWL_STYLE = (-16);
    private const int WS_CAPTION = 0xC00000;
    private const int WS_THICKFRAME = 0x40000;
    private const int WS_SIZEBOX = WS_THICKFRAME;
    private const int SWP_NOACTIVATE = 0x0010;

    [DllImport("user32.dll", SetLastError = true)]
    private static extern IntPtr SetParent(IntPtr hWndChild, IntPtr hWndNewParent);

    [DllImport("user32.dll", SetLastError = true)]
    public static extern int GetWindowLong(IntPtr hWnd, int nIndex);

    [DllImport("user32.dll")]
    public static extern int SetWindowLong(IntPtr hWnd, int nIndex, int dwNewLong);

    [DllImport("User32", SetLastError = true)]
    public static extern bool SetWindowPos(IntPtr hWnd, IntPtr hWndInsertAfter, int X, int Y, int cx, int cy, int uFlags);

    [DllImport("user32.dll", SetLastError = true)]
    public static extern uint GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);

    #endregion P/Invoke


  }
}

Third, you may want to use the User Control, but in order to host the User Contorl, you have to use the WindowsFormsHost control, here is how the MainWindows.xaml file is written.

<Window x:Class="ExcelInterop.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:excel="clr-namespace:ExcelInterop"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
    <Grid.ColumnDefinitions>
      <ColumnDefinition />
    </Grid.ColumnDefinitions>
    <Grid.RowDefinitions>
      <RowDefinition />
      <RowDefinition />
    </Grid.RowDefinitions>
    <WindowsFormsHost>
      <excel:ExcelView x:Name="excel"/>
    </WindowsFormsHost>
      <Button Grid.Row="1" Content="OpenExcel1" Click="OpenExcel1"
      ></Button>
  </Grid>
</Window>

Fourth, and we need to code the code-behind file. Here it is 

using System;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Excel = Microsoft.Office.Interop.Excel;


namespace ExcelInterop
{
  /// <summary>
  /// Interaction logic for MainWindow.xaml
  /// </summary>
  public partial class MainWindow : Window
  {
    public MainWindow()
    {
      InitializeComponent();
    }

    string BaseUrl = AppDomain.CurrentDomain.BaseDirectory;

    private void OpenExcel1(object sender, RoutedEventArgs e)
    {
      string path = BaseUrl + @"\excel\Demo1.xlsx";
      excel.OpenFile(path);

      foreach (Excel.Worksheet sheet in excel.Workbook.Worksheets)
      {
        sheet.BeforeDoubleClick += new Excel.DocEvents_BeforeDoubleClickEventHandler(sheet_BeforeDoubleClick);
      }
      
    }

    void sheet_BeforeDoubleClick(Excel.Range Target, ref bool Cancel)
    {
      Console.WriteLine("this has been pressed");
    }
  }
}

When you run it, and you open the .xslx file you will see the following