Створюємо власний генератор звітів Microsoft Excel для Delphi

Навіщо потрібна система формування звітів

Свого часу, коли для облікової системи знадобилося виводити інформацію на друк, я перебрав багато різних систем формування звітів. Деякі, як-от FastReport, мали вбудовані редактори й чудово формували друковані форми, але найчастіше користувачам подобається швидко й гарно отримувати інформацію у своєму улюбленому Excel, без конвертерів тощо.

В інтернеті вже давно є безліч матеріалів про взаємодію Delphi та Microsoft Excel, тож я не писатиму ще один посібник. Натомість пропоную подивитися, як досить просто розробити власну систему виведення звітів в Excel, достатню для розв’язання якщо не всіх, то більшості завдань.

 

Коли кожен звіт повністю формувався кодом Delphi, недоліком такого підходу було те, що за кодом Delphi не було видно логіки побудови звіту. А головне — щоб змінити будь-яку дрібницю в оформленні, доводилося вносити зміни в код і перекомпілювати проєкт. Тоді виникла ідея написати власний невеликий генератор звітів: шаблоном звіту буде звичайний файл Excel, у якому будь-який користувач зможе змінити потрібне й заздалегідь визначити формат виведення на друк.

Як влаштований власний генератор звітів для 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, відкриває потрібний шаблон і ініціалізує змінні, які знадобляться далі:

  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. Код цього методу дуже простий, адже пошук і заміну значення в заданому діапазоні виконує сам Excel:

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

Метод SetValue потрібно викликати стільки разів, скільки значень є в потрібному бенді. Нарешті, коли всі бенди виведено, викликаємо останній метод — Show. У ньому видаляється сам шаблон, який переміщували в кінець документа, а Excel переходить із невидимого стану у видимий.

Варто згадати: якщо, наприклад, перервати налагодження звіту до виклику Excel.Visible := true;, Excel так і залишиться невидимим процесом у пам’яті, а для наступного звіту буде створено новий екземпляр Excel.

Як використовувати генератор звітів у коді Delphi

А тепер найцікавіше — приклад використання цього генератора звітів:

Наводжу повну процедуру, яка за допомогою показаного вище шаблону виводить в Excel накладну заданого формату:

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