В прошлые раз мы с Вами остановились на сохранении рабочей книги. В принципе, ничего сложного в запуске Excel, создании и сохранении рабочей книги нет. Сегодня заглянем немного глубже в работу с Microsoft Excel в Delphi — рассмотрим как можно работать с листами рабочей книги, изменять внешний вид ячеек и попробуем скопировать таблицу из своего приложения в открытую рабочую книгу.
План статьи:
Однако прежде, чем забираться в дебри Excel я хотел продемонстрировать Вам насколько глубоко мы сегодня заглянем.
1. Объекты Microsoft Excel
На рисунке я попробовал изобразить объектную модель Excel в миниатюре. Т.е. из этой модели исключены не требующиеся пока нам элементы, например такие, как свойства рабочей книги или выделенные объекты Selection — об этом речь пойдет позже.
Как Вы можете судить по представленной модели, а также и по собственному опыту работы, приложение Excel в общем случае может содержать коллекцию рабочих книг (WorkBooks Collection). Для доступа к конкретной рабочей книге мы с Вами использовали свойства Item коллекции:
WorkBook:=WorkBooks.Item[index]
где index — порядковый номер рабочей книги в коллекции WorkBooks.
И на этом наше первое знакомство с Excel закончилось. Между тем, каждая отдельная рабочая книга может содержать N листов, каждый из которых состоит из N ячеек, которые могут быть объединены в объекты Range.
На каждой из ступеней этой модели можно останавливаться и изучать её месяцами. Если будет особая необходимость в изучении каких-либо дополнительных свойств и методов — мы обязательно вернемся и изучим. А теперь, приступим к работе в Delphi с листами рабочей книги.
2. Как активировать лист рабочей книги Excel?
Как Вы знаете при создании пустой рабочей книги Excel автоматически добавляет в эту книгу 3 пустых листа. Чтобы работать с конкретным листом (WorkSheet или просто Sheet) этот лист необходимо активировать. Один из способов активации листа рабочей книги Excel в Delphi выглядит следующим образом:
resourcestring rsEInvalidSheetIndex = 'Задан неверный индекс для WorkBooks. Активация листа прервана'; rsEInvalidSheetActivate = 'Активация листа завершена с ошибкой'; function ActivateSheet(WBIndex: integer; SheetName: string): boolean; var i: integer; begin Result := false; if WBIndex > MyExcel.WorkBooks.Count then raise Exception.Create(rsEInvalidSheetIndex); try for i := 1 to MyExcel.WorkBooks[WBIndex].Sheets.Count do if AnsiLowerCase(MyExcel.WorkBooks[WBIndex].Sheets.Item[i].Name) = AnsiLowerCase(SheetName) then begin MyExcel.WorkBooks[WBIndex].Sheets.Item[i].Activate; Result := true; break; end; except raise Exception.Create(rsEInvalidSheetActivate); end; end;
Здесь в качестве параметров функции задается индекс рабочей книги (WBIndex) в коллекции WorkBooks и название листа.
Если хотите, то можете активировать тот же лист по индексу в коллекции WorkSheets — при этом сама функция немного упрощается (не требуется условие проверки названия листа).
Также, если вы уверены, что в данный момент времени активна необходимая Вам рабочая книга, то можно избежать использования лишней переменной (индекса рабочей книги WBIndex) и активировать лист вот так:
MyExcel.ActiveWorkBook.Sheets.Item[i].Activate;
Теперь рассмотрим более подробно методы, используемые объектом WorkSheet.
3. Методы листа книги Excel
Теперь рассмотрим более подробно объект WorkSheet.
На рисунке представлены те методы, которые я когда-либо использовал в своей работе.
Всего объект WorkSheet насчитывает 30 различных методов, которые Вы можете использовать.
Метод | Описание |
Activate | Делает текущий лист активным. Работу метода мы уже с Вами рассмотрели |
Calculate | рассчитывает все открытые книги, конкретный лист в книге, или указанный диапазон ячеек на листе |
CheckSpelling | проверка орфографии на выбранном листе |
SaveAs | сохраняет все изменения в файле |
Delete | удаляет текущий лист WorkSheet |
Select | выделение листа |
Copy | копирует лист в другое место в рабочей книге |
Paste | вставляет содержимое буфера обмена на лист |
ChartObjects | возвращает объект, который представляет собой либо одну диаграмму (объект ChartObject) или совокупность всех диаграмм (объект ChartObjects) на листе |
Move | перемещение листа. Этот метод аналогичен методу Copy. Различие лишь в том, что после вcтавки копируемый лист удаляется из книги |
Рассмотрим теперь каждый из представленных выше методов.
Метод Calculate
Расчёт рабочей книги, листа или диапазона ячеек.
Стоит отметить, что вызов метода Calculate возможен не только для конкретного рабочего листа книги Excel. Рассмотрим варианты вызова метода Calculate.
Как рассчитать все открытые в данный момент книги Excel в Delphi?
var MyExcel: OleVariant; MyExcel.Calculate
Как рассчитать отдельный лист книги Excel в Delphi?
в приведенном ниже фрагменте кода рассчитывается активный лист
MyExcel.ActiveWorkBook.ActiveWorkSheet.Calculate
Как рассчитать диапазон ячеек листа Excel в Delphi?
Рассчитаем все формулы в столбцах A, B и C:
MyExcel.ActiveWorkBook.ActiveWorkSheet.UsedRange.Columns("A:C").Calculate
Метод CheckSpelling
Вызов метода выглядит следующим образом:
expression.CheckSpelling(CustomDictionary, IgnoreUppercase, AlwaysSuggest, SpellLang)
При этом в качестве expression должен выступать лист (WorkSheet).
Параметр | Тип | Описание |
CustomDictionary | Variant | строка, указывающая имя файла пользовательского словаря, который будет рассмотрен, если слово не найдено в основном словаре. Если этот аргумент опущен, то будет использоваться словарь по-умолчанию |
IgnoreUppercase | Variant | True, если необходимо, чтобы Microsoft Excel пропускал слова, которые написаны прописными буквами. False для того, чтобы Microsoft Excel проверить все слова. Если этот аргумент опущен, то используются текущие настройки Excel. |
AlwaysSuggest | Variant | True, для того, чтобы Microsoft Excel отображал список предложений других вариантов написания при обнаружении неправильного написанного слова. False, чтобы Microsoft Excel ожидал ввода правильного написание. Если этот аргумент опущен, то используются текущие настройки. |
SpellLang | Variant | язык словаря. Может принимать значения одной из констант MsoLanguageID, например для русского языка SpellLang = 1049, для английского (США) SpellLang = 1033 и т.д. Более подробно про идентификаторы языков см. эту статью. |
Как проверить грамматику на листе Excel в Delphi?
MyExcel.ActiveWorkBook.ActiveWorkSheet.CheckSpelling(CustomDictionary:=EmptyParam, IgnoreUppercase:= false, AlwaysSuggest:=EmptyParam, SpellLang:=1033)
в этом случае MS Excel проверит текущий лист, включая проверку слов из прописных букв. Язык проверки — русский. Обратите внимание, что для того, чтобы вызвать метод мы явно указывали, какому параметру какое значение присвоить. Причём для того, чтобы пропустить какой-либо параметр, мы использовали EmptyParam (пустой параметр), который в Delphi представляет собой переменную типа OleVariant.
Понятно, что подобный способ вызова методов (с явным указанием всех параметров) для Вас не совсем удобен и широко не практикуется в программировании на Delphi, но тем не менее только так и никак иначе можно вызывать методы, используемые в Excel.
Метод SaveAs
Сохранение рабочего листа книги Excel.
Вызов метода:
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru,TextCodepage, TextVisualLayout, Local)
Параметр | Тип | Описание |
FileName | Variant | строка, представляющая собой имя сохраняемого файла. Необходимо указывать полный путь к файлу, иначе файл будет сохранен в папку Excel. |
FileFormat | Variant | формат файла, который используется при сохранении. По умолчанию файл сохраняется в формате, который вы выбирали в последний раз в Excel. |
Password | Variant | пароль для файла (не более 15 символов). Пароль чувствителен к регистру. |
WriteResPassword | Variant | пароль, который будет использоваться для внесения изменений в рабочую книгу. Если пароль не будет указан, то книга будет открыта в режиме «Только для чтения» |
ReadOnlyRecommended | Variant | если параметр равен True, то при открытии файла на дисплее появится сообщение, в котором рекомендуется, что бы файл был открыт только для чтения. |
CreateBackup | Variant | True, для того, чтобы создать резервный файл |
AddToMru | Variant | True, для того, чтобы добавить имя сохраняемого файла в список недавно открытых файлов в главном меню Excel |
TextCodepage и TextVisualLayout | Variant | в настоящее время не используются и сохранены для обеспечения обратной совместимости |
Local | Variant | не обязательный параметр, по-умолчанию равен False. Значение True — сохранение файлов с языковыми Microsoft Excel (в том числе настройки панели управления). |
Как сохранить активный лист рабочей книги Excel в Delphi?
MyExcel.ActiveWorkBook.ActiveWorkSheet.SaveAs('C\:MyExcelFile.xls')
В этом случае при сохранении будут использованы настройки по умолчанию.
Если Вам необходимо сохранить файл Excel в другом формате, то в параметре FileFormat можно использовать одно из значений перечислителя xlFileFormat. Некоторые значения xlFileFormat представлены в таблице ниже:
Имя | Значение | Описание | Расширение файла |
xlCSV | 6 | CSV | *.csv |
xlExcel8 | 56 | Книга Excel 97-2003 | *.xls |
xlHtml | 44 | Формат HTML | *.htm; *.HTML |
xlOpenDocumentSpreadsheet | 60 | Электронная таблица OpenDocument | *.ods |
xlOpenXMLWorkbook | 51 | Книга Open XML | *.xlsx |
Как сохранить активный лист рабочей книги Excel в заданном формате?
Напишем небольшую процедуру, позволяющую сохранять лист Excel в различных форматах:
const xlCSV = 6; xlExcel8 = 56; xlHtml =44; xlOpenDocumentSpreadsheet = 60; xlOpenXMLWorkbook = 51; resourcestring rsESaveActiveSheet = 'Ошибка сохранения активного листа книги'; procedure SaveAs(const AFileName: TFileName; AFileFormat: integer); begin try MyExcel.ActiveWorkBook.ActiveSheet.SaveAs(AFileName, AFileFormat); except raise Exception.Create(rsESaveActiveSheet); end; end;
Метод Delete
Удаление рабочего листа Excel.
Этот метод используется без параметров. Например, представленный ниже код удаляет активный лист рабочей книги:
MyExcel.ActiveWorkBook.ActiveWorkSheet.Delete
Метод Select
Выделение рабочего листа книги Excel.
Вызов метода:
expression.Select(Replace)
Параметр | Тип | Описание |
Replace | Variant | True, чтобы заменить текущее выделение указанного объекта. False — продлить нынешний отбор включая все ранее выбранные объекты. По умолчанию используется True |
Как выделить листа рабочей книги Excel в Delphi?
MyExcel.ActiveWorkBook.WorkSheets.Item[3].Select
Выделяет третий лист книги
Метод Copy
Копирование листа в другое место в рабочей книге.
Вызов метода:
expression.Copy(Before, After)
Параметр | Тип | Описание |
Before | Variant | лист до которого будет вставлен копируемый лист. |
After | Variant | лист после которого будет вставлен копируемый лист |
При этом, если вы используете параметр Before, то вы не должны использовать After и наоборот. Обращу Ваше внимание, что указывается именно ЛИСТ, а не индекс листа.
Как копировать лист Excel в Delphi?
MyExcel.ActiveWorkBook.WorkSheets.Item[1].Copy(After:=MyExcel.ActiveWorkBook.WorkSheets.Item[2])
В этом случае первый лист будет скопирован и вставлен после третьего.
Метод Paste
Вставка содержимого буфера обмена на лист Excel.
Вызов метода:
expression.Paste(Destination, Link)
Параметр | Тип | Описание |
Destination | Variant | диапазон ячеек листа, который определяет, где будет вставлено содержимое буфера. Если этот аргумент опущен, используется текущий диапазон. Этот аргумент может быть указан только, если содержимое буфера обмена можно вставить в заданном диапазоне. Если этот параметр задан, то аргумент Link может не указываться |
Link | Variant | True, для того чтобы установить связь с источником вставляемых данных. Если этот параметр задан, Destination не может быть использован. Значение по умолчанию False |
Как вставить содержимое буфера обмена на лист Excel в Delphi?
MyExcel.ActiveWorkBook.ActiveSheet.Paste(Destination:=MyExcel.ActiveWorkBook.ActiveSheet.Range("D1:D5"))
В этом случае содержимое буфера будет вставлено в столбец D в строки с 1 по 5. Следует отметить, что если содержимое буфера не удовлетворяет условию, например в буфере только 1 число, то вызывается исключительная ситуация.
Метод ChartObjects
Возвращает объект, который представляет собой либо одну диаграмму (объект ChartObject) или совокупность всех диаграмм (объект ChartObjects) на листе.
Вызов метода:
expression.ChartObjects[Index]
Параметр | Тип | Описание |
index | Variant | индекс диаграммы в коллекции (нумерация начинается с 1) |
Как получить диаграмму на листе Excel в Delphi?
MyExcel.ActiveWorkBook.ActiveSheet.ChartObjects[1]
В этом случае будет получена первая диаграмма на листе.
Метод Move
Перемещение листа. Этот метод аналогичен методу Copy. Различие лишь в том, что после вcтавки копируемый лист удаляется из книги.
Вот та часть методов, которые я использовал когда-либо при работе с Excel в Delphi. Вообще можно сказать, что в Delphi можно управлять MS Excel не хуже, чем при непосредственной работе с этим приложением, главное не забывать, какой метод или свойство за что отвечает :). Ну, а для того, чтобы не забыть, я создал небольшой MindMap, который буду периодически дополнять и обновлять. Перейдя по ссылке, Вы сможете посмотреть объекты Excel, методы ими используемые, параметры методов и их типы в соответствии с типами Delphi. В общем небольшая графическая шпаргалка для любителей поразбираться с Excel в Delphi.
Книжная полка
Описание Описаны общие подходы к программированию приложений MS Office. Даны программные методы реализации функций MS Excel, MS Word, MS Access и MS Outlook в среде Delphi.
|
Я недавно работал с excel, работает очень тормозно. Проще было бы создать таблицу в xml, а потом импортировать или экспортировать. Правда формат у .xml, который создает microsoft office особенный, пока его не понял. Но зато быстро и просто :-)
Орион, незнаю, я обычно окно Execl пользователю не показываю пока программа пашет — не сказал бы, чтобы уж такие дикие тормоза были, особенно если в отдельный поток работу с Excel засунуть. Пока никто не жалуется
Всё супер, только еслиб в начало и конец каждой статьи добавить ссылку на предыдущую и следующую статью — было замечательно!!!!
Подскажите, как можно отловить изменения данных в excel?
Через обработку его (Excel) событий. Как это можно легко сделать в Delphi — понятия не имею. В С# делается буквально в пару строк кода.
Слишком поверхностно и методы не очень полезные описаны. Надеюсь в следующей статье найду то что мне нужно :D
dzlib,ну незнаю найдётся ли =) Про Excel уже давненько ничего не писАл…да и чего там писать-то осталось? MSDN на русском есть=)
Точно Действительно, все время забываю про кладец знаний от MS
[…] Забегая немного вперед, скажу, что есть несколько способов добавления диаграммы в рабочую книгу Excel. Чтобы все статьи по вопросам автоматизации Excel в блоге были как-то логически связаны, я решил сегодня рассмотреть способ добавления диаграммы через объект ChartObjects, с которым мы встречались, когда разбирали методы объекта WorkSheet. […]
[…] основы работы Delphi Excel […]
Не ошибка ли это в условии?
WBIndex>MyExcel.WorkBooks.Count (С)
скопировала из функции активации листа рабочей книги
можете не отвечать, я уже поняла. это знак больше
А не мог бы мне помочь, как мне данные с одного листа скопировать в другую книгу*