Сегодняшняя статья блога будет целиком посвящена работе с Excel Range или, говоря другими словами — работе с диапазонами ячеек Excel.
Про работу с этими объектами я уже вкратце говорил, а сегодня хотел бы поделиться с вами более полной информацией. Итак, начнем с самого простого.
1. Что такое Range и как его получить?
Согласно официальному определению Microsoft, Range :
представляет собой ячейки, строки, столбцы, набор ячеек, содержащих один или более смежных блоков ячеек, или 3-D диапазон.
Однако это определение не исключает того, что объектом Range может выступать и одна ячейка (Cell) листа. Таким образом, чтобы получить в свое распоряжение объект Range, можно выполнить следующие операции c объектом Excel в Delphi:
var MyRange: OLEVariat; begin {объект Range, состоящий из одной ячейки} MyRange:=MyExcel.ActiveWorkBook.ActiveSheet.Range['A1']; {объект Range в виде строки из четырех ячеек} MyRange:=MyExcel.ActiveWorkBook.ActiveSheet.Range['A1:D1']; {объект Range в виде столбца из четырех ячеек} MyRange:=MyExcel.ActiveWorkBook.ActiveSheet.Range['A1:A4']; {объект Range в виде таблицы 4х4 ячейки} MyRange:=MyExcel.ActiveWorkBook.ActiveSheet.Range['A1:D4']; end;
Если Вам неудобно в какой-либо ситуации использовать буквенные обозначение ячеек или Вы привыкли до этого момента иметь дело только с отдельными ячейками (Cells), то объект Range можно получить например вот так:
var MyRange: OLEVariat; Cell_1, Cell2: OLEVariant; begin {получаем ссылку на объект Cells, соответствующей ячейке A1} Cell_1:=MyExcel.ActiveWorkBook.ActiveSheet.Cells[1,1]; {получаем ссылку на объект Cells, соответствующей ячейке C5} Cell_2:=MyExcel.ActiveWorkBook.ActiveSheet.Cells[5,3]; {получаем объект Range размером 3х5} MyRange:=MyExcel.ActiveWorkBook.ActiveSheet.Range[Cell_1, Cell_2] end;
Какой из способов Вы будете использовать в 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)
var i:integer; s: single; str: string; Sheet: OLEVariant; begin i:=100; s:=2.12; str:='Hello World!'; Sheet.Range['A1'].Value:=i; Sheet.Range['A2'].Value:=s; Sheet.Range['A3'].Value:=str; end;
Как видите, обращение к ячейкам было одно и то же и нигде я не приводил данный к какому-то типу — записал в ячейки всё как есть.
Если Вы хотите записать в весь диапазон Range одно и то же значение, то просто выполните:
Sheet.Range['A1:A10'].Value:=str;
и получите одну и ту же строку «Hello World!» в десяти ячейках Excel, но такие операции очень редко необходимы при работе с Excel в Delphi. Зато очень часто необходимо воспользоваться другой стороной свойства Value — прочитать большой объем данных из книги Excel за один прием и получить весь массив данных в Delphi. Операция чтения данных из Excel в Delphi более проста, чем Вам может показаться на первый взгляд. Проведем обратную операцию — прочитаем данные из Excel:
var val: Variant; Sheet: OLEVariant; i:integer; begin Val:=Sheet.Range['A1:A3'].Value; for i:=1 to 3 do ShowMessage(val[i,1]); end;
Как видите здесь мы за один прием прочитали данные сразу из трех ячеек Excel и отобразили их в сообщении. Этот прием чтения на порядок более скоростной, чем, например чтение содержимого каждой ячейки (Cells) в отдельности.
Text
Ещё одно простенькое свойство объекта Range — возвращает текст из ячейки. Самое главное отличие от свойства Value — Text возвращает string только для чтения и использовать это свойство для чтения большого объема данных, как в предыдущем примере — ни в коем случае нельзя, так как переменная Val вернет значение Null.
Column
Возвращает номер первого столбца в первой области, в указанном диапазоне. Свойство только для чтения.
Чтобы продемонстрировать свойство в действии, давайте создадим такие диапазоны как показано на рисунке:
То есть каждый из диапазонов Range будет содержать по две несвязанные друг с другом области (Area). Причем первая область диапазона Range будет начинаться в столбце А, а первая область второго диапазона (Range 2) — в столбце B.
После того, как диапазоны будут созданы — посмотрим, что вернет нам свойство Column для каждого из диапазонов.
Листинг процедуры создания двух несвязных диапазонов Range следующий:
var Range1,Range2,BigRange: OLEVariant; begin {создаем первый диапазон} Range1:=Sheet.Range['A1:C4']; Range2:=Sheet.Range['E6:H9']; BigRange:=Sheet.Range[Range1,Range2]; ShowMessage(IntToStr(BigRange.Column)); //показываем значение свойства {создаем второй диапазон} Range1:=Sheet.Range['B7:C13']; Range2:=Sheet.Range['E1:H3']; BigRange:=Sheet.Range[Range1,Range2]; ShowMessage(IntToStr(BigRange.Column)); //показываем значение свойства end;
Так, в случае с первым Range Column вернет нам значение 1, а для второго Range — значение 2.
Columns
В отличие от предыдущего свойства, Columns возвращает не простое число, а объект Range, представляющий собой один столбец из всего диапазона.
Посмотрим как, например, используя это свойство можно изменять столбцы во всем диапазоне Range.
Для демонстрации воспользуемся предыдущим примером, изменим только окончание:
... BigRange:=Sheet.Range[Range1,Range2]; for i:=1 to BigRange.Columns.Count do BigRange.Columns[i].Value:='Столбец №'+IntToStr(i);
В итоге в каждый из столбцов диапазона должна записаться строка с номером этого столбца, результат представлен на рисунке. Как видите, в цикле все столбцы обработались «насквозь», хотя Range не содержал в себе столбец D.
Comment
Возвращает объект Comment для Range. В данном случае Range должен определять одну ячейку.
Для демонстрации свойства не будем заходить в Excel, поработаем с приложением прямо из Delphi. Для этого воспользуемся методом AddComment. То есть сначала запишем комментарий в ячейку, а потом прочитаем его используя свойство Comment:
Range2:=Sheet.Range['E6']; {записали комментарий} Range2.AddComment('Это комментарий ячейки области'); {прочитали} ShowMessage(Range2.Comment.Text);
Address
Возвращает реальный адрес диапазона Range. Например для диапазона ячеек, заданных вот таким образом:
Range2:=Sheet.Range['A1:E6']; Свойство Address будет содержать строку "$A$1:$E$6"
Это свойство удобно использовать, когда Вы оперируете в программе только числовыми значениями (номерами столбцов и строк) и появляется необходимость показать пользователю адреса ячеек из диапазона.
Помимо перечисленных выше свойств к объекту 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.
Вызывается метод без каких-либо дополнительных параметров:
Range['A1:H6'].CheckSpelling
PrintPreview
Выводит на экран окно предварительного просмотра перед печатью выбранного диапазона ячеек. Также как и предыдущий метод PrintPreview не имеет дополнительных параметров. Однако при попытке вызвать метод при скрытом окне Excel в лучшем случае возникнет исключительная ситуация, в худшем — зависание Вашего приложения. Так что перед вызовом:
MyRange.PrintPreview
не забывайте включить свойство Visible у Excel:
MyExcel.Visible:=true;
AutoFill
Автоматическое заполнение диапазона ячеек на основе данных из другого диапазона.
Вызов метода:
Range.AutoFill(Destination, Type)
Параметр | Тип | Описание |
Destination | Variant | представляет собой объект Range уже заполненных ячеек. Эти ячейки должны входить в автозаполняемый диапазон |
Type | Integer | тип автозаполнения (возможные значения см. ниже) |
Рассмотрим применение метода на примере.
Sheet.Range['A1'].Value:=1; Sheet.Range['A2'].Value:=2; Source:=Sheet.Range['A1:A6']; Range2:=Sheet.Range['A1:E6']; Source.AutoFill(Range2, xlFillDefault)
В результате лист 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
Изменяет ширину или высоту ячеек диапазона для наилучшего представления данных.
Пример вызова:
Sheet.Range['A1'].Value:=1234567891012; Sheet.Range['A2'].Value:=23456789; Source:=Sheet.Range['A1:A2']; Source.Columns.AutoFit
Приведет и изменению ширины столбца А таким образом, чтобы число было полностью видно на листе.
Методы очистки данных диапазона Range
Есть несколько различных методов очистки содержимого диапазона Range при работе с Excel в Delphi.
Удаляет все данные из диапазона.
Пример вызова:
MyRange.Clear
Удаляет все комментарии в диапазоне Range.
Пример вызова:
MyRange.ClearComments
Удаляет все формулы из диапазона Range
Пример вызова:
MyRange.ClearContents
Очищает форматы в диапазоне Range
Пример вызова:
MyRange.ClearFormats
Очищает все заметки в диапазоне Range
Пример вызова:
MyRange.ClearNotes
Методы работы с буфером обмена Excel
Копирует содержимое диапазона Range в буфер обмена или в другой диапазон.
Попробуем реализовать работу метода следующим образом: заполним столбец А некоторыми значениями, а затем скопируем его сначала в буфер, а потом в столбец Е:
Sheet.Range['A1'].Value:=1; Sheet.Range['A2'].Value:=2; Sheet.Range['A3'].Value:=3; Sheet.Range['A4'].Value:=4; Sheet.Range['A5'].Value:=5; Sheet.Range['A6'].Value:=6; Source:=Sheet.Range['A1:A6']; Source.Copy; //скопировали в буфер Range2:=Sheet.Range['E1:E6']; Source.Copy(Range2)//скопировали в новый диапазон
А для того, чтобы вставить данные из буфера обмена существует ещё один метод
Вызов метода:
MyRange.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
Параметр | Тип | Описание |
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.PasteSpecial(Operation:=xlPasteSpecialOperationAdd);
После выполнения этой операции все данные вставятся в диапазон Range2, т.к. диапазон до вставки был пуст, то данные скопировались один-к-одному.
Вырезает данные и при необходимости вставляет их в новый диапазон. Метод работает аналогично методу Copy, но с последующим удалением данных из источника.
Merge
Объединение ячеек диапазона.
Для новичков, только начинающих постигать азы работы с excel в Delphi этот метод в какой-то момент становится камнем преткновения :). Дело в том, что очень часто возникает необходимость объединить часть ячеек листа для записи в объединенную область большого количества данных или длинной строки. В Excel есть такой метод Union, который и отвечает за объединение — им-то и пробуют пользоваться многие. А метод Union при вызове из Delphi применительно к диапазону Range вызывает исключительную ситуацию. Про Merge люди либо не знают, либо забывают. А использовать его достаточно просто:
Range.Merge(Across)
Параметр | Тип | Описание |
Across | boolean | True, чтобы объединить ячейки в каждой строке указанного диапазона как отдельные объекты. Значение по умолчанию False |
Например, объединим ячейки в диапазоне Range2 из предыдущего примера:
Range2.Merge;
После выполнения этой операции в диапазоне останется только верхнее значение, т.е. 1.
Дополнительные методы автозаполнения ячеек диапазона Range
В отдельную группу методов можно выделить 4 метода автозаполнения ячеек диапазона:
- FillDown
- FillUp
- FillRight
- FillLeft
Принцип действия этих методов один и тот же за исключением направления автозаполнения.
Например, давайте запишем в ячейку A1 какое-нибудь значение и воспользуемся методами FillDown и FillRight:
Sheet.Range['A1'].Value:=1; Sheet.Range['A1:A6'].FillDown; Sheet.Range['A1:F1'].FillRight;
После этого в строк 1 и столбце A появятся числа 1. Аналогичным образом можно заполнять не только строки или столбцы, но и таблицы целиком — достаточно знать в какую сторону двигаться при автозаполнениии и применять соответствующие методы Excel для Range.
Вот в принципе краткий обзор возможностей работы с объектов Range в Excel. В целом можно отметить, что работа с этим объектом в Delphi один из самых эффективных способов организации пресылки/получения данных между Excel и Delphi, не считая работы через XML формат.
Книжная полка
Описание Описаны общие подходы к программированию приложений MS Office. Даны программные методы реализации функций MS Excel, MS Word, MS Access и MS Outlook в среде Delphi.
|
Свойства Text и Value не позволяют получить текст даже одной печатной страницы…
Неужели для получения большого объёма текста листа нужно использовать только буфер обмена? Имею в виду, что вначале выделить данные, потом методом Copy скопировать в буфер, а далее стандартными средствами Delphi изъять текст.
Почему не позволяют? Оба свойства на сколько мне помнится read/write. Другое дело, что ОЧЕНЬ медленно читать текст большого объема. Я обычно работаю с большими таблицами, причём форматированными по-разному, т.е. смысл и содержание одно, а формат разный — всегда копирую в вариантный массив по столбцам — так проще в случае чего сопоставлять данные в таблице. А вообще есть вариант, как говорят, ещё более скоростной — обмен через XML, но я им никогда не пользовался, поэтому сказать что он круче, лешче и т.д. не могу :)
А можно немного рассказать об методе Characters объекта Range? Столкнулся с такой проблеммой нужно было из базы делать экспорт в Excel (несколько Абревеатур в одну строчку, через запятую ) и раскрашивать эти абревеатуры чветом в зависомости от статуса этой абревиатуры. При выгрузке после добавления новой строчки строка обрабатывалась: находился начальный символ в стрке через Pos и затем длинна выгружаемого значания. Затем Pos1 и Pos2 — начало диапозона и длинна передавались свойству Active.Cells.Charactres (pos1,pos2).Font.Color:= в зависимости от значения выгрузки. Так вот, при выгрузке 2х значений все нармуль, только выгружаю 3-е все раскраска сбивается… Может кто подскажет в чем может быть проблемма?… Подробнее »
Расскажите, пожалуйста немного по-подробнее как сбивается работа программы? Возникает исключительная ситуация? Просто не так как надо раскрашивается? Если ошибка, то желательно текст ошибки тоже показать — так будет проще разобраться как решить вашу проблему.
Спасибо что откликнулись. Проблемма такого плана: допустим есть у тренера 3 курса: PL BYB MF со статусами : понижен, отстранен, нормальный соответственно. При выгрузке ни IDE ни Excel не ругается, сама выгрузка проходит, НО получается если 2 курса то все нармально , цвета корректно присваиваются, если больше 2 то тут начинается следующие: первый курс и все последующие,за исключением последнего (ему присваивается правельный цвет), получают значение цвета первого курса, почему не могу понять…. При отладке программы отслеживал pos1 и pos2 значения всегда верные, как видно из кода даже попробовал их обнулять принудительно (хотя это полный бред) но все без результатно…. Вот… Подробнее »
Понятно. Попробую написать небольшой примерчик. Думаю, что получится :)
У меня ругается на Unit4.MyExcel.ActiveWorkBook.ActiveWorkSheet.range[1,2].value:=’jkjh’;
такую строку. Говорит что ActiveWorkSheet «Not supported by automation object» как это побороть?
Добрый день!
Как добавить примечание в ячейку понятно, и спасибо.
А как можно изменить цвет фона в примечании, а еще лучше вставить картинку вместо фона.
В Excel это делается так
Формат примечания — Цвета и линии — Заливка: цвет — способы заливки — Рисунок.
Ответ на ваш воспрос — тут http://www.webdelphi.ru/2011/06/post-otvet-rabota-s-primechaniyami-v-excel/
+1 / хорошая статья.
А есть ли какой ни будь модуль для IDE что бы сделал возможным выбор методов и свойств OLE, чрез Parsing окошко, а то после точки ничего не видит, неудобно постоянно лазить в справочник за точным написанием.
dzlib, при такой работе как рассказано в статье никаких компонентов быть в принципе не может, т.к. здесь идёт позднее связывание на этапе выполнения программы. Чтоб просматривать свойства/методы это надо пользовать компоненты для работы с Office, которые лежат в модулях Access.pas, Word.pas и т.д. Но эти компоненты максимум поддерживают MS Office XP.
Здравствуйте!
Есть какой-то способ определения диапазона (
Range
) с данными? То есть я точно знаю, что значения ячеек в столбце «A» всегда заполнены. Как найти последнюю заполненную строку не прибегая к медленному просмотру каждой строки в цикле?Очень нужна Ваша помощь.
Александр, есть такой способ. Смотрите свойства листа — там есть пара свойств, содержащих количество столбцов и строк, которые заняты данными. Если напрягает пошаговый перебор каждой ячейки — читайте весь заданный диапазон в вариантный массив и перебирайте его (скорость возрастет на порядки)
есть метод UsedRange
пример 1Ски, к сожалению дельфи под рукой нет да и отошел от нее пару лет как
Эксель = Новый ComОбъект («Excel.Application»);
Книга = Эксель.WorkBooks.Open (Объект.ИмяФайла);
Лист = Книга.WorkSheets (1);
Массив = Лист.UsedRange.Value.Выгрузить();
Но думаю партировать без проблем можно
Внимательно прочитал статью, но, к сожалению, не нашел подходов для решения следующей задачи: Как организовать поиск записи, например, из Edit на форме Delphi на листе Excel?
Помогите! Заранее благодарю!
[…] Excel в Delphi. Работа с объектом Range (диапазон) […]
ActiveWorkSheet — то ли у Вас ошибка в тексте, то ли не знаю в чём проблема, но у меня выдаёт ошибку; ActiveSheet работает.
никак не могу решить такую задачу:
объединенно несколько ячеек ,и при считыание по каждой ячейки видим что результат всего объединения находится лишь в первой ячейке всего диапазона,а не во всех ячейках объединенного диапазано,как хотелось бы,но еще вопрос стоит в том чтобы найти такой объединенный диапазон..есть ли какое то свойство указывающее что ячейка объединенна или одиночная?спасибо