oldi

Вариации на тему 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