учебник информатика

Глава 5. Основы разработки программного обеспечения

– количество расчетных точек, необходимо изменять количество строк с формулами в таблице и диапазон данных для диаграммы. Вариант решения этой задачи программным путем показан далее. Текст программ должен располагаться в разделе кода для листа с именем График (текст предыдущей процедуры Табл_исх_данных мог располагаться здесь же или в программном модуле книги, см. рисунок 5.52):

Dim NLTG As Single	'	в разделе General	Declarations
	'	кода листа График

Private Sub Worksheet_SelectionChange _

(ByVal Target As Range)

'процедура для листа, автоматически выполняется

'при выборе нового объекта на листе

NLTG = Cells(4, 2)	'	запоминание старого
	'	количества строк в таблице

End Sub

Private Sub Worksheet_Calculate()

'процедура для листа, автоматически выполняется

'при выполнении расчета по формулам на листе

If Cells(4, 2) <> NLTG Then

NLT = NLTG ' запоминаем старое значение

NLTG = Cells(4, 2) ' запоминаем новое значение

'если этого не сделать, процедура зациклится

'т. к. она имеет рекурсивный характер

ActiveSheet.Unprotect

Range(Cells(7, 1), Cells(7 + NLT, 4)).Delete

' удаляем старую таблицу, в которой NLT строк

If Shapes.Count > 0 Then ' удаляем старый график

ActiveSheet.ChartObjects("Grafik").Activate ActiveChart.Parent.Delete

End If

Расчет 'выполняем процедуру Расчет График_функции 'выполняем процедуру

ActiveSheet.Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True

ActiveSheet.EnableSelection = xlUnlockedCells

'устанавливаем защиту на лист кроме ячеек B2:B4

End If

End Sub

Sub Расчет()

431

Глава 5. Основы разработки программного обеспечения

' расчет таблицы Y=F(x)

Range("A7").Formula = "X"

Range("B7").Formula = "Y" Range("A7:B7").HorizontalAlignment = xlCenter Range("A8").Formula = "= B2"

For i = 2 To Range("B4").Value

Cells(i + 7, 1).Value = "=R[-1]C+R5C2" Next

For i = 1 To Range("B4").Value

Cells(i + 7, 2).Value = "=SIN(RC[-1])"

Next

Range(Cells(8, 2), Cells(8 + _ Range("B4").Value, 2)).NumberFormat = "0.000"

End Sub

Sub График_функции()

'построение диаграммы

ActiveSheet.Shapes.AddChart.Select ActiveChart.Parent.Name = "Grafik" ActiveChart.Parent.Height = 250

ActiveChart.Parent.Width = 250 ActiveChart.Parent.Left = 120

ActiveChart.Parent.Top = 80

rng = "A7:B" + LTrim(Str(7 + Range("B4").Value)) ActiveChart.SetSourceData Source:=Range(rng)

'задаем диапазон исходных данных("A7:B22")

ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.PlotBy = xlRows

ActiveChart.PlotBy = xlColumns ActiveChart.ChartTitle.Text = "Y = sin(X)" ActiveChart.Axes(xlValue).HasTitle = True ActiveChart.Axes(xlValue).AxisTitle.Select ActiveChart.Axes(xlValue, xlPrimary). _

AxisTitle.Text = "X" ActiveChart.SetElement _

(msoElementPrimaryValueAxisTitleHorizontal) ActiveChart.Axes(xlCategory).HasTitle = True ActiveChart.Axes(xlCategory).AxisTitle.Select ActiveChart.Axes(xlCategory, xlPrimary). _

AxisTitle.Text = "Y" ActiveChart.Axes(xlCategory).AxisTitle.Select Selection.Left = 5

Selection.Top = 31.333

432

Глава 5. Основы разработки программного обеспечения

ActiveChart.Axes(xlValue).AxisTitle.Select Selection.Left = 325.011

Selection.Top = 194.794 ActiveChart.Legend.Select Selection.Delete

End Sub

Процедуры обработки событий для листа: Private Sub Work-

sheet_SelectionChange (ByVal Target As Range) и Private Sub

Worksheet_Calculate() не могут быть сгенерированы с помощью Записи макросов, их должен писать программист. Основа процедур Sub Расчет() и Sub График_функции() была создана с помощью режима Запись макроса, затем они были модифицированы: организована возможность их выполнения для различного количества точек графика, добавлено название

ActiveChart.Parent.Name = "Grafik" для реализации возможности его

удаления в процедуре Worksheet_Calculate(), добавлены координаты и размер графика, заданы координаты для надписей осей графика, и т. д.

Важная задача, которую можно решить в Excel – подключение к базе данных и отображение одной из ее таблиц на рабочем листе. На вкладке ленты Данные кнопка Получить внешние данные (см. рисунок 5.55) позво-

ляет выполнить импорт данных из текстовых файлов, баз данных системы

Access, серверов баз данных Microsoft SQL Server, Oracle и других, выпол-

нить отбор данных из этих источников с использованием запросов Microsoft Query. При этом используются OLE DB провайдеры (библиотеки COM интерфейсов), драйверы доступа к данным, информация о которых хранится в средстве Windows ODBC (Open Database Connectivity)¸ при-

кладные задачи работы с информацией базы данных могут решаться с ис-

пользованием библиотек Microsoft ActiveX Data Objects (ADO) и Microsoft Data Access Objects (DAO).

При выполнении операции открытия таблицы реляционной базы данных с использованием пункта ленты Excel Получить внешние данные программный код сохраняется в профиле пользователя в папке Мои источники данных в виде файла с расширением *.odc. В последующем этим подключением можно воспользоваться повторно из пункта Существующие подключения (см. рисунок 5.53) и свойства его просмотреть в группе Подключения. Другой вариант использования программного кода подключения и извлечения информации из баз данных – записать макрос подключения и в дальнейшем использовать его, как основу для написания программы работы с этой информацией.

433

Глава 5. Основы разработки программного обеспечения

Рисунок 5.53. Средства работы с внешними данными в Excel

К сожалению, стандартные средства, используемые в системе Excel не всегда правильно отображают тип данных реляционной таблицы, возможны также проблемы при работе с текстовой информацией национальных кодовых таблиц.

Например, код загрузки данных из Access может выглядеть следующим образом (из текста убраны параметры, используемые по умолчанию):

Sub access_Список()

'данные таблицы Список базы Контингент.accdb

With ActiveSheet.ListObjects.Add(SourceType:=0, _

Source:=Array("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;" _

&"Data Source=C:\AGTU\Контингент.accdb;" _

&"Mode=Share Deny Write;" _

&"Jet OLEDB:Engine Type=6;" _

&"Jet OLEDB:Database Locking Mode=0;" _

&"Jet OLEDB:Global Partial Bulk Ops=2;" _

&"Jet OLEDB:Global Bulk Transactions=1;"), _ Destination:=Range("$A$1")).QueryTable

434

Глава 5. Основы разработки программного обеспечения

.CommandType = xlCmdTable

.CommandText = Array("Список")

.PreserveFormatting = True

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.SourceDataFile = "C:\AGTU\Контингент.accdb"

.ListObject.DisplayName = "Табл_Контингент.accdb"

.Refresh BackgroundQuery:=False End With

End Sub

Результат выполнения данной программы – таблица данных (ListObject) на рабочем листе (см. рисунок 5.54), работа с которой выполняется с использованием контекстной вкладки Работа с таблицами.

Рисунок 5.54. Результат переноса данных из базы Access в Excel

Синтаксис команды добавления таблицы на рабочий лист следую-

щий:

ListObjects.Add(SourceType, Source, LinkSource, _

HasHeaders, Destination)

где SourceType – тип данных, равен 0 для внешних данных;

Source – данные, представлены в данном примере массивом строк таблицы типа QueryTable, которые определяются строкой установления связи с базой данных Access с использованием провайдера Microsoft Jet OLEDB. Параметры строки определяются используемым типом связи, их синтаксис и описание можно найти в справке языка

435

Глава 5. Основы разработки программного обеспечения

VBA системы Microsoft Access;

LinkSource и HasHeaders – по умолчанию имеют значение

True;

Destination – объект Range, специфицирующий адрес ячейки левого верхнего угла таблицы.

Другой вариант программного кода получения данных таблицы Список базы данных Контингент, расположенной на Microsoft SQL Server’е:

Sub MSSQLServer_Контингент()

' Данные с сервера, база Контингент str_connect = "Provider=SQLOLEDB.1;" _

&"Integrated Security=SSPI;" _

&"Auto Translate=True;" _

&"Initial Catalog=Контингент;" _

&"Data Source=.;" _

&"Workstation ID=LKSN"

Set myList = ActiveSheet.ListObjects.Add(SourceType:=0, _ Source:=Array("OLEDB;" & str_connect & ";" _

& "Tag with column collation when possible=False"), _

Destination:=Range("$A$1")).QueryTable myList.CommandType = xlCmdTable myList.PreserveFormatting = True myList.ListObject.DisplayName ="База_Контингент_Список " myList.CommandText = _

Array("""Контингент"".""dbo"".""Список""")

myList.Refresh End Sub

Результат работы будет выглядеть аналогично рисунку 5.54, однако для колонки с типом данных Дата необходимо будет задать этот тип в Excel’е вручную. Пример с использованием библиотеки доступа к данным

ADO:

Sub ADO_MS_SQL_Server_Контингент() Dim cnnConnect As ADODB.Connection Set cnnConnect = New ADODB.Connection

cnnConnect.Open "Provider=SQLOLEDB;" & _ "Data Source=(LOCAL);" & _

"Integrated Security=SSPI;" & _

"Workstation ID=LKSN;" & _

"Initial Catalog=Контингент"

436

Глава 5. Основы разработки программного обеспечения

Set rstRecordset = New ADODB.Recordset rstRecordset.Open _

Source:="Select * From Список", _

ActiveConnection:=cnnConnect, _ CursorType:=adOpenDynamic, _ LockType:=adLockReadOnly, _

Options:=adCmdText

With ActiveSheet.QueryTables.Add( _

Connection:=rstRecordset, _

Destination:=Range("A1"))

.Name = "Список"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = True

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.Refresh BackgroundQuery:=False End With

В этом случае в программе используется создание двух объектов библиотеки Microsoft ADO:

1)Set cnnConnect = New ADODB.Connection

–строка связи с базой данных;

2)Set rstRecordset = New ADODB.Recordset

–набор записей таблицы базы данных.

Библиотеку ADO нужно подключить в системе VBA в пункте меню Tools – References…, как показано на рисунке 5.55.

437

Глава 5. Основы разработки программного обеспечения

Рисунок 5.55. Подключение дополнительных библиотек ADO и DAO в системе VBA

Результат работы программы показан на рисунке 5.56. В этом случае данные представлены, как обычная информация в ячейках Excel с соблюдением формата Дата-время.

Рисунок 5.56. Данные Microsoft SQL Server’а с использованием ADO

Еще один пример – получение данных с сервера баз данных Oracle с использованием драйвера ODBC и запроса:

Sub Запрос_Ora_TTCCOM130203()

' Запрос_Ora_TTCCOM130203

438

Глава 5. Основы разработки программного обеспечения

With ActiveSheet.ListObjects.Add(SourceType:=0, _

Source:=Array(Array("ODBC;DSN=Oracle;UID=BAAN;;" & _ "DBQ=BAAN5 ;DBA=W;APA=T;EXC=T;" & _ "XSM=Owner;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;" & _ "RST=T;GDE=T;FRL=F;BAM=IfAllSuccessful;MTS=F;" & _ "MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")), _

Destination:=Range("$A$1")).QueryTable

.CommandText = Array("SELECT TTCCOM130203.T$NAMA," & _

"TTCCOM130203.T$NAMC, TTCCOM130203.T$NAME, " & _

"TTCCOM130203.T$PSTC, TTCCOM130203.T$DTLM " & _

Chr(13) & Chr(10) & "FROM BAANDB.TTCCOM130203" & _ TTCCOM130203 & Chr(13) & "" & Chr(10) & _

"WHERE (TTCCOM130203.T$DTLM > " & _

"{ts '2005-04-01 23:59:59'})" & _ "AND (Not TTCCOM130203.T$PSTC=' ')")

.FillAdjacentFormulas = False

.PreserveFormatting = True

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.ListObject.DisplayName = "Query_from_Oracle"

.Refresh BackgroundQuery:=False

End With

End Sub

Результаты выполнения запроса к базе данных Baan5 на сервере Oracle показаны на рисунке 5.57.

Рисунок 5.57. Данные с сервера Oracle

439

Глава 5. Основы разработки программного обеспечения

5.3.2.3Программирование на языке VBA в Microsoft Office Access

Всистеме Access также существует объект Access.Application, как и в двух предыдущих. С использованием его можно запустить в работу Access, его свойства и методы позволяют настроить параметры пользовательского интерфейса, получить доступ к некоторым групповым операциям с таблицами, к компонентам форм и отчетов, к программным модулям и пр.

При разработке клиентских приложений для работы с базой данных в виде экранных форм системы Access могут использоваться методы и свойства этого объекта и всех вложенных в него объектов (прежде всего полей) с использованием библиотеки Microsoft Access Object Library.

При написании программ на языке VBA используется библиотека

Visual Basic For Application.

Для выполнения сложных расчетных операций с данными таблиц базы данных в системе Access необходимо использовать библиотеки ADO

(Microsoft ActiveX Data Objects) или DAO (Microsoft Data Access Objects), о

которых упоминалось ранее, как о библиотеках, обеспечивающих подключение к базам данных и выполнение операций с их данными из самых разных программных систем (см. раздел 5.3.1.12 Работа с объектами). Библиотека ADO более современная, чем DAO, считается, что она содержит более развитые возможности работы с данными. Основы работы в этих двух системах очень близки, главным объектом в них на уровне базы данных является Recordset (набор записей).

Recordset – временная таблица (cursor), созданная из записей таблицы базы данных или в результате выполнения SQL-запроса

Cинтаксис SQL-запросов рассматривался ранее в разделе 3.3.4.6 Ис-

пользование запросов.

Просмотреть список всех используемых библитотек и подключить при необходимости другие библиотеки можно в окне References окна Microsoft Visual Basic системы Access (см. ранее рисунок 5.55).

Для объекта Recordset как в ADO, так и в DAO определено большое количество свойств и коллекций, методов и событий, краткое описание их для ADO приведено в Приложении 11. Например, определить, находимся ли мы в конце Recordset, можно с использованием метода EOF этого объекта, узнать количество записей позволяет метод RecordCount, добавить новую запись в Recordset позволяет метод AddNew, переместиться на следующую запись – MoveNext, создать временный набор записей (т. е. сам объект

Recordset) – метод Open и т. д.

440