Обмен данными с 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 ставим точку остановки напротив интересующей константы. Нажимаем выполнить и когда выполнение остановиться, наводим на интересующую константу:
Как отключить выводы сообщений в 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