Сегодня мы рассмотрим: Настоящие ценители музыки знают, что для качественного...
Очень многие документы создаются и хранятся в формате электронных таблиц Microsoft Excel. Несмотря на то, что эти таблицы обладают возможностями для автоматической обработки документа, нам, дельфистам, гораздо приятнее работать в привычной среде, что которая и обладает к тому же гораздо более развитыми возможностями. Давайте посмотрим, как получать данные из Excel. Естественно, табличные данные будем размещать в привычную нам таблицу StringGrid.
Для работы с Excel и другими программами из пакета Microsoft Office необходимо добавить в список uses модуль ComObj:
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, Grids, ComObj
;
var
Form1: TForm1;
Excel: Variant;
Далее, нужно создать объект Excel. Excell Application создаётся пустым, без таблиц, поэтому необходимо добавить хотя бы одну книгу. Делать это нужно в каком-либо обработчике, например обработчике нажатия кнопки, хотя можно и сразу в OnCreate Формы:
Excel:=CreateOleObject("Excel.Application");
Excel.Application.WorkBooks.Add("Имя_Файла.xls");
Если создаётся пустая книга, метод Add применяется без параметра - без имени файла. Естественно, можно предложить пользователю выбрать файл:
with
OpenDialog1 do
if
Execute then
Excel.Application.WorkBooks.Add(FileName);
Для отладки необходимо, чтобы таблица Excel была видимой, а также лучше запретить задавать вопросы о сохранении данных при закрытии:
Excel.Visible:=True; //После отладки можно закомментировать эту строку
Excel.DisplayAlerts:=False;
Сразу создайте метод закрытия объекта Excel, иначе при отладке, да и при работе пользователя в компьютере наплодится столько невидимых процессов Excel, что мама дорогая!.. В обработчике OnCloseQuery Формы напишите:
try
Excel.Quit;
except
end;
CanClose:=True;
Естественно, будет произведён выход из Excel, и затем закроется всё приложение. Но если нам нужно после закрытия процесса Excel продолжить работу с программой, то этот код помещается в обработчик нажатия кнопки. Однако, в данном случае его недостаточно. Попробуйте, и вы убедитесь, взглянув в список процессов в Диспетчере Задач, что наш процесс Excel жив и здоров! Это произошло потому, что он остаётся связанным с переменной, его создавшей (Excel же). Для реального уничтожения процесса нужно разорвать эту связь. Дополните вышеприведённый код строкой:
Excel:=Unassigned;
И при нажатии кнопки закрытия наш Excel исчезнет из списка процессов.
Теперь нужно получить данные из Excel. В Excel столбцы именуются буквами, но мы в Delphi обращаемся к ним привычно, по порядковым номерам. Обратите внимание, что, поскольку в Delphi первым в индексе идёт индекс столбца, а в таблице Excel индекс строки, то индексы должны быть расположены на противоположных местах. В обработчике нажатия кнопки:
with
StringGrid1 do
for
i:=1 to
RowCount-1 do
for
j:=1 to
ColCount-1 do
Cells:=Excel.WorkSheets.Item["Лист1"].Cells;
Маленькое предупреждение: если при отладке проверять внесение данных, то перед нажатием нашей кнопки нужно завершить ввод в Excel - нажать Enter. Ведь если ячейка таблицы Excel останется в режиме редактирования, то мы получим отказ от Excel.
И ещё. Данные в Excel адресуются начиная с 1. Попытка получить содержимое фиксированных ячеек не удаётся. Поэтому фиксированные ячейки в таблице StringGrid при необходимости нужно заполнять самому, отдельно.
А получить содержимое одной ячейки можно как указав номер строки и столбца, так и непосредственно указав адрес ячейки:
var
S1, S2: String
;
begin
S1:=Excel.WorkSheets.Item["Лист1"].Cells;
S2:=Excel.WorkSheets.Item["Лист1"].Range["F5"];
end;
В переменных S1 и S2 будет одинаковое значение.
Теперь в таблице StringGrid мы имеем данные для обработки, и делаем с ними что хотим. Затем можно перенести обработанные данные назад в таблицу Excel. Делается это совершенно аналогично, в обработчике нажатия другой кнопки:
for
i:=1 to
Grid.RowCount-1 do
for
j:=1 to
Grid.ColCount-1 do
Excel.WorkSheets.Item["Лист1"].Cells:=Grid.Cells;
Если эти операции производятся с активным листом Excel, то можно сократить написание, и вместо:
Excel.WorkSheets.Item["Лист1"].Cells
Excel.Cells
Или можно создать переменную и присвоить ей значение того листа Excel, с которым производится работа:
var
Sheet: Variant;
S1, S2: String;
begin
Sheet:=Excel.WorkSheets.Item["Лист1"];
S1:=Sheet.Cells;
S2:=Sheet.Range["F5"];
end;
Только имейте в виду, что таблица может содержать не только данные непосредственно в ячейках, но и формулы. При записи данных из нашей таблицы StringGrid всё, кроме непосредственно записываемого текста, будет уничтожено!
Напоследок нужно заставить таблицу Excel сохранить обработанные данные:
Excel.ActiveWorkbook.SaveAs("Имя_Файла");//Или SaveAs("OpenDialog1.FileName");
Можно вывести отчёт на печеть. Вот как задана функция печати:
function
PrintOut(
From: Variant;//Необязательно. Номер срааницы с которой начинается печать.
To: Variant;//Необязательно. Номер страницы по какую продолжается печать.
Copies: Variant;//Необязательно. Количество копий.
Preview: Variant;//Необязательно. Предварительный просмотр (True
или False
).
ActivePrinter: Variant;//Необязательно. Имя активного принтера.
PrintToFile: Variant; True
печать будет идти в файл.
Collate: Variant//Необязательно. При значении True
копии страниц объединяются.
): Workbook;
Воспользоваться этой функцией можно как методом переменной, указывающей страницу - Sheet (также Excel.ActiveWorkBook или Excel.WorkSheets ):
Sheet.PrintOut(1, 1, 1, False, True);
Будет произведён вывод на печать с первой страницы по первую, одной копии, без предварительного просмотра, без указания принтера - печать идёт в файл. Предварительно будет выдан запрос на указание имени файла. Создаётся файл типа *.xps . Для его просмотра нужны специальные программы.
Естественно, в Delphi можно осуществлять также и форматирование ячеек, и другие операции с таблицей Excel. Эту информацию добавлю чуть позже. А пока на первый раз достаточно.
Работа с регионом ячеек Excel
Продолжить хочу с того факта, что операции чтения и записи данных по одной ячейке занимают довольно много времени - вы уже и сами наверное заметили. Есть способ ускорить этот процесс. Для этого нужно освоить несложные операции работы с регионом ячеек Excel.Регион ячеек таблицы Excel также имеет тип Variant и задаётся прямоугольником, с указанием левой верхней и правой нижней ячеек:
Var Range: Variant;
begin
end;
В частности, регион может состоять и из одной ячейки:
Range:=Excel.Range, Excel.Cells];
Эту запись проще выполнить с указанием адреса как в таблице Excel:
Range:=Excel.Range["A1"];
Также можно задать и прямоугольный регион, если вам известны имена ячеек. Вот регион 4х4:
Range:=Excel.Range["A1:D4"];
А вот как выполнить перепись региона 100Х100 ячеек Excel в таблицу StringGrid:
var
Range: Variant;
i, j: Integer;
begin
Range:=Excel.Range, Excel.Cells];
with
StringGrid1 do
for
i:=1 to
100 do
for
j:=1 to
100 do
Cells:=Range.Cells;
end;
Вот и всё! На моём компьютере, эта операция переписи региона 100х100 ячеек Excel в таблицу StringGrid длится около 300 мсек, что на 2 порядка быстрее, чем чтение и запись по одной ячейке.
А, например, операция занесения какого-либо одного значения во все ячейки региона выполняется ещё проще. Занесём в наш вышеопределённый регион 100х100 слово "Привет" :
Excel.Range, Excel.Cells]:="Привет";
Очистка региона выполняется методом Clear:
Excel.Range, Excel.Cells].Clear;
В данном обзоре рассмотрены основные конструкции, позволяющие получить доступ к книге 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) граница для ячеек
При значении 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;
Удачной работы!
Работа с EXCEL из Delphi
В этой статье я познакомлю вас как работать с MS Excel из Delphi . Данная статья может быть полезна людям, которые хотят научиться создавать различные отчеты в Excel из программ написанных на Delphi . Статья содержит справочные данные необходимые для работы с MS Excel . Здесь вы найдете информацию о том как:
Подключить и правильно отключить интерфейс Excel;
- Как изменить размер, цвет и тип шрифта;
- Как выделить, объединить, заполнить и размножить диапазон ячеек;
- Как повернуть и отцентрировать текст;
- Как нарисовать границы ячеек;
- Как ввести формулу в ячейку и многое другое...
И так, начнем...
Работать будем через модуль ComObj, для этого в uses необходимо добавить модуль ComObj и модуль Excel_TLB (для MS Excel 2007 ).
Uses ……, ComObj, Excel_TLB;
Модуль Excel_TLB
содержит необходимые константы
для работы с Excel
, его можно не подключать, но тогда придется в ручную прописывать значения всех используемых констант из этого модуля. Значения констант можно найти внутри модуля или в интернете, но для разных версий MS Excel
они разные.
Внимание!!!
Модуль Excel_TLB
в других версиях MS Excel
может называться по другому. Перед подключением модуля Excel_TLB,
необходимо импортировать библиотеку Excel
. Для этого выберите Component->Import Component->Import a Type Library->
находим MS Excel
и следуем инструкциям.
В разделе описания переменных мы должны описать переменную типа Variant или OleVariant для подключения интерфейса Excel. Я описал переменную excel .
Form1: TForm1;
excel
: variant;
// Переменная в которой создаётся объект EXCEL
Создание документа
Внимание!!!
Всегда когда создаете объект интерфейса, заключайте процедуру создания в модуль обработки ошибок:
try
создаем интерфейс;
формируем отчет;
освобождаем интерфейс;
Except
обрабатываем ошибки;
освобождаем интерфейс;
end;
try
// создаем обьект EXCEL
// Чтоб не задавал вопрос о сохранении документа
excel.DisplayAlerts:= false;
// создаем новый документ
рабочую книгу
excel.WorkBooks.Add;
// или загружаем его из директории с программой
excel.WorkBooks.Open(GetCurrentDir() + "\отчет.xls");
{ GetCurrentDir()- возвращает путь к директории с программой}
// Делаем его видимым
данную функцию после отладки и тестирования лучше использовать в конце, после сформирования отчета (это ускоряет процесс вывода данных в отчет)
excel.Visible:= true;
//задаем тип формул в формате R1C1
excel.Application.ReferenceStyle:= xlR1C1;
// задаем тип формул в формате A1
excel.Application.ReferenceStyle:= xlA1;
// Задаем название первому и второму листу
excel.WorkBooks.WorkSheets.Name:= "Отчет1";
excel.WorkBooks.WorkSheets.Name:= "Отчет2";
//задаем формат числа для первой и четвертой колонки формат числа
excel.WorkBooks.WorkSheets.Columns.NumberFormat:= "0,00";
excel.WorkBooks.WorkSheets.Columns.NumberFormat:= "0,0";
// задаем ширину первой и второй колонки
excel.WorkBooks.WorkSheets.Columns.ColumnWidth:= 10;
excel.WorkBooks.WorkSheets.Columns.ColumnWidth:= 20;
// задаем начертание, цвет, размер и тип шрифта для первого ряда
excel.WorkBooks.WorkSheets.Rows.Font.Bold:= True; //жирный
excel.WorkBooks.WorkSheets.Rows.Font.Color:= clRed; // цвет красный
excel.WorkBooks.WorkSheets.Rows.Font.Size:= 12; //размер 12
excel.WorkBooks.WorkSheets.Rows.Font.Name:= "Times New Roman"; //шрифт
//присваиваем ячейке 1,4 и 2,4 значения (1 - ряд, 4 - колонка)
excel.WorkBooks.WorkSheets.Cells := "А так можно внести значение в ячейку";
excel.WorkBooks.WorkSheets.Cells := "А так можно внести значение в ячейку";
//ввод в ячейку "A12" формулы "=b5+c4"
excel.WorkBooks.WorkSheets.Range["A12"].Formula:="=b5+c4";
// Выравнивам первый ряд по центру по вертикали
excel.WorkBooks.WorkSheets.Rows.VerticalAlignment:= xlCenter;
// Выравнивам первый ряд по центру по горизонтали
excel.WorkBooks.WorkSheets.Rows.HorizontalAlignment:= xlCenter;
// Выравнивам в ячейке по левому краю
excel.WorkBooks.WorkSheets.Cells.HorizontalAlignment:= xlLeft;
// Выравнивам в ячейке по правому краю
excel.WorkBooks.WorkSheets.Cells.HorizontalAlignment:= xlRight;
// Обьединяем ячейки "A1:A8"
excel.WorkBooks.WorkSheets.Range["A1:A8"].Merge;
// Поворачиваем слова под углом 90 градусов для второго ряда
excel.WorkBooks.WorkSheets.Rows.Orientation:= 90;
// Поворачиваем слова под углом 45 градусов для диапазона ячеек "B3:D3"
excel.WorkBooks.WorkSheets.Range["B3:D3"].Orientation:= 45;
//рисуем границы выделенного диапазона левая
excel.Selection.Borders.LineStyle:= xlContinuous; // стиль линии сплошная
excel.Selection.Borders.Weight:= xlMedium;// толщина линии
//рисуем границы выделенного диапазона верхняя
excel.Selection.Borders.Weight:= xlMedium;
//рисуем границы выделенного диапазона нижняя
excel.Selection.Borders.Weight:= xlMedium;
//рисуем границы выделенного диапазона правая
excel.Selection.Borders.Weight:= xlMedium;
//рисуем границы выделенного диапазона вертикальные внутрениие
excel.Selection.Borders.Weight:= xlMedium;
//рисуем границы выделенного диапазона горизонтальные внутрениие
excel.Selection.Borders.Weight:= xlMedium;
//автозаполнение выделенного диапазона
//для примера заполним область ячеек "A10:C10" словом "привет"
//и размножим его вниз еще на пять ячеек "A10:C15"
excel.WorkBooks.WorkSheets.Range["A10:C10"].Value:="привет";
//выделяем диапазон ячеек "A10:C10"
excel.WorkBooks.WorkSheets.Range["A10:C10"].Select;
//автозаполняем (копируем) выделенным диапазоном область ячеек "A10:C15"
excel.selection.autofill(excel.WorkBooks.WorkSheets.Range["A10:C15"],xlFillDefault);
//отключаем предупреждения, чтобы не задавал вопросов о сохранении и других
excel.DisplayAlerts:= False;
//сохраняем документ в формате Excel 97-2003
excel.ActiveWorkBook.Saveas(GetCurrentDir() + "\отчет.xls",xlExcel8);
//сохраняем документ в текущем формате Excel 2007
excel.ActiveWorkBook.Saveas(GetCurrentDir() + "\отчет.xlsx");
//закроем все книги
excel.Workbooks.Close;
//закрываем Excel
excel.Application.quit;
//освобождаем интерфейсы
excel:= Unassigned;
//обрабатываем ошибки
//закроем все книги
excel.Workbooks.Close;
//закрываем Excel
excel.Application.quit;
//освобождаем интерфейсы
excel:= Unassigned;
end;
end;
При работе с листом Excel
мы можем использовать следующие варианты:
- работать с областью Range["B3:D3"];
- работать с ячейкой Cells где 2 - ряд, 4 - колонка;
- работать с рядами Rows или с диапазоном рядов Rows["1:5"];
- работать с колонками Columns или диапазоном колонок Columns["A:I"];
Range["A1"] и Cells обозначают одно и тоже.
После сформирования документа или возникновении ошибки вы должны правильно освободить интерфейсы.
Иначе при закрытии Excel
он скрывается с экрана, но если открыть диспетчер задач он продолжает там висеть и если данный процесс не завершить, то при каждом новом запуске их будет накапливаться больше и больше, пока компьютер не начнет виснуть. Поэтому обязательно необходимо освобождать все интерфейсы с вязанные с Excel
с его книгами и листами.
Например так:
//закроем все книги
excel.Workbooks.Close;
//закрываем Excel
excel.Application.quit;
//освобождаем интерфейсы
sheet:=Unassigned; //интерфейс листа если он был создан
WorkBook:= Unassigned;//интерфейс рабочей книги если он был создан
excel:= Unassigned;//интерфейс самого предложения если он был создан
Ниже привожу пример вывода в Excel из Delphi таблицы умножения с подробными комментариями.
Unit Unit1;
Uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, comobj, StdCtrls, Excel_TLB;
Type
TForm1 = class(TForm)
Button1: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
Var
Form1: TForm1;
excel: variant; // Переменная в которой создаётся объект EXCEL
MyData: variant; // Переменная в которой формируется таблица умножения
i,j:integer;
implementation
Procedure TForm1.Button1Click(Sender: TObject);
begin
try
// Обьект EXCEL
excel:= CreateOleObject("Excel.Application");
// Чтоб не задавал вопрос о сохранении документа
excel.DisplayAlerts:= false;
// новый документ
excel.WorkBooks.Add;
//объявляем вариантный массив
MyData:= VarArrayCreate(,varVariant);
for I:= 1 to 9 do
for J:= 1 to 9 do
MyData:=i*j;
// Обьединяем ячейки "A1:I1"
excel.WorkBooks.WorkSheets.Range["A1:I1"].Merge;
//Пишем заголовок
excel.WorkBooks.WorkSheets.Range["A1"].Value:="Таблица умножения";
// Выравнивам заголовок по центру
excel.WorkBooks.WorkSheets.Range["A1"].HorizontalAlignment:= xlCenter;
// задаем ширину колонок с A по I
excel.WorkBooks.WorkSheets.Columns["A:I"].ColumnWidth:= 3;
//выделяем область таблицы умножения ["A2:I10"] и рисуем границы
excel.WorkBooks.WorkSheets.Range["A2:I10"].select;
//рисуем границы выделенного диапазона левая
excel.Selection.Borders.LineStyle:= xlContinuous; // стиль линии сплошная
excel.Selection.Borders.Weight:= xlThin;// толщина линии
//рисуем границы выделенного диапазона верхняя
excel.Selection.Borders.LineStyle:= xlContinuous;
excel.Selection.Borders.Weight:= xlThin;
//рисуем границы выделенного диапазона нижняя
excel.Selection.Borders.LineStyle:= xlContinuous;
excel.Selection.Borders.Weight:= xlThin;
//рисуем границы выделенного диапазона правая
excel.Selection.Borders.LineStyle:= xlContinuous;
excel.Selection.Borders.Weight:= xlThin;
//рисуем границы выделенного диапазона вертикальные внутрениие
excel.Selection.Borders.LineStyle:= xlContinuous;
excel.Selection.Borders.Weight:= xlThin;
//рисуем границы выделенного диапазона горизонтальные внутрениие
excel.Selection.Borders.LineStyle:= xlContinuous;
excel.Selection.Borders.Weight:= xlThin;
//присваиваем диапазону ["A2:I10"] значения вариантного массива MyData это значительно ускоряет работу, нежели вывод по //ячеечно
Excel.WorkBooks.WorkSheets.Range["A2:I10"].Value:=MyData;
// Делаем его видимым
excel.Visible:= true;
//освобождаем интерфейсы
MyData:= Unassigned;
excel:= Unassigned;
Except
showmessage("Внимание! Произошла ошибка при создании MS Excel приложения");
//закрываем Excel
excel.Application.quit;
//освобождаем интерфейсы
MyData:= Unassigned;
excel:= Unassigned;
end;
end;
При написании статьи использовался материал с сайта
Обмен данными с 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;
В этой статье мы рассмотрим основные конструкции, позволяющие получить доступ к книге 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