Delphi и OLE Automation с Excel

Автоматизация позволяет одному приложению управлять другим приложением. Управляемое приложение называется сервером автоматизации (в нашем случае Excel). Приложение, управляющее сервером называется диспетчером автоматизации.

Есть два пути для получения доступа к серверам автоматизации:

Позднее связывание (Интерфейс IDispatch)

При использовании данного метода имена функций и типы параметров решаются во время выполнения программы, все параметры определены вариантным типом.

Поскольку во время компиляции невозможно определить соответствия имен функций и типов параметров, данный метод чреват ошибками.

Так как имена функций и типы параметров должны проверяться во время выполнения программы, данный метод выполняется медленно.

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

При использовании данного метода имена функций и типы параметров полностью решаются во время компиляции.

Библиотека типов должна импортироваться в Delphi. Библиотека типов является языковым нейтральным описанием всех объектов и функций, поддерживаемых сервером. (Это подобно файлу заголовка языка C).

При вызове функции должны обязательно присутствовать все параметры, даже те, которые в документации указаны как дополнительные (необязательные). Это позволяет обнаружить и исправить множество ошибок еще до запуска программы.

Скорость выполнения значительно быстрее, чем при использовании позднего связывания.

Из-за преимуществ второго метода остальная часть документа демонстрирует принципы создания приложений с ранним связыванием. Все приложения, использующие Excel автоматизацию, должны пользоваться последним методом, если нет причин для первого.

Модуль Pascal должен быть создан на основе файла библиотеки типов.

Выберите пункт меню Project|Import Type Library.

Нажмите кнопку Add и выберите следующий файл

Нажмите OK.

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

Наиболее простой путь заключается в следующем: удалите модуль excel_tlb из проекта и только после этого добавьте его в список используемых модулей.

Справочный файл c:\program files\microsoft office\office\vbaxl8.hlp содержит информацию о доступных объектах Excel.

"Записыватель" макросов позволяет быстро создавать VBA-код. После этого он довольно может легко быть портирован в Delphi.

Код следующего примера демонстрирует создание простой электронной таблицы и наполнение ее данными. Не забудьте добавить excel_tlb в список используемых модулей.

Настоятельно рекомендую хранить код автоматизации в отдельном модуле, это поможет избежать проблем конфликта имен.

unit sheet;

interface

uses
  windows, sysutils, excel_tlb;

procedure CreateSpreadsheet;

implementation

procedure CreateSpreadsheet(filename: string);
var

  xla: _Application;
  xlw: _Workbook;
  LCID: integer;
begin

  xla := CoApplication.Create;
  LCID := GetUserDefaultLCID;
  try
    xla.Visible[LCID] := true;
    // пустая книга
    //xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
    // новая книга на основе шаблона
    xlw := xla.Workbooks.Add(
      'c:\delphi\excel\sample\demo.xlt',
      LCID);
    xla.Range['A1', 'A1'].Value := 'Date';
    xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);
    xla.Cells[3, 1].Value := 'Numbers';
    xla.Range['B3', 'E3'].Value := VarArrayOf([1, 10, 100, 1000]);
    xla.Range['F3', 'F3'].Formula := '=Sum(B3:E3)';
    OLEVariant(xla).Run(
      'Demo',
      FormatDateTime('dd-mmm-yyyy', Now)
      );
    xlw.SaveAs(
      filename,
      xlWorkbookNormal,
      '', '', False, False,
      xlNoChange,
      xlLocalSessionChanges,
      true, 0, 0, LCID);
  finally
    xla.Quit;
  end;
end;

end.

Добавьте библиотеку типов в список используемых молулей.

uses
  windows, sysutils, excel_tlb;

Первая строчка кода создает объект Excel приложения.

xla := CoApplication.Create;

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

LCID := GetUserDefaultLCID;

Следующая строчка кода устанавливает в истину свойство видимости, что заставляет Excel вывести свое окно. Это полезно для контроля выполняемого кода.

Примечание: Для вызова этой функции необходим параметр LCID. К сожалению этот факт умалчивается в электронной документации по Excel. В файле c:\program files\borland\Delphi 3\imports\excel_tlb.pas наглядно видны свойства функций и определения методов.

xla.visible[LCID] := true;

Следующий код создает новую книгу и назначает ссылку на нее одной из переменных Delphi. Для VBA параметр шаблона необязателен, для Delphi - обязателен.

xlw := xla.Workbooks.Add('c:\delphi\excel\sample\demo.xlt', LCID); 

Примечание: Вам вовсе не обязательно подставлять файл шаблона Excel (.xlt), но все же это наилучший способ для форматирования информации. Чем больше сделано с помощью Excel, тем меньше придется делать с помощью Delphi. На данный момент это является лидирующей технологией.

Для создания пустой книги используйте:

xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);

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

xla.Range['A1', 'A1'].Value := 'Date';
xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);

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

xla.Range['A2', 'D2'].Value := VarArrayOf([1, 10, 100, 1000]);

Следующая строка демонстрирует использование формулы.

xla.Range['E2', 'E2'].Formula := '=Sum(a2:d2)';

Следующая строка кода выполняет VBA функцию, хранящуюся в файле шаблона. На первый взгляд все выглядит достаточно сложно, но это только кажется. Преобразование типа xla к OLEVariant позволяет вызвать функцию, используя позднее, а не раннее связывание. (Причина в имени метода и параметрах, решаемых только во время прогона программы, а никак во время разработки). Delphi просто не знает количество и тип параметров, передаваемых макросу ‘Demo’.

OLEVariant(xla).Run(
'Demo',
FormatDateTime('dd-mmm-yyyy', Now));

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

xlw.SaveAs(
filename,
xlWorkbookNormal,
'', '',False,False,
xlNoChange, xlLocalSessionChanges,
true, 0, 0, LCID);

Следующая строчка кода закрывает Excel и перераспределяет всю память, связанную с программой.

xla.quit;

Итог

• Всегда используйте раннее связывание.
• Если позднее связывание необходимо для вызовов некоторых функций, используйте где возможно раннее связывание и преобразование типа объектной переменной к типу OLEVariant для вызовов, требующим позднее связывание.
• Не включайте модуль библиотеки типов в ваш проект.
• Создавайте код автоматизации в отдельном модуле.
• Используйте "записыватель" макросов Excel для создания прототипа кода автоматизации.
• Используйте файл электронной справки vbaxl8.hlp для получения информации об объектах Excel.
• Используйте модуль excel_tlb.pas для проверки необходимых Delphi типов и количества параметров.
• Загружайте и используйте шаблоны Excel (.xlt файлы), содержащие предварительное форматирование и связывание данных. Этот способ существенно быстрее и не требует большого времени для создания форматированных электронных таблиц. Шаблоны ДОЛЖНЫ сохраняться приложением в своей рабочей директории. Это поможет избежать проблем, связанных с конфликтом имен. Файлы шаблонов могут также содержать макросы, которые могут быть вызваны из приложений Delphi.
• Удостоверьтесь в том, что ваш код содержит команду закрытия приложения Excel (xla.quit). Не вызывая xla.quit, можно быстро исчерпать системные ресурсы, особенно при работе с большим количеством документов Excel.
• Наличие множества незакрытых документов Excel легко проверить в Windows NT, используя Менеджер Задач (нажмите CTL+ALT+Del для его открытия).
• В больших электронных таблицах повысить быстродействие вам поможет обработка ячеек посредством "мультикоманды", оперирующей одновременно множеством ячеек. Это также улучшит читаемость кода.

Тестирование производилось на компьютере P166 с 64Мб памяти. Первоначальная инициализация приложения не производилась. Это гарантировало, что Excel при загрузке пользовался диском, а не кэшем. Первоначальная инициализация существенно уменьшила бы скорость загрузки приложения. В реальной ситуации процесс загрузки занимает около 5 секунд.

Тест включал в себя загрузку числовых данных в чистую электронную таблицу размером 10 колонок на n строк. Для вычисления быстродействия использовались следующие три метода:

• Заполнение листа ячейка за ячейкой.
• Заполнение одной колонки за один проход.
• Заполнение всей таблицы за один проход.

Приведенное время не включает в себя время, затраченное на поиск данных в базе данных. Но оно вносит существенные коррективы при создании больших электронных таблиц.

Время приведено в минутах и секундах, округленных до ближайшей целой.

Только небольшие электронные таблицы могут быть эффективно заполнены методом записи данных в каждую ячейку по отдельности.

Большие таблицы эффективно заполнять колонка за колонкой.

Также необходимо учесть дополнительную сложность при кодировании методом "ячейка за ячейкой".

Использовать буфер для передачи данных также не рекомендуется, так как это нарушит имеющиеся в нем данные и может привести к усложнению и неудобночитаемости кода.

Сохранение данных в CSV-файле и загрузка его в Excel поможет ускорить вывод данных, но для этого потребуется дополнительное форматирование книги Excel в самом коде, что усложняет само кодирование и может привести к дополнительным ошибкам.

Использованные для тестов процедуры:

//-----------------------------------------------------------------------

procedure FillByCell;
var

  xla: _Application;
  xlw: _Workbook;
  LCID: integer;
  i, j: integer;
begin

  xla := CoApplication.Create;
  LCID := GetUserDefaultLCID;
  try
    xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
    for i := 1 to ROWS do
    begin
      for j := 1 to 10 do
      begin
        xla.Cells[i, j] := i + j;
      end;
    end;
    xlw.close(false, '', false, LCID);
  finally
    xla.Quit;
  end;
end;

//-----------------------------------------------------------------------

procedure FillByRow;
var

  xla: _Application;
  xlw: _Workbook;
  CellFrom: string;
  CellTo: string;
  i, j: integer;
  Row: array[1..10] of variant;
  LCID: integer;
begin

  xla := CoApplication.Create;
  LCID := GetUserDefaultLCID;
  try
    xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
    for i := 1 to ROWS do
    begin
      for j := 1 to 10 do
      begin
        Row[j] := i + j;
      end;
      CellFrom := 'A' + InttoStr(i);
      CellTO := 'J' + InttoStr(i);
      xla.Range[CellFrom, CellTo].Value := VarArrayOf(Row);
    end;
    xlw.close(false, '', False, LCID);
  finally
    xla.Quit;
  end;
end;

//-----------------------------------------------------------------------

procedure FillBySheet;
var

  xla: _Application;
  xlw: _Workbook;
  CellFrom: string;
  CellTo: string;
  i, j: integer;
  range: Variant;
  row: array[1..10] of Variant;
  LCID: integer;
begin

  xla := CoApplication.Create;
  LCID := GetUserDefaultLCID;
  try
    xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
    Range := VarArrayCreate([1, ROWS], varVariant);
    for i := 1 to ROWS do
    begin
      for j := 1 to 10 do
      begin
        row[j] := i + j;
      end;
      Range[i] := VarArrayOf(row);
    end;
    CellFrom := 'A' + InttoStr(1);
    CellTO := 'J' + InttoStr(ROWS);
    xla.Range[CellFrom, CellTo].FormulaArray := Range;
    xlw.close(false, '', False, LCID);
  finally
    xla.Quit;
  end;
end;

Приложение 2 – Использование в Delphi класса-оболочки

Предпочтительней использовать вызовы Автоматизации непосредственно из приложения, нижеприведенный пример демонстрирует технологию создания в Delphi класса-оболочки для использования объектов Excel в ваших приложениях. Это позволит вам иметь простой интерфейс к объектам, а также помочь с любыми изменениями объектных интерфейсов Excel в ее будущих версиях.

unit sheet;

interface

uses

  EXCEL_TLB, windows, sysutils;

//-------------------------------------------------------------------------

type

  tExcel = class
  private
    xla: _Application;
    xlw: _Workbook;
    LCID: integer;
    procedure fSetVisible(Visible: boolean);
    function fGetVisible: boolean;
    procedure fSetCell(Cell: string; Value: OLEVariant);
    function fGetCell(Cell: string): OleVariant;
  public
    constructor create;
    destructor destroy; override;
    procedure AddWorkBook(Template: OleVariant);
    procedure SaveAs(filename: string);
    property Visible: boolean
      read fGetVisible write fSetVisible;
    property Cell[Cell: string]: OleVariant
    read fGetCell write fSetCell;
  end;

  //-------------------------------------------------------------------------

procedure CreateSpreadsheet(filename: string);

//-------------------------------------------------------------------------

implementation

//-------------------------------------------------------------------------

constructor tExcel.create;
begin

  LCID := GetUserDefaultLCID;
  xla := CoApplication.Create;
end;

//-------------------------------------------------------------------------

destructor tExcel.destroy;
begin

  xla.Quit;
  inherited;
end;

//-------------------------------------------------------------------------

procedure tExcel.AddWorkBook(Template: OleVariant);
begin

  xlw := xla.Workbooks.Add(Template, LCID);
end;

//-------------------------------------------------------------------------

procedure tExcel.fSetVisible(Visible: boolean);
begin

  xla.visible[lcid] := Visible;
end;

//-------------------------------------------------------------------------

function tExcel.fGetVisible: boolean;
begin

  result := xla.visible[lcid];
end;

//-------------------------------------------------------------------------

procedure tExcel.fSetCell(Cell: string; Value: OLEVariant);
begin

  xla.Range['A1', 'A1'].Value := value;
end;

//-------------------------------------------------------------------------

function tExcel.fGetCell(Cell: string): OleVariant;
begin

  result := xla.Range['A1', 'A1'].Value;
end;

//-------------------------------------------------------------------------

procedure tExcel.SaveAs(filename: string);
begin

  xlw.SaveAs(
    filename,
    xlWorkbookNormal,
    '',
    '',
    False,
    False,
    xlNoChange,
    xlLocalSessionChanges,
    true,
    0,
    0,
    LCID);
end;

Нижеприведенный пример использует данный класс для создания электронной таблицы.

procedure CreateSpreadsheet(filename: string);
var
  xl: tExcel;
begin
  xl := tExcel.create;
  try
    xl.AddWorkBook('c:\graham\excel\sample2\ssddemo.xlt');
    xl.visible := true;
    xl.cell['a1'] := 'тест';
    xl.SaveAs(filename);
  finally
    xl.free;
  end;
end;