Триколор 

Работа с EXCEL из Delphi. Совместная работа StringGrid и Excel

В данном обзоре рассмотрены основные конструкции, позволяющие получить доступ к книге 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.

Организация доступа к книге EXCEL

Для взаимодействия с MS excel в программе необходимо использовать модуль ComObj и объявить переменную для доступа к MS Excel вариантного типа.

uses ComObj;
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

Удаление строк со сдвигом вверх (при выполнении данных действий будут удалены строки с 5 по 15):

Excel.Rows["5:15"].Select;
Excel.Selection.Delete;

Установка закрепления области на активном листе Excel:

// снимаем закрепление области, если оно было задано
Excel.ActiveWindow.FreezePanes:=False;
// выделяем нужную ячейку, в данном случае D3
Excel.Range["D3"].Select;
// устанавливаем закрепление области
Excel.ActiveWindow.FreezePanes:=True;


Мы рассмотрели в разд. 6.4.2 основные операции, связанные с книгами. Теперь перейдем к операциям с листами книги. Коллекция листов содержится в свойстве Worksheets объекта книги. Эта коллекция по своим свойствам подобна рассмотренной ранее коллекции Workbooks. К листу можно обращаться по индексу или по имени. Например, следующие операторы при работе с серверами СОМ открывают и активизируют первый лист книги, представленной объектом Excel Workbook 1, передают указатель на этот лист в переменную Excel Worksheet 1 и активизируют лист, т.е. выдвигают его на первый план в окне Excel:

ExcelWorksheetl:= ExcelWorkbookl.Worksheets as ExcelWorksheet; ExcelWorksheetl.Activate(LOCALE_USER_DEFAULT);

ExcelWorksheetl:= ExcelWorkbookl.Worksheets; ExcelWorksheetl.Activate;

Свойство Worksheets имеется также в объекте сервера. Это свойство относится к активной книге. Так что следующие операторы при работе с серверами СОМ производят операции с активной книгой, открывая и активизируя в ней лист, имя которого (например, "Лист1") задано в окне редактирования Editl:

Worksheets as ExcelWorksheet; ExcelWorksheetl.Activate(LOCALE USER DEFAULT);

При работе с серверами автоматизации OLE аналогичные операторы выглядят так:

ExcelWorksheetl:= ExcelApplicationl.Worksheets; ExcelWorksheetl.Activate;

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

ExcelWorksheetl:= ExcelApplicationl.

Worksheets as ExcelWorksheet; ExcelWorksheetl.Activate(LOCALE_USER_DEFAULT); except

ShowMessage("He удалось открыть лист "" + Editl.Text + """); end;

Добавить новый лист в книгу можно методом Add объекта Worksheets:

Function Add(Before: OleVariant; After: OleVariant; Count: OleVariant; Type_: OleVariant; lcid: Integer): IDispatch;

Параметры Before или After - это объект листа, перед которым или после которого осуществляется вставка. Обычно достаточно задать только один из этих параметров, а другой сделать равным EmptyParam. Если оба параметра равны EmptyParam, то новые листы вставляются перед текущим активным листом. Параметр Count указывает число вставляемых листов. Если этот параметр равен EmptyParam, то вставляется один лист. Параметр Туре__ определяет тип вставки. При значении EmptyParam вставляется новый пустой лист.

Например, следующий код при работе с серверами СОМ вставляет один новый лист перед активным листом активной книги и передает указатель на него в переменную ExcelWorksheetl:

EmptyParam, EmptyParam, EmptyParam, EmptyParam, LOCALE_USER_DEFAULT) as ExcelWorksheet;

А следующий код вставляет два новых листа после третьего листа активной книги:

Var After, Num: OleVariant; After:= ExcelApplicationl.Worksheets; Num:= 2;

ExcelWorksheetl:= ExcelApplicationl.Worksheets.Add(

EmptyParam,After,Num,EmptyParam, LOCALE_USER_DEFAULT) as ExcelWorksheet;

При работе с серверами автоматизации OLE аналогичные операторы выглядят так:

ExcelWorksheetl:= ExcelApplicationl.Worksheets.Add;

ExcelWorksheetl:= ExcelApplicationl.Worksheets.Add(

After:= ExcelApplicationl.Worksheets, Count:= 2);

Имя вставленного или любого имеющегося листа можно изменить с помощью свойства Name. Например:

ExcelWorksheetl.Name:= "Счет-фактура"; Удалить лист из книги можно методом Delete:

ExcelWorksheetl.Delete(LOCALE_USER_DEFAULT);

Напечатать лист можно методом Printout, не отличающимся от аналогичного метода, описанного ранее для книги. Например, печать текущей страницы при работе с серверами СОМ может быть оформлена так:

ExcelWorksheetl:= ExcelApplicationl.ActiveSheet as, ExcelWorksheet; ExcelWorksheetl.Printout(EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, LOCALE_USER_DEFAULT);

При работе с серверами автоматизации OLE получается попроще:

ExcelWorksheetl:= ExcelApplicationl.ActiveSheet; ExcelWorksheetl.Printout;

Предварительный просмотр листа перед печатью можно осуществить методом PrintPreview:

Procedure PrintPreview(EnableChanges: OleVariant; leid: Integer);

Параметр EnableChanges указывает возможность внесения изменений при просмотре. Например, следующий оператор обеспечивает при работе с серверами СОМ предварительный просмотр активного листа книги:

(ExcelApplicationl.ActiveSheet as ExcelWorksheet).

PrintPreview (true, LOCALE_USER_DEFAULT) ;

Для серверов OLE аналогичный оператор имеет вид:

ExcelApplicationl.ActiveSheet;PrintPreview(true);

Здравствуйте, в этой статье я расскажу Вам, как использовать в своем приложении (программе) на Delphi БД в виде MS Excel . Да да именно MS Excel . Тут ничего сложного нету. Для начала давайте заполним наш лист в MS Excel. Первая строка в каждом столбце всегда будет брать наше приложение как название столбцов, то есть оно не берет название столбцов такое — A , B , C и так далее. Так что в первсой строке столбца А и столбца B я написал ФИО и Оценка соответственно для каждого столбца. Это и будут наши заголовки, затем ниже я заполнил данные, которые будут в моей БД, ну это фамилии и оценки сами напиши. Так БД у нас готова. Теперь создадим к ней подключение. создается подключение похоже как и в MS Access , так что тут ничего сложного нету. На форме у нас старые компоненты это

  • TDBGrid
  • TADOQuery
  • TADOConnection
  • TDataSource

Как связывать эти компоненты я расскажу быстро, так как это уже было сказано мною.

  • TADOQuery c TADOConnection в свойстве — Connection
  • TDataSource с TADOQuery в свойстве DataSet
  • TDBGrid c TDataSource в свойстве DataSource

В TADOConnection установим свойство LongPromt в False . Вроде бы небольшую настройку сделали. Теперь приходим к непосредственному подключени. В свойстве TADOConnection - ConnectionString нажимаем на кнопку «… «, далее у нас появляется окно вида

Нажимаем на кнопку «Build… » и появляется следующее окно

В данном окне выбираем следующего провайдера: Microsoft OLE DB Provider for ODBC Drivers и нажимаем кнопку «Далее>> » и видем следующее окно

В этом окне сразу ставим указатель на «Использовать строку подключения » и нажимаем на кнопку «Сборка «, после чего появляется окно

В этом окне переходим на вкладку Источник данных компьютера, в появившемся списке выбираем — Файлы Excel , а точнее жмем левой кнопкой мыши по этой сроке двойным щелчком и в появившемся окне указываем путь к нашей Excel -книги, которую мы создавали. После этого нажимаем на пноку «Ok «. Все подключение у нас готово. Хочу добавить еще, если у Вас файл Excel , где ваша таблица находится лежит в одном каталоге с программой, то в свойстве компонента TADOConnection - ConnectionString будет прописан путь к Вашему Excel-файлу, стерите путь, а оставьте просто имя Вашего Excel файла с расширением, а в свойстве DefaultDataBase этого же компонента, напишите имя вашего Excel -файла с расширением. Тогда при запуске вашего приложения, не будет возникать ошибок с неправильно заданым путем к вашей БД. Далее в TDBGrid нажмем по нему двойным щелчком и в появившемся окне создаим 2 строки, это наши столбцы будут. А создаются они путем нжатия в данном окне на кнопку «Add New (Ins) «. Далее выделив кажду строку в свойстве FieldName для первой строки напишем — ФИО , так как в Excel -файле я именно так написал в первую строку название столбца (для ячейки A1 я так написал), а выделив вторую строку, что создали в TDBGrid , в свойстве FieldName напишем — Оценка , так как именно я такое название столбца написал (в ячейке А2 я так написал). Все теперь нам можно активировать наши данные. Для этого на событие главной формы — OnCreate напишем следующее

Как видите все тот же запрос, только вместо имя таблица, как у нас было в MS Access мы указываем имя листа нашего в Excel , заключив его в квадратные скобки и поставив в конце знак $ . Как видите ничего сложного нету. В следующей статье про БД MS Excel я расскажу как вставлять данные, редактировать и так далее.

Хоче заметить, что если Вы является настоящим фанатом футбола, а тем более английского чемпионата, который на данный момент является одним из сильнейшим, то Вам наверняка будет очень интересно почитать про футбольный клуб

Очень многие документы создаются и хранятся в формате электронных таблиц 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;