Обмен данными с MS Excel в Delphi при помощи OLE.

добавлено автор:

Обмен данными с MS Excel в Delphi при помощи OLE.

Здравствуйте уважаемые коллеги!

Все мы рано или поздно сталкиваемся с задачами обмена данных с приложениями пакета MS Office. Одно из них — это MS Excel. И именно о взаимодействии с данным продуктом MS Office пойдет речь в данной статье.

Один из способов взаимодействия Delphi c MS Excel — это подключиться к нему как к OLE объекту.

Итак.
Прежде всего для работы с MS Excel и OLE добавим в секцию Uses модули ComObj и ActiveX.

И первое что нам нужно проверить, а установлен ли MS Excel на компьютере пользователя в принципе.
Для этого воспользуемся функцией CLSIDFromProgID, которая ищет в реестре CLSID для переданного ProgID:
Справка из MSDN: Метод CLSIDFromProgID
Параметры:
pszProgID: POleStr — Строка с именем объекта
clsid: TCLSID — Указатель на структуру TGUID в которую передается найденный объект;
Возвращает:
HRESULT — Результат, который может принимать значения:
S_OK — объект найден;
CO_E_CLASSSTRING — Зарегистрированный CLSID для ProgID является недействительным;
REGDB_E_WRITEREGDB — Ошибка записи CLSID в реестр.
Из перечисленных результатов нам нужен S_OK.
Напишем функию для определения наличия Excel у пользователя:

function IsXlsInstall: boolean;
var
  CLSID: TCLSID;
begin
  Result := (CLSIDFromProgID('Excel.Application', CLSID) = S_OK);
end;

Если Excel установлен, тогда выполним подключение к нему. Сделать это можно двумя способами: GetActiveOleObject — Получить ссылку на уже запущенный экземпляр Excel или CreateOleObject — Создать новый экземпляр Excel.
Если у нас стоит задача получать данные из запущенного Excel, тогда мы должны использовать только первый вариант, в остальных случаях пробуем подключиться и если не получается, то создаем.
Напишем 2 функции, для подключения XlsConnect и запуска нового XlsStart:
Добавим переменную FXlsApp с типом Variant, которая будет содержать в себе ссылку на объект Excel.

private
  FXlsApp: variant;
***  
function XlsConnect: boolean;
begin
  Result := False;
  try
    FXlsApp := GetActiveOleObject('Excel.Application');
    Result := True;
  except
  end;
end;

procedure XlsStart;
begin
  FXlsApp := CreateOleObject('Excel.Application');
end;

Теперь можно добавить кнопку, на клик которой подключимся к MS Excel используя написанные функции:

procedure btnConnectClick(Sender: TObject);
begin
  if not IsXlsInstall then
    raise Exception.Create('Приложение MS Excel не найдено на данном компьютере!');
  if not XlsConnect then
    XlsStart;
  FXlsApp.Visible := True;
end;

По умолчанию окно Excel запускается в фоновом режиме. Строка FXlsApp.Visible := True; делает фоновое окно Excel видимым.

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

procedure XWorkbookAdd(const FilePath: string = '');
begin
  FXlsApp.WorkBooks.Add(FilePath);
end;

Книга добавлена, теперь попробуем записать что-нибудь в неё.

FXlsApp.Cells[Row,Col] := 'Тестовая строка';

Где Row — индекс строки, и Col — индекс столбца, которые начинаются с единицы.

FXlsApp.Range['A1'] := 'Ячейка А1';

Где Range — массив ячеек, а А1 — привычные для Excel координаты ячейки.
В качестве координат может быть указан диапазон. Например, код

FXlsApp.Range['A3:A10'] := 5;

заполнит цифрой 5 все ячейки с А3 по А10, а код

FXlsApp.Range['A3:A10'].Interior.Color := clMoneyGreen;

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

ShowMessage(FXlsApp.Cells[5,1]);

Выведет сообщение с содержимым ячейки с координатами: Строка=5, Столбец=1.

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

FXlsApp.ActiveWorkbook.SaveAs('C:\Test.xlsx');

Где ActiveWorkbook — текущая книга.
И закрыть приложение Excel командой:

FXlsApp.Quit;

Как понимаете этим возможности управления Excel из Delphi не ограничиваются. И есть один достаточной простой способ узнать, как выполнить необходимо действие с Excel из Delphi.
Называется оно — Макросы.

Представим, что нам необходимо выполнить объединение нескольких ячеек в одну и мы не знаем как это сделать. Но хотим узнать. Для этого выполняем следующие шаги:
1. Запускаем Excel и создаем пустую книгу.
2. Запускаем команду «Записать макрос», по умолчанию название макроса будет «Макрос1». (В разных версиях Excel данная команда находится в разных пунктах меню).
3. Выделяем некоторый диапазон ячеек и нажимаем кнопку «Объединить и поместить в центре».
4. Останавливаем запись макроса.
5. Вызываем список макросов и выбираем там свой записанный макрос.
6. Нажимаем кнопку «Изменить»
Запускается редактор Microsoft Visual Basic for Application в котором видим код проделанных действий:

Sub Макрос1()
'
' Макрос1 Макрос
'
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
End Sub

Давайте разберем по подробнее, что же такого он нам тут написал:
With Selection — Для выделенного диапазона ячеек настраиваем свойства:
HorizontalAlignment = xlCenter — Горизонтальная ориентация = по центру.
VerticalAlignment = xlBottom — Вертикальная ориентация — по нижнему краю.
WrapText = False — Перенос текста по словам — выключен.
Orientation = 0 — Ориентация 0 градусов.
AddIndent = False — Использование автоматического отступа вкл/выкл.
IndentLevel = 0 — Уровень отступа в 0.
ShrinkToFit = False — Сжимать текст по размерам столбца вкл/выкл.
ReadingOrder = xlContext — Порядок чтения по контексту.
MergeCells = False — Объединенные ячейки вкл/выкл
End With — Конец секции работы с выделенным диапазоном.
Selection.Merge — Объединить выделенный диапазон.

Теперь попробуем объединить ячейки из Delphi:

FXlsApp.Range['B5:C8'].Select;

Выделяем нужный нам диапазон. 

FXlsApp.Selection.MergeCells := True;

Объединяем ячейки задав свойство. Или при помощи метода:

FXlsApp.Selection.Merge;

Таким способом можно получать код практически для любых необходимых манипуляций.
А если какое-то свойство или метод вызывает вопросы, то можно воспользоваться справкой на MSDN.

Обратите внимание на особенность работы с массивами в VBA. Индексы в массивах в Delphi оборачиваются в квадратные скобки, в то время как в VBA они будут в круглых. И код в Delphi

FXlsApp.Range['B5:C8'].Select;

в VBA будет выглядеть как

Range("D5:H14").Select;

Ниже приведу небольшой FAQ по вопросу взаимодействия с Excel из Delphi

Как определить значения констант в Excel для использования в Delphi?

В редакторе VBA ставим точку остановки напротив интересующей константы. Нажимаем выполнить и когда выполнение остановиться, наводим на интересующую константу:
constatns

Как отключить выводы сообщений в Excel?

FXlsApp.DisplayAlerts := False;

Как получить список книг из Excel?

for i := 1 to FXlsApp.Workbooks.Count do
  ListBox1.Items.Add(FXlsApp.Workbooks.Item[i].Name);

Как отключить отображение сетки?

FXlsApp.ActiveWindow.DisplayGridlines := False;

Как вывести текущий лист на предпросмотр печати?

FXlsApp.ActiveWindow.SelectedSheets.PrintPreview;

Как выделить жирным часть текста в ячейки?

var
  Row: integer; // Индекс строки
  Col: integer; // Индекс ячейки
  TextSelStart: integer; // Начиная с символа
  TextSelLength: integer; // Кол-во выделенных символов
begin
  FXlsApp.Cells[Row, Col].Characters(TextSelStart, TextSelLength).Font.Bold := True;
end;

Как выполнить автоподбор высоты строки для склеенной ячейки?

var
  merge_area: variant;
  cell_width,
  cells_width,
  i: integer
begin
  // Сохраняем диапазон склеенных ячеек в переменную
  merge_area := FXlsApp.Range['D5'].MergeArea;
  // Сохраняем ширину ячейки, для которой будем подбирать высоту
  cell_width := FXlsApp.Range['D5'].ColumnWidth;
  cells_width := 0;
  for i := 1 to merge_area.Columns.Count do
    // Получаем общую ширину всех столбцов склеенного диапазона
    cells_width := cells_width + merge_area.Columns[i].ColumnWidth; 
  // Разъединяем ячейки
  merge_area.UnMerge; 
  // Устанавливаем ширину интересуемой ячейки равной общей ширине
  FXlsApp.Range['D5'].ColumnWidth := cells_width;
  // Вызываем стандартный метод автоподбора высоты строки
  FXlsApp.Rows[5].EntireRow.AutoFit; 
  // Возвращаем исходную ширину интересуемой ячейки
  FXlsApp.Range['D5'].ColumnWidth := cell_width;
  // Склеиваем обратно диапазон
  merge_area.Merge;
end;

Как получить используемый диапазон ячеек?

Result := exApp.ActiveSheet.UsedRange;

Как получить букву столбца по индексу?

uses Math;
***
function ColIdxToStr(const Col: integer): string
const
  CharsCount: integer = 26;
  Offset: integer = 64;
var
  Rank: byte;
  Col, Tmp: integer;
begin
  Result := '';
  while Col > 0 do begin
    Rank := 0;
    Tmp := Col;
    while Tmp > CharsCount do begin
      Tmp := Ceil(Tmp / CharsCount - 1);
      Inc(Rank);
    end;
    Result := Result + Chr(Tmp + Offset);
    Col := Col - Trunc(Power(CharsCount,Rank)) * Tmp;
  end;
end;

На этом пока все. Если у вас появятся вопросы по данной теме, пишите их в комментариях или мне на почту:info@asd-soft.ru

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *