Пустая не пустая ячейка delphi excel
И самое удивительное - если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа Редактирование -Очистить содержимое) - то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд - "" ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:
- Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет - они просто заполняются строкой нулевой длины.
- в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
=ЕСЛИ( A1 =1;10;"")
=IF(A1=1,10,"")
в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо "" ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно -Показывать нули в ячейках, которые содержат нулевые значения
А если такой файл делали не Вы - он достался "по наследству" или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:
Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox "В выделенных ячейках нет значений!", vbInformation, "www.excel-vba.ru" Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value If Not IsArray(avR) Then Redim avR(1 to 1, 1 to 1) avR(1,1) = rR.Value end if For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = "" Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox "Строки нулевой длины заменены", vbInformation, "www.excel-vba.ru" Exit Sub End If MsgBox "Строк нулевой длины на листе нет или лист защищен", vbInformation, "www.excel-vba.ru" End Sub
Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?
- создаем в книге новый стандартный модуль: Alt+F11 -Insert -Module()
- копируем в созданный модуль приведенный выше код
- выделяем нужный диапазон(если надо заменить на всем листе - то можно выделить все ячейки листа или целиком нужные столбцы - программа сама определить нужные данные)
- нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString
Александр Шабля
дата публикации 26-05-2006 08:58
- Как записать значения сразу в несколько ячеек?
- Как определить область выделенных ячеек и ее границы?
- Как записывать значения в ячейку (Value, Value2, Text, Formula)?
- Что работает быстрее, запись в Range или Cells?
- Что такое UsedRange? Как найти последнюю используемую ячейку? Что такое SpecialCells? Что такое CurrentRegion?
- Как получить адрес ячейки?
- Нужно ли выделять ячейку/область для того чтобы вносить в нее данные?
- В чем отличие Range.Activate от Range.Select?
- Как установить свойству ячейки NumberFormat "общий" формат, текстовый формат, формат даты, числа, валюты?
- Передаю в ячейку 385.00, а показывает 385. Почему?
- При записи в ячейку чисел как текста, целые числа автоматический преобразуются в численный формат, а вещественные нет. Вследствие чего возникает ошибка "число сохранено как текст".
- Делаю экспорт в Excel, допустим, текст "000069987", а он выводит в ячейку 69987, т.е. удаляет лидирующие нули. Как мне сделать, чтобы в ячейку выводилось 000069987?
- Как очистить область ячеек? Как определить что ячейка Excel пустая?
- Как задать имя области ячеек? Как обратиться к ячейке по имени? Как определить, что такое имя существует на листе? Как задать имя области ячеек?
- Как объеденить ячейки? Как узнать, что ячейка входит в объединенную область и определить границы этой области?
- Как записывать данные из вариантного массива в Excel?
- Как прочесть данные из области ячеек в массив?
- Почему при выгрузке данных в Excel не могу записать строк больше 65536?
- Как писать в ячейки нескольких листов сразу?
- Как изменить цвет фона и шрифта ячейки?
- Как изменить атрибуты шрифта части текста в ячейке (цвет, размер, имя)?
- Как узнать позицию курсора в редактируемой по F2 ячейке или в строке формул и дописать в нее текст?
- Как изменить выравнивание/угол наклона текста, отступы в ячейке?
- Как задать границы для области ячеек (Borders)?
- Как скопировать форматы и формулы из строки в нижележащую область (AutoFill)?
- Как скопировать область ячеек с сохранением всех форматов? Как скопировать только значения ячейки?
- Как скопировать область, чтоб сохранились размеры строк/столбцов?
- Как сделать автоперенос строк в ячейке?
- Как вставить несколько строк/столбцов? Как удалить несколько строк/столбцов? Как прятать/показывать строки и столбцы? Как программно изменить высоту строки или ширину столбца?
- Как подогнать высоту или ширину ячеек для отображения всего текста?
- Как сделать автоподбор высоты строк для объединенных ячеек?
- Как программно "заморозить" строки/столбцы?
- Как добавить примечание к ячейке? Как удалить примечание? Как изменить атрибуты шрифта примечания?
- Как добавить URL? Как сделать гиперссылку для рисунка?
- Как отсортировать область ячеек?
- Как сделать поиск значений в области ячеек или по всему листу?
- Как, имея ссылку на ячейку, узнать имя листа, которому она принадлежит? Узнать имя книги?
Константа lcid в примерах определена как
Cells, Range, Rows, Columns
Объект Cells предназначен для доступа к ячейкам в стиле R1C1 к одной ячейке. Range — в стиле A1 к области (коллекции) ячеек. Удобство объекта Range в том, что можно, при использовании оператор with, обращаться к нескольким свойствам и методам. Объект Rows возвращает коллекцию строк и Columns — коллекцию столбцов объекта Range (вместо этих свойств можно использовать свойства EntireRow и EntireColumn).
Как записать значения сразу в несколько ячеек?
Для записи в несколько (область) ячеек используется объект Range (ExcelRange). Пример как можно получить объект Range для области ячеек.
Заметьте, что при обращении к свойству Range и Cells объекта Range, адресация будет уже работать относительно области, указанной в объекте Range. Например, нижеприведенный код будет указывать не на ячейку "A1", как сразу можно подумать, а на "C2":
А вот такой код вернет ячейку с адресом "D3":
Как определить область выделенных ячеек и ее границы?
Чтоб получить область (или области) выделенных ячеек нужно получить объект Range из свойства Selection объекта Excel.Application и обратиться к свойству Range.Areas.
Как записывать значения в ячейку (Value, Value2, Text, Formula)?
Для записи локализованных ("русских") форматов данных и формул используются свойства с окончанием Local, например FormulaLocal.
Если вы попробуете записать макрос в Excel, то увидите, что запись значений ведется в свойство FormulaR1C1. С тем же успехом можно писать и в свойство Formula.
Внимание! При записи в свойство Formula, если это не формула, следите, чтобы текст не начинался с символов "=", "+", "-", "*", "/". Или просто к тексту прибавляйте в начало знак апострофа (код символа 39):
Что работает быстрее, запись в Range или Cells?
Для перевода из координат R1C1 в A1 удобно пользоваться "самодельными" функциями, например:
Что такое UsedRange? Как найти последнюю используемую ячейку? Что такое SpecialCells? Что такое CurrentRegion?
UsedRange — прямоугольная область, включающая все заполненные ячейки и незаполненные, в промежутках между заполненными ячейками, на листе. Координаты области не обязательно начинаются в ячейке A1. Также для определения координат различных ячеек можно использовать объект SpecialCells, например, с параметром xlCellTypeLastCell для нахождения последней (крайней справа снизу) используемой ячейки. CurrentRegion возвращает область вокруг ячейки, выделенную пустыми (незаполненными) ячейками. End — находит последнюю ячейку в строке или столбце перед первой попавшейся пустой ячейкой, или первую заполненную, если вызывать метод End для пустой ячейки.
Как получить адрес ячейки?
Нужно ли выделять ячейку/область для того чтобы вносить в нее данные?
Не нужно. Достаточно указать адрес области ячеек в объекте Range для выбранного объекта Worksheet (и/или Workbook). Любой Select или Activate только замедлит работу вашей программы. Кроме того, метод Select возможно вызвать только на активном листе активной книги! Не используйте Select и Activate без необходимости.
В чем отличие Range.Activate от Range.Select?
И метод Activate и Select делают одно и то же — выделяют (активируют) ячейку. Разница лишь в том, что метод Activate позволяет выбрать только одну ячейку на листе или сделать активной любую ячейку в области, выделенной методом Select. Метод Select позволяет выделять одну и более областей ячеек.
Как установить свойству ячейки NumberFormat "общий" формат, текстовый формат, формат даты, числа, валюты?
Для правильной работы NumberFormat с английскими форматами не забудьте подключить модуль TrDispCall
Передаю в ячейку 385.00, а показывает 385. Почему?
Потому что в ячейке установлен "общий" формат (general), который отсекает не значащие цифры. В данном примере всегда будут указываться 2 цифры после запятой:
При записи в ячейку чисел как текста, целые числа автоматический преобразуются в численный формат, а вещественные нет. Вследствие чего возникает ошибка "число сохранено как текст".
Лучше числа не записывать в ячейку как текст и не надеяться, что Excel всегда сможет "на лету" преобразовать текст верно. Вы никогда не можете быть уверенными, какие локальные установки формата чисел будут установлены на компьютере пользователя. Всегда перед записью переводите записываемые числа из текста в число (Float или Integer) в своей программе.
Делаю экспорт в Excel, допустим, текст "000069987", а он выводит в ячейку 69987, т.е. удаляет лидирующие нули. Как мне сделать, чтобы в ячейку выводилось 000069987?
При записи текста, содержащего одни цифры, Excel пытается его преобразовать в число. Чтобы избажать такой "помощи" со стороны Excel'я перед записью в ячейку установите в свойтво NumberFormat текстовый формат или добавьте перед текстом символ апострофа "'" (код символа 39).
Как очистить область ячеек? Как определить что ячейка Excel пустая?
Чтобы радикально очистить ячейки (данные, форматы, примечания и т.д.) можно вызвать метод Clear.
Как задать имя области ячеек? Как обратиться к ячейке по имени? Как определить, что такое имя существует на листе? Как задать имя области ячеек?
Если вы зададите области имя уже существующее в листе, то старое имя будет утеряно, т.е. получится перезапись имени. Присваивать имена области ячеек можно и не активному листу. Задавать адрес ячейки можно и как текст (не обязательно ссылку на объект Range), а также можно в стиле R1C1, указав адрес области ячеек в параметре RefersToR1C1.
Как объеденить ячейки? Как узнать, что ячейка входит в объединенную область и определить границы этой области?
Смотрите дальше как сделать автоподбор высоты строк для объединенных ячеек.
Как записывать данные из вариантного массива в Excel?
Запись данных из вариантного массива (VarArray) очень хорошо расписана в статьях "По волнам интеграции… III" и "Зарисовка на тему экспорта в Excel". Для разнообразия, приведу еще раз этот вариант быстрого экспорта в Excel.
Внимание! Если вы пытаетесь записать в область одну строку, то МАССИВ все равно ДОЛЖЕН БЫТЬ ДВУМЕРНЫМ! Т.е. varData := VarArrayCreate([1, 1, 1, ColumnCount], varVariant); При записи массива вы должны указать в адресе области ячеек Range ВСЮ область для заполнения.
Как прочесть данные из области ячеек в массив?
Точно также как и при экспорте, только самому создавать массив не нужно — Excel все сделает за вас. В принципе, после получения данных в массив, Excel уже не нужен, и от него можно отсоединиться.
Почему при выгрузке данных в Excel не могу записать строк больше 65536?
Потому что это максимально возможное количество строк объекта Worksheet. Если вы записываете больше строк, чем 65536, то помещайте их на следующий лист книги — благо, что количество листов ограничено только оперативной памятью комьютера.
Как писать в ячейки нескольких листов сразу?
Чтобы занести данные в несколько листов сразу, вы можете объединить листы методом Worksheets.Select и воспользоваться методом FillAcrossSheets
Как изменить цвет фона и шрифта ячейки?
Смотрите свойства Font и Interior объекта Range
Как изменить атрибуты шрифта части текста в ячейке (цвет, размер, имя)?
Чтобы изменить часть текста ячейки можно воспользоваться свойством Characters объекта Range.
Как узнать позицию курсора в редактируемой по F2 ячейке или в строке формул и дописать в нее текст?
Никак! При входе в режим редактирования ячейки объект Excel.Application становится полностью недоступен через OLE.
Как изменить выравнивание/угол наклона текста, отступы в ячейке?
Смотрите свойства HorizontalAlignment, VerticalAlignment, AddIndent и Orientation объекта Range
Как задать границы для области ячеек (Borders)?
Смотрите свойство Borders объекта Range.
Как скопировать форматы и формулы из строки в нижележащую область (AutoFill)?
Это как раз самый удобный метод копирования форматов и формул для расширения области данных при использовании шаблонов. Подразумевается, что между НАЧАЛО/КОНЕЦ находится подготовленные ячейки шаблона (форматирование, именованная область DataRange для данных).
Как скопировать область ячеек с сохранением всех форматов? Как скопировать только значения ячейки?
Метод Copy позволяет не только копировать содержимое области ячеек в буфер обмена (при пустом параметре), но и задать конкретный адрес ячеек для копирования. Если вы хотите вставить из буфера только некоторые параметры скопированной в БО ячейки, то для вставки используйте метод PasteSpecial, указав необходимый XlPasteType (первый аргумент).
Как скопировать область, чтоб сохранились размеры строк/столбцов?
К сожалению, при копировании не сохраняются размеры строк и столбцов. Для сохранения размеров строк и столбцов можно использовать несколько способов:
Как сделать автоперенос строк в ячейке?
Чтобы сделать перенос слов в ячейке установите свойство WrapText объекта Range.
Как вставить несколько строк/столбцов? Как удалить несколько строк/столбцов? Как прятать/показывать строки и столбцы? Как программно изменить высоту строки или ширину столбца?
Как подогнать высоту или ширину ячеек для отображения всего текста?
Для отображения всего текста в ячейке или области ячеек используйте метод AutoFit объекта Range.
Как сделать автоподбор высоты строк для объединенных ячеек?
Как известно, метод AutoFit для подбора высоты объединенных ячеек не срабатывает. Для этого был придуман простой метод (взят отсюда и просто адаптирован под Delphi). Работает для объеденных ячеек в одной строке. Просто укажите одну из объединенных ячеек области (свойство WrapText должно быть включено).
Конечно, функция должна быть вызвана для каждой строки, что, естественно, будет работать довольно долго. Поэтому старайтесь не использовать перенос текста в объединенных ячейках.
Как программно "заморозить" строки/столбцы?
Как добавить примечание к ячейке? Как удалить примечание? Как изменить атрибуты шрифта примечания?
Комментарий — это своеобразный объект Shape, привязанный к определенному объекту Range.
Как добавить URL? Как сделать гиперссылку для рисунка?
Как отсортировать область ячеек?
Пример сортировки всех данных на листе по первому, второму и третьему столбцам.
Как сделать поиск значений в области ячеек или по всему листу?
Как, имея ссылку на ячейку, узнать имя листа, которому она принадлежит? Узнать имя книги?
Получить ссылку на объект Worksheet, содержащий данную ячейку можно из свойства Parent.
Сегодняшняя статья блога будет целиком посвящена работе с 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 в Delphi рассказывается в статье «Быстрая обработка данных Excel в Delphi.«
Ещё одно простенькое свойство объекта 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 люди либо не знают, либо забывают. А использовать его достаточно просто:
Видимо любители экономить килобайты оперативной памяти могут меня закидать помидорами или ещё чем по-хуже, но все-таки я скажу, что интеграция приложений (мегабайты оперативы) — это большой плюс нынешней разработки приложений.
Как ни крути, а время DOS и килобайтов оперативной памяти прошло. Вряд ли кто-то всерьез сейчас задумывается над тем куда это с винчестера пропал мегабайт? Да и использование в своих приложениях функциональности программ, которых ты не писал, но которые выполняют что-то лучше — это всё-таки больший прогресс, нежели корпеть год-два над программой, а потом узнать, что время-то прошло.
Введение
Итак, цель сегодняшней статьи — поделиться с Вами опытом работы с Microsoft Excel в приложениях, написанных на Delphi.
Вспомнился сейчас один случай. Когда я только начинал работать по своей специальности, пригласили меня написать программу-расчётник для экологов нашего нефтезавода. В принципе ничего серьёзного — программа считает выброс от нагревательной печи и выдает табличку результатов, которую необходимо распечатать и уложить в толстую папку с отчётами. Естественно, что в области разработки подобных приложения я далеко не пионер, поэтому дали взглянуть на аналог будущей программы, который работал ещё под DOS и печатались отчёты на дико скрипящем матричном принтере с 12-ю иголками. Ну посмотрел, элементарная таблица, расчёт немного запутан, но жить можно — начал по-тихоньку писать. И попалась мне тогда на глаза статейка про работу с Excel в Delphi. Вот я и решил попробовать выдавать отчёт не только на форму приложения, а ещё и скидывать весь ход расчёта с формулами и прочим делом в Excel…Надо сказать более сильно детской радости начальника отдела я не видел до сих пор :). Люди, всю жизнь проработавшие в DOS увидели как тот же самый расчёт может выглядеть в современных условиях. Вот теперь при определении технических заданий на каждую новую программу, обязательно присутствует пункт, гласящий, что программа должна передавать данные либо в MS Word либо в MS Excel.Соответственно и цена на разработку возрастает, иногда значительно.
Отсюда можно сделать простой и однозначный вывод — заказчики готовы пожертвовать лишними деньгами только для того, чтобы всё в программе было красиво. Excel может дать вашему приложению ту самую красоту и удобство.
Ну, а для того, чтобы каждый раз не утруждать себя выполнением однотипных операций, я разработал небольшой модуль для работы с Excel. Этот же модуль я в настоящее время дорабатываю под ещё одну задачу, но об этом после. Сегодня основы основ работы с Excel в Delphi.
1. Как проверить установлен ли Excel на компьютере пользователя?
Создаем новый модуль (unit) и подключаем в uses следующие модули:
поэтому в excel я пытаюсь избавиться от пустых ячеек между моими ячейками, в которых есть информация, используя F5 для поиска пустых ячеек, затем Ctrl + - для их удаления и сдвига ячеек вверх. Но когда я пытаюсь это сделать, он говорит мне, что "клеток не найдено".
Я заметил, что если я выбираю свои "пустые" ячейки, Excel все еще считает их: что странно. Но если я нажму Delete на эти выбранные ячейки, счетчик исчезнет, а затем я могу перейти F5, пробелы, Ctrl + - и Сдвиньте клетки вверх, и это сработает.
Итак, мой вопрос в том, как я могу это сделать, но с этими пустыми ячейками, которые Excel считает не пустыми? Я попытался пройти и просто нажать delete над пустыми ячейками, но у меня много данных и понял, что это займет слишком много времени. мне нужно найти способ выбрать эти "пустые" ячейки в пределах выбора данных.
заранее спасибо за вашу помощь! :)
откровение: некоторые пустые ячейки на самом деле не пустой! Как я покажу, ячейки могут иметь пробелы, новые строки и true empty:
чтобы найти эти ячейки быстро вы можете сделать несколько вещей.
удаление этих:
если у вас есть только пробел в клетках они могут быть легко удалены с помощью:
- пресс ctrl + h чтобы открыть find и replace.
- введите одно место в найти, оставить заменить на пустой и убедитесь, что у вас есть матч всей ячейки содержание галочкой в опциях.
- пресс заменить все.
если у вас newlines это сложнее и требует VBA:
-
щелкните правой кнопкой мыши на вкладке листа > Просмотр кода.
затем введите следующий код. Помните Chr(10) новая строка заменяет это только по мере необходимости, например " " & Char(10) это пробел и новая строка:
Теперь запустите ваш код нажатие Ф5 .
после файл поставляется: выберите диапазон интересов для повышения производительности, затем выполните следующие действия:
простой способ выбрать и очистить эти пустые ячейки, чтобы сделать их пустыми:
- пресс ctrl + a или предварительно выберите свой диапазон
- пресс ctrl + f
- оставить найти пустой и выберите матч всего содержимого ячейки.
- нажмите найти все
- пресс ctrl + a выбрать все пустые ячейки нашли
- закрыть поиск диалоговое окно
- пресс backspace или удалить
Это сработало для меня:
- CTR-H для поиска и замены
- оставьте "найти что" пустой
- изменить "заменить на" на уникальный текст, то, что вы
положительный не будет найден в другой ячейке (я использовал 'xx') - клик "Заменить Все"
- скопируйте уникальный текст на Шаге 3, чтобы найти то, что'
- удалить уникальный текст в "заменить на"
- нажмите "заменить все"
У меня была аналогичная проблема, когда разбросанные пустые ячейки из экспорта из другого приложения все еще отображались в подсчетах ячеек.
мне удалось очистить их
- выбор столбцов / строк, которые я хотел очистить, а затем делать
- "найти" [без текста] и" заменить " [слово выбора].
- затем я сделал "найти" [слово выбора] и" заменить " на [нет текста].
он избавился от всех скрытых / фантомных персонажей в тех ячейки. Может, это сработает?
все, это довольно просто. Я пытался сделать то же самое, и это то, что сработало для меня в VBA
С уважением, Ананд Ланка
Если у вас нет форматирования или формул, которые вы хотите сохранить, вы можете попробовать сохранить файл в виде текстового файла с разделителями табуляции, закрыть его и снова открыть с помощью excel. Это сработало для меня.
нашел другой способ. Установите автофильтр для все столбцы (важно или вы будете смещать данные), выбрав строку заголовка > вкладка "данные" > сортировка и фильтр - "фильтр". Используйте раскрывающийся список в первом столбце данных, снимите флажок "выбрать все" и выберите только "(пробелы) " > [OK]. Выделите строки (Теперь все вместе) > щелкните правой кнопкой мыши > "удалить строку". Вернитесь к раскрывающемуся меню > 'Select all'. Престо:)
Не уверен, что это уже было сказано, но у меня была аналогичная проблема с ячейками, ничего не показывающими в них, но не пустыми при запуске формулы IsBlank ().
Я выбрал весь столбец, выбрал Find & Replace, нашел ячейки ни с чем и заменил на 0, затем снова запустил find и replace, найдя ячейки с 0 и заменив их на "".
Это решило мою проблему и позволило мне искать пустые ячейки (F5, специальные, пробелы) и удалять строки, которые были пусты. БУМ.
может не работать для каждого приложения, но это решило мою проблему.
иногда в ячейках есть пробелы, которые кажутся пустыми, но если вы нажмете F2 на ячейке, вы увидите пробелы. Вы также можете выполнить поиск таким образом, если знаете точное количество пробелов в ячейке
Это работает с цифрами.
мой метод похож на предложение Курта выше о сохранении его как файла с разделителями табуляции и повторном импорте. Предполагается, что данные имеют только значения без формул. Это, вероятно, хорошее предположение, потому что проблема "плохих" пробелов вызвана путаницей между пробелами и нулями-обычно в данных, импортированных из какого-то другого места, - поэтому не должно быть никаких формул. Мой метод -разобрать на месте -- очень похоже на сохранение в виде текстового файла и повторный импорт, но вы можете сделать это без закрытия и повторного открытия файла. Он находится в разделе Данные > текст в Столбцы > разделители > удалить все символы синтаксического анализа (также можно выбрать текст, если хотите) > готово. Это должно заставить Excel повторно распознавать ваши данные с нуля или из текста и распознавать пробелы как действительно пустые. Вы можете автоматизировать это в подпрограмме:
вы также можете включить эту прокомментированную строку, чтобы эта подпрограмма выполнялась "в фоновом режиме". Для этой подпрограммы улучшается производительность только немного (для других это действительно может помочь). Имя-F2Enter, потому что исходный ручной метод для исправления этой проблемы "пробелов" - заставить Excel распознать формулу, нажав F2 и Enter.
вот как я исправил эту проблему без какого-либо кодирования.
- выберите весь столбец, из которого я хотел удалить "пустые" ячейки.
- щелкните вкладку Условное форматирование вверху.
- Выберите "Новое Правило".
- нажать "форматировать только ячейки, которые содержат".
- изменить "между" На "равно".
- нажмите на поле рядом с полем" равно".
- нажмите одну из проблемных" пустых " ячеек.
- клик кнопка формат.
- выберите случайный цвет для заполнения коробки.
- нажмите "OK".
- Это должно изменить все проблемные" пустые " ячейки на цвет, который вы выбрали. Теперь щелкните правой кнопкой мыши одну из цветных ячеек и перейдите в раздел "сортировать" и "поместить выбранный цвет ячейки сверху".
- это поставит все проблемные ячейки в верхней части столбца, и теперь все ваши другие ячейки останутся в исходном порядке, в котором вы их поместите. Теперь вы можете выбрать все проблемные ячейки в одной группе и нажмите кнопку Удалить ячейку сверху, чтобы избавиться от них.
самым простым решением для меня было:
1) Выберите диапазон и скопируйте его (ctrl+c)
2)Создайте новый текстовый файл (в любом месте, он будет удален в ближайшее время), откройте текстовый файл, а затем вставьте в excel информацию (ctrl+v)
3) Теперь, когда информация в Excel находится в текстовом файле, выполните select all в текстовом файле (ctrl+a), а затем скопируйте (ctrl+c)
4) перейдите к началу исходного диапазона на шаге 1 и вставьте его старая информация из копии на Шаге 3.
готово! Больше никаких фальшивых заготовок! (теперь вы можете удалить временный текстовый файл)
Goto - > Special - >blanks не любит объединенные ячейки. Попробовать unmerging клеток выше диапазона, в котором вы хотите выбрать то, болванками попробовать снова.
У меня была аналогичная проблема с получением формулы COUNTA для подсчета непустых ячеек, она считала все из них (даже пустые как непустые), я попытался =CODE (), но у них не было пробелов или новых строк.
Я обнаружил, что когда я щелкнул в ячейке, а затем щелкнул из нее, формула будет считать ячейку. У меня были тысячи ячеек, поэтому я не мог сделать это вручную. Я написал этот оператор VBA, чтобы буквально проверить все ячейки, и если они были пустыми, то сделать их пустыми. Игнорируйте бессмысленность этого макроса и поверьте мне, что он действительно работал, заставляя Excel распознавать пустые ячейки как пустые.
Читайте также: