Наверное многие программисты сталкивались с такой проблемой, как создание отчёта. Репортинговых систем сейчас предостаточно, но всё таки, если нужно использовать или включать как дополнительную опцию для вывода отчёта установленный на компьютере заказчика MS Excel. Или наоборот нужно не выводить данные в таблицу, а брать оттуда какие либо данные, автоматизировать обработку данных хранящихся в таблице.
Данная статья поможет Вам разобраться в принципах работы с MS Excel, сэкономить Ваше драгоценное время и полезна для ознакомления не только начинающим, но и опытным программистам. Статья периодически обновляется, добавляются новые нюансы, способы работы с документом и примеры.

 

 

Работа с компонентой ExcelApplication, производится как с любым объектом Delphi. Кидаем его на форму. В инспекторе объектов мы наблюдаем свойства:

ConnectKind - определяет как осуществляется соединение с сервером и может принимать следующие значения:

ckNewInstance - всегда создавать новый экземпляр сервера
ckRunningOrNew - присоединиться к выполняемому серверу или создать новый экземпляр
ckRunningInstance - Только присоединиться к выполняющемуся серверу
ckRemote - Присоединиться к удалённому серверу Сочетается со свойством RemoteMachineName
ckAttachToInterface - не присоединяться к серверу. Вместо этого приложение обеспечивает интерфейс методом ConnectTo

AutoConnect - определяет, должен ли запускаться сервер при запуске приложения
AutoQuit - обеспечивает автоматическое закрытие Excel при завершении приложения
RemoteMachineName - Удалённый компьютер, на котором выполняется сервер и к которому необходимо присоединиться

Запуск и соединение с сервером можно произвести несколькими вариантами. Например одним из вышеописанных свойств:

ExcelApplication1.AutoConnect:=true;

или с помощью имеющегося метода Connect:

ExcelApplication1. Connect;

При чём, можно сразу определить видимым ли будет приложение или нет:

ExcelApplication1.Visible[lcid]:=true;

Эту же операцию можно произвести в любом месте кода, после запуска сервера. Соответственно перед запуском сервера желательно указать и как ему соединяться:

ExcelApplication1.ConnectKind := ckNewInstance;
ExcelApplication1.AutoConnect:=True;
ExcelApplication1.Visible[lcid]:=true;

Но в отличие от WordApplication мы здесь видим, что появился дополнительный параметр – lcid - это идентификатор локализации, в основном равен 0, но целесообразней передавать значения предназначенных для этого констант:
LOCALE_USER_DEFAULT и LOCALE_SYSTEM_DEFAULT - идентификаторы текущего пользователя и системы соответственно. Для работы с книгой и обращения к нужной книге есть свойства ActiveWorkbook – активная книга. Это объект Workbooks, представляющая из себя массив открытых книг (Workbook) и через который также можно обращаться к нужной книге:

...
i:=5;
ExcelWorkbook1:=ExcelApplication1.Workbooks[i];
// активация 5-ой книги
ExcelWorkbook1.Activate(lcid);
...

Для определения количества открытых книг, соответственно, у Workbooks существует свойство только для чтения Count, показывающая общее количество открытых книг. Свойство только для чтения и используется для проверки, есть ли хоть один открытый документ. Создание нового документа осуществляется методом Add

...
ExcelApplication1.Workbooks.Add(EmptyParam,lcid);
...

В первый параметр этого метода можно передать строку, содержащую имя файла книги. Если создаётся новая книга, то она создаётся с числом листов установленных по умолчанию, но эту переменную мы можем поменять, для этого есть свойство SheetsInNewWorkbook:

...
ExcelApplication1.SheetsInNewWorkbook[lcid]:=1;
...

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

...
ExcelApplication1.Worksheets.Add(Before, After, Count, Type_, lcid);
...

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

Подобным же способом лист можно удалить, используя метод Delete:

...
ExcelWorksheet1.Delete(lcid);
...

Как известно у Листа имеется имя, его тоже можно изменить, обратившись к свойству листа Name:

...
ExcelWorksheet1.Name:='Отчёт';
...

Навигацию по листам осуществляется по подобию с Workbook, только через свойство Sheets объекта Workbook (используется тип _Workbook), представляющее из себя массив листов. Для него, точно также существует свойство Count, с помощью которого можно узнать количество листов в книге:

…
ColSheet:=ExcelWorkbook1.Sheets.Count;
…

Копирование листа можно произвести методом Copy :

...
ExcelApplication1.Sheets.Copy(EmptyParam,EmptyParam,0);
...

Приведённый код скопирует лист в новую книгу, если же необходимо произвести вставку листа в туже книгу, с которой работаем, то необходимо указать это, к примеру так:

...
var ran : OleVariant;
...
ran:=ExcelWorkbook1.ActiveSheet;
ExcelApplication1.Sheets.Copy(ran,EmptyParam,0);
...

Компонент позволяет и произвести настройки листа для печати, например Выставить Ориентацию листа будет выглядеть так:

...
// Альбомная ориентация
ExcelWorksheet1.PageSetup.Orientation:=xlLandscape;
...

Как уже ранее было указано, открытие книги можно осуществить с помощью метода Add.

...
lcid:=LOCALE_SYSTEM_DEFAULT;
ExcelApplication1.ConnectKind:=ckNewInstance;
ExcelApplication1.AutoQuit:=true;
// делаем видимым Excel
ExcelApplication1.Visible[lcid]:=true;
ExcelApplication1.AutoConnect:=true;
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,lcid));
ExcelWorksheet1.ConnectTo(ExcelApplication1.ActiveWorkbook.ActiveSheet as ExcelWorkSheet);
...

Кроме того можно использовать и метод Open :

...
ExcelApplication1.ConnectKind:=ckNewInstance;
ExcelApplication1.Visible[lcid]:=true;
ExcelApplication1.AutoConnect:=true;
ExcelApplication1.AutoQuit:=true;
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Open(getcurrentdir+'\Report.xls'));
ExcelWorksheet1.ConnectTo(ExcelApplication1.ActiveWorkBook.ActiveSheet as ExcelWorksheet);
ExcelApplication1.ActiveWorkbook.ActiveSheet;
...

Есть также и более расширенный вариант метода Open :

function Open(FileName,UpdateLinks,ReadOnly,Format, Password,
WriteResPassword,IgnoreOnlyRecommended, Origin, Delimiter,
Editable,Notify,Converter, AddToMru, Local, CorruptLoad, lcid)
Filename – OleVariant(String) - Имя файла
UpdateLinks - OleVariant( Integer) - Режим обновления ссылок в рабочей книге
ReadOnly - OleVariant (Boolean) - открыть только для чтения
Format - OleVariant( Integer) - формат открытия текстовых файлов
Password - OleVariant (String) - Пароль
WriteResPassword - OleVariant (String) - Пароль для сохранения изменений
IgnoreReadOnlyRecomended - OleVariant( Boolean)- отключение сообщения только для чтения
Origin - OleVariant (Ineger) - кодировка для открываемого текстового файла
Delimiter - OleVariant (Integer) - код симбола-разделителя колонок для открываемого текстового файла
Editable - OleVariant( Boolean) - доп. режим при открытии Excel-файлов более ранных версий, чем версия 5.0
Notify - OleVariant (Boolean) - Если была попытка открыть файл в режиме чтение/запись, но в этот момент это было невозможно, то при значении True этого аргумента приложение получит уведомление когда файл станен доступен. Если False или значение опущено, и файл занят, то попытки открыть его для чтения/обречены на неудачу.
Converter - OleVariant (Integer) - индекс конвертора, используемого при открытии файла
AddToMRU - OleVariant(Boolean) - добавить имя файла в список недавно открытых в меню "Файл"

У Excel XP и 2003 есть ещё метод OpenXML :

...
// запускаем Excel
ExcelApplication1.AutoConnect:=true;
// открываем книгу
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.OpenXML(OpenDialog1.FileName,EmptyParam));
// соединяемся с книгой
ExcelApplication1.ConnectTo(ExcelWorkbook1.Application);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.ActiveSheet as ExcelWorksheet);
// делаем видимым Excel
ExcelApplication1.Visible[lcid]:=true;
...

Работа с ячейками происходит через объект Range. При чём этому объекту можно назначить не только одну ячейку но и несколько. Что очень удобно, когда нужно занести множество значений и не в одну ячейку. К тому же это произойдёт быстрее, т.к. соответственно обращений к серверу будет всего одно вместо нескольких. Так же и через этот объект можно форматировать содержащийся в ячейках текст с помощью свойства Font. Итак, начнём с занесения текста в ячейку, через метод Range это будет выглядеть так:

...
ExcelApplication1.Range['C1',EmptyParam].Value2:='Hello!';
...

По аналогии производится и чтение из ячейки. Также получить значение ячейки можно и через свойство Cells:

…
ExcelApplication1.Cells.Item[Row, Col]:='Hello!';
…

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

...
// Горизонтальное выравнивание по центру
ExcelApplication1.Range['A1','D1'].HorizontalAlignment := xlCenter;
// Вертикальное выравнивание по центру
ExcelApplication1.Range['A1','D1'].VerticalAlignment:=xlCenter;
// сетка таблицы
ExcelApplication1.Range['A1','D1'].Borders.LineStyle:=xlContinuous;
// перенос текста в ячейках по словам
ExcelApplication1.Range['A1','D1'].WrapText:=true;
// стиль шрифта в ячейке жирный
ExcelApplication1.Range['A1','D1'].Font.Bold:=true;
// заливка ячеек жёлтым цветом
{ чёрный -1, красный -3, ярко-зеленый -4, синий – 5, зелёный – 10, голубой - 33,
светло-желтый – 36, бледно-зеленый -35, розовый -38,
изумрудный -50, серый (80%) – 56}
ExcelApplication1.Range['A1','D1'].Interior.ColorIndex:=36;
...

Таким же способом можно устанавливать ширину столбцов и высоту строк:

...
// Выставляем ширину столбцов
// ширина выставится для первых пяти столбцов
ExcelApplication1.Range['A1',E1'].Columns.ColumnWidth:=12;
...
// Выставляем высоту строчек
// первые 5 строчек будут шириной 24
ExcelApplication1.Range['A1','A5'].Rows.RowHeight:=24;
...

Что же говоря о сетке таблицы, то здесь есть определённый нюанс работы с ней. В частности, могут возникнуть некоторые проблемы, когда необходимо установить, допустим, только нижнюю границу. В этом случае требуется перебирать все границы и устанавливать им необходимые значения. К примеру, отобразим толстую нижнюю границу ячеек:

...
ExcelApplication1.Range['A1','H11'].Borders.Weight:=xlMedium;
ExcelApplication1.Range['A1','H11'].Borders.Item[xlEdgeBottom].LineStyle:=xlContinuous;
ExcelApplication1.Range['A1','H11'].Borders.Item[xlEdgeLeft].LineStyle:=xlNone;
ExcelApplication1.Range['A1','H11'].Borders.Item[xlEdgeTop].LineStyle:=xlNone;
ExcelApplication1.Range['A1','H11'].Borders.Item[xlEdgeRight].LineStyle:=xlNone;
...

если мы ограничимся только первой и второй строчкой, то все границы могут отобразится толстой линией.
Зачастую, требуется установить или узнать установленный формат отображения в ячейке информации. Это можно сделать с помощью свойства NumberFormat. К примеру установить текстовый формат ячейки и формат времени:

…
// Формат ячейки текстовый
ExcelApplication1.Range['A1',EmptyParam].NumberFormat:='@';
...
// формат ячейки время в формате HH:mm
ExcelApplication1.Range['A5',EmptyParam].NumberFormat:='h:mm;@';
...

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

...
ExcelApplication1.Range['A1','A3'].MergeCells:=True;
...

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

...
ExcelApplication1.DisplayAlerts[0]:=false;
...

Актуальнейшим вопросом является получение количества строк и столбцов, которые необходимо считать. В решении этого вопроса можно использовать несколько методов. Следующий код, показывает самый распространённый способ:

...
ExcelWorksheet1.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
// количество строк и столбцов
Caption:='Строк: '+inttostr(ExcelApplication1.ActiveCell.Row)+' Столбцов: '+ inttostr(ExcelApplication1.ActiveCell.Column);
...

Но у этого способа, есть один небольшой недостаток, в точности, если страница заблокирована паролем от изменений, то данный код выдаст ошибку. Этой проблемы лишён следующий способ:

...
// Количество столбцов
caption:=inttostr(ExcelWorksheet1.UsedRange[0].Columns.Count );
...

Соответственно со строками по аналогии.

Продолжение следует...

 

СУВ, Albinos_X (С)

Задать вопрос или связаться со мной можно по ICQ или E-mail. Их можно узать в Контактах.

ВНИМАНИЕ: Коммерческая публикация данной статьи запрещена без согласования с автором, т.е. со мной. Не коммерческая публикация статьи должна обязательно быть с указанием источника.