Лекция № 30
Тема: «Создание отчетов в Microsoft Excel»
План
1. Операции с COM-сервером Microsoft Excel
2. Работа с книгами в Microsoft Excel
3. Работа с листами книги в Microsoft Excel
4. Запись данных и формуд в ячейки листа Microsoft Excel
5. Форматирование ячеек
1. Операции с COM-сервером Microsoft Excel
Функции, реализующие механизм доступа к COM-объектам, находится в модуле Comobj, который должен быть подключен в коде формы.
Затем нужно описать переменную (лучше глобальную) для хранения ссылки на сервер в виде:
var e:variant;
После описания переменной можно создать новый объект COM-сервера с помощью команды:
e:=CreateOleObject('Excel.Application');
После выполнения этого оператора приложение Excel запустится, но его окно не появится на экране монитора. В диспетчере задач в списке процессов можно увидеть, что процесс excel.exe запущен и находится в памяти компьютера.
Чтобы окно программы стало видимым, необходимо установить свойство:
e.Visible:=true;
При формировании отчетов свойство Visible лучше включать после полного завершения формирования листа книги. Так можно сократить время создания отчетов и повысить производительность работы приложений.
Запуск или подключения к Excel
Мы рассмотрели пример создания нового объекта для запуска копии Microsoft Excel. Однако такой подход имеет недостаток: если Excel уже будет запущен пользователем, то вызов функции CreateOleObject( 'Excel.Application') запустит новую копию программы, что приведет к нерациональному расходу памяти компьютера. Для решения данной проблемы нужно иметь в виду, что имеется функция, которая не создает объект, а подключается к уже существующему (запущенному):
e:=GetActiveOleObject('Excel.Application');
Поэтому перед использования COM-сервера Excel целесообразно выполнять создание объекта или подключение к объекту в зависимости от состояния программы Excel. Правильный подход при обращении к Excel следующий:
try
// пытаемся подключиться к Excel
e:=GetActiveOleObject('Excel.Application');
except
// если подключение не удалось (Excel не запущен)
// создаем новый объект, запуская Excel
e:=CreateOleObject('Excel.Application');
end;
2. Работа с книгами в Microsoft Excel
Итак, объект Excel загружен в память компьютера, и у нас есть доступ к нему через переменную e:variant. Объект имеет методы, с помощью которых можно создавать, открывать и сохранять книги.
Создание книги
Для создания книги используют метод:
e.WorkBooks.Add;
После выполнения этого метода будет создана книга на основании стандартного шаблона.
Вызвать метода Add можно с аргументом (именем файла шаблона). В этом случае создается документ по шаблону. Для своих приложений можно создать дополнительные шаблоны книг, например шаблон формы платежного поручения или налоговой декларации. В дальнейшем, используя метод Add с указанием на шаблон, легко создать нужный отчет, программно заполняемый информацией. Использование шаблонов позволит создавать гибкие и удобные для пользователя приложения в среде Delphi.
Пример. Пусть в папке с программой имеется папка шаблонов с именем Templates, а в ней файл шаблона с именем Anketa.xlt. Создадим новую книгу на основе шаблона.
e.Workbooks.Add(ExtractFilePath(Application.ExeName) + 'Templates\Anketa.xlt');
Сохранение книги
Можно программно сохранить созданную книгу на диск. Для этого используется метод вида:
e.ActiveWorkBook.SaveAs(FileName:='имя файла' [,Password:='пароль']);
Метод имеет аргументы:
Например: сохраним книгу в папку с программой в подпапку Books под именем Report.xls:
e.ActiveWorkBook.SaveAs(FileName:=ExtractFilePath(Application.ExeName)+'Books\Report.xls');
Если нужно сохранить книгу и защитить ее паролем, то код будет иметь вид:
e.ActiveWorkBook.SaveAs(FileName:=ExtractFilePath(Application.ExeName)+'Books\Report.xls', Password='111');
Открытие книги
Для открытия файла книги с диска имеется метод:
e.WordBooks.Open(FileName:='имя файла' [,Password:='пароль']);
Метод имеет аргументы:
Например: откроем сохраненный ранее файл:
e.WorkBooks.Open(FileName:=ExtractFilePath(Application.ExeName) + 'Books\Report.xls');
Если файл имеет пароль, то его можно открыть так:
e.WorkBooks.Open(FileName:=ExtractFilePath(Application.ExeName) + 'Books\Report.xls', Password:='111');
Закрытие книги и приложения Excel
Если книга сохранена, то ее можно закрыть с помощью метода:
e.ActiveWorkBook.Close;
После закрытия книги можно закрыть и само приложение Excel. Для этого используют два оператора:
e.Quit;
e:=UnAssigned;
Используя методы работы с книгами можно организовывать фоновое создание файлов без отображения их на экране.
Пример. На основании шаблона Anketa.xlt из папки Templates создать новую книгу. Сохранить книгу с именем Report.xls в папку Books. После сохранения выдать сообщение об успешном создании файла и предложить пользователю открыть файл для просмотра. Если ответ положительный, то файл открывается, если отрицательный, то Excel закрывается.
try
// подключаемся к Excel
e:=GetActiveOleObject('Excel.Application');
except
// если подключение не удалось (Excel не запущен)
// создаем новый объект, запуская excel
e:=CreateOleObject('Excel.Application');
end;
// создаем новую книгу на основе шаблона
e.WorkBooks.Add(ExtractFilePath(Application.ExeName) + 'Templates\Anketa.xlt');
// сохраняем книгу в указанный файл
e.ActiveWorkBook.SaveAs(ExtractFilePath(Application.ExeName) + 'Books\Report.xls');
// выдаем запрос на открытие сохраненного файла
if Application.MessageBox('Файл сохранен. Открыть? ', 'Запрос', mb_yesno + mb_iconquestion) = idyes then
// если ответ да, делаем Excel видимым
e.Visible:=true
else
// если ответ нет, закрываем документ и Excel
begin
e.ActiveWorkBook.Close;
e.Quit;
e:=UnAssigned;
end;
3. Работа с листами книги в Microsoft Excel
Каждая книга имеет несколько листов (по умолчанию 3). Для обращения к листу используют запись вида:
e.ActiveWorkBook.Sheets.Item[i]
где i - номер листа в книге (нумерация начинается с 1).
Каждый лист имеет команды для управления ими:
// добавляет лист слева от i-го листа
e.ActiveWorkBook.Sheets.Item[i].Add;
// удаляет i-й лист
e.ActiveWorkBook.Sheets.Item[i].Delete;
// копирует i-й лист, вставляя его после j-го листа
e.ActiveWorkBook.Sheets.Item[i].Copy(After:=e.ActiveWorkBook.Sheets.Item[j]);
// задает имя i-го листа
e.ActiveWorkBook.Sheets.Item[i].Name:='новое имя';
Замечание. При удалении листа Excel может выдавать сообщения на подтверждении операции. Для выключения выдачи окон с сообщениями необходимо вначало кода добавлять команду вида:
e.DisplayAlerts:=false;
Для включения функции выдачи сообщений используют команду:
e.DisplayAlerts:=true;
Пример. Пусть есть некоторый шаблон Primer.xlt, состоящий из одного лист. Необходимо написать код, который добавляет еще девять копий этого листа и каждый лист нумерует числами от 1 до 10.
try
// подключаемся в Excel
e:=GetActiveOleObject('Excel.Application');
except
// если подключение не удалось (Excel не запущен)
// создаем новый объект, запуская Excel
e:=CreateOleObject('Excel.Application');
end;
// создаем новую книгу на основе шаблона
e.WorkBooks.Add(ExtractFilePath(Application.ExeName) + 'Templates\Primer.xlt');
// запускаем цикл, добавляем 9 листов
for i: = 1 to 9 do
begin
// копируем первый лист и вставляем его после i-го листа
e.ActiveWorkBook.Sheets.Item[1].Copy(After:=e.ActiveWorkBook.Sheets.Item[i]);
// i-му листу присваиваем имя, которое является значением переменной i
e.ActiveWorkBook.Sheets.Item[i].Name:=IntToStr(i);
end;
// делаем окно Excel видимым для просмотра результата
e.Visible:=true;
4. Запись данных и формул в ячейки листа Microsoft Excel
Шаблоны является самым удобным способом формирования книг Excel. Разработчик может создать коллекцию шаблонов, в которых задать расположение текстовых блоков и их форматирования. Так как лист Excel – это таблица, то можно, записывая данные в определенные ячейеки такой таблицы, формировать отчеты в программе. Для обращения к ячейке таблицы используют запись:
e.ActiveWorkBook.Sheets.Item[n].Cells[i,j].Value:= 'значение';
где n – номер листра в книге (нумерация с 1);
i – номер строки на листе (нумерация с 1);
j – номер колонки (нумерация с 1).
Например, пусть в шаблоне Anketa.xlt из папки Tempates на первом листе в ячейках второй колонки друг под другом необходимо подставить значение Иванов, Иван, Иванович.
try
// подключаемся в Excel
e:=GetActiveOleObject('Excel.Application');
except
// если подключение не удалось (Excel не запущено)
// создаем новый объект, запуская Excel
e:=CreateOleObject('Excel.Application');
end;
// создаем новую книгу на основе шаблона
e.WorkBooks.Add(ExtractFilePath(Application.ExeName) + 'Templates\Anketa.xlt');
// записываем в 1 строку 2 столбца текст «Иванов»
e.ActiveWorkBook.Sheets.Item[1].Cell[1,2].Value:='Иванов';
// записываем во 2 строку 2 столбца текст «Иван»
e.ActiveWorkBook.Sheets.Item[1].Cell[2,2].Value:='Иванов';
// записываем в 3 строку 2 столбца текст «Иванович»
e.ActiveWorkBook.Sheets.Item[1].Cell[3,2].Value:='Иванович';
// делаем книгу видимой
e.Visible:=true;
Вместо переменных в шаблоне можно подставлять не только конкретные значения, но и содержимое полей таблицы БД. Такой подход позволяет создавать отчеты по БД с экспортом их в Excel.
Пример. Пусть в шаблоне Anketa.xlt из папки Tempates на первом листе в ячейках второй колонки друг под другом необходимо подставить значение полей fam, imya, otch из таблицы БД.
Для этого напишем код:
try
// подключаемся в Excel
e:=GetActiveOleObject('Excel.Application');
except
// если подключение не удалось (Excel не запущено)
// создаем новый объект, запуская Excel
e:=CreateOleObject('Excel.Application');
end;
// создаем новую книгу на основе шаблона
e.WorkBooks.Add(ExtractFilePath(Application.ExeName) + 'Templates\Anketa.xlt');
// записываем в 1 строку 2 столбца поле fam
e.ActiveWorkBook.Sheets.Item[1].Cell[1,2].Value:= ADOTable1.FieldByName('fam').Value;
// записываем во 2 строку 2 столбца поле imya
e.ActiveWorkBook.Sheets.Item[1].Cell[2,2].Value:= ADOTable1.FieldByName('imya').Value;
// записываем в 3 строку 2 столбца поле otch
e.ActiveWorkBook.Sheets.Item[1].Cell[3,2].Value:= ADOTable1.FieldByName('otch').Value;
// делаем книгу видимой
e.Visible:=true;
Пример. Пусть с помощью компонента ADOQuery1 на форме отобраны нужные записи (выполнена фильтрация). Необходимо для каждой найденной записи сформировать свой отдельный лист на основании единого листа шаблона Anketa.xlt.
try
// подключаемся в Excel
e:=GetActiveOleObject('Excel.Application');
except
// если подключение не удалось (Excel не запущено)
// создаем новый объект, запуская Excel
e:=CreateOleObject('Excel.Application');
end;
// создаем новую книгу на основе шаблона
e.WorkBooks.Add(ExtractFilePath(Application.ExeName) + 'Templates\Anketa.xlt');
// копируем первый лист шаблона столько раз,
// сколько отобранных записей в запросе
for i:=1 to ADOQuery1.RecordCount-1 do
e.ActiveWorkBook.Sheets.Item[1].Copy(After:=w.ActiveWorkBook.Sheets.Item[i]);
// становимся на первую запись запроса
ADOQuery1.First;
// сначала счетчик сформированных листов равен 0
i:=0;
// запускаем цикл до конца набора записей в запросе
while not ADOQuery1.EOF do
begin
// увеличиваем счетчик сформированных листов
i:=i+1;
//выводим данные на i листе
// записываем в 1 строку 2 столбца поле fam
e.ActiveWorkBook.Sheets.Item[i].Cell[1,2].Value:= ADOTable1.FieldByName('fam').Value;
// записываем во 2 строку 2 столбца поле imya
e.ActiveWorkBook.Sheets.Item[i].Cell[2,2].Value:= ADOTable1.FieldByName('imya').Value;
// записываем в 3 строку 2 столбца поле otch
e.ActiveWorkBook.Sheets.Item[i].Cell[3,2].Value:= ADOTable1.FieldByName('otch').Value;
// переходим, на следующую запись в запросе
ADOQuery1.Next;
end;
// делаем книгу видимой
e.Visible:=true;
Формирование таблицы
Работа с шаблонами, рассмотренная выше, позволяет создавать отчет только для отдельной записи таблицы. Гораздо чаще отчет создается для нескольких записей одновременно. При этом данные представляются на одном листе в виде таблицы-списка. Для формирования списка на одном листе необходимо запускать цикл по всем записям набора данных и менять номер строки ячейки при выводе данных.
Пример. Пусть имеется шаблон List.xlt, который находится в папке Templates. Шаблон имеет шапку таблицы с полями № п/п, Фамилия, Имя, Дата рождения. Шапка находится в первой строке листа.
На основании данных таблицы БД сформировать список из записей.
var i:integer; //переменная для номера строки
...
try
// подключаемся в Excel
e:=GetActiveOleObject('Excel.Application');
except
// если подключение не удалось (Excel не запущено)
// создаем новый объект, запуская Excel
e:=CreateOleObject('Excel.Application');
end;
// создаем новую книгу на основе шаблона
e.WorkBooks.Add(ExtractFilePath(Application.ExeName) + 'Templates\Anketa.xlt');
// i - номер строки, в которой находится шапка таблицы
i:=1;
// организуем цикл по таблице
while not ADOTable1.EOF do
begin
// увеличиваем число строк в таблице
i:=i+1;
// записываем в i строку 1 столбца значение № п/п
e.ActiveWorkBook.Sheets.Item[1].Cells[i,1].Value:=i-1;
// записываем в i строку 2 столбца поле fam
e.ActiveWorkBook.Sheets.Item[i].Cell[i,2].Value:= ADOTable1.FieldByName('fam').Value;
// записываем во i строку 3 столбца поле imya
e.ActiveWorkBook.Sheets.Item[i].Cell[i,3].Value:= ADOTable1.FieldByName('imya').Value;
// записываем в i строку 4 столбца поле datar
e.ActiveWorkBook.Sheets.Item[i].Cell[i,4].Value:= ADOTable1.FieldByName('datar').Value;
// переходим на следующую запись в таблице
ADOTable1.Next;
end;
// делаем книгу видимой
e.Visible:=true;
Работа с формулами
Одним из основных преимуществ Excel является использование формул. Если в ячейке написать текст формулы, то она автоматически будет рассчитана самой программой Microsoft Excel.
Для ввода формулы используют свойство вида:
e.ActiveWorkBook.Sheets.Item[1].Cells[i,j].Formula:='=текст формулы';
При вводе формул нужно учитывать , что каждая функция Excel записывается в ее англоязычном варианте.
К примеру. В ячейке A3 посчитать сумму ячеек A1 и A2.
e.ActiveWorkBook.Sheets.Item[1].Cells[3,1].Formula:='=A1+A2';
Пусть в ячейках A1: A6 находятся некоторые числовые значения. Необходимо посчитать сумму значений в этих ячейках и отразить результат в ячейке А7.
e.ActiveWorkBook.Sheets.Item[1].Cells[7,1].Formula:='=sum(A1:A6)';
5. Форматирование ячеек
Для форматирования ячеек используют следующие свойства:
// задает цвет фона в ячейках диапазона
e.ActiveWorkBook.shEets.Item[i].Range['диапазон'].Interior.ColorIndex:=цвет;
// задает толщину границы. Может принимать значения 1, 2, 4, -4138
e.ActiveWorkBook.Sheets.Item[i].Range['диапазон'].Borders.Weight:=число;
// задает тип линии границы. Может принимать значения 1, 4, 5, 13, -4115, -4118, -4119, -4142
e.ActiveWorkBook.Sheets.Item[i].Range['диапазон'].Border.LineStyle:=тип,
// задает цвет линии границы
e.ActiveWorkBook.Sheets.Item[i].Range['диапазон'].Border.Color:=цвет;
Вопросы для самоконтроля
1. Опишите принципы запуска Excel из проектов Delphi
2. Какие основные операции над книгами Excel можно реализовать в Delphi?
3. Опишите принцип работы с листами Microsoft Excel в Delphi. Пример.
4. Как записывать данные и формулы в ячейки книги Excel в программах на Delphi?
5. Какие свойства используются для форматирования ячеек на листе Excel при создании программ в Delphi?