Создание интерактивных офисных документов в Excel

В понятие офисное программирование я вкладываю более широкий смысл, чем программирование задач, возникающих в офисной деятельности. Сфера применения офисного программирования может быть значительно шире - его можно использовать для разработки любых приложений небольшого и среднего объема, всего того, что не входит в понятие "большого проекта". Эта система с успехом может применяться при обучении. Главное ее предназначение - использование в практической работе людей самых разных специальностей: бизнесменов, экономистов, журналистов, социологов. В связи с этим у программистов, занимающихся офисным программированием, сфера прикладной ориентации может быть весьма широка.

Система Microsoft Office задумывалась, в первую очередь, для решения офисных задач, а посему их рассмотрение представляет большой интерес. Поэтому я в этой главе и ряде последующих глав уделю таким задачам большое внимание. Чем занимаются в офисе? Ответ прост - людьми и документами. Людей оставим в покое и займемся документами. В современном офисе практически все документы должны быть интерактивными. Что такое интерактивный документ? Как сделать документ интерактивным? Решением этих вопросов сейчас и займемся.

Интерактивные документы и базы данных

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

На три момента хочу обратить внимание:

• Документы должны храниться достаточно долго. Поэтому без базы данных не обойтись. При этом не всегда обязательно хранить сам документ в той форме, в которой с ним работает пользователь. Часто достаточно хранить в базе данных информацию, связанную с документом. При этом важно следовать следующему принципу. Если информация, размещаемая на документе в момент его создания, уже имеется в базе данных, то ее следует переносить в документ из базы данных, а не создавать заново. Всякую новую информацию, полученную в процессе работы пользователя с документом, следует перенести в базу данных. Этот принцип работы с документами способствует поддержанию полноты и непротиворечивости данных.
• Чаще всего, именно Excel является тем приложением, где обрабатываются данные, хранящиеся в базах данных. Поэтому многие офисные документы, требующие интерактивность, создаются как документы Excel.
• Хотя всюду в тексте этой главы я буду говорить о базе данных, следует иметь в виду, что источники данных для документа, в общем случае, могут быть различные, - как структурированные (базы данных), так и не структурированные. Об этом достаточно подробно говорилось в предыдущих главах при рассмотрении объектов ADO.

Интерактивная форма документа "Счет-фактура" офиса РР

Я буду рассматривать вопросы создания интерактивных документов на конкретном примере. Надеюсь, он будет достаточно убедительным и позволит продемонстрировать, как многие из проблем, возникающих в процессе создания подобных документов, так и пути решения этих проблем.

Создание документа начнем не на пустом месте. В качестве такового рассмотрим один из документов офиса "РР", о котором уже шла речь в предыдущих главах, и для которого база данных уже создана. Будем предполагать, что создаваемый документ используется в этом офисе при оформлении заказов на книги, и представляет вариацию типового документа, называемого обычно "Счет-фактура".

Я напомню, что один из тезисов, пропагандируемых мною в офисном программировании, состоит в том, что программист является членом команды разработчиков, целью которой является создание системы документов. Программный проект, создаваемый программистом, не является самоцелью, - он лишь часть документа. Другие части документа могут быть созданы другими участниками совместной работы без программирования. Об этом я достаточно подробно говорил во всех предыдущих книгах по офисному программированию. В частности, в книге [2] я подробно описал процесс создания бланка "Счет-фактура" офиса РР, который был создан "руками", без программирования. Именно этот документ и послужит нам основой для дальнейшей работы. Вот как он выглядит:

Рис. 7.1. Документ "Счет-фактура" в начальной стадии

Наша цель - придать интерактивность уже созданному документу.

Программную работу с документом начнем с инициализации. И первый шаг в инициализации - подключение базы данных офиса РР. Это разумно, поскольку, как я уже говорил, интерактивный документ практически всегда взаимодействует с базой данных. Инициализация документа проводится, обычно, в момент открытия документа, а это значит - в обработчике события Open. Вот текст этого обработчика:

Private Sub Workbook_Open()
	'Инициализация документа
	InitAccount
End Sub

Заметьте, обычно, я пытаюсь строить простые обработчики событий, текст которых состоит из одной строчки, содержащей вызов соответствующей процедуры стандартного модуля. Здесь, и в дальнейшем, я буду следовать этому принципу. А посему, мне, прежде всего, пришлось добавить в программный проект стандартный модуль, которому я дал имя InterAction. Приведу заголовочную часть этого модуля:

'Модуль InterAction
'Обеспечивает интерактивность документа "Счет-фактура"
'Глобальные переменные
Public Con1 As New ADODB.Connection
Public Cmd1 As New ADODB.Command
Public Rst1 As New ADODB.Recordset

Уже из объявлений переменных можно понять, что связь с базой данных будет осуществляться через объекты ADO, ведь недаром их описанию я посвятил две главы. В модуль InterAction я буду помещать все вызываемые процедуры, приводимые в данной главе. Вот первая из этих процедур:

Public Sub InitAccount()
	'Установить соединение с базой данных
	CreateConnection
	'Конфигурирование команды
	ConfigCommand
	'Очистить поля бланка
	ClearFields
End Sub

Как видите, при инициализации документа решатся три задачи - устанавливается соединение с базой данных, задается исходная конфигурация объекта Command и производится чистка полей бланка. Каждая из этих задач решается соответствующей процедурой. О чистке поговорим чуть позже, а вот текст процедуры CreateConnection привести самое время:

Public Sub CreateConnection()
	'Создание соединения с базой данных офиса РР
	Dim strConnStr As String, PathDB As String
	PathDB = ThisWorkbook.Path & "\dbPP2000.mdb"
	If Con1.State = adStateOpen Then Con1.Close	'закрыть соединение
		
		'Конфигурирование соединения Con1
		Con1.Provider = "Microsoft.jet.oledb.4.0"
		Con1.ConnectionString = "Data Source=" & PathDB
		'Открытие соединения
		Con1.Open
			'печать характеристик соединения
'	Debug.Print "Attributes = ", Con1.Attributes
'	Debug.Print "CommandTimeout = ", Con1.CommandTimeout
'	Debug.Print "ConnectionString = ", Con1.ConnectionString
'	Debug.Print "ConnectionTimeout = ", Con1.ConnectionTimeout
'	Debug.Print "CursorLocation = ", Con1.CursorLocation
'	Debug.Print "DefaultDatabase = ", Con1.DefaultDatabase
'	Debug.Print "Mode = ", Con1.Mode
'	Debug.Print "Properies.Count = ", Con1.Properties.Count
'	Debug.Print "State = ", Con1.State
'	Debug.Print "Version = ", Con1.Version
End Sub

Я не буду особенно комментировать эту процедуру, да и другие процедуры, где речь идет о связи Excel с базой данных. Я надеюсь, что перед этой главой Вы уже прочли три предыдущие главы, посвященные этому вопросу. Заметьте, что процедура CreateConnection сохранила свое название и лишь слегка видоизменена в сравнении с текстом, приведенным в главе 5.

Теперь, когда некоторые начальные действия уже выполнены, можно переходить к решению основной задачи - придание интерактивных свойств нашему документу. Заметьте, в бланке документа можно выделить несколько частей или разделов. Некоторые из них постоянные и не меняются в процессе работы с бланком. К ним, например, относятся шапка документа и утверждающие подписи. В шапке указывается название организации, логотип, реквизиты. Эти части документа нас сейчас интересовать не будут. Выделим три раздела бланка "Счет-фактура", в которые добавим интерактивность. Эти разделы условно будем называть:

• Реквизиты покупателя (заказчика).
• Сотрудники.
• Заказы.

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