Взаимодействие Microsoft Excel с приложениями .NET - позднее связывание

Вступление.

Многим разработчикам рано или поздно приходится сталкиваться с задачами, которые подразумевают использование Microsoft Excel (далее по тексту просто Excel). В этих случаях можно применить утилиту tlbimp.exe, импортировать библиотеку типов, добавить ссылку на нее, и вам станет доступно пространство имен Excel, со всеми классами-обертками, вокруг "внутренностей" Excel. Или еще проще, можно просто добавить ссылку на COM-объекты Excel в Visual Studio, и она сделает все сама. Все это хорошо, конечно. И просто. Но иногда возникают ситуации, когда описанное вкратце "раннее связывание" неприемлемо. Например, ваше приложение будет выложено на сайт, следовательно, нужно уменьшить его размер. Раннее связывание порождает две сопутствующие сборки - Microsoft.Office.Interop.Excel.dll и Microsoft.Vbe.Interop.dll, которые содержат классы-обертки. Суммарный размер этих сборок превышает мегабайт, и, несмотря на то, что сборки .NET хорошо сжимаются архиваторами, "тащить" за собой все классы-обертки Excel ради использования двух-трех методов явно нецелесообразно.

И тогда на помощь приходит т.н. "позднее связывание", когда не нужно никаких сопутствующих сборок.

Описывать позднее связывание в этой статье нет смысла, в литературе, как и в Интернете, достаточно материала по этой теме. Все примеры выполнены на C#, но, надеюсь, программисты, использующие в своей работе другие .NET-языки, смогут разобраться в коде без особого труда.

Запуск и завершение работы Excel.

Запуск Excel и его корректное завершение - это самая первая задача, которую нужно решить программисту, если он собрался использовать Excel в своем приложении. Возможно, Excel уже запущен, и операция запуска уже не нужна, достаточно получить ссылку на Excel и начать работу с ним. В получении ссылки на уже работающую копию Excel кроется один неприятный момент, связанный с ошибкой в самом приложении Excel (которая, вроде бы, исправлена в MSOffice 2003)[2]. Эта ситуация подробно описана в конце этого раздела.

А теперь по порядку. В первую очередь нужно подключить к приложению два пространства имен:

using System.Runtime.InteropServices;
using System.Reflection; 

В этих пространствах имен описаны типы, необходимые для организации позднего связывания. Один из них - класс Marshal, который предоставляет богатые возможности для организации взаимодействия между managed и unmanaged-объектами.

Для получения ссылки на процесс Excel нужно знать GUID Excel. Однако можно поступить намного проще - использовать программный идентификатор Excel: "Excel.Application".

Для получения ссылки на работающий Excel можно воспользоваться статическим методом GetActiveObject() класса Marshal:

string appProgID = "Excel.Application";
object excel = Marshal.GetActiveObject(appProgID); 

Если Excel уже запущен (COM-объект Excel присутствует), то вызов данного метода вернет ссылку на объект-отображение Excel в .NET, который можно использовать для дальнейшей работы. Если Excel не запущен, то возникнет исключение.

Для запуска Excel необходимо воспользоваться классом Activator, описанным в пространстве имен System.

string appProgID = "Excel.Application";
// Получаем ссылку на интерфейс IDispatch
Type excelType = Type.GetTypeFromProgID(appProgID);
// Запускаем Excel
object excel = Activator.CreateInstance(excelType); 

После получения ссылки или же запуска становится доступна вся объектная модель Excel. С точки зрения программиста она выглядит так:

Рисунок 1. Объектная модель Excel.

Для работы вместе с объектом Excel необходимо получить ссылку на его коллекцию книг. С ее помощью можно получить доступ к любой книге. У каждой книги есть коллекция страниц, ссылку на которую также нужно получить для доступа к конкретной странице. Хочу сразу заметить, что доступ к книгам и к страницам можно получить как по их имени, так и по их порядковому номеру.

Чтобы корректно завершить работу с приложением Excel, для всех объектов, которые полученных с помощью позднего связывания, необходимо применить метод ReleaseComObject класса Marshal:

// Уничтожение объекта Excel.
Marshal.ReleaseComObject(excel);
// Вызываем сборщик мусора для немедленной очистки памяти
GC.GetTotalMemory(true); 

Если вызов GC.Collect() не помогает, попробуйте очистку памяти этим способом. Если проигнорировать эту операцию, то в памяти останутся объекты Excel, которые будут существовать даже после того, как вы завершите свое приложение. При этом, даже если пользователь закрыл Excel, в списке запущенных процессов Диспетчера Задач Windows остается процесс Excel. В этом случае, если запустить .NET-приложение и попытаться получить ссылку на работающий Excel, это удастся без проблем. Но, скорее всего, в этом случае, при установке свойства Visible в true, главное окно Excel будет прорисовано не полностью. На экране будут присутствовать только панели инструментов и окантовка главного окна. Эта ситуация нередко наблюдалась с Excel 2000 и Excel XP. В Excel 2003 подобных явлений не замечено.

Но, тем не менее, если ваша программа получает ссылки на какие-либо объекты Excel, вы обязательно должны вызвать для них ReleaseComObject() класса Marshal.

А перед завершением работы с Excel обязательно произведите очистку памяти:

GC.GetTotalMemory(true); 

Управление книгами и страницами.

Позднее связывание подразумевает, что тип объекта, с которым планируется работать, неизвестен, а это значит, что нельзя непосредственно обращаться к его методам и полям, используя оператор ".". Поэтому для вызова метода необходимо знать его название и список формальных параметров, которые он принимает. Для вызова метода в классе Type предусмотрен метод InvokeMember(). Поэтому достаточно получить ссылку на экземпляр класса Type, описывающий тип объекта, с которым устанавливается позднее связывание, и вызвать метод InvokeMember(). Я не буду останавливаться подробно на этом методе, он достаточно хорошо описан в технической документации. Отмечу только самое необходимое.

Метод InvokeMember() перегружен, и имеет три модификации.

public object InvokeMember(
  string name, BindingFlags flags, Binder binder, 
  object target, object[] args);
public object InvokeMember(
  string name, BindingFlags flags, Binder binder, 
  object target, object[] args, CultureInfo info); 
public abstract object InvokeMember(
  string name, BindingFlags flags, Binder binder,
  object target, object[] args, ParameterModifier[] modifiers,
  CultureInfo info, string[] namedParameters); 

Здесь будет использоваться только первая модификация метода. В качестве первого параметра метод получает строковое название метода (поля, свойства) того объекта, с которым устанавливается связь. При этом в названии не должно быть пробелов или лишних символов, кроме того, этот параметр чувствителен к регистру.

Второй параметр принимает на вход флаги, характеризующие связывание. В данном случае понадобятся только следующие флаги:

• BindingFlags.InvokeMethod - найти метод, определить его точку входа, и выполнить, передав ему массив фактических параметров.
• BindingFlags.GetProperty - установить свойство
• BindingFlags.SetProperty - получить значение свойства.

Третий параметр - binder - устанавливается в null.

Через четвертый параметр - target - передается ссылка на объект, к методу которого нужно обратиться.

Пятый параметр - args - это массив с параметрами, который принимает на вход вызываемый поздним связыванием метод или массив, который содержит один элемент - устанавливаемое значение свойства.

Метод InvokeMember() возвращает результат выполнения метода или значение свойства.

Для управления книгами и страницами в первую очередь нужно получить ссылку на их коллекции. Для получения ссылки на коллекцию книг необходимо выполнить следующий код (считается, что ссылка на excel успешно получена):

object workbooks = excel.GetType().InvokeMember(
  "Workbooks", BindingFlags.GetProperty, null, excel, null); 

Объект workbooks и есть managed-ссылка на коллекцию книг.

Для получения доступа к конкретной книге выполняется следующий код, использующий коллекцию книг:

// Доступ к книге по ее порядковому номеру 
// Создаем массив параметров
object[] args = new object[1];
// Мы хотим получить доступ к первой книге Excel
args[0] = 1;
// Получаем ссылку на первую книгу в коллекции Excel
object workbook = workbooks.GetType().InvokeMember(
  "Item",BindingFlags.GetProperty, null, workbooks, args); 
// Доступ к книге по ее названию
// (обратите внимание, что расширение в названии не указывается)
object[] args = new object[1];
// Указываем название книги, к которой мы хотим получить доступ
args[0] = "Книга1";
// Получаем ссылку на первую книгу в коллекции Excel
object workbook = workbooks.GetType().InvokeMember(
  "Item", BindingFlags.GetProperty, null, workbooks, args); 

Если книги с указанным названием не существует, генерируется исключение.

Чтобы открыть, закрыть или создать книгу, воспользуемся соответствующими методами коллекции книг workbooks, ссылка на которую уже успешно получена.

Для создания новой книги у коллекции workbooks есть несколько модификаций метода Add. Если вызвать этот метод без параметров, будет создана новая книга с именем по умолчанию, содержащая количество страниц, также принятое по умолчанию.

// Создаем новую книгу
object workbook = workbooks.GetType().InvokeMember(
  "Add", BindingFlags.InvokeMethod, null, workbooks, null); 

Для создания книги на основе шаблона достаточно передать полное имя файла, содержащего этот шаблон:

// Заносим в массив параметров имя файла
object[] args = new object[1]; args[0] = "D:\MyApp\Templates\invoice.xls";
// Создаем новую книгу
object workbook = workbooks.GetType().InvokeMember(
  "Add", BindingFlags.InvokeMethod, null, workbooks, args); 

Для открытия файла с книгой используется метод Open объекта workbooks:

// Открытие файла d:\book1.xls
// Заносим в массив параметров имя файла
object[] args = new object[1];
args[0] = "D:\book1.xls";
// Пробуем открыть книгу
object workbook = workbooks.GetType().InvokeMember(
  "Open", BindingFlags.InvokeMethod, null, workbooks, args); 

Закрытие книги возможно с помощью метода Close объекта workbook. При этом он принимает несколько необязательных параметров. Рассмотрим два варианта (обратите внимание, что мы вызываем метод Close книги, а не коллекции книг, и целевым объектом у нас выступает workbook, а не workbooks):

// Вариант 1. Закрываем книгу с принятием всех изменений
object[] args = new object[1];
// с принятием всех изменений
args[0] = true;
// Пробуем закрыть книгу
workbook.GetType().InvokeMember(
  "Close", BindingFlags.InvokeMethod, null, workbook, args); 
// Вариант 2. Закрываем книгу с принятием всех изменений
object[] args = new object[2]; 
args[0] = true;
// И под определенным названием
args[1] = @"D:\book2.xls";
// Пробуем закрыть книгу
workbook.GetType().InvokeMember(
  "Close", BindingFlags.InvokeMethod, null, workbook, args); 

Нужно отметить, что сохранение произойдет только в том случае, если в рабочей книге произведены какие-либо изменения. Если создать рабочую книгу и попробовать ее сразу же закрыть, сохранив под другим именем - ничего не выйдет. Excel просто закроет книгу и все.

Чтобы просто сохранить изменения в книге, достаточно вызвать для нее метод Save или SaveAs, передав последнему в качестве параметра имя файла, под которым нужно сохранить книгу.

// Просто сохраняем книгу
workbook.GetType().InvokeMember(
  "Save", BindingFlags.InvokeMethod, null, workbook, null);
// Задаем параметры метода SaveAs - имя файла
object[] args = new object[2];
args[0] = @"d:\d1.xls";
// Сохраняем книгу в файле d:\d1.xls
workbook.GetType().InvokeMember(
  "SaveAs", BindingFlags.InvokeMethod, null, workbook, args);
// Просто сохраняем рабочую книгу. По умолчанию новая книга без
// изменений будет сохранена в папку "Мои Документы" 
// текущей учетной записи Windows
workbook.GetType().InvokeMember(
  "Save", BindingFlags.InvokeMethod, null, workbook, null); 

Для работы со страницами необходимо получить доступ к их коллекции. Естественно, к этому моменту необходимо иметь ссылку на рабочую книгу. Для получения ссылки на коллекцию страниц нужно вызвать свойство Worksheets рабочей книги:

object oWorksheets = workbook.GetType().InvokeMember(
  "Worksheets", BindingFlags.GetProperty, null, workbook, null); 

Объект oWorksheets - это managed-ссылка на коллекцию страниц текущей книги. Зная ссылку на эту коллекцию, можно получить доступ к конкретной странице по ее имени или порядковому номеру (аналогично коллекции рабочих книг):

// Задаем порядковый номер страницы - 1
object[] args = new object[1];
args[0] = 1;
// Получаем ссылку на эту страницу
object worksheet = oWorksheets.GetType().InvokeMember(
  "Item", BindingFlags.GetProperty, null, oWorksheets, args); 
// Задаем имя страницы
object[] args = new object[1];
args[0] = "Лист1";
// Получаем ссылку на страницу с именем "Лист1"
worksheet = oWorksheets.GetType().InvokeMember(
  "Item", BindingFlags.GetProperty, null, oWorksheets, args); 

Работа со страницами. Объект Range. Использование записи макросов для автоматизации Excel.

Страница имеет ссылку на объект Range, который по сути представляет собой диапазон ячеек. Через объект Range можно получить доступ к любой ячейке и ее свойствам. Но объект Range содержит массу методов, и для позднего связывания нужно знать не только формат передаваемых им формальных параметров, но и точное название метода (или свойства, которое по сути дела является комбинацией методов). Иными словами, чтобы успешно вызвать метод через позднее связывание, нужно знать его сигнатуру. До сих пор мы использовали простые методы типа Open, Close, Save, с которыми, в принципе, все понятно. У них немного параметров, и список этих параметров интуитивно ясен.

Чтобы узнать, какие методы поддерживает объект Range, можно воспользоваться утилитой OleView, предназначенной специально для этого. Она поставляется с Visual Studio и лежит в [путь к Visual Studio]\Common7\Tools. Если вы не установили эту утилиту при инсталляции VS, ее можно найти в Интернет. Кроме того, можно, импортировав с помощью утилиты tlbimp.exe библиотеку типов Excel, открыть и изучить ее с помощью дизассемблера/декомпилятора (например, ildasm.exe, Reflector или Anakrino). Дизассемблер покажет объект Range и все его методы.

Но есть более простой способ, который позволит существенно сэкономить время. Это сам Excel, а точнее его запись макросов. Допустим, что нужно отформатировать ячейки определенным образом, например так, как показано на рисунке 2:

Рисунок 2. Результат работы макроса.

Для этого нужно открыть Excel, включить запись макросов и отформатировать указанные ячейки. Полученный макрос будет выглядеть следующим образом:

Sub Макрос1()
'
' Макрос1 Макрос
' Макрос записан 17.04.2005 (Powerful)
'
    Range("B3").Select
    With Selection.Interior
         .ColorIndex = 45
         .Pattern = xlSolid
    End With
    Range("C3").Select
    Selection.Font.ColorIndex = 3
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "Привет"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "из NET!"
    Range("B3:C3").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
         .LineStyle = xlContinuous
         .Weight = xlThin
         .ColorIndex = xlAutomatic
    End With
End Sub 

Как видно, здесь очень часто используется вызов метода Select у объекта Range. Но ведь с ячейками можно работать напрямую, минуя их выделение. Метод Select просто переопределяет ссылки, которые будут возвращаться объектом Selection. Объект Selection - это тот же самый Range. Таким образом, задача существенно упрощается. Нужно просто получить ссылки на соответствующие объекты Range, получить доступ к их внутренним объектам и произвести вызов соответствующих методов или свойств, используя метод InvokeMember().

Например, следующий участок кода окрашивает цвет фона ячейки B3 в оранжевый (причем заливка ячейки - сплошная), а цвет текста ячейки C3 устанавливает в красный:

...
    Range("B3").Select
    With Selection.Interior
        .ColorIndex = 45
        .Pattern = xlSolid
    End With
    Range("C3").Select
    Selection.Font.ColorIndex = 3
...

Попробуем реализовать этот участок в приложении. Допустим, что мы успешно получили ссылки на нужную книгу и страницу.

Ссылка на страницу хранится в переменной worksheet.

// Получаем ссылку на ячейку B3 (точнее на объект Range("B3"))
object range = worksheet.GetType().InvokeMember(
  "Range", BindingFlags.GetProperty, null, worksheet, new object[]{"B3"});
// Получаем ссылку на объект Interior
object interior = range.GetType().InvokeMember(
  "Interior", BindingFlags.GetProperty, null, range, null); 
// Устанавливаем заливку (Аналог вызова
// Range("B3").Interior.ColorIndex) 
interior.GetType().InvokeMember(
  "ColorIndex", BindingFlags.SetProperty, null, interior, new object[]{45}); 
// Устанавливаем способ заливки (Pattern = xlSolid)
/* Для того, чтобы узнать значение константы xlSolid, можно посмотреть документацию, использовать описанный выше импорт библиотеки типов, а можно просто прогнать наш макрос в Visual Basic по шагам и посмотреть значение в контроле переменных, что существенно сэкономит Ваше время. */
// Задаем параметр xlSolid = 1;
object[] args = new object[]{1}
// Устанавливаем свойство Pattern в xlSolid
interior.GetType().InvokeMember(
  "Pattern", BindingFlags.SetProperty, null, interior, args); 

Чтобы задать текст, можно использовать свойство Value объекта Range.

range.GetType().InvokeMember(
  "Value", BindingFlags.SetProperty, null, range, new object[]{"Привет"});

Далее разбирать код я не буду, советую читателям самим поэкспериментировать с установкой свойств Excel из приложений .NET, по аналогии с приведенными здесь примерами. А сейчас перейдем к событиям Excel и их перехвату с использованием позднего связывания.

Перехват событий Excel

Перехват событий Excel позволяет отслеживать его состояние и контролировать некоторые действия. Например, можно отследить закрытие рабочей книги и корректно отключиться от Excel, произведя очистку памяти и прочие завершающие процедуры. Чтобы объяснить, как перехватывать события, проведу небольшой экскурс в события COM-объектов. В этом отступлении я предполагаю, что читатель немного знаком с архитектурой COM.

Если объект хочет получать события другого COM-объекта, то он должен уведомить об этом источник событий, зарегистрировав себя в списке объектов-получателей уведомлений о событиях. Для этого СОМ предоставляет интерфейс IConnectionPointContainer, содержащий метод FindConnectionPoint. С помощью вызова метода FindConnectionPoint объект-получатель события получает "точку подключения", интерфейс IConnectionPoint, и регистрирует c помощью метода Advise свою реализацию интерфейса IDispatch, методы которого будут вызываться при возникновении тех или иных событий. Excel определяет интерфейс, который должен реализовываться классом-приемником событий.

interface
["00024413-0000-0000-C000-000000000046"]
{
  DispId(0x61d)]
  void NewWorkbook(object Wb); 
  DispId(0x616)]
  void SheetSelectionChange(object Sh, object Target); 
  DispId(0x617)]
  void SheetBeforeDoubleClick(object Sh, object Target, ref bool Cancel);
  DispId(1560)]
  void SheetBeforeRightClick(object Sh, object Target, ref bool Cancel); 
  DispId(0x619)]
  void SheetActivate(object Sh); 
  DispId(0x61a)]
  void SheetDeactivate(object Sh); 
  DispId(0x61b)]
  void SheetCalculate(object Sh); 
  DispId(0x61c)]
  void SheetChange(object Sh, object Target); 
  DispId(0x61f)]
  void WorkbookOpen(object Wb); 
  DispId(0x620)]
  void WorkbookActivate(object Wb); 
  DispId(0x621)]
  void WorkbookDeactivate(object Wb); 
  DispId(1570)]
  void WorkbookBeforeClose(object Wb, ref bool Cancel); 
  DispId(0x623)]
  void WorkbookBeforeSave(object Wb, bool SaveAsUI, ref bool Cancel); 
  DispId(0x624)]
  void WorkbookBeforePrint(object Wb, ref bool Cancel); 
  DispId(0x625)]
  void WorkbookNewSheet(object Wb, object Sh); 
  DispId(0x626)]
  void WorkbookAddinInstall(object Wb); 
  DispId(0x627)]
  void WorkbookAddinUninstall(object Wb); 
  DispId(0x612)]
  void WindowResize(object Wb, object Wn); 
  DispId(0x614)]
  void WindowActivate(object Wb, object Wn); 
  DispId(0x615)]
  void WindowDeactivate(object Wb, object Wn); 
  DispId(0x73e)]
  void SheetFollowHyperlink(object Sh, object Target); 
  DispId(0x86d)]
  void SheetPivotTableUpdate(object Sh, object Target); 
  DispId(2160)]
  void WorkbookPivotTableCloseConnection(object Wb, object Target); 
  DispId(0x871)]
  void WorkbookPivotTableOpenConnection(object Wb, object Target); 
} 

Таким образом, класс-приемник событий должен реализовывать этот интерфейс и регистрировать себя, используяIConnectionPointContainer и IConnectionPoint. Библиотека базовых классов .NET уже определяет managed-версии интерфейсов: дляIConnectionPointContainer это UCOMIConnectionPointContainer, а для IConnectionPoint - UCOMIConnectionPoint, которые определены в пространстве имен System.Runtime.InteropServices.

Регистрация класса-приемника событий будет выглядеть так:

// Объявляем ссылки на IConnectionPointContainer 
UCOMIConnectionPointContainer icpc;
// и на IConnectionPoint
UCOMIConnectionPoint icp; 
// Получаем ссылку на Excel
object excelApplication = Marshal.GetActiveObject("Excel.Application"); 
// Получаем ссылку на интерфейс IConnectionPointContainer
icpc = (UCOMIConnectionPointContainer)excelApplication; 
// Получаем "точку подключения"
Guid guid = new Guid("00024413-0000-0000-C000-000000000046");
icpc.FindConnectionPoint(ref guid, out icp); 
// Регистрируем класс - приемник событий, который реализует
// интерфейс с GUID ["00024413-0000-0000-C000-000000000046"]
// При этом наш класс получает уникальный идентификатор
// cookie, который нужно сохранить, чтобы иметь
// возможность отключиться от источника событий
icp.Advise(ExcelEventSink, out cookie); 

Для отключения от событий достаточно вызвать метод Unadvise(), и передать ему в качестве параметра идентификатор cookie, полученный при регистрации класса-приемника событий методом Advise:

icp.Unadvise(cookie); 

Заключение.

В статье на примере работы с MS Excel рассматривалось взаимодействие COM и NET с использованием позднего связывания. Используя аналогичный подход, можно организовать управление любым COM-сервером.

В приложенном к данной статье файле находится класс, с помощью которого можно организовать обработку событий Excel в любом приложении .NET.

Литература

1. Эндрю Троелсен. С# и платформа .NET. Библиотека программиста. - СПб. Питер, 2004.
2. Н. Елманова, С. Трепалин, А. Тенцер. Delphi 6 и технология СОМ. - СПб. Питер, 2002.
3. Техническая документация MSDN.