Обращение к Microsoft Excel из среды Delphi
Обмен данными с 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 — индекс столбца, которые начинаются с единицы.
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.
После того, как мы произвели необходимые нам манипуляции с 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.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.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.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;
uses ComObj;
var Excel: Variant;
Создание новой книги:
Excel.Workbooks.Add;
Excel.Workbooks.Open;
Закрытие Excel:
Excel.ActiveWorkbook.Close;
Excel.Application.Quit;
Excel.DisplayAlerts:=False;
Отображаем Excel на экране:
Excel.Visible:= True;
или скрываем:
Excel.Visible:= False;
Excel.ActiveSheet.PrintOut;
Чтение/запись данных в EXCEL
где B2
- адрес ячейки.
Формат ячеек в EXCEL
или
1) объединение ячеек
2) перенос по словам
5) граница для ячеек
Excel.Selection.Borders.LineStyle:=1;
Использование паролей в EXCEL
try
except
end;
где pass - пароль, установленный для защиты книги.
Excel.Rows["5:15"].Select;
Excel.Selection.Delete;
Excel.Range["D3"].Select;
Удачной работы!
Источник:
В данном обзоре рассмотрены основные конструкции, позволяющие получить доступ к книге Excel из DELPHI.
Организация доступа к книге EXCEL
Для взаимодействия с MS excel в программе необходимо
использовать модуль ComObj
uses ComObj;
и объявить переменную для доступа к MS excel следующего типа:
var Excel: Variant;
Инициализация переменной Excel в простейшем случае можно
осуществить так:
Excel:= CreateOleObject("Excel.Application");
Создание новой книги:
Excel.Workbooks.Add;
Открытие существующей книги (где path
- путь к фалу с расширением xls.):
Excel.Workbooks.Open;
Открытие существующей книги только для чтения:
Excel.Workbooks.Open;
Закрытие Excel:
Excel.ActiveWorkbook.Close;
Excel.Application.Quit;
Блокировка запросов (подтвеждений, уведомлений) Excel,
например, запретить запрос на сохранение файла:
Excel.DisplayAlerts:=False;
Отображаем Excel на экране:
Excel.Visible:= True;
или скрываем:
Excel.Visible:= False;
Печать содержимого активного листа excel:
Excel.ActiveSheet.PrintOut;
Чтение/запись данных в EXCEL
Доступ к ячейке в текущей книге Excel можно осуществить
следующим образом:
Excel.Range["B2"]:="Привет!";
- для записи
значения в ячейку или
s:=Excel.Range["B2"];
- для чтения,
где B2
- адрес ячейки.
Или используя стиль ссылок R1C1:
Excel.Range]:="Привет!";
, где
- координата ячейки.
Вообще, ячейке Excel можно присваивать любое значение (символьное, целое, дробное, дата) при этом Excel установит форматирование в ячейке применяемое по умолчанию.
Формат ячеек в EXCEL
Выделить (выбрать) группу ячеек для последующей работы можно
так:
Excel.Range, Excel.Cells].Select;
или
Excel.Range["A1:C5"].Select;
при этом будет выделена область находящаяся между ячейкой A1 и C5.
После выполнения выделения можно установить:
1) объединение ячеек
Excel.Selection.MergeCells:=True;
2) перенос по словам
Excel.Selection.WrapText:=True;
3) горизонтальное выравнивание
Excel.Selection.HorizontalAlignment:=3;
при присваивании значения 1 используется выравнивание по умолчанию, при 2 -
выравнивание слева, 3 - по центру, 4 - справа.
4) вериткальное выравнивание
Excel.Selection.VerticalAlignment:=1;
присваиваемые значения аналогичны горизонтальному выравниванию.
5) граница для ячеек
Excel.Selection.Borders.LineStyle:=1;
При значении 1 границы ячеек рисуются тонкими сплошными линиями.
Кроме этого можно указать значения для свойства Borders, например, равное 3.
Тогда установится только верхняя граница для блока выделения:
Excel.Selection.Borders.LineStyle:=1;
Значение свойства Borders задает различную комбинацию граней ячеек.
В обоих случаях можно использовать значения в диапазоне от 1 до 10.
Использование паролей в EXCEL
Установка пароля для активной книги может быть произведена
следующим образом:
try
// попытка установить пароль
Excel.ActiveWorkbook.protect("pass");
except
// действия при неудачной попытке установить пароль
end;
где pass - устанавливаемый пароль на книгу.
Снятие пароля с книги аналогично, использовуем команду
Excel.ActiveWorkbook.Unprotect("pass");
где pass - пароль, установленный для защиты книги.
Установка и снятие пароля для активного листа книги Excel
производится командами
Excel.ActiveSheet.protect("pass"); // установка пароля
Excel.ActiveSheet.Unprotect("pass"); // снятие пароля
где pass - пароль, установленный для защиты книги.
Вспомогательные операции в EXCEL
Удаление строк со сдвигом вверх:
Excel.Rows["5:15"].Select;
Excel.Selection.Delete;
при выполнении данных действий будут удалены строки с 5 по 15.
Установка закрепления области на активном листе Excel
// снимаем закрепление области, если оно было задано
Excel.ActiveWindow.FreezePanes:=False;
// выделяем нужную ячейку, в данном случае D3
Excel.Range["D3"].Select;
// устанавливаем закрепление области
Excel.ActiveWindow.FreezePanes:=True;
В данном обзоре рассмотрены основные конструкции, позволяющие получить доступ к книге Excel из DELPHI.
Организация доступа к книге EXCEL
Для взаимодействия с MS excel в программе необходимо использовать модуль ComObj
uses ComObj;
и объявить переменную для доступа к MS excel следующего типа:
var Excel: Variant;
Инициализация переменной Excel в простейшем случае можно осуществить так:
Excel:= CreateOleObject("Excel.Application");
Создание новой книги:
Excel.Workbooks.Add;
Открытие существующей книги (где path - путь к фалу с расширением xls.):
Excel.Workbooks.Open;
Открытие существующей книги только для чтения:
Excel.Workbooks.Open;
Закрытие Excel:
Excel.ActiveWorkbook.Close;
Excel.Application.Quit;
Блокировка запросов (подтвеждений, уведомлений) Excel, например, запретить запрос на сохранение файла:
Excel.DisplayAlerts:=False;
Отображаем Excel на экране:
Excel.Visible:= True;
или скрываем:
Excel.Visible:= False;
Печать содержимого активного листа excel:
Excel.ActiveSheet.PrintOut;
Чтение/запись данных в EXCEL
Доступ к ячейке в текущей книге Excel можно осуществить следующим образом:
Excel.Range["B2"]:="Привет!";- для записи значения в ячейку или s:=Excel.Range["B2"]; - для чтения, где B2 - адрес ячейки.
Или используя стиль ссылок R1C1:
Excel.Range]:="Привет!";, где - координата ячейки.
Вообще, ячейке Excel можно присваивать любое значение (символьное, целое, дробное, дата) при этом Excel установит форматирование в ячейке применяемое по умолчанию.
Формат ячеек в EXCEL
Выделить (выбрать) группу ячеек для последующей работы можно так:
Excel.Range, Excel.Cells].Select; или Excel.Range["A1:C5"].Select; при этом будет выделена область находящаяся между ячейкой A1 и C5.
После выполнения выделения можно установить:
1) объединение ячеек Excel.Selection.MergeCells:=True;
2) перенос по словам Excel.Selection.WrapText:=True;
3) горизонтальное выравнивание Excel.Selection.HorizontalAlignment:=3; при присваивании значения 1 используется выравнивание по умолчанию, при 2 - выравнивание слева, 3 - по центру, 4 - справа.
4)вериткальное выравнивание Excel.Selection.VerticalAlignment:=1; присваиваемые значения аналогичны горизонтальному выравниванию.
5) граница для ячеек Excel.Selection.Borders.LineStyle:=1; При значении 1 границы ячеек рисуются тонкими сплошными линиями.
Кроме этого можно указать значения для свойства Borders, например, равное 3. Тогда установится только верхняя граница для блока выделения: Excel.Selection.Borders.LineStyle:=1;
Значение свойства Borders задает различную комбинацию граней ячеек. В обоих случаях можно использовать значения в диапазоне от 1 до 10.
Использование паролей в EXCEL
Установка пароля для активной книги может быть произведена следующим образом:
try // попытка установить пароль
Excel.ActiveWorkbook.protect("pass");
except // действия при неудачной попытке установить пароль
end;
где pass - устанавливаемый пароль на книгу.
Снятие пароля с книги аналогично, использовуем команду
Excel.ActiveWorkbook.Unprotect("pass");
Установка и снятие пароля для активного листа книги Excel производится командами
Excel.ActiveSheet.protect("pass"); // установка пароля
Excel.ActiveSheet.Unprotect("pass"); // снятие пароля
где pass - пароль, установленный для защиты книги.
Вспомогательные операции в EXCEL
Удаление строк со сдвигом вверх:
Excel.Rows["5:15"].Select;
Excel.Selection.Delete;
при выполнении данных действий будут удалены строки с 5 по 15.
Установка закрепления области на активном листе Excel
// снимаем закрепление области, если оно было задано
Excel.ActiveWindow.FreezePanes:=False;
// выделяем нужную ячейку, в данном случае D3
Excel.Range["D3"].Select;
// устанавливаем закрепление области
Excel.ActiveWindow.FreezePanes:=True;
Удачной работы!
Content-Disposition: form-data; name="sort" 50
В этой статье мы рассмотрим основные конструкции, позволяющие получить доступ к книге MS Excel из Delphi.
Организация доступа к книге EXCEL
Для взаимодействия с MS Excel в программе необходимо использовать модуль ComObj
Uses ComObj;
и объявить переменную для доступа к MS Excel следующего типа:
Var MsExcel: Variant;
Инициализация переменной Excel в простейшем случае можно осуществить так:
MsExcel:= CreateOleObject("Excel.Application");
Создание новой книги:
MsExcel.Workbooks.Add;
Открытие существующей книги (где path - путь к фалу с расширением xls.):
MsExcel.Workbooks.Open;
Открытие существующей книги только для чтения:
MsExcel.Workbooks.Open;
Закрытие Excel:
MsExcel.ActiveWorkbook.Close; MsExcel.Application.Quit;
Блокировка запросов (подтверждений, уведомлений) Ms Excel, например, запретить запрос на сохранение файла:
MsExcel.DisplayAlerts:=False;
Отображаем Excel на экране:
MsExcel.Visible:= True;
или скрываем:
MsExcel.Visible:= False;
Печать содержимого активного листа MS Excel:
MsExcel.ActiveSheet.PrintOut;
Чтение/запись данных в EXCEL
Доступ к ячейке в текущей книге Excel можно осуществить следующим образом:
Для записи значения в ячейку:
MsExcel.Range["B2"]:="Привет!";
Для чтения значения из ячейки:
S:=MsExcel.Range["B2"];
где B2 - адрес ячейки.
Или используя стиль ссылок R1C1:
MsExcel.Range]:="Привет!";
где - координата ячейки.
Вообще, ячейке Excel можно присваивать любое значение (символьное, целое, дробное, дата) при этом Ms Excel установит форматирование в ячейке применяемое по умолчанию.
Формат ячеек в EXCEL
Выделить (выбрать) группу ячеек для последующей работы можно так:
MsExcel.Range, MsExcel.Cells].Select; // или MsExcel.Range["A1:C5"].Select;
при этом будет выделена область находящаяся между ячейкой A1 и C5.
После выполнения выделения можно установить объединение ячеек, перенос по словам, а также горизонтальное и вертикальное выравнивание:
// объединение ячеек MsExcel.Selection.MergeCells:=True; // перенос по словам MsExcel.Selection.WrapText:=True; // горизонтальное выравнивание MsExcel.Selection.HorizontalAlignment:=3; // вертикальное выравнивание MsExcel.Selection.VerticalAlignment:=1;
Для вертикального и горизонтального выравнивания используются следующие значения:
1 - используется выравнивание по умолчанию
2 - выравнивание слева
3 - по центру
4 - справа.
Граница ячеек
При значении 1 границы ячеек рисуются тонкими сплошными линиями.
Кроме этого можно указать значения для свойства Borders, например, равное 3. Тогда установится только верхняя граница для блока выделения:
MsExcel.Selection.Borders.LineStyle:=1;
Значение свойства Borders задает различную комбинацию граней ячеек. В обоих случаях можно использовать значения в диапазоне от 1 до 10.
Использование паролей в EXCEL
Установка пароля для активной книги может быть произведена следующим образом:
Try // попытка установить пароль MsExcel.ActiveWorkbook.protect("pass"); except // действия при неудачной попытке установить пароль end;
где pass - устанавливаемый пароль на книгу.
Снятие пароля с книги аналогично, используем команду
MsExcel.ActiveWorkbook.Unprotect("pass");
где pass
Установка и снятие пароля для активного листа книги Excel производится командами
MsExcel.ActiveSheet.protect("pass"); // установка пароля MsExcel.ActiveSheet.Unprotect("pass"); // снятие пароля
где pass - пароль, установленный для защиты книги.
Вспомогательные операции в EXCEL
Удаление строк со сдвигом вверх:
MsExcel.Rows["5:15"].Select; MsExcel.Selection.;
при выполнении данных действий будут удалены строки с 5 по 15.
Установка закрепления области на активном листе Excel
// снимаем закрепление области, если оно было задано MsExcel.ActiveWindow.FreezePanes:=False; // выделяем нужную ячейку, в данном случае D3 MsExcel.Range["D3"].Select; // устанавливаем закрепление области MsExcel.ActiveWindow.FreezePanes:=True;
Сохранение активной книги Excel