Создаем свой генератор отчетов Microsoft Excel для Delphi

Зачем нужна система формирования отчетов

В свое время, когда для системы учета потребовалось сделать систему вывода информации на печать перебрал много разных систем формирования отчетов — некоторые, как FastReport, имели в составе редакторы, и замечательно формировали печатные формы, но чаще всего пользователям нравится, когда можно быстро и красиво получить информацию в ихнем любимом Excel, не прибегая к конверторам и т.п.

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

Когда каждый отчет полностью формировался кодом на Delphi недостаток такого метода был в том, что за делфийским кодом не было видно логики построения отчета, а самое главное — для изменения любой мелочи в оформлении приходилось вносить изменения в код и перекомпилировать проект. Тогда возникла идея написать свой маленький генератор отчетов, причем шаблон отчета — это обычный экселевский файл, в котором любой пользователь сможет подправить то что ему нужно, и определить заранее формат вывода на печать.

Устройство собственного генератора отчетов для Excel

Основу шаблона составляют “бэнды”. Бэнд это одна или более строк, расположенных подряд и имеющих одну и ту-же метку — название бэнда. Оно проставляется в первой колонке каждой строки. Вот так примерно будет выглядеть готовый шаблон отчета:

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

Сам класс отчета будет очень простым:

 TA7xReport = class(TComponent)
  private
    Excel, TemplateSheet: Variant;
    Progress: TWcProgress;
    CurrentLine: integer; // текущая строка построения отчета
    FirstBandLine, LastBandLine: integer; // положение последнего добавленного бэнда
  protected
  public
    procedure OpenTemplate(FileName: string);
    procedure PasteBand(BandName: string);
    procedure SetValue(VarName: string; Value: Variant);
    procedure Show;
    destructor Destroy; override;
  published
  end;

Начинаться построение любого отчета будет вызовом процедуры OpenTemplate, которая будет запускать эксель, открывать в нем нужный шаблон, а также инициализировать нужные далее переменные:

  Excel := CreateOleObject('Excel.Application');
  Excel.Workbooks.Open(FileName, True, True);
  TemplateSheet := Excel.Workbooks[1].Sheets[1];
  Excel.DisplayAlerts := False; // Чтобы подавить сообщение об ошибке при замене ненайденного значения в операции SetValue
  CurrentLine := 1;
  Progress := TA7xProgress.Create(Self);
  Application.ProcessMessages;

Здесь упоминается объект TA7xProgress, который представляет собой просто форму, с парой меток, для вывода пользователю прогресса построения отчета, чтобы он не подумал что программа висит.

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

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

  FirstBandLine := 0; LastBandLine := 0;
  i := CurrentLine;
  while ((LastBandLine = 0) and (i < CurrentLine + MaxBandLines)) do begin
    v := Variant(TemplateSheet.Cells[i, 1].Value);
    if (varType(v) = varOleStr) and (FirstBandLine = 0) then begin
      if v = BandName then begin // нашли начало бенда
        FirstBandLine := i;
      end;
    end;
    if (FirstBandLine <> 0) then begin
      if not ((varType(v) = varOleStr) and (v = BandName)) then LastBandLine := i - 1;
    end;
    inc(i);
  end;

Далее этот найденный шаблон копипастим сразу за последним вставленным нами бэндом, если такой уже был:

  Range := TemplateSheet.Rows[IntToStr(FirstBandLine) + ':' + IntToStr(LastBandLine)];
  Range.Copy;
  Range := TemplateSheet.Rows[IntToStr(CurrentLine) + ':' + IntToStr(CurrentLine)];
  Range.Insert;

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

  CurrentLine := CurrentLine + (LastBandLine - FirstBandLine) + 1;
  // вычисляем позицию куда был скопирован бэнд
  FirstBandLine := CurrentLine - (LastBandLine - FirstBandLine) - 1;
  LastBandLine := CurrentLine - 1;

Следующий метод, который будет использоваться для вывода значений на уже скопированный кусок шаблона – SetValue. Код этого метода предельно прост, так как выполняет поиск-замену значения в заданном диапазоне средствами самого экселя:

  Range := TemplateSheet.Rows[IntToStr(FirstBandLine) + ':' + IntToStr(LastBandLine)];
  Range.Replace(VarName, s);

Метод SetValue должен вызываться столько раз, сколько у нас находится значений на нужном нам бэнде. Ну и наконец после того как все бэнды будут выведены, вызывается последнй метод – Show, в котором наконец удаляется сам шаблон, который вытеснялся в конец документа, и эксель перевдится из невидимого состояния в видимое.

Здесь стоит упомянуть, что если скажем прервать отладку отчета до вызова Excel.Visible := true; то эксель так и останется висеть в памяти невидимкой, а для следующего отчета будет создана новый экземпляр Excel.

Как выглядит применение генератора отчетов в Delphi-коде

Теперь самое интересное – пример использования этого генератора отчетов:

Привожу полностью процедуру, которая используя показанный выше шаблон выводит в эксель заданного формата накладную:

procedure TdrashodForm.Print(Template: string);
var i: integer;
  summa_: double;
  h : string;
begin
  Rep.OpenTemplate(Template);
  Rep.PasteBand('TITLE');
  Rep.SetValue('#ID_RASHOD#', ID_RASHOD);
  Rep.SetValue('#PB_NAME#', PbFrame.PbEdit.Text);
  Rep.SetValue('#D#', DateToStr(NaklDTP.Date));
  Rep.SetValue('#POSTNAME#', PostEdit.Text);
  i := 1; summa_ := 0;
  NaklQuery.First;
  while not NaklQuery.Eof do begin
      Rep.PasteBand('STR');
      Rep.SetValue('#N#', i);
      h := NaklQuery['KH_NAME'];
      if h<>'' then h := '['+h+']';
      Rep.SetValue('#NM_NAME#', NaklQuery['NM_NAME']+' '+h);
      Rep.SetValue('#QUANT#', NaklQuery['RSD_QUANT']);
      Rep.SetValue('#SUMMA#', coalesce(NaklQuery['RSD_OSUMMA'],0));
      Rep.SetValue('#NM_UNIT#', coalesce(NaklQuery['NM_UNIT'],''));
      Rep.SetValue('#ZK_NUMBER#', coalesce(NaklQuery['ZK_NUMBER'],''));
      Rep.SetValue('#TW_NAME#', coalesce(NaklQuery['TW_NAME'],''));
      summa_ := summa_ + coalesce(NaklQuery['RSD_OSUMMA'],0);
      inc(i);
    NaklQuery.Next;
  end;
  Rep.PasteBand('FOOT');
  Rep.SetValue('#SUMMA_#', summa_);
  Rep.Show;
end;

Как видно, логика формирования печатной формы полностью прозрачна и не отягощена техническим кодом вывода значений в нужные ячейки Microsoft Excel – обо всем этом заботится наш компонент отчета, а кроме того в отличии от многих генераторов отчетов, у нас имеется вся мощь Delphi для расчета и получения нужных нам значений в разных частях процедуры построения отчета.

И теперь самое главное…

Где взять эту замечательную систему да еще и бесплатно

Домашняя страница проекта, находится на a7in.com