Я родом из DOS. Кто-нибудь помнит, что это такое? Кто-нибудь помнит те компьютеры, на которых это крутилось? А какие усилия прилагались для того, чтобы сделать программу с минимальными требованиями к памяти и диску. Как использовали сначала EMS, а потом X MS с одной единственной целью - оптимизация. Много времени с тех пор прошло (старею что ли?).

Сейчас другие времена (наконец-то!). Кругом Win32, OLE, пара-тройка гигабайт на процесс, «безразмерные» диски. Кто сейчас придает значение лишним четырем килобайтам? Сотня килобайт - туда, сотня - сюда. Вот и я сейчас буду утверждать, что интеграция прило жений (мегабайты потерь оперативной памяти) - это правильно. Да, наверно это правильно, когда ты начинаешь использовать функциональность программ, которых ты не писал. Просто, эти самые программы умеют больше, чем можешь ты сам. А аналог написать слабо - пару лет попыхтишь и на пенсию по несостоятельности (мне уже давно пора!).

А зачем оно нужно?

Собственно, цель этой статьи мне понятна - поделиться своим опытом с народом. Делюсь…

Итак, зачем нам, лучшим в мире программистам, нужен Excel, порождение "злого" гения Microsoft? Конечно, часто это лишнее - «юзать» Excel для отчетов. Напечатать «платежку» можно и в QReport-е. Но…

Есть заказчики, готовые отдать «кучищи» денег за то, что они будут знать все и всегда о своем предприятии. Да еще, чтоб это было красиво и со вкусом.

Приезжает один из моих заказчиков (немец - они повсюду! курорты Испании просто куплены ими - это знаю наверняка) на свое местное предприятие и начинает задавать интересные вопросы. Как трудились за время его отсутствия, сколько продукции выпустили, кому с колько отгрузили, в разных валютах, итого в USD и пр.? А я ему в ответ открываю отчет, неслабый такой, - сводная таблица по движению готовой продукции (посвященные знают, что это 40-ой счет в бухгалтерии). А в ней одних PageField-ов десяток. И на каждый е го вопрос я начинаю отвечать не напрягаясь, потихоньку перетаскивая поля таблицы туда-сюда, фильтрую кое-что, строю диаграммы. Что, вы думаете, было потом? Он, как маленький ребенок, сидел за этой сводной таблицей несколько часов, все восхищался. И правил ьно, наши программисты круче ихних! Заодно и мы спокойно поработали (ему занятие нашлось). О деньгах тут вообще не говорим.

Потом я ему показал, как эту самую сводную таблицу в Сеть можно опубликовать. Сейчас просит, чтоб ему доступ из Германии сделали к этой табличке. Мы, конечно, рады стараться.

Я бы привел еще несколько примеров, но, думаю, читатели уже поняли меня. Excel - вещь практически незаменимая во всяческих анализах (не путать с поликлиникой). А для тех, кто не понял, я еще напишу. Отдельно.

Так как же с ним работать?

А просто. Создал "Excel.Application", использовал его по назначению, "убил" и готово. Вот именно об этом я и попытаюсь написать здесь.

Важно!

Параллельно с написанием статьи создавался демо-проект (точнее два - для Delphi 4 и 5), где вы сможете найти весь код примеров статьи. Проект для Delphi 4.0 использует импортированную Type Library из Excel 97. Здесь я использую ранее связывание, ибо Creat eOLEObject отлично описал мой любимый классик в "Delphi 4 Unleashed" (мне ли с ним тягаться?). Кроме того, обращайтесь к комментариям в исходных текстах этого проекта. Местами там написано намного понятней, нежели здесь. Delphi 5 содержит более удобный ме ханизм импорта библиотек типов с поддержкой событий и прекрасной генерацией ко-классов. Специально для счастливых обладателей Delphi 5 (я тоже им являюсь) я создал проект, но уже применительно к TexcelApplication (правда ли, что импортированный MS Office есть только в версии Enterprise?). Примеры кода я буду приводить сначала для Delphi 4, потом для Delphi 5. Заранее приношу прощения за дублирование информации в комментариях и в статье - писал сразу везде. И еще. Эффективная работа с Excel-ом из Delphi-приложений немыслима без знания одной важной вещи. И имя ей - интерфейс. Мне, конечно, хотелось бы написать о принципах работы с интерфейсами здесь, в этой статье. Более того, я обещал сделать это самой Корол еве. Но...

Мне ли (совсем еще не профессионалу - и это так!) пытаться сделать это лучше, чем классики этой области. Я честно признаюсь, что не смогу этого сделать быстро (в небольшом объеме) и качественно. Поэтому всякого, не знакомого еще с этой областью программир ования, я с глубочайшими извинениями отсылаю к книге Чеппела "OLE Inside".

Достойную помощь (уже применительно к Delphi) может вам оказать "Delphi 4 Unleashed" Чарльза Калверта.

Создание экземпляра Excel.Application.

Модуль импортированной Excel TLB (неважно, для D4 или D5) содержит описания всех интерфейсов, которые правильные программисты из Microsoft решили выставить наружу. Там есть все необходимое: типы, константы и интерфейсы. Этого вполне достаточно для работы с Excel-ом из Delphi-приложения (во написал! а что еще нужно-то?). Я создаю Excel для последующего его использования с помощью такого кода:

Delphi 4.0

procedure TForm1.CreateExcel(NewInstance: boolean);
var IU: IUnknown;
    isCreate: boolean;
begin
  // FIXLSApp - private-поле у формы
  //            у меня в привычке добавлять букву I для всех интерфейсов
  //            понятно почему FI… ?
  if not Assigned(FIXLSApp) then begin // а зачем создавать, если уже есть?
    isCreate := NewInstance or
      (not SUCCEEDED( GetActiveObject(CLASS_Application_, nil, IU) ) );
    if isCreate then
      FIXLSApp := CreateComObject(CLASS_Application_) as _Application
    else
      FIXLSApp := IU as _Application;
  end;
end;

Этот достаточно простой код вы найдете практически во всех книгах, посвященных работе с интерфейсами. Как и везде, я напишу, что в результате выполнения этого кода создастся объект COM с CLSID-ом «{00024500-0000-0000-C000-000000000046}» (читайте и перечит ывайте Калверта, это не только укрепляет сон!).

Delphi 5.0

procedure TForm1.CreateExcel(NewInstance: boolean);
begin
  if not Assigned(IXLSApp) then begin
    FIXLSApp := TExcelApplication.Create(Self);
    if NewInstance then FIXLSApp.ConnectKind := ckNewInstance;
    FIXLSApp.Connect;
  end;
end;

В отличие от предыдущих версий, Delphi 5.0 предоставляет более удобный сервис при импорте библиотек типов. Большой шаг вперед - появление класса ToleServer с поддержкой событий. Теперь работа с существующими и создание новых OLE-серверов стала намного удо бней. Как видите, не приходится обращаться к низкоуровневым функциям. Впрочем, в Delphi 4.0 тоже существовал этот класс, только не от Borland. Отличная библиотека была создана Бином Ли (Binh Ly) в COM Nodes - это Threading COM Library. С легкой руки Алекс ея Вуколова (специальное спасибо!) я использовал ее для построения масштабируемых COM-серверов в сервисах WinNT.

Обращу ваше внимание только на параметр NewInstance. Он позволяет создать новый процесс. Я часто задаю себе вопрос - "А нужен ли NewInstance?". Одна копия процесса, все ж, требует меньше памяти. Но еще чаще я думаю - "Боже, как хорошо я сделал, когда созд ал новый процесс!". Почему? Если вы не хотите потерять уже открытые, но еще не сохраненные книги, экспериментируя даже с моими примерами, создавайте новый процесс. Печальный опыт научил меня использовать GetActiveObject только в случае полной уверенности в коде, который будет выполняться после. Поэтому, мой вам совет, тестируйте свои приложения только с NewInstance. Или закрывайте важные книги пред этим. Excel - хитрая программа, бывает, улетает в неизвестность, ни слова не сказав. Это не вина Microsoft. Это неудачное расположение звезд.

Как показать Excel, если он, разумеется, создан?

Вот здесь начинаются хитрости. Любой, читавший помощь по Excel VBA, скажет, что достаточно написать FIXLSApp.Visible := true. Не тут-то было. Я делаю так:

Delphi 4.0 / 5.0

procedure TForm1.ShowExcel;
begin
  if Assigned(FIXLSApp) then begin // а если он не создан?
    FIXLSApp.Visible[0] := true;
    if FIXLSApp.WindowState[0] = TOLEEnum(xlMinimized) then
      FIXLSApp.WindowState[0] := TOLEEnum(xlNormal);
    FIXLSApp.ScreenUpdating[0] := true;
  end;
end;

Зачем здесь условие на минимайз и какой-то ScreenUpdating? Давайте попробуем закомментировать эти строки, остаиви только Visible, запустить проект, создать Excel (кнопка CreateExcel), показать его (кнопка ShowExcel), минимизировать, вернуться в приложение и сделать снова ShowExcel. Да-да, Visible = true переводит фокус в минимизированный Excel, не восстанавливая размеры окна. Это ситуация, с которой я борюсь условием на xlMinimized. Но ScreenUpdating зачем?

Знающие люди говорят, что это свойство отвечает за перерисовку окон Excel. Это все равно, что DisableControls у TDataSet. Добавляет скорости, если в нем false. И это правда что, если выключить его во время длительных пересчетов, то быстрее пересчитается. Но мы, ведь, не выключали его. Зачем тогда эта строка?

Делаем так: комметируем эту строку, запускаем демо, CreateExcel, ShowExcel, закрываем его (можно кнопкой с крестиком в правом верхнем углу окна, кому нравится - через меню "Файл/Выход"). Знающие люди скажут, что Excel на самом деле не закрыт. Интерфейс мы не освободили, поэтому в TaskManager мы его и увидим. Итак, Excel по-прежнему у нас в руках. Мы имеем право сделать ему снова Show.

После такого действия у меня возникает ощущение, что я переплатил за свою видеокарту. Фокус в Excel-е, но я по-прежнему наблюдаю форму демо-проекта. Видимо, программисты из MS не рассчитывали на то, что кто-то закроет Excel, вызванный через создание Excel .Application, а потом захочет увидеть его снова. Но я-то захотел?!

Свойства Visible, WindowState и ScreenUpdating вызываются с каким-то непонятным индексом массива - 0. В модуле Excel TLB во многих свойствах и методах вы можете встретить параметр или индекс lcid. Не помню, у кого я это прочитал (Калверт или Канту), но с тех пор я туда передаю всегда 0. И все работает. LCID - это что-то насчет локализации. В MSDN написано "Indicates that the parameter is a locale ID (LCID)".

Спрячем Excel от посторонних глаз!

На свой процесс я всегда создаю один экземпляр Excel.Application. Уже пару лет все отчеты у меня - это отчеты Excel. Я написал несколько классов, которые мне очень помогают в этом. Сегодня у меня целая «отчетная» подсистема, зашитая в класс и обслуживающа я непомерно большие запросы моих пользователей. В промежутках между работой с отчетами нет необходимости «мозолить глаза» лишним окном в TaskBar-е. Вот и прячу я этот Excel. Это очень просто и комментариев, думаю, не требует:

Delphi 4.0 / 5.0

procedure TForm1.HideExcel;
begin
  if Assigned(FIXLSApp) then begin
    FIXLSApp.Visible[0] := false;
  end;
end;

Закроем Excel корректно!

Собственно говоря, при закрытии приложения Excel сам будет закрыт, если вы там не устели чего-нибудь отредактировать. И это правильно. Программисты Borland (Inprise до сих пор мне режет слух, да и некоторым в Inprise, судя по всему, тоже) позаботились об этом. Но я еще с Delphi 3 заимел дурную привычку освобождать все самостоятельно. Освобождать обычным присваиванием в nil (это касается проекта для D4). Труда это не составляет, да и проверка на Assigned удобна. Поэтому, и еще из кое-каких соображений, я д елаю так:

Delphi 4.0

procedure TForm1.ReleaseExcel;
begin
  if Assigned(FIXLSApp) then begin
    if (FIXLSApp.Workbooks.Count > 0) and (not FIXLSApp.Visible[0]) then begin
      FIXLSApp.WindowState[0] := TOLEEnum(xlMinimized);
      FIXLSApp.Visible[0] := true;
      Application.BringToFront;
    end;
  end;
  FIXLSApp := nil;
end;

Ну вот, написал только про nil, а кода - на полстраницы. Опишу ситуацию. Вы не запускали новый процесс, вы «законнектились» к уже существовавшему. В нем была открыта книга. Попробуйте: CreateExcel, ShowExcel, HideExcel (имеем право), ReleaseExcel. Если оставить только присваивание в nil, то существовавший процесс не будет выгр ужен (он же существовал до запуска нашего демо), но будет спрятан от пользователя с его открытой книгой.

Delphi 5.0

procedure TForm1.ReleaseExcel;
begin
  if Assigned(IXLSApp) then begin
    if (IXLSApp.Workbooks.Count > 0) and (not IXLSApp.Visible[0]) then begin
      IXLSApp.WindowState[0] := TOLEEnum(xlMinimized);
      IXLSApp.Visible[0] := true;
      if not(csDestroying in ComponentState) then Self.SetFocus;
      Application.BringToFront;
    end;
  end;
  FreeAndNil(FIXLSApp);
end;

Практически тот же код. Только в D5 вы работаете уже не с интерфейсом напрямую, а с экземпляром класса TexcelApplcation. Если посмотреть его предков, то можно увидеть, что это настоящий класс, освободить который просто необходимо. Поэтому вместо присваива ния в nil там написано FreeAndNil (помните такую процедуру?).

Лучшее решение - шаблоны

Excel, интегрированный с моими приложениями, хорош (для меня - программиста) только по одной причине. Я всегда создаю шаблоны и использую их потом при построении отчетов. Шаблоны позволяют мне избежать ручного (в исходном тексте) форматирования. В общем с лучае, алгоритм выглядит просто: по шаблону создается книга, каким-то образом помеченные области заполняются данными и… (а дальше все уже готово). Как я создаю книгу по шаблону:

Delphi 4.0 / 5.0

function TForm1.AddWorkbook(const WorkbookName: string): Excel8TLB._Workbook;
begin
  Result := nil;
  if Assigned(FIXLSApp) and (trim(WorkbookName) <> '') then begin
    Result := FIXLSApp.Workbooks.Add(WorkbookName, 0);
  end;
end;

В этом коде нет ничего сложного. В принципе при работе с Excel я мало находил мест, где что-либо сделать было бы сложно. Чаще достаточно прочитать справку по VBA или записать макрос (благо, Microsoft встроила в Excel хороший пишущий player). После выполне ния этого метода будет добавлена книга, близнец шаблона, с именем шаблона и порядковым номером (как "Книга1.xls" или "Книга228.xls"). Правда здесь есть одна тонкость. Эти «циферки» в имя книги Excel добавляет после поиска книг с таким же названием в катал оге по умолчанию. Я несколько раз наступал на грабли (больно!), когда пытался сохранять книги в другом каталоге и создавать новую - по этому же шаблону. К сожалению, не может эта «злобная» программа держать открытыми несколько книг с одинаковыми названиям и, несмотря на то, что они лежат в разных каталогах.

Как я помечаю области, в которые необходимо разместить данные? В Excel существует возможность объединить ячейки в группу и поименовать эту группу. В терминах Microsoft это объект Range (область). Для своего проекта я создал тестовую книгу "Test.xls", в ко торой на листе "Лист1" разместил область "TestRange" (см. рисунок). Более того, для ячеек этой области я указал форматы вывода (Field4 - дата, Field3 - красный цвет шрифта). Я надеюсь, что после переноса тестовых данных форматы сохранятся.

Что есть шаблон без данных в нем?

Существует масса способов передать данные в Excel, начиная с DDE и заканчивая обычным присваиванием (типа Cell.Value := NewValue ). Конечно, максимальную скорость передачи данных можно получить, только используя DDE. Но я отказался от этого пути из-за нек оторых ограничений и давно смущающего меня флажка в настройках Excel ("Игнорировать DDE-запросы"). Поэтому здесь я опишу менее эффективный, но работоспособный, путь решения этой проблемы. Итак, после нажатия кнопки CreateExcel имеем открытый шаблон с лист ом "Лист1" и областью с именем "TestRange". Для чистоты эксперимента (скорей из лени, великая вещь - собственная лень) я описал константный массив с тестовыми данными - TestDataArray. Именно эти данные я и передаю в ячейки области:

Delphi 4.0

procedure TForm1.btnDataToBookClick(Sender: TObject);
var LaunchDir: string;
    IWorkbook: Excel8TLB._Workbook;
    ISheet: Excel8TLB._Worksheet;
    IRange: Excel8TLB.Range;
    NewValueArray, V: OLEVariant;
    i: integer;
begin
  if Assigned(IXLSApp) then begin
    LaunchDir := ExtractFilePath( ParamStr(0) );
    IWorkbook := AddWorkbook( LaunchDir + 'Test.xls' );
    try
      ISheet := IWorkbook.Worksheets.Item['Лист1'] as Excel8TLB._Worksheet;
      IRange := ISheet.Range['TestRange', EmptyParam];
      NewValueArray := VarArrayCreate([0, 20, 1, 4], varVariant);
      for i := 0 to 20 do begin
        NewValueArray[i, 1] := TestDataArray[i].V1;
        NewValueArray[i, 2] := TestDataArray[i].V2;
        NewValueArray[i, 3] := TestDataArray[i].V3;
        NewValueArray[i, 4] := date + i;
      end;
      IRange.Value := NewValueArray;
    finally
      IRange := nil;
      ISheet := nil;
      IWorkbook := nil;
    end;
  end;
end;

Delphi 5.0

procedure TForm1.btnDataClick(Sender: TObject);
type
var LaunchDir: string;
    IWorkbook: Excel97.ExcelWorkbook;
    ISheet: Excel97.ExcelWorksheet;
    IRange: Excel97.Range;
    NewValueArray, V: OLEVariant;
    i: integer;
begin
  if Assigned(IXLSApp) then begin
    LaunchDir := ExtractFilePath( ParamStr(0) );
    IWorkbook := AddWorkbook( LaunchDir + 'Test.xls' );
    try
      ISheet := IWorkbook.Worksheets.Item['Лист1'] as Excel97.ExcelWorksheet;
      IRange := ISheet.Range['TestRange', EmptyParam];
      NewValueArray := VarArrayCreate([0, 20, 1, 4], varVariant);
      for i := 0 to 20 do begin
        NewValueArray[i, 1] := TestDataArray[i].V1;
        NewValueArray[i, 2] := TestDataArray[i].V2;
        NewValueArray[i, 3] := TestDataArray[i].V3;
        NewValueArray[i, 4] := date + i;
      end;
      IRange.Value := NewValueArray;
    finally
      IRange := nil;
      ISheet := nil;
      IWorkbook := nil;
    end;
  end;
end;

Я знаю, что многие профессионалы (к коим, к сожалению, я не принадлежу) взвоют от негодования при виде такого кода. Их можно понять, они знают намного более эффективные решения. Именно об этих решениях я и собираюсь написать в следующий раз. А вы пока не забудьте почитать комментарии в проекте-примере.

To be continue…

Excel - занимательная программа. Я давно с ней. Библиотека типов Excel громадна, как «Титаник» (хорошо, что не тонет). Я отдаю должное программистам, создавшим этот не менее замечательный, чем Delphi, продукт. И в одну статью все, что хочется написать, не вместишь (почему-то у меня уже шесть листов? Знаю, я слишком многословен). Поэтому ругайте меня и ждите продолжения…