Сегодняшняя статья блога будет целиком посвящена работе с Excel Range или, говоря другими словами — работе с диапазонами ячеек Excel.
Про работу с этими объектами я уже вкратце говорил, а сегодня хотел бы поделиться с вами более полной информацией. Итак, начнем с самого простого.
Содержание
План статьи:
1. Что такое Range и как его получить?
Согласно официальному определению Microsoft, Range :
представляет собой ячейки, строки, столбцы, набор ячеек, содержащих один или более смежных блоков ячеек, или 3-D диапазон.
Однако это определение не исключает того, что объектом Range может выступать и одна ячейка (Cell) листа. Таким образом, чтобы получить в свое распоряжение объект Range, можно выполнить следующие операции c объектом Excel в Delphi:
Если Вам неудобно в какой-либо ситуации использовать буквенные обозначение ячеек или Вы привыкли до этого момента иметь дело только с отдельными ячейками (Cells), то объект Range можно получить например вот так:
Какой из способов Вы будете использовать в Delphi — не важно, так как результат будет один и тот же. Получив в свое распоряжение диапазон Вы можете использовать его далее, например, для того, чтобы установить вид его границ.
2. Свойства объекта Excel Range.
Рассмотрим основные свойства объекта Range и их применение работе в Excel в Delphi.
Вначале перечислю свойства, которые будут далее рассмотрены в статье.
| Свойство | Описание |
| Formula | Возвращает или помещает в диапазон формулу |
| Value | Возвращает или устанавливает значение для диапазона |
| Text | возвращает текст из ячейки |
| Column | Возвращает номер первого столбца в первой области, в указанном диапазоне |
| Columns | возвращает объект Range, представляющий собой один столбец из всего диапазона |
| Comment | Возвращает объект Comment для Range. В данном случае Range должен определять одну ячейку. |
| Address | Возвращает реальный адрес диапазона Range |
Formula
Возвращает или помещает в диапазон формулу.
Value
Возвращает или устанавливает значение для диапазона.Свойство Value замечательно тем, что с помощью него можно записать в ячейки абсолютно любые данные, особо не задумываясь о формате данных. Например, запишем в ячейки диапазона строку, число типа integer и число типа single: чтобы каждый раз не повторяться в листингах и не писать одни и те же элементы по 100 раз, будем считать, что в переменной Sheet уже содержится ссылка на активный лист (ActiveWorkSheet) активной книги (ActiveWorkBook) Excel (MyExcel)
Как видите, обращение к ячейкам было одно и то же и нигде я не приводил данный к какому-то типу — записал в ячейки всё как есть.
Если Вы хотите записать в весь диапазон Range одно и то же значение, то просто выполните:
и получите одну и ту же строку «Hello World!» в десяти ячейках Excel, но такие операции очень редко необходимы при работе с Excel в Delphi. Зато очень часто необходимо воспользоваться другой стороной свойства Value — прочитать большой объем данных из книги Excel за один прием и получить весь массив данных в Delphi. Операция чтения данных из Excel в Delphi более проста, чем Вам может показаться на первый взгляд. Проведем обратную операцию — прочитаем данные из Excel:
Как видите здесь мы за один прием прочитали данные сразу из трех ячеек Excel и отобразили их в сообщении. Этот прием чтения на порядок более скоростной, чем, например чтение содержимого каждой ячейки (Cells) в отдельности.
Ещё одно простенькое свойство объекта Range — возвращает текст из ячейки. Самое главное отличие от свойства Value — Text возвращает string только для чтения и использовать это свойство для чтения большого объема данных, как в предыдущем примере — ни в коем случае нельзя, так как переменная Val вернет значение Null.
Column
Возвращает номер первого столбца в первой области, в указанном диапазоне. Свойство только для чтения.
Чтобы продемонстрировать свойство в действии, давайте создадим такие диапазоны как показано на рисунке:
То есть каждый из диапазонов Range будет содержать по две несвязанные друг с другом области (Area). Причем первая область диапазона Range будет начинаться в столбце А, а первая область второго диапазона (Range 2) — в столбце B.
После того, как диапазоны будут созданы — посмотрим, что вернет нам свойство Column для каждого из диапазонов.
Листинг процедуры создания двух несвязных диапазонов Range следующий:
Так, в случае с первым Range Column вернет нам значение 1, а для второго Range — значение 2.
Columns
В отличие от предыдущего свойства, Columns возвращает не простое число, а объект Range, представляющий собой один столбец из всего диапазона.
Посмотрим как, например, используя это свойство можно изменять столбцы во всем диапазоне Range.
Для демонстрации воспользуемся предыдущим примером, изменим только окончание:
В итоге в каждый из столбцов диапазона должна записаться строка с номером этого столбца, результат представлен на рисунке. Как видите, в цикле все столбцы обработались «насквозь», хотя Range не содержал в себе столбец D.
Comment
Возвращает объект Comment для Range. В данном случае Range должен определять одну ячейку.
Для демонстрации свойства не будем заходить в Excel, поработаем с приложением прямо из Delphi. Для этого воспользуемся методом AddComment. То есть сначала запишем комментарий в ячейку, а потом прочитаем его используя свойство Comment:
Address
Возвращает реальный адрес диапазона Range. Например для диапазона ячеек, заданных вот таким образом:
Это свойство удобно использовать, когда Вы оперируете в программе только числовыми значениями (номерами столбцов и строк) и появляется необходимость показать пользователю адреса ячеек из диапазона.
Помимо перечисленных выше свойств к объекту Range применимы все свойства, описанный в статье об изменении внешнего вида ячеек Excel, т.е. borders, color и пр.
3. Методы объекта Excel Range.
Теперь рассмотрим несколько полезных методов, которые могут Вам пригодиться при работе с Excel в Delphi.
| Метод | Описание |
| CheckSpelling | Проверяет грамматику в выбранном диапазоне и при нахождении ошибок выводит окно для замены |
| PrintPreview | Выводит на экран окно предварительного просмотра перед печатью выбранного диапазона ячеек |
| AutoFill | Автозаполнение диапазона ячеек на основе данных из другого диапазона |
| AutoFit | Изменяет ширину или высоту ячеек диапазона для наилучшего представления данных. |
| Clear | Удаляет все данные из диапазона |
| ClearComments | Удаляет все комментарии в диапазоне Range |
| ClearContents | Удаляет все формулы из диапазона Range |
| ClearFormats | Очищает форматы в диапазоне Range |
| ClearNotes | Очищает все заметки в диапазоне Range |
| Copy | Копирует содержимое диапазона Range в буфер обмена или в другой диапазон |
| PasteSpecial | Специальная вставка диапазона |
| Cut | Вырезает данные и при необходимости вставляет их в новый диапазон |
| Merge | Объединение ячеек диапазона |
CheckSpelling
Проверяет грамматику в выбранном диапазоне и при нахождении ошибок выводит окно для замены. Замечательной особенностью этого метода является то, что окно замены появляется даже при скрытом окне Excel, то есть, когда свойство Visible у объекта Excel равно false.
Вызывается метод без каких-либо дополнительных параметров:
PrintPreview
Выводит на экран окно предварительного просмотра перед печатью выбранного диапазона ячеек. Также как и предыдущий метод PrintPreview не имеет дополнительных параметров. Однако при попытке вызвать метод при скрытом окне Excel в лучшем случае возникнет исключительная ситуация, в худшем — зависание Вашего приложения. Так что перед вызовом:
не забывайте включить свойство Visible у Excel:
AutoFill
Автоматическое заполнение диапазона ячеек на основе данных из другого диапазона.
| Параметр | Тип | Описание |
| Destination | Variant | представляет собой объект Range уже заполненных ячеек. Эти ячейки должны входить в автозаполняемый диапазон |
| Type | Integer | тип автозаполнения (возможные значения см. ниже) |
Рассмотрим применение метода на примере.
В результате лист Excel примет следующий вид:
Как видите все столбцы второго диапазона Range заполнились значениями из диапазоны Source.
При использовании этого метода следует иметь в виду, что диапазон-источник (в нашем случае — это source) по одному из измерений должен совпадать с источником назначения. В приведенном примере совпадает размерность по строкам (в обоих диапазонах их шесть). Если размерности диапазонов не совпадают, то возникает исключительная ситуация.
При использовании метода мы использовали одну из констант в параметре Type — xlFillDefault = 0. Используя её мы скопировали данные один-к-одному. Дополнительно Вы можете использовать следующие константы при автозаполнении:
| Имя | Значение | Описание |
| xlFillDefault | 0 | скопировать данные один-к-одному |
| xlFillDays | 5 | копирование дней недели с расширением, т.е., если Вы запишете в ячейку слово «Понедельник» и попробуете провести автозаполнение ещё на 2 строки, то во второй и третьей строке появятся «Вторник» и «Среда» |
| xlFillCopy | 1 | копирует все данные и форматы, повторяя при необходимости |
| xlFillFormats | 3 | копирует только форматы источника |
| xlFillMonths | 7 | копирует названия месяцев. Работает аналогично xlFillDays |
| xlFillSeries | 2 | копирует данные с расширением, например 1,2,3 будут при копировании расширены до 4,5,6 и т.д. Также копирует форматы данных |
| xlFillValues | 4 | копирует только значения |
| xlFillWeekdays | 6 | копирует дни рабочей недели, работает аналогично xlFillDays, но только до пятницы |
| xlFillYears | 8 | копирует года. Работает аналогично xlFillDays |
| xlGrowthTrend | 10 | копирует числовые значения из источника, расширяя их в предположении, что каждое последующее число представляет собой предыдущее, но умноженное на некоторую величину. Например 1,2 раскопируются в 4, 8, 16 и т.д. Формат данных также копируется |
| xlLinearTrend | 9 | копирует числовые значения из источника, расширяя их в предположении, что каждое последующее число представляет собой предыдущее + некоторая величина. Например 1,2 раскопируются в 3, 4, 5 и т.д. Формат данных также копируется |
AutoFit
Изменяет ширину или высоту ячеек диапазона для наилучшего представления данных.
Приведет и изменению ширины столбца А таким образом, чтобы число было полностью видно на листе.
Методы очистки данных диапазона Range
Есть несколько различных методов очистки содержимого диапазона Range при работе с Excel в Delphi.
1. Clear
Удаляет все данные из диапазона.
2. ClearComments
Удаляет все комментарии в диапазоне Range.
3. ClearContents
Удаляет все формулы из диапазона Range
4. ClearFormats
Очищает форматы в диапазоне Range
5. ClearNotes
Очищает все заметки в диапазоне Range
Методы работы с буфером обмена Excel
1. Copy
Копирует содержимое диапазона Range в буфер обмена или в другой диапазон.
Попробуем реализовать работу метода следующим образом: заполним столбец А некоторыми значениями, а затем скопируем его сначала в буфер, а потом в столбец Е:
А для того, чтобы вставить данные из буфера обмена существует ещё один метод
2. PasteSpecial
| Параметр | Тип | Описание |
| Paste | Integer | определяет какая часть данных диапазона будет вставлена (возможные значения см. ниже) |
| Operation | Integer | операция, которая будет выполнена при вставке данных (возможные значения см. ниже) |
| SkipBlanks | boolean | True, для того чтобы пустые ячейки из буфера обмена не вставлялись в диапазон назначения. Значение по умолчанию False |
| Transpose | boolean | транспонирование столбцов и строк после вставки. По умолчанию устанавливается значение False |
При определении параметра Paste следует использовать следующие константы:
| Значение | Описание | |
| xlPasteAll | -4104 | Вставка всех данных |
| xlPasteAllExceptBorders | 7 | Вставка всего содержимого за исключением вида границ диапазона |
| xlPasteAllUsingSourceTheme | 13 | Вставка всего содержимого, используя тему оформления источника |
| xlPasteColumnWidths | 8 | Копирует ширину столбцов |
| xlPasteComments | -4144 | Вставка комментариев |
| xlPasteFormats | -4122 | Вставка форматов данных |
| xlPasteFormulas | -4123 | Вставка формул |
| xlPasteFormulasAndNumberFormats | 11 | Вставка формул и чисел |
| xlPasteValidation | 6 | Вставка проверок |
| xlPasteValues | -4163 | Вставка значений |
| xlPasteValuesAndNumberFormats | 12 | Вставка значений и чисел |
При использовании параметра Operation следует использовать следующие константы:
| Значение | Описание | |
| xlPasteSpecialOperationAdd | 2 | К скопированным данным будут добавлены значения из целевых ячеек |
| xlPasteSpecialOperationDivide | 5 | Скопированные данные будут разделены на значения в целевых ячейках |
| xlPasteSpecialOperationMultiply | 4 | Скопированные данные будут умножены на значения в целевых ячейках |
| xlPasteSpecialOperationNone | -4142 | При вставке значений никакие операции не будут применяться |
| xlPasteSpecialOperationSubtract | 3 | Из скопированных данных будут вычитаться значения целевых ячеек |
Теперь рассмотрим применение метода на примере. В качестве исходных данных возьмем данные из предыдущего примера, но для вставки используем метод PasteSpecial:
После выполнения этой операции все данные вставятся в диапазон Range2, т.к. диапазон до вставки был пуст, то данные скопировались один-к-одному.
3. Cut
Вырезает данные и при необходимости вставляет их в новый диапазон. Метод работает аналогично методу Copy, но с последующим удалением данных из источника.
Merge
Объединение ячеек диапазона.
Для новичков, только начинающих постигать азы работы с excel в Delphi этот метод в какой-то момент становится камнем преткновения :). Дело в том, что очень часто возникает необходимость объединить часть ячеек листа для записи в объединенную область большого количества данных или длинной строки. В Excel есть такой метод Union, который и отвечает за объединение — им-то и пробуют пользоваться многие. А метод Union при вызове из Delphi применительно к диапазону Range вызывает исключительную ситуацию. Про Merge люди либо не знают, либо забывают. А использовать его достаточно просто:
В этом курсе:
Данные из нескольких ячеек можно объединить в одну с помощью символа "амперсанд" (&) или функции СЦЕП.
Объединение данных с помощью символа "амперсанд" (&)
Выделите ячейку, в которую вы хотите вставить объединенные данные.
Введите = (знак равенства) и выберите первую ячейку, которую нужно объединить.
Введите символ & и пробел, заключенный в кавычки.
Выберите следующую ячейку, которую нужно объединить, и нажмите клавишу ВВОД. Пример формулы: =A2&" "&B2.
Объединение данных с помощью функции СЦЕП
Выделите ячейку, в которую вы хотите вставить объединенные данные.
Введите выражение = СЦЕП(.
Выберите первую ячейку, которую нужно объединить.
Для разделения объединяемых ячеек используйте запятые, а для добавления пробелов, запятых и другого текста — кавычки.
Введите закрывающую скобку в конце формулы и нажмите клавишу ВВОД. Пример формулы: =CONCAT(A2, " Семья").
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Программирование. Теория и практика.
Обмен данными с 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 у пользователя:
Если Excel установлен, тогда выполним подключение к нему. Сделать это можно двумя способами: GetActiveOleObject — Получить ссылку на уже запущенный экземпляр Excel или CreateOleObject — Создать новый экземпляр Excel.
Если у нас стоит задача получать данные из запущенного Excel, тогда мы должны использовать только первый вариант, в остальных случаях пробуем подключиться и если не получается, то создаем.
Напишем 2 функции, для подключения XlsConnect и запуска нового XlsStart:
Добавим переменную FXlsApp с типом Variant, которая будет содержать в себе ссылку на объект Excel.
Теперь можно добавить кнопку, на клик которой подключимся к MS Excel используя написанные функции:
По умолчанию окно Excel запускается в фоновом режиме. Строка FXlsApp.Visible := True; делает фоновое окно Excel видимым.
Окно Excel запускается пустое и в него нужно добавить рабочую книгу. Делается это при помощи метода WorkBooks.Add, который добавляет новую книгу или открывает ранее сохраненную, если указать путь к файлу.
Добавим процедуру, которая будет это делать:
Книга добавлена, теперь попробуем записать что-нибудь в неё.
Где Row — индекс строки, и Col — индекс столбца, которые начинаются с единицы.
Где Range — массив ячеек, а А1 — привычные для Excel координаты ячейки.
В качестве координат может быть указан диапазон. Например, код
заполнит цифрой 5 все ячейки с А3 по А10, а код
выделит тот же диапазон светло-зеленым цветом.
В обратную сторону, то есть для получения данных из Excel, работает так же. Строка
Выведет сообщение с содержимым ячейки с координатами: Строка=5, Столбец=1.
После того, как мы произвели необходимые нам манипуляции с Excel, мы можем сохранить полученную книгу в файл следующей командой:
Где ActiveWorkbook — текущая книга.
И закрыть приложение Excel командой:
Как понимаете этим возможности управления Excel из Delphi не ограничиваются. И есть один достаточной простой способ узнать, как выполнить необходимо действие с Excel из Delphi.
Называется оно — Макросы.
Представим, что нам необходимо выполнить объединение нескольких ячеек в одну и мы не знаем как это сделать. Но хотим узнать. Для этого выполняем следующие шаги:
1. Запускаем Excel и создаем пустую книгу.
2. Запускаем команду «Записать макрос», по умолчанию название макроса будет «Макрос1». (В разных версиях Excel данная команда находится в разных пунктах меню).
3. Выделяем некоторый диапазон ячеек и нажимаем кнопку «Объединить и поместить в центре».
4. Останавливаем запись макроса.
5. Вызываем список макросов и выбираем там свой записанный макрос.
6. Нажимаем кнопку «Изменить»
Запускается редактор Microsoft Visual Basic for Application в котором видим код проделанных действий:
Давайте разберем по подробнее, что же такого он нам тут написал:
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:
Выделяем нужный нам диапазон.
Объединяем ячейки задав свойство. Или при помощи метода:
Таким способом можно получать код практически для любых необходимых манипуляций.
А если какое-то свойство или метод вызывает вопросы, то можно воспользоваться справкой на MSDN.
Обратите внимание на особенность работы с массивами в VBA. Индексы в массивах в Delphi оборачиваются в квадратные скобки, в то время как в VBA они будут в круглых. И код в Delphi
в VBA будет выглядеть как
Ниже приведу небольшой FAQ по вопросу взаимодействия с Excel из Delphi
Как определить значения констант в Excel для использования в Delphi?
В редакторе VBA ставим точку остановки напротив интересующей константы. Нажимаем выполнить и когда выполнение остановиться, наводим на интересующую константу: 
Как отключить выводы сообщений в Excel?
Как получить список книг из Excel?
Как отключить отображение сетки?
Как вывести текущий лист на предпросмотр печати?
Как выделить жирным часть текста в ячейки?
Как выполнить автоподбор высоты строки для склеенной ячейки?
Как получить используемый диапазон ячеек?
Как получить букву столбца по индексу?
