Click here to Skip to main content
14,740,998 members
Articles » Platforms, Frameworks & Libraries » .NET Framework » How To
Article
Posted 25 Nov 2016

Stats

23.1K views
1K downloads
26 bookmarked

Automate multiple Excel instances

Rate me:
Please Sign up or sign in to vote.
4.79/5 (19 votes)
25 Nov 2016CPOL
.NET library to access to any running instance of Excel.

Introduction

This article shows how to create automation for Microsoft Excel that can work with multiple Excel instances, or a single Excel instance when other instances may be running. 

Why?

The standard Excel API exposed though the COM interop library (Microsoft.Office.Interop.Excel) does not have any support for multiple Excel instances.  (I believe the situation is similar for Visual Studio Tools for Office, which is largely a wrapper for the COM interop library, but I have not used VSTO very much.)  In that object model, the Application is the top-level object, which does not belong to any kind of collection.  Application instances can only be acquired easily in a few restrictive ways:

  1. If you have a reference to any object in the Microsoft.Office.Interop.Excel namespace, you can use its Application property to get a reference to the Application containing it.  The catch is that you need to start with a reference to an object inside the Application, which is difficult to get without already having the Application.
  2. You can use Application's constructor to launches a new Excel instance.  However, this cannot be used to access existing instances, and the new instance will be in automation mode, which prevents add-ins and other features from loading and is not always desirable.
  3. Add-ins can access the Application instance they are loaded in, but this does not work for standalone applications.
  4. The System.Runtime.InteropServices.Marshal class can be used to get the "active" instance, but this does not give any control as to which instance is acquired.

What?

The focus of this article is on a class library called ExcelExtensions which provides classes to extend the Excel object model to incorporate multiple Excel instances.  There is also a demo application called ExcelAppManager to show an example use case and how to consume ExcelExtensions from other code.  At the top of the page there are download links for just the compliled ExcelExtensions assembly, and for the full ExcelAppManager solution.  Both the library and demo app are far from perfect, so please notify me of any bugs you encounter, and do not use this library in a production enviroment without thorough testing.

This is a second iteration on an article I wrote on this topic in April 2016,  A lot of credit goes to this anonymous article, as well as some hints from various users of StackOverflow, and the CodeProject members that left comments on my previous article.  I'll also include some relevant links throughout the article, and will repost links at the end in the Links section.

The rest of this article is broken down into the following major sections:

  • Requirements
  • Conventions
  • ExcelExtensions Library
  • Demo Walkthrough
  • Demo Implementation
  • Closing Remarks
  • Links
  • History

Requirements

The source code is written in C#6, so it will require some familiarity with those syntax features, and Visual Studio 2015 will be required to open the solution.  The assembly targets .NET 4.0, so it can be consumed by older applications.  Some of the implementation uses calls to the Win32 API, but understanding that API is not necessary for consumers.  A general understanding of Windows processes and window handles will be helpful.  There isn't any complex use of the Excel automation, but you should know what the Microsoft.Office.Interop.Excel.Application class is. 

I am not an expert on the Win32 API myself, but learned a good bit about it from this project.  Please let me know if the code violates any best practices for dealing with Win32.

NOTE, this code has not been tested on all versions of Excel or Windows.  (Please help me test them all out.) I believe this code may be particularly prone to issues based on different Excel and Windows versions.

Tested environments:

  • Windows 7 64-bit with Excel 2016 32-bit

Conventions

NOTE, paragraphs starting with "NOTE," are usually important or unexpected details, like the environmental conditions mentioned above.

To keep the code examples brief, I have removed many of the comments which can be found in the solution in the ZIP file.  The solution in the ZIP file has pretty good XML comment coverage.

I've omitted using directives for the most part, but the you can assume the following are always in effect:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using XL = Microsoft.Office.Interop.Excel;

I've also omitted namespace declarations, but assume all the classes in the code samples are in the namespace ExcelExtensions.

NOTE, the namespace alias XL.  I will also refer to types in this namespace as "XL types" and instances of these types as "XL objects".


ExcelExtensions Library

The library implementation consist of four classes:

  1. NativeMethods - This internal class encapsulates calls to the unmanaged Windows API. Generally, you don't need to know how everything in this class works, but a basic understanding will explain some of this library's quirks.
  2. ApplicationExtensionMethods - This public class has extension methods for XL.Application.
  3. ProcessExtensionMethods - This public class has extension methods for System.Diagnostics.Process.
  4. Session - This public class represents a collection of all Excel instances running in a specific Windows session.  This may be the only class required by consuming code.  It extends the hierarchy of XL types up a level, so that Sessions contain Applications contain Workbooks contain Worksheets.

NativeMethods

This class encapsulates calls to the unmanaged Windows API.  It has five public methods, each handling a separate responsibility of the class and using separate Windows API calls. 

NOTE, the implementation of this class is not bullet-proof; passing invalid parameters can result in COMExceptions or unexpected null values being returned. 

Here is a high-level view of the class, with regions for each responsibility collapsed.

internal static class NativeMethods {

    private const string USER32 = "User32.dll";
    private const string OLEACC = "Oleacc.dll";

    #region Process ID from handle

    #region Excel window from handle

    #region Excel App from handle
   
    #region Window Z  

    #region Bring Process to front
}

Here are the each of the regions:

#region Process ID from handle

//Gets the process ID of the process that owns the window with the given handle.
public static int ProcessIdFromWindowHandle(int windowHandle) {
    if (windowHandle == 0) throw new ArgumentOutOfRangeException(
        "Window handle cannot be 0.", nameof(windowHandle));

    int processId;
    GetWindowThreadProcessId(windowHandle, out processId);
    return processId;
}

[DllImport(USER32)]
private static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);

#endregion

 

#region Excel window from handle

//Gets the XL.Window object with the given handle.
public static XL.Window ExcelWindowFromHandle(int handle) {
    XL.Window result;
    AccessibleObjectFromWindow(handle, windowObjectId, windowInterfaceId, out result);
    return result;
}

[DllImport(OLEACC)]
private static extern int AccessibleObjectFromWindow(int hwnd, uint dwObjectID, 
    byte[] riid, out XL.Window ppvObject);

private const uint windowObjectId = 0xFFFFFFF0;
private static byte[] windowInterfaceId = \
    new Guid("{00020400-0000-0000-C000-000000000046}").ToByteArray();

#endregion

 

#region Excel App from handle

//Gets an XL.Application object for the instances with the given main window handle.
public static XL.Application AppFromMainWindowHandle(int mainWindowHandle) {
    if (mainWindowHandle == 0) throw new ArgumentOutOfRangeException("Window handle cannot be 0.", nameof(mainWindowHandle));

    int childHandle = 0;
    EnumChildWindows(mainWindowHandle, NextChildWindowHandle, ref childHandle);

    var win = ExcelWindowFromHandle(childHandle);

    return win?.Application;
}

[DllImport(USER32)]
private static extern bool EnumChildWindows(int hWndParent, EnumChildCallback lpEnumFunc, 
    ref int lParam);

private delegate bool EnumChildCallback(int hwnd, ref int lParam);

private static bool NextChildWindowHandle(int currentChildHandle, ref int nextChildHandle) {
    const string excelClassName = "EXCEL7";

    var result = true;

    var className = GetClassName(currentChildHandle);
    if (className == excelClassName) {
        nextChildHandle = currentChildHandle;
        result = false;
    }
    return result;
}

#region Get class name

//Gets the name of the COM class to which the specified window belongs.
private static string GetClassName(int windowHandle) {
    var buffer = new StringBuilder(128);
    GetClassName(windowHandle, buffer, 128);
    return buffer.ToString();
}

[DllImport(USER32, CharSet = CharSet.Unicode)]
private static extern int GetClassName(int hWnd, StringBuilder lpClassName, int nMaxCount);

#endregion

#endregion

 

#region Window Z

//Gets the depth of the window with the given handle.
//This will return a negative number for invalid handles or invisible windows.
public static int GetWindowZ(int windowHandle) {
    var z = 0;
    //Count all windows above the starting window
    for (var h = new IntPtr(windowHandle);
        h != IntPtr.Zero;
        h = GetWindow(h, GW_HWNDPREV)) {

        z++;
    }
    return z;
}

[DllImport(USER32)]
private static extern IntPtr GetWindow(IntPtr hWnd, uint uCmd);

private const int GW_HWNDPREV = 3;
#endregion

 

#region Bring Process to front

//Brings the main window of the given process to the front of all windows.
public static bool BringToFront(Process process) {
    if (process == null) throw new ArgumentNullException(nameof(process));

    var handle = process.MainWindowHandle;
    if (handle == IntPtr.Zero) return false;
    try {
        SetForegroundWindow(handle);
        return true;
    }
    catch { return false; }
}

[DllImport(USER32)]
private static extern bool SetForegroundWindow(IntPtr hWnd);

#endregion

ProcessExtensionMethods

This is a pretty simple static class with extension methods for System.Diagnostics.Process, and some for IEnumerable<Process> as well.  It relies heavily on NativeMethods to do its dirty work. 

NOTE, the AsExcelApp method can throw a COMException or return null if an invalid process is used, or the Excel instance is currently busy, such as when a dialog window is opened.

public static class ProcessExtensionMethods {
    
    //Gets the depth of the main window of the process.
    public static int MainWindowZ(this Process process) {
        if (process == null) throw new ArgumentNullException(nameof(process));

        return NativeMethods.GetWindowZ(process.MainWindowHandle.ToInt32());
    }

    //Gets the input sequence, with processes ordered by the depth of their main window.
    public static IEnumerable<Process> OrderByZ(this IEnumerable<Process> processes) {
        if (processes == null) throw new ArgumentNullException(nameof(processes));

        return processes
            .Select(p => new {
                Process = p,
                Z = MainWindowZ(p)
            })
            .Where(x => x.Z > 0) //Filter hidden instances
            .OrderBy(x => x.Z) //Sort by z value
            .Select(x => x.Process);
    }

    //Gets the process from the input sequence with the main window having the lowest Z value.
    public static Process TopMost(this IEnumerable<Process> processes) {
        if (processes == null) throw new ArgumentNullException(nameof(processes));

        return OrderByZ(processes).FirstOrDefault();
    }
    
    //Converts a Process to an Xl.Application.
    public static XL.Application AsExcelApp(this Process process) {
        if (process == null) throw new ArgumentNullException(nameof(process));

        var handle = process.MainWindowHandle.ToInt32();
        return NativeMethods.AppFromMainWindowHandle(handle);
    }
    
    //Determines if the process is currently visible.
    public static bool IsVisible(this Process process) {
        if (process == null) throw new ArgumentNullException(nameof(process));

        return process.MainWindowHandle.ToInt32() != 0;
    }
}

ApplicationExtensionMethods

This is another simple extension method class, extending XL.Application.

public static class ApplicationExtensionMethods {

    //Gets the Session containing the given Application.
    public static Session Session(this XL.Application app) {
        if (app == null) throw new ArgumentNullException(nameof(app));

        using (var process = app.AsProcess()) {
            return new Session(process.SessionId);
        }
    }

    //Determines if the given Application is the most recently active one.
    public static bool IsActive(this XL.Application app) {
        if (app == null) throw new ArgumentNullException(nameof(app));

        return Equals(app, app.Session().TopMost);
    }

    //Activates the given Application.
    public static void Activate(this XL.Application app) {
        if (app == null) throw new ArgumentNullException(nameof(app));

        using (var process = app.AsProcess()) {
            NativeMethods.BringToFront(process);
        }
    }

    //Determines if the given Application is visible and accesible to automation.
    public static bool IsVisible(this XL.Application app) {
        if (app == null) throw new ArgumentNullException(nameof(app));

        try {
            using (var process = app.AsProcess()) {
                return app.Visible && process.IsVisible();
            }
        }
        catch (COMException x)
        when (x.Message.StartsWith("The message filter indicated that the application is busy.")
            || x.Message.StartsWith("Call was rejected by callee.")) {
            //This means the application is in a state that does not permit COM automation.
            //Often, this is due to a dialog window or right-click context menu being open.
            return false;
        }
    }

    //Converts an Application to a Process.
    public static Process AsProcess(this XL.Application app) {
        if (app == null) throw new ArgumentNullException(nameof(app));

        var mainWindowHandle = app.Hwnd;
        var processId = NativeMethods.ProcessIdFromWindowHandle(mainWindowHandle);
        return Process.GetProcessById(processId);
    }
}

Session

This is an instance class that represents the set of all running Excel instances in a specific Windows session.  It extends the XL type hierarchy up one level, so that Sessions contain Applications contain Workbooks contain Worksheets.

public class Session {

    //Gets an instance representing the current Windows session.
    public static Session Current => new Session(Process.GetCurrentProcess().SessionId);

    //Creates a new instance for the given session.
    public Session(int sessionId) {
        SessionId = sessionId;
    }

    public int SessionId { get; }

    //Gets the sequence of currently running processes in this session named "Excel".
    private IEnumerable<Process> Processes =>
        Process.GetProcessesByName("EXCEL")
        .Where(p => p.SessionId == this.SessionId);

    //Gets the Application corresponding to a Process, or null if that fails.
    private static XL.Application TryGetApp(Process process) {
        try {
            return process.AsExcelApp();
        }
        catch {
            return null;
        }
    }

    //Gets the IDs for current Excel processes.
    public IEnumerable<int> ProcessIds =>
        Processes
        .Select(p => p.Id)
        .ToArray();

    //Gets the IDs for current Excel processes that are reachable by automation.
    public IEnumerable<int> ReachableProcessIds =>
        AppsImpl.Select(a => a.AsProcess().Id).ToArray();

    //Gets the IDs for the current Excel processes that are not reachable by automation.
    public IEnumerable<int> UnreachableProcessIds =>
        ProcessIds
        .Except(ReachableProcessIds)
        .ToArray();

    //Gets an untyped sequence of running Applications.
    public IEnumerable Apps => AppsImpl;

    //Gets a strongly-typed sequence of running Applications.
    private IEnumerable<XL.Application> AppsImpl =>
        Processes
        .Select(TryGetApp)
        .Where(a => a != null && a.AsProcess().IsVisible())
        .ToArray();

    //Gets the Application with the topmost window, or null if there are none.
    public XL.Application TopMost {
        get {
            var dict = AppsImpl.ToDictionary(
                keySelector: a => a.AsProcess(),
                elementSelector: a => a);

            var topProcess = dict.Keys.TopMost();

            if (topProcess == null) {
                return null;
            }
            else {
                try {
                    return dict[topProcess];
                }
                catch {
                    return null;
                }
            }
        }
    }

    //Gets the default Application that double-clicked files will open in, 
    //or null if there are none.
    public XL.Application PrimaryInstance {
        get {
            try {
                return (XL.Application)Marshal.GetActiveObject("Excel.Application");
            }
            catch (COMException x)
            when (x.Message.StartsWith("Operation unavailable")) {
                Debug.WriteLine("Session: Primary Excel instance unavailable.");
                return null;
            }
        }
    }
}

NOTE, the public Apps property returns an IEnumerable, not an IEnumerable<XL.Application>.  This is because XL.Application (like all XL types) is an embedded COM interop type, and as such can not be used as a generic type parameter for a public member.  It is however, completely safe to cast elements of this collection to IEnumerable<XL.Application> in consuming code, like so:

XL.Application[] myApps = mySession.Apps.OfType<XL.Application>().ToArray();

For more on embedding interop types, see this MSDN article.

NOTE, the distinction between TopMost and PrimaryInstance. TopMost is the instance with the foremost window, which is likely the last one a user clicked on; this may change frequently if dealing with multiple instances. PrimaryInstance is the default instance that double-clicked files will open in; this generally does not change until the existing PrimaryInstance is closed.


Demo Walkthrough

ExcelAppManager is a WinForms application that allows you to monitor Excel instances and also update the value in cell A1 for any worksheet in any Excel instance.  The top-left of the form contains a few buttons for launching or cleaning up processes, the bottom left has controls for updating cell A1, and the right side displays a JSON-like tree of currently running Excel instances.  First we'll go over using the display on the right side, then we'll update cell values.

Monitoring Excel Instances

Here is what the application looks like when no Excel instances are running. 

 

Click "Launch Excel instance" to launch an Excel instance, and watch the right side of the form update accordingly.

 

A new Excel 2016 instance:

 

Create a new workbook, and the form will update accordingly:

 

Opening multiple workbooks will add their details to the form.  Note that multiple workbooks open in the same Excel instance normally:

 

Click "Launch Excel instance" again to launch another process:

 

NOTE, if a dialog window, context menu, or mouseover tooltip is open in Excel, the application is not accessible to automation and will temporarily dissappear from the display on the right of the form.


Updating Cell Values

To update cell values, first check the "Pause display updates" checkbox.  This prevents the selected values in the comboboxes from constantly resetting themselves, but also freezes the JSON display on the right of the form.  If you open or close a new Excel instance, workbook, or worksheet while updates are paused, you will have to unpause updates to refresh the items in the comboboxes.

 

The "ProcessID" combobox will allow you to select a running Excel instance by processID.  This combobox will always have items.

 

The "Book Name" combobox will allow you to select a workbook by name, from the selected Excel instance above. 

 

The "Sheet Name" combobox will allow you to select a worksheet by name, from the selected book above.  When a sheet is selected, the value of its A1 cell will be displayed in the readonly textbox below.

 

Clicking "Change cell value" will update the value of the selected sheet's A1 cell to be the value of the "New A1 Value" textbox.


Demo Implementation

I won't post all of the code for the application here, but I'll describe its basic structure, with some code samples for the most interesting parts.  The application has four classes:

  1. MainForm - This is the UI and contains the core of the application logic.  This is the only class I will examine further here.
  2. Program - This is the standard WinForms application entry point; it just creates a MainForm instance.
  3. AppSerializer - This class has methods for serializing XL objects in a JSON-like format, which MainForm uses for displaying the state of the current session.
  4. ProcessLauncher - This class has methods for launching and disposing of processes, which are called by MainForm's button-click events, so that multiple Excel instances can be easily observed.

MainForm

This class is the UI and contains some of the core application logic.  Since there is a lot going on in this class, I've grouped the source code into regions.  This first code sample shows the class constructor and its fields, but has three regions at the end collapsed.

using System.Drawing;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;

public partial class MainForm : Form {

    public MainForm() {
        InitializeComponent();

        launcher = new ProcessLauncher();
        session = Session.Current;

        //Start a background task to update the "Session" textbox.
        var task = new Task((Action)UpdateDisplayLoop);
        task.Start();
    }

    #region Fields

    //Used to launch and cleanup processes
    private readonly ProcessLauncher launcher;

    //Used to get access to currently running Excel instances.
    private readonly Session session;

    //Determines whether the UI data will refresh periodically or not.
    //This must be set to false to allow the user to make drop-down selections.
    private bool IsPaused {
        get { return isPaused; }
        set {
            isPaused = value;
            cmb_ProcessID.Enabled = value;
            cmb_BookName.Enabled = value;
            cmb_SheetName.Enabled = value;
            txt_Session.BackColor = value ? Color.LightGray : Color.White;
        }
    }
    private bool isPaused = false;

    #endregion


    #region Update loop

    #region Control event handlers

    #region Update selection options

}

As you can see, the constructor acquires a ProcessLauncher and a Session instance and stores them in its fields.  The ProcessLauncher will be used to launch new Excel instances, or clean up any that have been launched by this application.  The Session can be queried to get XL.Application objects for currently running Excel instances.  The constructor also starts a background Task that continuously updates the UI, which we'll examine shortly.  The last field, isPaused, determines whether UI updates are paused (true) or enabled (false); the property IsPaused also updates the display of several controls.


The update loop is pretty simple.  In an endless loop, it checks to see if updates are paused, and if not paused, updates the textbox to contain a string representation of the Session, them waits 100ms.

#region Update loop

//Updates the display, then waits. Repeats forever.
private void UpdateDisplayLoop() {
    while (true) {
        if (!isPaused) {
            Invoke((MethodInvoker)UpdateDisplay);
        }
        Thread.Sleep(100);
    }
}

private void UpdateDisplay() {
    var sessionText = ExcelSerializer.SerializeSession(this.session);
    txt_Session.Text = sessionText;
    ResetSelections();
}

#endregion

The control event handlers section is pretty straight-forward.  The "Pause Updates" checkbox is linked to the IsPaused property. The process management buttons on the top-left of the form use ProcessLauncher.  The controls on the bottom-left of the form call functions which are defined in the last region. 

NOTE, the following abbreviations are used for control names:

  • chk = CheckBox
  • btn = Button
  • cmb = ComboBox
#region Control event handlers

private void chk_PauseUpdates_CheckedChanged(object sender, EventArgs e) {
    IsPaused = chk_PauseUpdates.Checked;
}


private void btn_LaunchTaskMgr_Click(object sender, EventArgs e) =>
    this.launcher.LaunchTaskManager();

private void btn_LaunchExcel_Click(object sender, EventArgs e) =>
    this.launcher.LaunchExcelInstance();

private void btn_CleanUpExcel_Click(object sender, EventArgs e) =>
    this.launcher.Dispose();


private void cmb_ProcessID_SelectedIndexChanged(object sender, EventArgs e) =>
    TryUpdateSelectableBookNames();

private void cmb_BookName_SelectedIndexChanged(object sender, EventArgs e) =>
    TryUpdateSelectableSheetNames();

private void cmb_SheetName_SelectedIndexChanged(object sender, EventArgs e) =>
    TryUpdateOldCellValue();

private void btn_ChangeCell_Click(object sender, EventArgs e) =>
    TryChangeCellValue();

#endregion

The last region is the biggest, but none of the methods are too complicated.

ResetSelections clears the contents of the controls on the bottom-left of the form, and is called as part of the update loop. 

The TryXxx methods are called by control event handlers, and each attempt to update the contents of a specific control or perform a task, but refresh the display if they cannot.  For example, if the user selects "Book1" after Book1 has been closed, the display will refresh since the selection is invalid.

The GetSelectedXxx methods each attempt to return an XL object that corresponds to the selection in a specific control.

The UpdateXxx methods each replace the contents of a specific control to contain data about a given XL object.

#region Update selection options

private void ResetSelections() {
    UpdateSelectableProcessIds(this.session);
    cmb_BookName.Items.Clear();
    cmb_SheetName.Items.Clear();
    txt_OldValue.Text = "";
}

private void TryUpdateSelectableBookNames() {
    var app = GetSelectedApp();
    if (app == null) { UpdateDisplay(); }
    else { UpdateSelectableBookNames(app); }
}

private void TryUpdateSelectableSheetNames() {
    var book = GetSelectedBook();
    if (book == null) { UpdateDisplay(); }
    else { UpdateSelectableSheetNames(book); }
}

private void TryUpdateOldCellValue() {
    var sheet = GetSelectedSheet();
    if (sheet == null) { UpdateDisplay(); }
    else { UpdateOldCellValue(sheet); }
}

private void TryChangeCellValue() {
    var sheet = GetSelectedSheet();
    if (sheet == null) {
        UpdateDisplay();
    }
    else {
        xlRange cell = sheet.Cells[1, 1];
        var text = txt_NewValue.Text;
        cell.Value = text;
    }
}

#region Get current selections

private XL.Application GetSelectedApp() {
    XL.Application result = null;
    var text = cmb_ProcessID.Text;
    if (text.Length > 0) {
        var processId = int.Parse(text);
        result = session.Apps.OfType<XL.Application>()
            .FirstOrDefault(a => a.AsProcess().Id == processId);
    }
    return result;
}

private XL.Workbook GetSelectedBook() {
    XL.Workbook result = null;
    var app = GetSelectedApp();
    if (app != null) {
        var text = cmb_BookName.Text;
        if (text.Length > 0) {
            try {
                result = app.Workbooks[text];
            }
            catch {
                result = null;
            }
        }
    }
    return result;
}

private XL.Worksheet GetSelectedSheet() {
    XL.Worksheet result = null;
    var book = GetSelectedBook();
    if (book != null) {
        var text = cmb_SheetName.Text;
        if (text.Length > 0) {
            try {
                result = book.Sheets[text];
            }
            catch {
                result = null;
            }
        }
    }
    return result;
}

#endregion

#region Update displayed data

private void UpdateSelectableProcessIds(Session session) {
    cmb_ProcessID.Items.Clear();
    foreach (var id in session.ReachableProcessIds) {
        cmb_ProcessID.Items.Add(id);
    }
}

private void UpdateSelectableBookNames(XL.Application app) {
    cmb_BookName.Items.Clear();
    foreach (XL.Workbook book in app.Workbooks) {
        cmb_BookName.Items.Add(book.Name);
    }
}

private void UpdateSelectableSheetNames(XL.Workbook book) {
    cmb_SheetName.Items.Clear();
    foreach (XL.Worksheet sheet in book.Sheets) {
        cmb_SheetName.Items.Add(sheet.Name);
    }
}

private void UpdateOldCellValue(XL.Worksheet sheet) {
    XL.Range cell = sheet.Cells[1, 1];
    var text = cell.Text;
    txt_OldValue.Text = text;
}

#endregion

#endregion

Closing Remarks

I hope this article helps.  Keep in mind, there are still bugs in this library, and not all Windows and Excel versions have been tested.  Please let me know if you find any bugs, or if you have any questions about usage or design choices.

I am also currently working on a WPF application called ExcelBrowser that uses the techniques outlined in this article.  It will serve as an "object browser" window for all Excel instances running on the current users Windows session.  The ExcelExtensions library seen here was actually derived from part of its codebase, so the implementation is a little different.


Links


History

  • Posted 11/23/2016
  • Previous article posted 2/23/16

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

JamesFaix
Software Developer
United States United States
I am a professional developer, but I really create software because it's fun. I've always been interested in deconstructing complex systems, and software engineering has proven to be an inexhaustable supersystem of such systems. In the past I've also spent time focusing on music composition, audio engineering, electronics, game design, history, and philosophy.

My strongest languages are English and C#, in fact I'm a Microsoft Certified Professional for "Programming in C#". I do not have any certifications for English, so please trust me there. I've spent a lot of time working on Windows desktop applications, particularly for interacting with SQL Server or automating Microsoft Office programs, using technologies such as C#, VB.NET, VBA, T-SQL, WinForms, WPF, ADO.NET, the MS Office PIA's, ExcelDNA, EPPlus, and Crystal Reports. I've also done some web development using JavaScript, HTML, CSS, TypeScript, ASP.NET, WCF, jQuery, and requirejs. I am very interested in functional programming (F#, Haskell, Clojure), and try to use C# and JavaScript in a "functional" way at times, but I haven't had the opportunity to use a functional language for a serious project yet.

Comments and Discussions

 
QuestionIt was very difficult for me to find the answer here Pin
The Gridlock9-Nov-19 5:53
MemberThe Gridlock9-Nov-19 5:53 
QuestionUnable to get the PivotTables property of the Worksheet class System.Runtime.InteropServices.COMException Pin
Member 141429566-Nov-19 5:44
MemberMember 141429566-Nov-19 5:44 
QuestionArgumentOutOfRangeException in AppFromMainWindowHandle Pin
Jonas Thor3-Jan-19 10:16
MemberJonas Thor3-Jan-19 10:16 
QuestionHow to apply this code? Pin
jrdnoland27-Nov-16 6:30
Memberjrdnoland27-Nov-16 6:30 
AnswerRe: How to apply this code? Pin
JamesFaix28-Nov-16 8:57
MemberJamesFaix28-Nov-16 8:57 
GeneralRe: How to apply this code? Pin
jrdnoland28-Nov-16 12:02
Memberjrdnoland28-Nov-16 12:02 
GeneralMy vote of 5 Pin
jrdnoland27-Nov-16 2:48
Memberjrdnoland27-Nov-16 2:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.