Вариации на тему Digital Dashboard. Часть 3
Малоизвестные возможности Excel
Электронная почта
Одним из основных компонентов Digital Dashboard является электронная почта. В литературе, посвященной DD, часто можно встретить примеры специальной обработки папки «Входящие», сделанные для фильтрации и упорядочивания большого объема корреспонденции. Однако возможности стандартных почтовых агентов по сортировке входящей почты практически ничуть не уступают самодельным средствам, а при умеренном объеме почты такие средства и вовсе не нужны.
Кроме того, для обращения к почтовому агенту у нас уже есть необходимые средства. Если в качестве почтового агента используется Outlook, то мы имеем команды, открывающие любую из его папок. Если в качестве почтового агента используется Outlook Express, Netscape Messenger или любая другая программа, то ее можно запускать с помощью ShellApplication. Ниже приведен фрагмент кода HTML, который можно использовать в DD для запуска Outlook Express и Netscape Messenger:
<tr> <td><img src="images/olexp.gif" alt="Outlook Express" border="0" WIDTH="16" HEIGHT="16" onClick="parent.document.location= 'vba:ShellApplication(C:\\Program Files\\ Outlook Express\\msimn.exe)'"></td> <td><a href="vba:ShellApplication(C:\Program Files\ Outlook Express\msimn.exe)">Outlook Express</a></td> </tr> <tr> <td><img src="images/nsmsg.gif" alt="Netscape Messenger" border="0" WIDTH="16" HEIGHT="16" onClick="parent.document.location= 'vba:ShellApplication(C:\\Program Files\\ Netscape\\Communicator\\Program\\ netscape.exe -mail, C:\\Program Files\\Netscape\\ Communicator\\Program)'"></td> <td><a href="vba:ShellApplication(C:\Program Files\ Netscape\Communicator\Program\ netscape.exe -mail, C:\Program Files\Netscape\Communicator\ Program)">Netscape Messenger</a></td> </tr>
Здесь, как и в примере с Outlook, вызов приложения осуществляется двумя способами — с помощью события onClick элемента <IMG> и с помощью элемента <A>. Обратите внимание, что для вызова Netscape Messenger используется параметр-mail, и устанавливается определенный рабочий каталог.
Но DD — не просто еще одна панель вызова приложений. Она предназначена для того, чтобы обеспечить максимальные удобства на рабочем месте. Что касается почты, то такими удобствами являются возможность быстрого составления исходящих писем без предварительного открытия приложения почтового агента, отправка писем стандартного содержания и автономная работа с адресной книгой.
Чтобы обеспечить такие возможности, потребуются элементы управления ActiveX MAPIMessages и MAPISession из библиотеки Microsoft MAPI Controls. Разместить эти элементы управления можно на любой форме VBA-проекта.
В качестве примера использования этих элементов разработаем три новые команды DD:
'-- ComposeMail() Case "COMPOSEMAIL" ComposeMail '-- AddressBook() Case "ADDRESSBOOK" AddressBook '-- StandardMail(Address, Subject, Note) Case "STANDARDMAIL" If UBound(varArray) >= 4 Then StandardMail varArray(1), varArray(2), varArray(3) Else ComposeMail End If
Базовая процедура здесь — это ComposeMail. В примере предполагается, что элементы управления MAPI расположены в контейнере FormHidden и имеют имена MAPIMessages1 и MAPISession1:
Public Sub ComposeMail() '-- Создать сообщение Load FormHidden With FormHidden '-- Log onto the mail system. On Error Resume Next .MAPISession1.DownLoadMail = False .MAPISession1.NewSession = False .MAPISession1.LogonUI = False .MAPISession1.SignOn If Err <> 0 Then MsgBox "Logon Failure: " + Error$ Exit Sub Else .MAPIMessages1.SessionID = .MAPISession1.SessionID .MAPIMessages1.Compose .MAPIMessages1.Send True End If '-- Log off from the mail system. On Error Resume Next .MAPISession1.SignOff If Err <> 0 Then MsgBox "Logoff Failure: " + Error$ Else .MAPIMessages1.SessionID = 0 End If End With Unload FormHidden End Sub
Процедура AddressBook имеет точно такое же начало и окончание сессии, только работа с MAPIMessages1 ведется несколько иначе:
If Err <> 0 Then MsgBox "Logon Failure: " + Error$ Exit Sub Else .MAPIMessages1.SessionID = .MAPISession1.SessionID .MAPIMessages1.Compose .MAPIMessages1.Show If .MAPIMessages1.RecipAddress Then .MAPIMessages1.Send True End If End If
На практике это выглядит следующим образом. Прежде всего открывается адресная книга почтового агента (метод .Show). Если пользователь перемещает какой-либо адрес в поле адреса получателя, то открывается окно для формирования сообщения этому адресату.
Наконец, последняя процедура позволяет формировать письма стандартного содержания. Это могут быть как стандартные получатели, так и стандартные тексты и темы писем. А может, и все три поля вместе.
Процедура StandardMail имеет три параметра:
Public Sub StandardMail(varAddress, varSubject, varNote) ‘— Создать стандартное сообщение
Открытие и закрытие сессии MAPI выполняется так же, как и в базовой процедуре ComposeMail, и отличие тоже только в работе с MAPIMessages1:
If Err <> 0 Then MsgBox "Logon Failure: " + Error$ Exit Sub Else .MAPIMessages1.SessionID = .MAPISession1.SessionID .MAPIMessages1.Compose .MAPIMessages1.RecipAddress = varAddress .MAPIMessages1.MsgSubject = varSubject .MAPIMessages1.MsgNoteText = varNote .MAPIMessages1.Send True End If
Любители компактных программ могут объединить эти три процедуры в одной, тем более что почти все операторы в последовательности одинаковы.
Прежде чем закончить с почтой, необходимо сделать следующие замечания. При использовании процедур MAPI в варианте с классом InternetExplorerWithEvents необходимо закомментировать операторы вывода сообщений об ошибках MsgBox; лучше, если Internet Explorer выведет их самостоятельно с помощью функции alert(). При формировании текста стандартных писем понадобятся символы перевода строки, которые в DD придется закодировать особым образом (например, %10). И наконец, следует помнить, что стандартные письма можно формировать только с помощью HTML-кода начиная с четвертой версии Internet Explorer.
 |
|
Запросы
Все предшествующие решения сводились лишь к вызовам из DD полезных программ, форм или к открытию офисных документов. Возврат данных после обработки средствами Office хотя и был обозначен, но практического применения не получил. Постараемся восполнить этот пробел с помощью исключительно важных для принятия решений компонентов — запросов к различным источникам информации.
Сами запросы могут быть реализованы многими способами. В стандартных DD это прежде всего использование SQL Server. Мы же ограничимся только простыми средствами Microsoft Office, доступными любому пользователю. Для этой цели обратимся к объектам QueryTable Microsoft Excel. Внешне QueryTable представляет собой таблицу, формируемую автоматически. Существует два основных способа формирования QueryTable. Первый использует вспомогательное приложение Microsoft Query, строящее SQL-запросы к самым разнообразным базам данных. Второй способ заключается в выполнении так называемых Web-запросов, получающих данные из Internet или Intranet точно так, как это делает обозреватель Web-страниц, когда выполняет методы GET и POST. Кстати, использовать механизм Web-запросов Excel настолько просто и удобно, что можно гораздо эффективнее реализовать многие привычные процессы получения информации из Всемирной сети, начиная от курсов валют и ценных бумаг и кончая программами телепередач, прогнозами погоды и даже выборками ссылок, запрашиваемых в поисковых системах. Детальное рассмотрение механизмов запросов выходит за рамки настоящей статьи, поэтому мы ограничимся только понятиями, имеющими принципиальное значение для поставленной задачи.
Прежде всего, для выполнения запроса необходимо открыть рабочую книгу Excel. Таким образом, два параметра будущей команды уже известны — это путь к книге и признак «Только чтение». Далее, поскольку запросы входят в коллекцию объектов листа, а не книги, то одним из параметров должно стать имя страницы Worksheet. Наконец, необходимо указать номер или имя самого запроса в коллекции, что является четвертым параметром, требуемым для идентификации объекта.
Ценность запросов заключается в том, что они способны представлять актуальные данные. Для актуализации используется метод .Refresh, эквивалентный выполнению команды «Обновить данные» в меню Excel. Поскольку заранее неизвестно, будет ли пользователь довольствоваться имеющимся состоянием или ему необходимы самые свежие данные, такой параметр также необходимо предусмотреть в команде DD.
Результаты запроса всегда представляются в виде таблицы, адрес левой верхней ячейки которой помещается в свойство .Destination объекта QueryTable. Если запрос адресован однозначно, то c помощью свойств .Destination.CurrentRegion определяется и вся результирующая таблица. Однако это не всегда будет то, что требуется. Запросы Excel замечательны тем, что позволяют комбинировать данные запроса с формулами Excel и формировать новые таблицы или графики путем вторичной обработки. Чтобы предусмотреть возможность получения на странице DD не только первичных, но и обработанных данных, требуется задать и адрес вторичной таблицы.
Кроме того, в команде DD совершенно необходимо указать имя элемента Web-страницы, куда следует поместить HTML-код результата. Принимая во внимание то, что страница DD спроектирована нами как совокупность большого количества фреймов, необходимо также указывать и имя фрейма, принимающего информацию.
Таким образом, синтаксис команды и соответствующий оператор Case получились следующими.
'-- Query(Path, -- путь к Workbook ' ReadOnly, -- флаг «только чтение» ' Sheet, -- имя Worksheet ' Cell, -- адрес вторичной таблицы или 0 ' Query, -- номер или имя запроса на листе ' Update, -- флаг необходимости обновления ' Target, -- имя объекта в DD ' IFrame) -- имя фрейма или 0 Case "QUERY" strParam = varArray(1) If UBound(varArray) >= 9 Then QueryProc strParam, varArray(2), varArray(3), _ varArray(4), varArray(5), varArray(6), _ varArray(7), varArray(8) Else blnFlag = varArray(2) ExcelProc strParam, False, blnFlag End If
Оператор IF здесь использован для того, чтобы несколько улучшить ситуацию в том случае, когда нужное количество параметров не было принято. Вместо отказа просто открывается рабочая книга, адресованная первым параметром, а дальше пусть пользователь разбирается сам — ищет и выполняет запрос вручную.
Рассмотрим, как может выглядеть процедура QueryProc:
Public Sub QueryProc(strPath As String, varReadOnly, _ varSheet, varCell, varQuery, varUpdate, _ varTarget, varFrame) '-- Запросы Excel Dim strSheet As String ' имя листа Dim strCell As String ' адрес ячейки Dim intQuery As Integer ' номер запроса Dim blnReadOnly As Boolean ' только чтение Dim blnUpdate As Boolean ' обновить Dim objExcel As Excel.Application Dim objBook As Excel.Workbook Dim objSheet As Excel.Worksheet Dim objQuery As Excel.QueryTable Dim objRange As Excel.Range strSheet = varSheet intQuery = varQuery blnReadOnly = varReadOnly blnUpdate = varUpdate '-- Проверка, выполняется ли Microsoft Excel. On Error Resume Next ' Отложенный перехват ошибок. '-- функция GetObject, вызванная без указания '-- первого аргумента, '-- возвращает ссылку на экземпляр приложения. '-- Если это приложение '-- не запущено, возвращается ошибка. Set objExcel = GetObject(, "Excel.Application") If (Err.Number <> 0) Then Err.Clear ' Очищаем объект Err на случай ошибки. Set objExcel = New Excel.Application End If With objExcel .Visible = True .WindowState = xlMinimized Set objBook = .Workbooks.Open(strPath, , _ blnReadOnly) Set objSheet = objBook.Worksheets(strSheet) objSheet.Select Set objQuery = objSheet.QueryTables(intQuery) If blnUpdate Then objQuery.Refresh If varCell = "0" Then Set objRange = _ objQuery.Destination.CurrentRegion Else strCell = varCell Set objRange = _ objSheet.Range(strCell).CurrentRegion End If gstrHTML = "" MakeTableHTML objRange ExportIntoDD varFrame, varTarget End With End Sub
Из примера видно, что эта процедура не особенно отличается от представленной выше процедуры ExcelProc, открывающей рабочую книгу в приложении Excel. Здесь только объявляется больше объектов для того, чтобы добраться до конкретной таблицы (objRange). Непосредственная работа выполняется процедурами MakeTableHTML и ExportIntoDD. Первая процедура формирует код HTML, соответствующий выбранной таблице, а вторая помещает этот код на страницу DD.
Процедура MakeTableHTML достаточно универсальна и может применяться для экспорта в формате HTML любых таблиц Excel, адресуемых в виде объекта Range. Для ее работы требуются константы, задающие необходимые тэги:
Const BEG_TABLE = "<table width=""100%"" _ cellpadding=""3"" cellspacing=""3"">" Const BEG_TR_ODD = " <tr class=""lineOdd"">" Const BEG_TR_EVEN = " <tr class=""lineEven"">" Const BEG_TH = " <th" Const BEG_TD = " <td" Const END_TH = " </td>" Const END_TD = " </td>" Const END_TR = " </tr>" Const END_TABLE = "</table>"
Результат генерации помещается в глобальную переменную, которая используется в дальнейшем для установки свойства .innerHTML в элементе DD:
Public gstrHTML As String ' HTML код
Private Sub MakeTableHTML(objTable As Excel.Range)
'-- Преобразование таблицы в HTML
Dim objTr As Excel.Range ' строка
Dim objTd As Excel.Range ' ячейка
Dim strTag As String ' тэг начала
Dim strEnd As String ' тэг конца
Dim intRow As Integer ' номер строки
intRow = 0
PrintHTML BEG_TABLE
For Each objTr In objTable.Rows
intRow = intRow + 1
If intRow Mod 2 = 0 Then
PrintHTML BEG_TR_EVEN '-- even row
Else
PrintHTML BEG_TR_ODD '-- odd row
End If
For Each objTd In objTr.Cells
If intRow = 1 Then
strTag = BEG_TH
strEnd = END_TH
Else
strTag = BEG_TD
strEnd = END_TD
End If
If objTd.MergeCells Then
If objTd.MergeArea.Range("A1").Address = _
objTd.Address Then
If objTd.MergeArea.Rows.Count > 1 Then
strTag = strTag & " rowspan=""" & _
objTd.MergeArea.Rows.Count & """"
End If
If objTd.MergeArea.Columns.Count > 1 Then
strTag = strTag & " colspan=""" & _
objTd.MergeArea.Columns.Count & """"
End If
strTag = strTag & ">"
PrintHTML strTag & objTd.Value & LTrim(strEnd)
End If
Else
strTag = strTag & ">"
PrintHTML strTag & objTd.Value & LTrim(strEnd)
End If
Next objTd
PrintHTML END_TR
Next objTr
PrintHTML END_TABLE
End Sub
Как уже говорилось выше, процедура MakeTableHTML универсальна. Она корректно обрабатывает объединенные ячейки и оформляет четные и нечетные строки разными стилями. Прежде эта процедура использовалась для экспорта HTML-кода в текстовый файл, поэтому пришлось заменить оператор файлового вывода на специальную процедуру:
Private Sub PrintHTML(strLine As String) gstrHTML = gstrHTML & strLine ' Debug.Print strLine End Sub
Если раскомментировать оператор Debug.Print, то можно полюбоваться отпечатанным кодом таблицы, оформленным в виде правильной лесенки.
Наконец, завершает всю работу процедура ExportIntoDD:
Private Sub ExportIntoDD(varFrame As Variant, _ varItem As Variant) Dim objDoc As HTMLDocument Dim objFrame As HTMLIFrame Dim objFrameDoc As HTMLDocument Dim objItem As HTMLBaseElement gobjIE.IE.Visible = True Set objDoc = gobjIE.IE.Document With objDoc If varFrame = "0" Then Set objItem = .all.Item(varItem) Else Set objFrameDoc = .parentWindow(varFrame) Set objItem = _ objFrameDoc.Document.all.Item(varItem) End If objItem.innerHTML = gstrHTML End With End Sub
Это процедура одинаково хорошо справляется со вставкой содержимого как в основную страницу (varFrame = “0”), так и в любую Web-страницу, открытую в <IFRAME>.
На рис. 1 показан результат выполнения SQL-запроса (запрашивался список сотрудников) и динамической вставки списка в один из фреймов DD. В качестве источника информации использована учебная база данных «Борей», поставляемая с Microsoft Access.
Процедуры, представленные в этом разделе, можно применять не только к запросам. Например, можно просто копировать произвольные таблицы из документов Office в DD (другое дело, когда это может понадобиться).
|
|
Малоизвестные возможности Excel
Пожалуй, самым эффективным аналитическим инструментом в DD на сегодняшний день являются сводные таблицы — PivotTable. Для их применения разработан специальный элемент управления ActiveX PivotTable List, входящий в состав Microsoft Office Web Components. Данные для этого компонента могут быть представлены в формате локальных многомерных кубов OLAP, что уже само по себе требует специальной подготовки.
Как правило, сводные таблицы в DD хотя и оформляются в виде Web Parts, но отображаются на отдельных страницах, чтобы обеспечить пользователю достаточное пространство на экране для обозрения данных и всевозможных манипуляций. С учетом того, что анализируемые массивы могут достигать гигантских размеров (десятки мегабайт), очевидно, что требуется максимальное оперативное пространство для работы со сводными таблицами.
Но в таком случае сводные таблицы Excel оказываются ничуть не хуже специальных Web-компонентов: внешний вид и принцип работы у них одинаковы, а от наличия мощной поддержки основного приложения сводные таблицы, открытые в Excel, только выиграют.
Единственной проблемой, затрудняющей использование сводных таблиц Excel, является большой размер рабочих книг, в которых такие таблицы расположены. Конечно, это будет ощущаться только в случае, если рабочие книги со сводными таблицами поместить на сервере. Вероятнее всего накладные расходы, связанные с загрузкой книг Excel посредством FTP, сведут на нет все удовольствие от их использования.
Однако решение этой проблемы существует. Уже Excel 8 (Office’97) обладал возможностью динамически создавать сводные таблицы на основе данных, представленных в весьма компактной форме, а именно в формате HTML. Выглядит это следующим образом. Исходные данные в виде таблицы произвольного размера заключаются в тэги <TABLE>, <TR> и <TD>. В результате получается относительно небольшой текстовый файл. Если этот файл открыть в обозревателе Web-страниц, то ничего, кроме исходной таблицы с рамочками ячеек, не увидишь. Но если этот же файл открыть в Excel, то исходная таблица будет скрыта, а вместо нее появится элегантная сводная таблица со всеми присущими ей элементами управления. Секрет подобного «фокуса» заключается в том, что Microsoft придумала специальные расширения для тэгов, которые интерпретируются только в Excel при открытии HTML-документов. Существует два расширения для тэга <TABLE> — это CROSSTAB и CROSSTABGRAND, а также шесть расширений для тэгов <TH> и <TD> — ROWFIELD, COLFIELD, DATAFIELD, PAGEFIELD, AGGREGATOR, и SUBTOTAL. Ниже представлен код HTML-документа с такими расширениями:
<HTML> <BODY bgcolor=#FFFFFF> <TABLE border CROSSTAB CROSSTABGRAND=ROWCOLUMN> <TR> <TH ROWFIELD>Store # <TH>Date <TH PAGEFIELD>Channel <TH COLFIELD>Division <TH>Product <TH DATAFIELD AGGREGATOR="SUM">Units <TH>Price <TR> <TD>Store 2 <TD>1/1/96 <TD>Wholesale <TD>Brass <TD>Trumpet <TD>7 <TD>325 <TR> <TD>Store 6 <TD>1/8/96 <TD>Wholesale <TD>Electronic <TD>Keyboard <TD>8 <TD>795 . . . </table> </body> </html>
На рис. 2 показано, как выглядит этот документ, открытый в Excel.
Смысл и назначение расширений достаточно просты и понятны всем, кто имел дело со сводными таблицами Excel. Единственное, что можно добавить, это то, что атрибут AGGREGATOR, кроме SUM, может принимать значения: COUNT, AVERAGE, MAX, MIN, PRODUCT, COUNTNUMS, STDDEV, STDDEVP, VAR и VARP. Словом, все то, что допустимо использовать в разделе данных сводной таблицы. Кроме того, возможны комбинации нескольких значений, например:
<TH DATAFIELD AGGREGATOR=”SUM,AVERAGE”>Sales
Кроме специальных расширений для сводных таблиц, имеются расширения для установки фильтров (автофильтр), допустимые в тэгах <TD>, <TH>, <TABLE> или <TR>, например:
<TABLE border> <TR> <TD FILTER=ALL>Store # <TD FILTER=NONBLANKS>Date <TD FILTER=ALL>Channel <TD FILTER=ALL>Division
Кроме того, существуют расширения, позволяющие использовать в документах HTML формулы и стили (форматы данных) Excel.
Перечисленные средства способны значительно обогатить возможности DD, ведь в разработанной нами модели все документы являются равноправными. Офисные документы открываются так же легко, как и сами Web-страницы. А храниться на сервере все документы могут в формате HTML, минимизируя тем самым время, необходимое для их открытия в нужном приложении. Более того, именно технология DD позволяет раскрыть подобные малоизвестные возможности Office. Трудно себе представить, чтобы кто-то пытался открывать Web-страницы не с помощью обозревателя, а Excel, да еще выбирая файлы на сервере командой “Файл.Открыть…”. Тогда как при использовании разработанной выше команды DD “vba:OpenWorkBook”, помещенной в гиперссылку, это происходит просто и естественно.
|
|
Заключение
В работе, представленной вниманию читателя, описаны простые подходы к реализации новейшей информационной технологии. Оказывается, для ее внедрения не требуется полностью менять все программное обеспечение. Вполне удовлетворительных результатов можно достигнуть даже на базе Microsoft Office 97. Вместе с тем не стоит полагаться на то, что альтернативный подход к реализации DD исключает необходимость использования компонентов DDRK. Появление новых компонентов только приветствуется, ведь их приобретение и установка улучшают окончательный продукт.
Предложенные решения могут применяться и в обычных проектах. Например, внедренный WebBrowser позволит создавать в VBA-проектах эффектный интерфейс, в котором могут быть использованы элементы DirectX, не допускающие прямого включения в обычные формы и документы Office.
Отдельного внимания заслуживают и расширенные возможности по обработке специальных кодов HTML. Освоение этой темы позволит в Intranet обмениваться документами Office, представленными в очень компактной форме. Мощным инструментом для работы в Сети могут стать и Web-запросы Excel.
В любом случае, объединение возможностей Web-страниц и документов Office открывает широкие перспективы для творчества.
КомпьютерПресс 6'2001
