Работа с Excel из Delphi. Часть 1

Подготовка

Откроем нашу среду разработки Delphi (в моем случае это Delphi 2010). Создадим новый проект. Должен сказать, что работа с Excel происходит через объекты COM. Поэтому нам надо подключить в раздел USES нашего проекта модуль COMObj. Так же нам понадобится модуль ActiveX. Это будет выглядеть примерно вот так:

unit Unit1;
 
interface
 
uses
  Windows, …, ActiveX, COMObj;

Проверка наличия установленного Microsoft Excel в операционной системе

Теперь собственно нам нужно, как писалось выше, определить наличие Excel в системе. Если он не будет обнаружен, то мы сообщим об этом пользователю и завершим программу. Для этого создадим простенькую функцию и объявим её в секции private нашей формы TForm1:

…
private
    function CheckExcelInstalled(AValue: String): boolean;
…  
function TForm1.CheckExcelInstalled(AValue: String): boolean;
var
  FCLSID: TCLSID;
begin
  Result := (CLSIDFromProgID(PChar(AValue), FCLSID) = S_OK);
end;

function CLSIDFromProgID(pszProgID: POleStr; out clsid: TCLSID): HResult;

HKEY_LOCAL_MACHINE\SOFTWARE\Classes

то там можно будет обнаружить много разделов. Среди них есть как раз нужный нам «Excel.Application» (при условии, что данное ПО установлено в системе). К сведению: у этого раздела имеется еще два подраздела:

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Excel.Application\CLSID
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Excel.Application\CurVer

Параметры функции:
- pszProgID: строка, содержащая ProgID.
- clsid: получает CLSID.

Возвращаемые значения:
- S_OK - CLSID создан успешно.
- CO_E_CLASSSTRING - CLSID зарегистрированный для ProgID неверен.
- REGDB_E_WRITEREGDB - ошибка записи в реестр.
- E_OUTOFMEMORY - нет памяти.
- E_INVALIDARG - указывает что один или более аргументов неверны. Стоит проверить правильность строки pszProgID.
- E_UNEXPECTED - неизвестная ошибка.

Теперь на событии OnCreate формы TForm1 напишем следующий код:

procedure TForm1.FormCreate(Sender: TObject);
begin
  if not CheckExcelInstalled('Excel.Application') then
  begin
    Application.MessageBox(PChar('Для работы программы необходимо ' +
          'иметь установленное программное обеспечение Microsoft Excel.'#13#10
          + 'Приложение будет закрыто.'), 'Ошибка', MB_ICONERROR);
    Halt;
  end;
end;

Проверить, запущен ли Microsoft Excel

Далее давайте кинем на форму одну кнопку TButton. На событии OnClick будем писать код работы с Excel. Первое что мы сделаем, это добавим локальную переменную типа Variant для доступа к Excel с именем, например, FExcel:

var
  FExcel: Variant;

…
private
    …
    function CheckExcelRun(AValue: String; var ADest: Variant): boolean;
…  
function TForm1.CheckExcelRun(AValue: String; var ADest: Variant): boolean;
begin
  try
    ADest := GetActiveOleObject(AValue);
    Result := true;
  except
    Result := false;
  end;
end;

function GetActiveOleObject(const ClassName: string): IDispatch;

procedure TForm1.Button1Click(Sender: TObject);
var
  FExcel: Variant;
begin
  if not CheckExcelRun('Excel.Application', FExcel) then
    Exit;
end; 

Создание нового экземпляра Microsoft Excel

Создать новый экземпляр можно с помощью функции CreateOleObject. Давайте сразу напишем шаблон.

 procedure TForm1.Button1Click(Sender: TObject);
var
  FExcel: Variant;
begin
  try
    // Создаем новый экземпляр Excel
    FExcel := CreateOleObject('Excel.Application');
    FExcel.Visible := true;
    ... // Тут ваш код
  finally
    // Если переменная не пустая, то...
    if not VarIsEmpty(FExcel) then
    begin
      // ...отключаем диалог с вопросом сохранять ли файл при выходе или нет
      FExcel.DisplayAlerts := false;
      // Закрываем Excel
      FExcel.Quit;
      // Присваиваем неопределенный тип, освобождая при этом 
      // процесс excel.exe, чтобы он мог завершиться. Если этого не
      // сделать, то процесс останется висеть в памяти.
      FExcel := Unassigned;
    end;
  end;
end;

function VarIsEmpty(const V: Variant): Boolean;

Операции над программой Microsoft Excel из Delphi

Теперь давайте рассмотрим, что вообще можно делать с Excel из Delphi. Операций достаточно много. И все описать просто невозможно. Но если в моем описании не окажется нужного вам метода, то огорчаться не стоит. Методы можно узнать самому. Открываете Excel. Затем в меню Сервис – Макрос – Начать запись. Там жмем ОК и выполняем необходимые нам операции в Excel. Когда закончите, жмем на Стоп. Затем нажимаем комбинацию клавиш Alt+F11 и перед нами открывается редактор Visual Basic. Вот в нем слева вверху есть древовидный список. В нем находится папка с названием Modules. В свою очередь в ней располагаются модули. Вот эти модули и есть ваши макросы. Кликнем два раза на тот модуль, который мы только что записали (если это первая запись, то модуль будет один). И тут будут отображены все ваши действия в виде исходного кода на языке Visual Basic. Вам остается только перевести это дело на Delphi.
По поводу констант. В Delphi придется объявлять их самим. Посмотреть чему они равны можно в том же редакторе Visual Basic. Делается это так. Вписываем любую нужную нам константу в редакторе. Кликаем правой клавишей мыши, чтобы появилось контекстное меню. И там выбираем пункт «Quick Info». При этом будет показана всплывающая подсказка со значением этой константы. Все просто! Единственное оговорюсь. В Visual Basic будет показано так, например: &HFFFFEFF4, но Delphi этого не поймет. Надо объявлять в нем так: $FFFFEFF4.

FExcel.Visible := true;
Делать Excel видимым (True)/не видимым(False). При установке Visible в FALSE, программа Excel Так же пропадает из панели задач Windows.

FExcel.Application.EnableEvents := false;
Показывать (True)/не показывать(False) системные сообщения Excel. Рекомендую отключать сообщения при построении отчета. А сам код писать после создания экземпляра Excel. Это увеличит скорость создания отчета. Пример:

FExcel := CreateOleObject('Excel.Application');
FExcel.Visible := false;
FExcel.Application.EnableEvents := false;

FExcel.WindowState := xlMaximized;
Состояние окна Excel:
const xlMaximized = $FFFFEFD7 (или -4137) - развернуть Excel на весь экран.
const xlNormal = $FFFFEFD1 (или -4143) - восстановить Excel.
const xlMinimized = $FFFFEFD4 (или -4140) - свернуть Excel на панель задач.

FExcel.ScreenUpdating := false;
Включить (True)/отключить (False) перерисовку окон. Используется для ускорения работы макроса, т.к. в это время не обновляется экран. Я в своих проектах не использовал данный метод, но возможно построение отчета будет выполняться быстрее, если отключить обновление окон Excel. После построения отчета перерисовку окон можно будет включить обратно.

FExcel.Workbooks.Open(path);
Открытие существующей книги (где path - путь к фалу). Функция Open описана так:

function Open(
  const Filename: String; 
  UpdateLinks: Variant; 
  ReadOnly: Variant;
  Format: Variant; 
  Password: Variant; 
  WriteResPassword: Variant; 
  IgnoreReadOnlyRecommended: Variant; 
  Origin: Variant;
  Delimiter: Variant; 
  Editable: Variant; 
  Notify: Variant;
  Converter: Variant; 
  AddToMru: Variant
): Workbook;

UpdateLinks - Необязательный параметр. Определяет способ обновления связи в открываемом файле. Если данный аргумент отсутствует, то пользователю будет выдан запрос на определение способа обновления связей. Данный параметр имеет следующие допустимые значения:
0 – никакие связи не обновляются;
1 – обновляются внешние ссылки, но не обновляются удаленные ссылки;
2 – обновляются удаленные ссылки, но не обновляются внешние ссылки;
3 – обновляются оба типа ссылок

ReadOnly - необязательный параметр. Если файл имеет атрибут только для чтения, то при открытии его в Excel выдается соответствующее предупреждение*. Чтобы его игнорировать, передайте в качестве данного параметра True.

Format - необязательный параметр. При открытии текстового файла этот параметр определяет вид символов-разделителей. Ниже представлены допустимые значения данного аргумента:
1 – символы табуляции;
2 – запятые;
3 – пробелы;
4 – точка с запятой;
5 – разделители отсутствуют;
6 – вид разделителя определяется пользователем (с помощью параметра Delimiter).

Password - необязательный параметр, задающий строку пароля, необходимого для открытия защищенной рабочей книги. Если при открытии защищенной книги данный аргумент отсутствует, то пользователю будет выдан запрос на ввод пароля*.

WriteResPassword - необязательный параметр. Предназначен для задания пароля, необходимого для записи в рабочую книгу. Если при открытии защищенной книги данный аргумент отсутствует, то пользователю будет выдан запрос на ввод пароля*.

IgnoreReadOnlyRecommended - необязательный параметр. В случае задания ему значения True этот аргумент позволяет устранить вывод сообщения с рекомендацией открытия книги только для чтения (в том случае, если данная рабочая книга была сохранена с параметром Read-Only Recommended).

Origin - необязательный параметр. При открытии текстового файла этот параметр указывает, где был создан этот файл, что необходимо для правильного распознавания страницы кодировки. Значениями данного аргумента может быть одна из констант: xlMacintosh, xlWindows или xlMSDOS**. Если данный аргумент отсутствует, то используются текущие параметры операционной системы.

Delimiter - необязательный параметр. Если открывается текстовый файл и параметр Format имеет значение, равное 6, то этот аргумент определяет символ-разделитель.

Editable - необязательный параметр. Если файл является файлом MS Excel 4.0, то этот параметр при задании ему значения True позволяет открыть надстройку как видимое окно. По умолчанию ему присвоено значение False.

Notify - необязательный параметр. Если файл не может быть открыт для записи, то при задании данному аргументу значения True файл будет добавлен в список уведомления. Если данный аргумент равен False или отсутствует, то открыть файл будет невозможно.

Converter - необязательный параметр. Предназначен для определения индекса фильтра, который будет использован при открытии данного файла. Если фильтр не сможет распознать формат файла, то будут последовательно использованы все доступные фильтры.

AddToMRU - необязательный параметр типа Variant. Если данный параметр имеет значение True, то рабочая книга будет добавлена в список недавно использованных файлов. По умолчанию ему присвоено значение False.

Примечание:
* Сообщения будут выведены на экран, если вы не отключили их с помощью:
FExcel.Application.EnableEvents := false;
Показывать (True)/не показывать(False) системные сообщения Excel.

** Delphi не знает о существовании констант, поэтому чтобы посмотреть их значения, нужно воспользоваться средствами VBA. Эти константы соответственно равны 1, 2 и 3.

FExcel.Workbooks.Add(path); // path - путь к фалу
Создание новой книги. Причем создается стандартная книга с тремя листами. В этом методе можно указать стандартный тип шаблона Excel. Если же в нем указать имя (с полным путем) подготовленного файла (шаблоном может быть и "обычный" файл XLS, а не только файл XLT), то можно открыть книгу на диске как шаблон.

FExcel.Workbooks.Add(Template);
Template - Необязательный параметр. Этот параметр определяет, как будет создана рабочая книга. Если этот параметр будет иметь значение строки, определяющей имя файла (включая путь) существующей рабочей книги, то создается рабочая книга с использованием данного файла как шаблона. В случае когда данный параметр является одной из констант: xlWBATExcel4IntlMacroSheet = 4;
xlWBATExcel4MacroSheet = 3;
xlWBATWorksheet = $FFFFEFB9 (или -4167);
xlWBATChart = $FFFFEFF3 (или -4109);
Создаваемая книга будет содержать один лист соответствующего типа. В том случае, если этот параметр отсутствует, то создается рабочая книга с количеством листов, определяемым значением свойства SheetslnNewWorkbook.

function SaveAs(
  FileName: Variant; 
  FileFormat: Variant; 
  Password: Variant;
  WriteResPassword: Variant;
  ReadOnlyRecommended: Variant;
  CreateBackup: Variant;
  AccessMode: Variant;
  ConflictResolution: Variant;
  AddToMru: Variant;
  TextCodePage: Variant;
  TextVisualLayout: Variant
): Workbook;

FileFormat - необязательный параметр. Данный параметр задает формат файла. Его значением может быть одна из констант, описанных в таблице 1. В том случае, если файл уже существует, в качестве его формата по умолчанию будет назначен формат, использовавшийся при его предыдущем сохранении.

Формат                                            Константа
 
Книга MS Excel                                    xlWorkbookNormal
Web-страница                                      xlHTML
Шаблон                                            xlTemplate
Текстовые файлы (с разделителями табуляции)       xlTextWindows
Текст Юникод                                      xlUnicodeText
Книга MS Excel 5.0/95                             xlExcel7
Книга MS Excel 95/97/2000                         xlExcel9795
CSV (разделители – запятые)                       xlCSVWindows
Файл MS Excel 4.0                                 xlExcel4
Файл MS Excel 3.0                                 xlExcel3
Файл MS Excel 2.1                                 xlExcel2
Книга MS Excel 4.0                                xlExcel4Workbook
WK4 (1-2-3)                                       xlWK4
WK3, FM3 (1-2-3)                                  xlWK3FM3
WK3 (1-2-3)                                       xlWK3
WK1, FMT (1-2-3)                                  xlWK1FMT
WK1, ALL (1-2-3)                                  xlWK1ALL
WK1 (1-2-3)                                       xlWK1
WKS (1-2-3)                                       xlWKS
WQ1 (Quattro Pro/Dos)                             xlWQ1
DBF4 (dBase IV)                                   xlDBF2
DBF3 (dBase III)                                  xlDBF3
DBF2 (dBase II)                                   xlDBF4
Форматированный текст (разделители – пробелы)     xlTextPrinter
Текст (Macintosh)                                 xlTextMac
Текст (MS-DOS)                                    xlTextMSDOS
CSV (Macintosh)                                   xlCSVMac
CSV (MS-DOS)                                      xlCSVMSDOS
DIF (Data Interchange Format)                     xlDIF
SYLK (Symbolic Link)                              xlSYLK
Надстройка MS Excel                               xlAddln

Password - необязательный параметр. Данный параметр определяет защитный пароль для сохраняемого файла. Пароль определяется строкой, содержащей не более 15 символов.

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

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

CreateBackup - необязательный параметр. Определяет возможность создания резервного файла (значение True).

AccessMode - необязательный параметр. Этот параметр определяет режим доступа к рабочей книге и может иметь одно из следующих значений:
xINoChange – режим доступа не изменяется (равно 1);
xlShared – общие листы (равно 2);
xlExclusive – монопольный доступ (равно 3).

ConflictResolution - необязательный параметр. Определяет способ разрешения конфликтов в том случае, если книга имеет общие листы. Значением данного свойства может быть значение одной из констант:
xlUserResolution — отображает окно диалога для разрешения конфликтов (равно 1);
xlLocalSessionChanges — автоматически принимаются изменения локального пользователя (равно 2);
xlOtherSessionChanges — принимаются другие изменения вместо изменений локального пользователя (равно 3).

AddToMru - необязательный параметр. Позволяет поместить сохраняемый файл в список сохраненных файлов в меню Файл (File). Для реализации этой возможности значение этого аргумента должно равняться True.

TextCodePage - необязательный параметр. В локализованной версии MS Office не используется.

TextVisualLayout - необязательный параметр. В локализованной версии MS Office не используется.

FWorkbook.Close;
Закрыть книгу. При этом если Excel видимый, то появится диалог с вопросом, хотите ли вы сохранить файл.
Функция Close описана так:

function Close(
  SaveChanges: Variant; 
  FileName: Variant; 
  RouteWorkbook: Variant
): Workbook;

// Отключаем предупреждающие сообщения,
// в нашем случае чтобы не было вопроса о том,
// перезаписывать ли файл, если таковой существует
FExcel.DisplayAlerts := false;
// Сохраняем книгу в файл "123.xls" на диск D:
FWorkbook.Close(true, 'D:\123.xls');

FExcel.Workbooks[1].WorkSheets[1] .Name := 'Отчёт';
Присваиваем первому листу в первой книге имя «Отчет». Если будем работать с самим листом, то чтобы не писать много кода мы можем просто объявить новую переменную, например, FSheet типа Variant:

 procedure TForm1.Button1Click(Sender: TObject);
var
  FExcel, FSheet: Variant;
begin
  try
    ... // Тут ваш код
  finally
    ...
      // Обязательно! нужно все созданные Variant-переменные 
      // очищать перед выходом
      FSheet := Unassigned;
      FExcel := Unassigned;
    ...
  end;
end;

И присвоим ей ссылку на наш Лист1 первой книги:

FSheet := FExcel.Workbooks[1].WorkSheets[1];
FSheet.Name := 'Отчёт';

FSheet.PageSetup.Orientation := 2;
Ориентация страницы (1 – книжная, 2 – альбомная).

FSheet.PageSetup.LeftMargin := X;
Отступ от левого края страницы

FSheet.PageSetup.RightMargin := X;
Отступ от правого края страницы

FSheet.PageSetup.TopMargin := X;
Отступ от верхнего края страницы.

FSheet.PageSetup.BottomMargin := X;
Отступ от нижнего края страницы.

FSheet.PageSetup.HeaderMargin := X;
Высота верхнего колонтитула страницы.

FSheet.PageSetup.FooterMargin := X;
Высота нижнего колонтитула страницы.
Где X - это отступ в пикселях. Можно в принципе воспользоваться функцией перевода из, например, сантиметров в пиксели. Делается это так:
FSheet.PageSetup.BottomMargin := FExcel.Application.CentimetersToPoints(1.5);
Это будет полтора сантиметра, а как там переводится в пиксели это не наша забота.

FSheet.PageSetup.Zoom := false (или значение в процентах Z);
Масштаб. Принимает следующие значения:
Z - «Установить Z% от натуральной величины», где X – это количество процентов. Т.е. если вы решите включить масштабирование, то вам необходимо вместо FALSE подставить число, например: FSheet.PageSetup.Zoom := 100; (что означает 100%).
FALSE - «Разместить не более чем на: X стр. в ширину и Y стр. в высоту, где:
FSheet.PageSetup.FitToPagesWide := 1;
Количество страниц в ширину (X).
FSheet.PageSetup.FitToPagesTall := 100;
Количество страниц в высоту (Y).

FSheet.PageSetup.CenterFooter := 'Стр. &С из &К';
Центральный нижний колонтитул. В нем будет информации о номере страницы из всего страниц. Данные переменные (&C, &K и т.п.) описаны в справочнике самого Excel. Аналогично:
FSheet.PageSetup.LeftFooter:='Левый нижний колонтитул';
FSheet.PageSetup.RightFooter:='Правый нижний колонтитул';
FSheet.PageSetup.LeftHeader:='Левый верхний колонтитул';
FSheet.PageSetup.CenterHeader:='Центральный верхний колонтитул';
FSheet.PageSetup.RightHeader:='&7Правый верхний колонтитул';*
Примечание: *для изменения размера шрифта добавьте к колонтитулу управляющий символ "&" и размер шрифта, в нашем случае 7

// const xlAutomatic = $FFFFEFF7 (или -4105)
FSheet.PageSetup.FirstPageNumber := xlAutomatic;
Номер первой страницы. В данном случае «Авто».

FSheet.PageSetup.Order := 1;
Последовательность вывода страниц на печать (1 - вниз, затем вправо; 2 - вправо, затем вниз).

FSheet.PrintPreview;
Предварительный просмотр страницы. Если вы не продолжите печать, а нажмете "Отмена", то будет вызвано исключение.
Примечание: Excel, перед выполнением этой команды, обязательно должен быть видимым (FExcel.Visible := true;).

FSheet.PrintOut;
Вывод страницы на печать. Функция PrintOut описана так:

function PrintOut(
  From: Variant; 
  To: Variant; 
  Copies: Variant;
  Preview: Variant;
  ActivePrinter: Variant;
  PrintToFile: Variant;
  Collate: Variant
): Workbook;

To - необязательный параметр. Задает последнюю печатаемую страницу. Если этот аргумент отсутствует, то печать продолжается до последней страницы.

Copies - необязательный параметр. Задает количество печатаемых копий.

Preview - необязательный параметр, принимающий одно из двух значений:
TRUE - приводит к выводу перед печатью окна предварительного просмотра;
FALSE (значение по умолчанию) - печать производится без предварительного просмотра.

ActivePrinter - необязательный параметр. Задает имя активного принтера.

PrintToFile - необязательный параметр. Если данный параметр имеет значение True, то производится печать в файл. При этом пользователю выдается запрос на ввод имени файла.

Collate - необязательный параметр. Если ему задать значение True, то копии будут объединены.
Пример:

FExcel.Application.EnableEvents := true;
FExcel.DisplayAlerts := true;
FExcel.Visible := true;
FSheet.PrintOut(1, 2, 1, False, PrintToFile := True)

При составлении справочника я использовал собственные наработки и информацию со следующих сайтов:
1) ]]>www.taurion.ru/excel]]>
2) ]]>www.superadm.net/index.php?name=pages&op=cat&id=19]]>
3) ]]>www.afalinasoft.com/rus/tips/]]>
4) ]]>www.codenet.ru/progr/delphi/stat/Excel-Export.php]]>
Так же не обошлось без составления макросов в редакторе Visual Basic и последующим переводом кода из VBA в среду разработки Delphi 2010.