Применение Excel для анализа информации из базы данных

• запрещать/разрешать общий доступ к рабочей книге можно при помощи методов SaveAs() или ExclusiveAccess();
• по умолчанию возможность совместного редактирования для книги отключена (проверить можно при помощи свойства MultiUserEditing);
• получить список всех пользователей (а также когда они открыли файл и в каком режиме) можно при помощи свойства UserStatus.

• FileFormat — формат книги (доступен напрямую только для чтения, можно изменять при сохранении). Форматов очень много: множество версий Excel, DBF, Lotus 1-2-3, форматы TXT, CSV, XML — всего несколько десятков.

• свойство Names возвращает коллекцию всех именованных диапазонов в данной рабочей книге.

Методов у объекта Workbook также очень много, однако значения самых употребимых — Activate(), Close(), Save(), SaveAs(), PrintOut(), Protect() и Unprotect() очевидны.

2.3 Коллекция Sheets и объект Worksheet, их свойства и методы

В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.

Чаще всего для ввода  данных в Excel (напрямую или из базы данных) нам потребуется в первую очередь определиться с листом, на который пойдет ввод данных — либо просто выбрать его, либо вначале создать, а потом выбрать. Процесс создания выглядит очень просто:

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add () 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Add() ' Создаемновыйлист

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

Метод Add() для коллекции Worksheets принимает несколько необязательных параметров, главная задача которых — определить, между какими существующими листами будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.

Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем "Лист1" и переименовываем его в "Новый лист":

Dim oExcel As New Excel.Application 'Запускаем Excel

oExcel.Visible = True 'Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add() 'Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Item("Лист 1") ' НаходимЛист1

oSheet.Name = "Новый лист" 'Присваиваем ему имя "Новый лист"

Обратите внимание, что  в английской версии Excel этот код, скорее всего, не пройдет, поскольку листы там по умолчанию называются "Sheet1", "Sheet2" и т.п. Если вы в вашем коде используете имена листов по умолчанию и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.

У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.

У объекта Worksheet — множество важных свойств:

• Cells — одно из наиболее часто используемых свойств объекта Worksheet. Работает точно так же, как и рассмотренное выше одноименное свойство объекта Application — за исключением того, что вам больше не нужно ограничиваться только активным листом. Аналогично работают свойства Columns и Rows.
• EnableCalculation — возможность отключить автоматический пересчет значений ячеек в книге.
• EnableSelection — возможность запретить выделять на листе: все, ничего не запрещать, или разрешить выделять только незаблокированные ячейки.
• Next — возможность получить ссылку на следующий лист в книге. Previous — то же самое для предыдущего листа.
• свойство Protection позволяет получить объект Protection, при помощи которого можно запретить пользователю вносить изменения в лист Excel. Настройке параметров защиты также служат и другие свойства, названия которых начинаются на Protection.
• QueryTables — исключительно важное свойство. Оно возвращает коллекцию QueryTables — набор объектов QueryTable, которые, в свою очередь, представляют данные, полученные из внешних источников (как правило, из баз данных).
• Range — самое важное свойство объекта Worksheet. Возвращает объект Range (диапазон ячеек), который в объектной модели Excel занимает примерно такое же место, что и одноименный объект в объектной модели Word. Этот объект будет рассматриваться ниже.
• Type — возможность определить тип данного листа. Обычно используются два типа: xlWorksheet (обычный лист) и xlChart (диаграмма).
• UsedRange — возвращает объект Range, представляющий собой прямоугольную область, включающую все непустые ячейки. Удобно для целей копирования или форматирования.
• Visible — возможность спрятать лист с глаз пользователя (например, если он используется для служебных целей).

Некоторые важные методы объекта Worksheet:

• Методы Activate() , Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select(), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect() намужезнакомы .Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
• метод PivotTables() возвращает коллекцию очень интересных объектов PivotTable (сводная таблица);
• метод Scenarios() возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных сценариев (разные суммы продаж, уровни налогов, расходов и т.п.)
• SetBackgroundPicture() — возможность назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачное — "водяной знак", иначе на его фоне будет трудно читать текст в ячейках).
• ShowAllData() — показать все скрытые и отфильтрованные данные на листе.

Самое важное событие объекта  Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.

У объекта Worksheet есть еще два очень удобных события (их сильно не хватает объекту Document в Word). Это — события BeforeRightClick() и BeforeDoubleClick(). Как понятно из названия, первое событие позволяет перехватывать щелчок правой кнопкой мыши по любому месту в листе, а второе — двойной щелчок мышью. При помощи этих событий вы можете назначить свою реакцию (открытие контекстных меню, выдачу предупреждающих сообщений, переход в другой режим работы и т.п.) на действия пользователя.

2.4 Программная работа с ячейками и диапазонами ячеек средствами VBA

Пожалуй, наиболее часто используемый объект в иерархии объектной модели Excel — это объект Range. Этот объект может представлять одну ячейку, несколько ячеек (в том числе несмежные ячейки или наборы несмежных ячеек) или целый лист.

В Microsoft Knowledge Base есть статья под номером 291308, в котором описываются 22 способа получения объекта Range в Excel. Вряд ли вы будете пользоваться всеми эти способами. Мы рассмотрим только самые распространенные:

• самый простой и очевидный способ — воспользоваться свойством Range. Это свойство предусмотрено для объектов Application, Worksheet и самого объекта Range (если вы решили создать новый диапазон на основе уже существующего).
• второй способ — воспользоваться свойством Cells. Возможностей у этого свойства меньше — мы можем вернуть диапазон, состоящий только из одной ячейки. Зато мы можем использовать более удобный синтаксис (с точки зрения передачи переменных, перехода в любую сторону на любое количество ячеек и т.п.).
• третий способ — воспользоваться многочисленными свойствами объекта Range, которые позволяют изменить текущий диапазон или создать на основе его новый. Эти свойства будут рассмотрены ниже.

Поскольку объект Range с функциональной точки зрения очень важен, то свойств и методов у него очень много. Ниже представлены некоторые самые употребимые свойства:

• Address — позволяет вернуть адрес текущего диапазона. Этому свойству можно передать много параметров — для определения стиля ссылки, абсолютного или относительного адреса для столбцов и строк, по отношению к чему этот адрес будет относительным и т.п. Свойство доступно только для чтения. AddressLocal — то же самое, но с поправкой на особенности локализованных версий Excel.

На практике встречается  множество ситуаций, когда адрес  ячейки нужно разобрать на части  и вернуть из него имя столбца  или номер строки. Это очень  просто сделать при помощи строковых  функций — спасибо знакам доллара. Например, имя столбца для объекта oRange, представляющего одну ячейку, можно вернуть так: sColumnName = Mid(oRange.Address, 2, (InStr(2, oRange.Address, "$") — 2))

На первый взгляд кажется  сложным, но на самом деле все очень  просто — для имени столбца мы просто берем все, что у нас находится между первым знаком доллара (он у нас всегда первый символ) и вторым, а для номера строки бы берем все, что у нас находится после второго знака доллара. Найти этот второй знак доллара можно при помощи встроенной функции InStr(), а взять нужное количество символов начиная с какого либо проще всего при помощи встроенной функции Mid().

• AllowEdit — это свойство, доступное только для чтения, позволяет определить, сможет ли пользователь править данную ячейку (набор ячеек) на защищенном листе. Используется для проверок.
• Cells — это свойство есть и для объекта Range. Работает оно точно так же, за исключением того, что опять-таки используется своя собственная виртуальная адресация на основе диапазона.
• Точно такие же особенности у свойств Row и Rows, Column и Columns.
• Characters — это простое с виду свойство позволяет решить непростую задачу: как изменить (текст или формат) части текста в ячейке, не затрагивая остальные данные. .
• Count — возвращает количество ячеек в диапазоне.
• CurrentRegion — очень удобное свойство, которое может пригодиться, например, при копировании/экспорте данных, полученных из внешнего источника. Оно возвращает объект Range, представляющий диапазон, окруженный пустыми ячейками (то есть непустую область, в которую входит исходный диапазон/ячейка).
• End — еще одно часто используемое свойство. Оно позволяет получить объект Range, представляющий последнюю ячейку исходного диапазона. В какой стороне будет считаться последняя ячейка, можно определить при помощи передаваемого параметра.
• Errors — свойство, которое через коллекцию Errors позволяет получить доступ к объектам Error, представляющим обнаруженные ошибки в диапазоне.
• Font — это свойство позволяет получить доступ к объекту Font, при помощи которого можно настроить особенности оформления текста в ячейке (цвет, шрифт, размер букв и т.п.)
• FormatConditions — возможность создать собственный объект, представляющий вариант оформления ячеек, который затем можно применять к разным ячейкам и диапазонам.
• Formula — одно из самых важных свойств объекта Range. Доступно и на чтение, и на запись. Если используется на чтение, то возвращает текст формулы, прописанной в ячейку (а не вычисленное значение), если используется на запись, то позволяет записать формулу в ячейку. Если применить это свойство для диапазона, в который входит несколько ячеек, то формула будет прописана во все ячейки диапазона.
• Свойство FormulaLocal, как и AddressLocal, позволяет внести поправки на особенности нумерации ячеек в локализованной версии Excel (для русских версий Excel в нем нет необходимости).
• FormulaHidden — возможность спрятать формулы в диапазоне от пользователя. Работает только на защищенных листах.
• Interior — еще одно свойство, связанное с форматированием. В основном позволяет покрасить ячейки диапазона.
• Locked — это свойство позволяет заблокировать ячейки диапазона при защите листа.
• Next — перейти на следующую ячейку. Если лист не защищен, то следующей ячейкой будет считаться ячейка справа, если защищен — то следующая незаблокированная ячейка.
• Offset — это свойство позволяет получить новый объект Range с определенным смещением от исходного.
• свойство Orientation позволяет сориентировать текст в ячейках. Указывается угол наклона в градусах.
• PageBreak — это свойство обычно используется для программной вставки разрывов страницы.
• Range — это свойство, как уже говорилось выше, позволяет создать новый диапазон на основе уже существующего. Необходимо помнить про особенности нумерации ячеек в этом случае.
• Resize — возможность изменить текущий диапазон.
• ShrinkToFit — это свойство позволяет автоматически настроить размер текста в диапазоне таким образом, чтобы текст умещался в ширину столбца.
• Style — это свойство позволяет вернуть объект Style, представляющий стиль для указанного диапазона.
• Value — наиболее часто используемое свойство объекта Range. Позволяет получить или назначить значение (числовое, текстовое или какое-либо другое) ячейкам диапазона. Точно для той же цели используется свойство Value2, единственное отличие — это свойство не поддерживает типы данных Currency и Date.

Теперь — о методах объекта Range:

• Activate() — выделяет текущий диапазон и устанавливает курсор ввода на его первую ячейку.
• AddComment() — возможность добавить комментарий к ячейке. Ячейка будет помечена красным уголком, а текст комментария будет показан в виде всплывающей подсказки.
• AutoFill() — возможность использовать автозаполнение для диапазона (например, если первые две ячейки будут заполнены как 1 и 2, то дальше в автоматическом режиме будет продолжено: 3, 4, 5 и т.п.)
• AutoFit() — автоматически поменять ширину всех столбцов и высоту всех строк в диапазоне, чтобы туда уместился текст ячеек.
• BorderAround() — возможность поместить диапазон в рамку с выбранными вами параметрами.
• методы Clear… позволяют очистить содержимое диапазона — от значений, форматирования, комментарий и т.п.
• Copy() — возможность скопировать диапазон в другое место. Если место назначения не указано, он копируется в буфер обмена. Аналогично работает метод Cut(), при котором данные исходного диапазона вырезаются.
• CopyFromRecordset() — очень удобный метод, который позволяет вставить данные из объекта ADO Recordset на лист Excel, начиная с верхнего левого угла указанного диапазона.
• DataSeries() — метод, который может сэкономить множество времени и избежать возни с функциями даты и времени. Этот метод позволяет увеличить вами значения даты в диапазоне на указанный вами временной интервал. Например, если у вас в диапазоне стоит первое января, то при помощи этого метода можно сгенерировать первое число любого другого месяца.
• Delete() — удаляет данные текущего диапазона. В качестве необязательно параметра можно определить, с какой стороны будут сдвигаться ячейки на место удаленных.
• Dirty() — пометить ячейки диапазона как "грязные". Такие ячейки будут пересчитаны при следующем же пересчете. Пересчитать ячейки диапазона можно и принудительно — при помощи метода Calculate().
• методы Fill… (FillDown(), FillUp(), FillLeft(), FillRight()) позволяют размножить одно и то же значение по ячейкам диапазона в указанном вами направлении.
• метод Find() позволяет произвести поиск по ячейкам диапазона и вернуть новый объект Range, который представляет первую ячейку, в котором было найдено нужное значение. Методы FindNext() и FindPrevious() позволяют продолжить поиск, начатый методом Find(), в разных направлениях.
• метод Insert() позволяет вставить ячейки в диапазон, сдвинув остальные (можно выбрать — вправо или вниз).
• метод Justify() позволяет равномерно распределить текст по диапазону. Если в данный диапазон он не помещается, он будет распространен на соседние ячейки (с перезаписью их значений).
• Parse() — позволяет разбить одну ячейку на несколько по указанному вами шаблону.
• PasteSpecial() — операция, дополняющая Copy() и Cut(). Она позволяет вставить то, что лежит в буфере обмена, с указанием специальных параметров вставки (вставлять с добавлением к существующим данным, с умножением, вычитанием, делением и т.п.).
• PrintOut() и PrintPreview() — позволяют вывести диапазон на печать или открыть режим просмотра перед печатью.
• Replace() — метод, дополняющий метод Find(). Позволяет проводить поиск и замену значений в диапазоне.
• Select() — возможность выделить указанный диапазон. Объекта Selection в Excel нет — вместо него есть возможность получить объект Range, представляющий выделенную область.
• Show() — экран будет проскроллирован таким образом, чтобы показать указанный диапазон.
• ShowErrors() — показать источник ошибки для указанной ячейки.
• Sort() — возможность произвести сортировку ячеек в диапазоне. Можно использовать большое количество необязательных параметров для настройки сортировки.
• метод Table() позволяет создать таблицу на основе передаваемого столбца, строки и функции, которую нужно использовать для вычисления ячеек таблицы.
• TextToColumns() — сложный метод, который позволяет разбить столбцы в диапазоне на несколько столбцов в соответствии с определенным алгоритмом.